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); }