private void ReadHyperLinks_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
Xls = new XlsFile();
Xls.Open(openFileDialog1.FileName);
dataGrid.CaptionText = "Hyperlinks on file: " + openFileDialog1.FileName;
HlDataTable.Rows.Clear();
for (int i = 1; i <= Xls.HyperLinkCount; i++)
{
TXlsCellRange Range = Xls.GetHyperLinkCellRange(i);
THyperLink HLink = Xls.GetHyperLink(i);
string HLinkType = Enum.GetName(typeof(THyperLinkType), HLink.LinkType);
object[] values = { i, TCellAddress.EncodeColumn(Range.Left) + Range.Top.ToString(),
TCellAddress.EncodeColumn(Range.Right) + Range.Bottom.ToString(),
HLinkType,
HLink.Text,
HLink.Description,
HLink.TextMark,
HLink.TargetFrame,
HLink.Hint };
HlDataTable.Rows.Add(values);
}
}
private static string Get1Ref(bool R1C1, int Row, int Col, int CellRow, int CellCol, bool RowAbs, bool ColAbs)
{
if (Row < 1 || Col < 1 || Row > FlxConsts.Max_Rows + 1 || Col > FlxConsts.Max_Columns + 1)
{
return(TFormulaMessages.ErrString(TFlxFormulaErrorValue.ErrRef));
}
if (R1C1)
{
return(Get1R1C1Ref(Row, Col, CellRow, CellCol, RowAbs, ColAbs));
}
string Result = ""; //We won't use a string builder here as it would probably have more overhead.
if (ColAbs)
{
Result = fts(TFormulaToken.fmAbsoluteRef);
}
Result = Result + TCellAddress.EncodeColumn(Col);
if (RowAbs)
{
Result = Result + fts(TFormulaToken.fmAbsoluteRef);
}
return(Result + Row.ToString());
}
public static void Search(ExcelFile xls, TXlsCellRange Range, TCellAddress Start, bool ByRows, TSearchOrReplace Action)
{
int StartRow = 1;
int EndRow = 0;
int StartCol = 1;
int EndCol = 0;
if (Range != null)
{
StartRow = Range.Top;
EndRow = Range.Bottom;
StartCol = Range.Left;
EndCol = Range.Right;
}
else
{
EndRow = xls.RowCount;
EndCol = xls.ColCount;
}
int FirstStartCol = StartCol;
int FirstStartRow = StartRow;
if (Start != null)
{
FirstStartRow = Start.Row;
if (ByRows)
{
FirstStartRow++;
}
FirstStartCol = Start.Col;
if (!ByRows)
{
FirstStartCol++;
}
if (FirstStartRow > EndRow)
{
FirstStartRow = StartRow;
FirstStartCol++;
}
if (FirstStartCol > EndCol)
{
FirstStartCol = StartCol;
FirstStartRow++;
}
}
if (ByRows)
{
SearchByRows(xls, FirstStartRow, StartRow, EndRow, FirstStartCol, EndCol, Action);
}
else
{
SearchByCols(xls, FirstStartRow, EndRow, FirstStartCol, StartCol, EndCol, Action);
}
}
private void ImportFile(string FileName)
{
try
{
XlsFile xls = new XlsFile();
xls.VirtualMode = true; //Remember to turn virtual mode on, or the event won't be called.
//By default, FlexCel returns the formula text for the formulas, besides its calculated value.
//If you are not interested in formula texts, you can gain a little performance by ignoring it.
//This also works in non virtual mode.
xls.IgnoreFormulaText = cbIgnoreFormulaText.Checked;
CellData = new SparseCellArray();
//Attach the CellReader handler.
CellReader cr = new CellReader(cbFirst50Rows.Checked, CellData, cbFormatValues.Checked);
xls.VirtualCellStartReading += new VirtualCellStartReadingEventHandler(cr.OnStartReading);
xls.VirtualCellRead += new VirtualCellReadEventHandler(cr.OnCellRead);
DateTime StartOpen = DateTime.Now;
//Open the file. As we have a CellReader attached, the cells won't be loaded into memory, they will be passed to the CellReader
xls.Open(FileName);
DateTime StartSheetSelect = cr.StartSheetSelect;
DateTime EndSheetSelect = cr.EndSheetSelect;
DateTime EndOpen = DateTime.Now;
statusBar.Text = "Time to open file: " + (StartSheetSelect - StartOpen).ToString() + " Time to load file and fill grid: " + (EndOpen - EndSheetSelect).ToString();
//Set up grid.
GridCaption.Text = FileName;
if (CellData != null)
{
DisplayGrid.ColumnCount = CellData.ColCount;
DisplayGrid.RowCount = CellData.RowCount;
}
else
{
DisplayGrid.ColumnCount = 0;
DisplayGrid.RowCount = 0;
}
for (int i = 0; i < DisplayGrid.ColumnCount; i++)
{
DisplayGrid.Columns[i].Name = TCellAddress.EncodeColumn(i + 1);
}
}
catch
{
GridCaption.Text = "Error Loading File";
CellData = null;
throw;
}
}
/// <summary>
/// Добавляем диапазон в формулу
/// </summary>
/// <param name="formulaKey">Ключ формулы</param>
/// <param name="row">Строка</param>
/// <param name="addHalfhour">Добавляем колонки получасовок</param>
/// <param name="addDrum">Добавляем колонки барабанов</param>
/// <param name="isMinus">Минусовать</param>
/// <param name="footerName">Название подитога (если нужно), в который добавляем данные</param>
public void AddToFormula(string formulaKey, int row, bool addHalfhour = true, bool addDrum = true,
bool isMinus = false)
{
//Добавляем барабаны для подитога
if (!string.IsNullOrEmpty(formulaKey))
{
if (addDrum)
{
AddRowRangeToFormulaSum(formulaKey + "_drum", new FormulaRowsRange
{
Col = _colDrum,
Row1 = row,
Row2 = row,
IsMinus = isMinus,
}); //Для итого по перетоку
}
//Добавляем получасовки для подитога
if (addHalfhour)
{
for (var i = 0; i < _halfhourCount; i++)
{
AddRowRangeToFormulaSum(formulaKey + "_halfhour" + i, new FormulaRowsRange
{
Col = _colHalfHour + i,
Row1 = row,
Row2 = row,
IsMinus = isMinus,
});
}
}
}
if (_isAnalisIntegral && addHalfhour && addDrum)
{
//Делаем сравнительный анализ барабанов и получасовок
var formulaHalfhour = TCellAddress.EncodeColumn(_colHalfHour) + row;
var formulaStr = formulaHalfhour + " - " +
TCellAddress.EncodeColumn(_colDrum) + row
+ _halfhourToIntegralUnitCoeff;
_xls.SetCellValue(row, _colAnalis,
new TFormula("=" + formulaStr), _xls.ProfileFormat);
_xls.SetCellValue(row, _colAnalis + 1,
new TFormula("=(" + formulaStr + ")/" + formulaHalfhour), _xls.ProfileFormat);
}
}
internal void WriteFormulaToCell(XlsFile xls, string id, int row, int col, int format, double defaultValue)
{
if (_isFormingFormulasToCell)
{
var formula = new StringBuilder("=");
#region Пишем формулу из словаря
List <FormulaRowsRange> existsRanges;
if (_formulas.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
{
foreach (var formulasRange in existsRanges)
{
formula.Append(!string.IsNullOrEmpty(formulasRange.Before) ? formulasRange.Before : "+");
if (!string.IsNullOrEmpty(formulasRange.SheetName))
{
formula.Append("'").Append(formulasRange.SheetName).Append("'!");
}
formula.Append(TCellAddress.EncodeColumn(formulasRange.Col)).Append(formulasRange.Row1);
}
//Обрабатываем последнюю запись
try
{
xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
}
catch (Exception ex)
{
if (_errors != null)
{
_errors.Append("Ошибка формирования формулы в '" + TCellAddress.EncodeColumn(col) + row + "': " + ex.Message + "\n" + formula);
}
xls.SetCellValue(row, col, defaultValue, format);
}
_formulas.Remove(id);
return;
}
#endregion
}
//Нет формулы, или не надо ее формировать
xls.SetCellValue(row, col, defaultValue, format);
}
internal void ChangeFmla(ExcelFile Workbook, TEscherClientDataRecord Cd, TCellAddress LinkedCellAddress, TParsedTokenList LinkedCellFmla, bool ReadingXlsx)
{
//The line below would interfere with xlsx loading since it would build the tree and maybe remove links
//before group boxes have been created.
if (!ReadingXlsx)
{
BuildTree(Workbook); //This avoids that 2 calls to different objects could have no secuential effects.
}
TRadioGroupCache rgc;
if (!RadioButtonsById.TryGetValue(Cd.ObjId, out rgc))
{
//It is not in the built part.
(Cd.ClientData as TMsObj).SetObjFormulaLink(Workbook, LinkedCellAddress, LinkedCellFmla);
return;
}
(rgc.Buttons[0].ClientData as TMsObj).SetObjFormulaLink(Workbook, LinkedCellAddress, LinkedCellFmla);
}
internal void FixLinks(ExcelFile Workbook, int ActiveSheet)
{
BuildTree(Workbook);
for (int i = 0; i < Tree.Count; i++)
{
if (Tree[i].Buttons.Count > 0)
{
TMsObj ms = Tree[i].Buttons[0].ClientData as TMsObj;
if (ms != null)
{
TCellAddress LinkedCell = ms.GetObjectLink(Workbook);
if (LinkedCell != null)
{
CheckRadioButtons(Workbook, ActiveSheet, i, LinkedCell);
}
}
}
}
}
private static string GetColRange(bool R1C1, int Col1, int Col2, int CellCol, bool Abs1, bool Abs2)
{
if (R1C1)
{
return(GetFullR1C1Range(TFormulaToken.fmR1C1_C, Col1, Col2, CellCol, Abs1, Abs2));
}
string Result = "";
if (Abs1)
{
Result = fts(TFormulaToken.fmAbsoluteRef);
}
Result = Result + TCellAddress.EncodeColumn(Col1) + fts(TFormulaToken.fmRangeSep);
if (Abs2)
{
Result = Result + fts(TFormulaToken.fmAbsoluteRef);
}
return(Result + TCellAddress.EncodeColumn(Col2));
}
private void CheckRadioButtons(ExcelFile Workbook, int ActiveSheet, int i, TCellAddress LinkedCell)
{
int Sheet = ActiveSheet + 1;
if (!string.IsNullOrEmpty(LinkedCell.Sheet))
{
Sheet = Workbook.GetSheetIndex(LinkedCell.Sheet, false);
}
if (Sheet > 0)
{
int XF = 1;
object r = Workbook.GetCellValue(Sheet, LinkedCell.Row, LinkedCell.Col, ref XF);
if (r == null)
{
CheckGroup(i, 0); return;
}
if (r is TFlxFormulaErrorValue)
{
if ((TFlxFormulaErrorValue)r == TFlxFormulaErrorValue.ErrNA)
{
CheckGroup(i, 0); return;
}
//if it isn't n/a, its value doesn't matter. rb stays as is.
}
else
{
double pd;
if (TBaseParsedToken.ExtToDouble(r, out pd)) //something like a string doesn't matter
{
if (pd <= 0 || pd >= int.MaxValue)
{
CheckGroup(i, 0); return;
} // in this case it does matter, all cbs are unselected.
int p = (int)pd;
CheckGroup(i, p);
}
}
}
}
private void AddChart(TXlsNamedRange DataCell, ExcelFile Xls)
{
//Find the cell where the cart will go.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
//Insert cells to expand the range for the chart. It already has 2 rows, so we need to insert Country.Length - 2
//Note also that we insert after ChartRange.Top, so the chart is updates with the new range.
Xls.InsertAndCopyRange(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top, ChartRange.Left + 1),
ChartRange.Top + 1, ChartRange.Left, Country.Length - 2, TFlxInsertMode.ShiftRangeDown); //we use shiftrangedown so not all the row goes down and the chart stays in place.
//Get the cell addresses of the data range.
TCellAddress FirstCell = new TCellAddress(DataCell.Top, DataCell.Left);
TCellAddress SecondCell = new TCellAddress(DataCell.Top + DataRows, DataCell.Left + 1);
TCellAddress FirstSumCell = new TCellAddress(DataCell.Top, DataCell.Left + 1);
//Fill a table with the data to be used in the chart.
for (int r = ChartRange.Top; r < ChartRange.Top + Country.Length; r++)
{
Xls.SetCellValue(r, ChartRange.Left, Country[r - ChartRange.Top]);
Xls.SetCellValue(r, ChartRange.Left + 1, new TFormula("=SUMIF(" + FirstCell.CellRef + ":" + SecondCell.CellRef +
",\"" + Country[r - ChartRange.Top] + "\", " + FirstSumCell.CellRef + ":" + SecondCell.CellRef + ")"));
}
}
internal override void Clear()
{
Cell = null;
}
internal override bool OnFound(ExcelFile xls, object oldval, int row, int col)
{
Cell = new TCellAddress(row, col);
return(true);
}
internal override bool Go(ExcelFile xls, object value, int row, int col)
{
Cell = null;
return(base.Go(xls, value, row, col));
}
private static string GetR1SimpleRef(int RowCol, int CellRowCol, bool Abs)
{
return(TCellAddress.GetR1SimpleRef(RowCol, CellRowCol, Abs));
}
internal static string Get1R1C1Ref(int Row, int Col, int CellRow, int CellCol, bool RowAbs, bool ColAbs)
{
return(TCellAddress.GetR1C1Ref(Row, Col, CellRow, CellCol, RowAbs, ColAbs));
}
public static string EncodeColumn(int C)
{
return(TCellAddress.EncodeColumn(C));
}
protected void CalcBounds(TOneCellValue aRange)
{
TValueAndXF val = new TValueAndXF();
val.Workbook = aRange.Workbook;
aRange.Evaluate(0, 0, 0, 0, val);
TXlsNamedRange XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, val.Workbook.ActiveSheet);
if (XlsRange == null)
{
XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, 0);
}
if (XlsRange != null)
{
FTop = XlsRange.Top;
FLeft = XlsRange.Left;
Bottom = XlsRange.Bottom;
Right = XlsRange.Right;
Sheet1 = XlsRange.SheetIndex;
Sheet2 = XlsRange.SheetIndex;
RowAbs1 = false; ColAbs1 = false; RowAbs2 = false; ColAbs2 = false;
return;
}
string[] Addresses = FlxConvert.ToString(val.Value).Split(TFormulaMessages.TokenChar(TFormulaToken.fmRangeSep));
if (Addresses == null || (Addresses.Length != 2 && Addresses.Length != 1))
{
FlxMessages.ThrowException(FlxErr.ErrInvalidRef, FlxConvert.ToString(val.Value));
}
TCellAddress FirstCell = new TCellAddress(Addresses[0]);
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet1 = -1;
}
else
{
Sheet1 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
FTop = FirstCell.Row;
FLeft = FirstCell.Col;
RowAbs1 = FirstCell.RowAbsolute;
ColAbs1 = FirstCell.ColAbsolute;
if (Addresses.Length > 1)
{
FirstCell = new TCellAddress(Addresses[1]);
}
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet2 = Sheet1;
}
else
{
Sheet2 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
Bottom = FirstCell.Row;
Right = FirstCell.Col;
RowAbs2 = FirstCell.RowAbsolute;
ColAbs2 = FirstCell.ColAbsolute;
}
/// <summary>
/// Пишем формулу в excel
/// </summary>
/// <param name="id">Идентификатор</param>
/// <param name="row">Ячейка</param>
/// <param name="col">Колонка</param>
/// <param name="format">Формат</param>
/// <param name="defaultValue">Значение по умолчанию, если не excel, или не найдена формула</param>
internal void WriteRowRangeFormulaToCell(string id, int row, int col, int format, double defaultValue, FormulaRowsRange rowsRange = null)
{
if (_isFormingFormulasToCell)
{
var formula = new StringBuilder("=SUM(");
#region Формирование формулы
var formingFormulaAction = (Action <string, int, int, int, int?, bool>)((sheetName, row1, row2, col1, col2, isMinus) =>
{
var sheet = isMinus ? "-" : string.Empty;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = "'" + sheetName + "'!";
}
if (row1 == row2 && !col2.HasValue)
{
formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
.Append(",");
}
else
{
formula.Append(sheet + TCellAddress.EncodeColumn(col1)).Append(row1)
.Append(":")
.Append(sheet + TCellAddress.EncodeColumn(col2 ?? col1)).Append(row2)
.Append(",");
}
});
#endregion
#region Если пишем формулу не из словаря
if (rowsRange != null)
{
formingFormulaAction(rowsRange.SheetName, rowsRange.Row1, rowsRange.Row2, rowsRange.Col, rowsRange.Col2, rowsRange.IsMinus);
formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний ;
_xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
return;
}
#endregion
#region Пишем формулу из словаря
List <FormulaRowsRange> existsRanges;
if (_formulas.TryGetValue(id, out existsRanges) && existsRanges.Count > 0)
{
var prevSheetName = string.Empty;
var prevCol = -1;
var prevRow1 = -1;
var prevRow2 = -1;
bool isMinus = false;
foreach (var formulasRange in existsRanges.OrderBy(r => r.SheetName)
.ThenBy(r => r.Row1).ThenBy(r => r.Row2))
{
//Обрабатываем предыдущую запись
if (Equals(prevSheetName, (formulasRange.SheetName ?? string.Empty)) &&
prevCol == formulasRange.Col && prevRow2 >= formulasRange.Row1 - 1)
{
//Пересекаются диапазоны, присоединяем диапазон к предыдущему
prevRow2 = formulasRange.Row2;
}
else
{
//Пишем предыдущую запись
if (prevCol > 0)
{
formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null, isMinus);
}
//Сохраняем текущую
prevSheetName = formulasRange.SheetName ?? string.Empty;
prevCol = formulasRange.Col;
prevRow1 = formulasRange.Row1;
prevRow2 = formulasRange.Row2;
}
isMinus = formulasRange.IsMinus;
}
//Обрабатываем последнюю запись
if (prevCol > 0)
{
formingFormulaAction(prevSheetName, prevRow1, prevRow2, prevCol, null, isMinus);
}
formula.Replace(",", ")", formula.Length - 1, 1); //Удаляем последний
if (col <= 16384)
{
_xls.SetCellValue(row, col, new TFormula(formula.ToString()), format);
}
_formulas.Remove(id);
return;
}
#endregion
}
//Нет формулы, или не надо ее формировать
_xls.SetCellValue(row, col, defaultValue, format);
}
private void CompareXls(XlsFile xls1, XlsFile xls2, DataTable table)
{
int DiffCount = 0;
xls1.Recalc();
for (int sheet = 1; sheet <= xls1.SheetCount; sheet++)
{
xls1.ActiveSheet = sheet;
xls2.ActiveSheet = sheet;
int aColCount = xls1.ColCount;
for (int r = 1; r <= xls1.RowCount; r++)
{
for (int c = 1; c <= aColCount; c++)
{
TFormula f = xls1.GetCellValue(r, c) as TFormula;
if (f != null)
{
TCellAddress ad = new TCellAddress(r, c);
TFormula f2 = (TFormula)xls2.GetCellValue(r, c);
if (f.Result == null)
{
f.Result = "";
}
if (f2.Result == null)
{
f2.Result = "";
}
double eps = 0;
if (f.Result is Double && f2.Result is Double)
{
if ((Double)f2.Result == 0)
{
if (Math.Abs((double)f.Result) < Double.Epsilon)
{
eps = 0;
}
else
{
eps = Double.NaN;
}
}
else
{
eps = (double)f.Result / (Double)f2.Result;
}
if (Math.Abs(eps - 1) < 0.001)
{
f.Result = f2.Result;
}
}
if (!f.Result.Equals(f2.Result))
{
if (table == null)
{
report.Text += "\nSheet:" + xls1.SheetName + " --- Cell:" + ad.CellRef + " --- Calculated: " + f.Result.ToString() + " Excel: " + f2.Result.ToString() + " dif: " + eps.ToString() + " formula: " + f.Text;
Application.DoEvents();
}
else
{
table.Rows.Add(new object[] { xls1.SheetName, ad.CellRef, f.Result.ToString(), f2.Result.ToString(), eps.ToString(), f.Text });
}
DiffCount++;
}
}
}
}
}
if (table == null)
{
report.Text += "\nFinished Comparing.";
if (DiffCount == 0)
{
report.Text += "\n**********No differences found!**********";
}
else
{
report.Text += String.Format("\n --->Found {0} differences", DiffCount);
}
}
}
private void ImportFile(string FileName, bool Formatted)
{
try
{
//Open the Excel file.
XlsFile xls = new XlsFile(false);
DateTime StartOpen = DateTime.Now;
xls.Open(FileName);
DateTime EndOpen = DateTime.Now;
//Set up the Grid
DisplayGrid.DataBindings.Clear();
DisplayGrid.DataSource = null;
DisplayGrid.DataMember = null;
DataSet dataSet1 = new DataSet();
sheetCombo.Items.Clear();
//We will create a DataTable "SheetN" for each sheet on the Excel sheet.
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
sheetCombo.Items.Add(xls.SheetName);
DataTable Data = dataSet1.Tables.Add("Sheet" + sheet.ToString());
Data.BeginLoadData();
try
{
int ColCount = xls.ColCount;
//Add one column on the dataset for each used column on Excel.
for (int c = 1; c <= ColCount; c++)
{
Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String)); //Here we will add all strings, since we do not know what we are waiting for.
}
string[] dr = new string[ColCount];
int RowCount = xls.RowCount;
for (int r = 1; r <= RowCount; r++)
{
Array.Clear(dr, 0, dr.Length);
//This loop will only loop on used cells. It is more efficient than looping on all the columns.
for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once.
{
int Col = xls.ColFromIndex(r, cIndex);
if (Formatted)
{
TRichString rs = xls.GetStringFromCell(r, Col);
dr[Col - 1] = rs.Value;
}
else
{
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
TFormula Fmla = val as TFormula;
if (Fmla != null)
{
//When we have formulas, we want to write the formula result.
//If we wanted the formula text, we would not need this part.
dr[Col - 1] = Convert.ToString(Fmla.Result);
}
else
{
dr[Col - 1] = Convert.ToString(val);
}
}
}
Data.Rows.Add(dr);
}
}
finally
{
Data.EndLoadData();
}
DateTime EndFill = DateTime.Now;
statusBar.Text = String.Format("Time to load file: {0} Time to fill dataset: {1} Total time: {2}", (EndOpen - StartOpen).ToString(), (EndFill - EndOpen).ToString(), (EndFill - StartOpen).ToString());
}
//Set up grid.
DisplayGrid.DataSource = dataSet1;
DisplayGrid.DataMember = "Sheet1";
sheetCombo.SelectedIndex = 0;
DisplayGrid.CaptionText = FileName;
}
catch
{
DisplayGrid.CaptionText = "Error Loading File";
DisplayGrid.DataSource = null;
DisplayGrid.DataMember = "";
sheetCombo.Items.Clear();
throw;
}
}
/// <summary>
/// Обсчитываем все что выделено между {}
/// </summary>
public static void Evaluate(XlsFile xls, ISpreadsheetProperties properties, out int row, StringBuilder errors)
{
row = 1;
var rowCount = Math.Min(xls.RowCount, 100);
for (var r = 1; r <= rowCount; r++)
{
var isNotExistVoidCol = false;
for (var c = 1; c <= xls.ColCount; c++)
{
var cVal = xls.GetCellValue(r, c);
if (!isNotExistVoidCol && cVal != null)
{
isNotExistVoidCol = true;
}
var str = cVal as string;
if (!string.IsNullOrEmpty(str))
{
var indxStart = str.IndexOf('{');
if (indxStart >= 0)
{
var indxEnd = str.IndexOf('}');
if (indxEnd > 0)
{
try
{
var subStr = str.Substring(indxStart, indxEnd - indxStart + 1);
var ev = ProryvParsersFactory.ParseTextValue(subStr, properties);
if (str[0] == '=' && ev != null)
{
//Это формула
xls.SetCellValue(r, c, new TFormula(ev.ToString().Replace(',', '.')));
}
else
{
xls.SetCellValue(r, c, str.Replace(subStr, ev.ToString()));
}
}
catch (Exception ex)
{
if (errors != null)
{
errors.Append(" Ошибка в ячейке ").Append(TCellAddress.EncodeColumn(c))
.Append(r).Append(": ").Append(ex.Message).Append(" ")
.Append(
ex.InnerException != null ? ex.InnerException.Message : string.Empty)
.Append("\n");
}
}
}
}
}
}
//Есть хоть одна заполненная ячейка, смещаем начальную ячейку
if (isNotExistVoidCol)
{
row = r;
}
}
row++;
}
/// <summary>
/// Creates a new Cell address token.
/// </summary>
/// <param name="aAddress">Cell where this token will point to.</param>
public TTokenCellAddress(TCellAddress aAddress) : base(TTokenType.CellAddress)
{
Address = aAddress;
}
public static DataSet ToDataSet(byte[] data, bool firstRowIsPropertyName = true)
{
bool formatted = false;
DataSet dataSet = new DataSet();
Stream stream = new MemoryStream(data);
XlsFile xls = new XlsFile(false);
xls.Open(stream);
for (int sheet = 1; sheet <= xls.SheetCount; sheet++)
{
xls.ActiveSheet = sheet;
DataTable Data = dataSet.Tables.Add("Sheet" + sheet.ToString());
Data.BeginLoadData();
try
{
int ColCount = xls.ColCount;
int beginIndexRow = 1;
if (firstRowIsPropertyName)
{
beginIndexRow = 2;
for (int c = 1; c <= ColCount; c++)
{
int r = 1;
int XF = 0; // This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, c, ref XF);
string propName = string.Empty;
TFormula Fmla = val as TFormula;
propName = Convert.ToString(Fmla != null ? Fmla.Result : val);
Data.Columns.Add(propName, typeof(String));
}
}
else
{
beginIndexRow = 1;
// Add one column on the dataset for each used column on Excel.
for (int c = 1; c <= ColCount; c++)
{
// Here we will add all strings, since we do not know what we are waiting for.
Data.Columns.Add(TCellAddress.EncodeColumn(c), typeof(String));
}
}
string[] dr = new string[ColCount];
int rowCount = xls.RowCount;
for (int r = beginIndexRow; r <= rowCount; r++)
{
Array.Clear(dr, 0, dr.Length);
//This loop will only loop on used cells. It is more efficient than looping on all the columns.
for (int cIndex = xls.ColCountInRow(r); cIndex > 0; cIndex--) //reverse the loop to avoid calling ColCountInRow more than once.
{
int Col = xls.ColFromIndex(r, cIndex);
if (formatted)
{
TRichString rs = xls.GetStringFromCell(r, Col);
dr[Col - 1] = rs.Value;
}
else
{
int XF = 0; //This is the cell format, we will not use it here.
object val = xls.GetCellValueIndexed(r, cIndex, ref XF);
TFormula Fmla = val as TFormula;
if (Fmla != null)
{
//When we have formulas, we want to write the formula result.
//If we wanted the formula text, we would not need this part.
dr[Col - 1] = Convert.ToString(Fmla.Result);
}
else
{
dr[Col - 1] = Convert.ToString(val);
}
}
}
Data.Rows.Add(dr);
}
}
finally
{
Data.EndLoadData();
}
}
return(dataSet);
}
