private static DataTable AsDataTable(IExcel self, ExcelDataTableConfiguration configuration) { var result = new DataTable { TableName = self.Name }; result.ExtendedProperties.Add("visiblestate", self.VisibleState); var first = true; var emptyRows = 0; var columnIndices = new List <int>(); while (self.Read()) { if (first) { if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null) { configuration.ReadHeaderRow(self); } for (var i = 0; i < self.FieldCount; i++) { if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i)) { continue; } var name = configuration.UseHeaderRow ? Convert.ToString(self.GetValue(i)) : null; if (string.IsNullOrEmpty(name)) { name = configuration.EmptyColumnNamePrefix + i; } // if a column already exists with the name append _i to the duplicates var columnName = GetUniqueColumnName(result, name); var column = new DataColumn(columnName, typeof(object)) { Caption = name }; result.Columns.Add(column); columnIndices.Add(i); } result.BeginLoadData(); first = false; if (configuration.UseHeaderRow) { continue; } } if (configuration.FilterRow != null && !configuration.FilterRow(self)) { continue; } if (IsEmptyRow(self, configuration)) { emptyRows++; continue; } for (var i = 0; i < emptyRows; i++) { result.Rows.Add(result.NewRow()); } emptyRows = 0; var row = result.NewRow(); for (var i = 0; i < columnIndices.Count; i++) { var columnIndex = columnIndices[i]; var value = self.GetValue(columnIndex); if (configuration.TransformValue != null) { var transformedValue = configuration.TransformValue(self, i, value); if (transformedValue != null) { value = transformedValue; } } row[i] = value; } result.Rows.Add(row); } result.EndLoadData(); return(result); }
private static void FixDataTypes(DataSet dataset) { var tables = new List <DataTable>(dataset.Tables.Count); bool convert = false; foreach (DataTable table in dataset.Tables) { if (table.Rows.Count == 0) { tables.Add(table); continue; } DataTable newTable = null; for (int i = 0; i < table.Columns.Count; i++) { Type type = null; foreach (DataRow row in table.Rows) { if (row.IsNull(i)) { continue; } var curType = row[i].GetType(); if (curType != type) { if (type == null) { type = curType; } else { type = null; break; } } } if (type == null) { continue; } convert = true; if (newTable == null) { newTable = table.Clone(); } newTable.Columns[i].DataType = type; } if (newTable != null) { newTable.BeginLoadData(); foreach (DataRow row in table.Rows) { newTable.ImportRow(row); } newTable.EndLoadData(); tables.Add(newTable); } else { tables.Add(table); } } if (convert) { dataset.Tables.Clear(); dataset.Tables.AddRange(tables.ToArray()); } }
private bool ReadWorksheet(XlsWorksheet sheet) { m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); XlsBiffBOF bof = m_stream.Read() as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } XlsBiffIndex idx = m_stream.Read() as XlsBiffIndex; if (null == idx) { return(false); } idx.IsV8 = IsV8(); DataTable dt = new DataTable(sheet.Name); XlsBiffRecord trec; XlsBiffDimensions dims = null; do { trec = m_stream.Read(); if (trec.ID == BIFFRECORDTYPE.DIMENSIONS) { dims = (XlsBiffDimensions)trec; break; } } while (trec != null && trec.ID != BIFFRECORDTYPE.ROW); int maxCol = 256; if (dims != null) { dims.IsV8 = IsV8(); maxCol = dims.LastColumn - 1; sheet.Dimensions = dims; } InitializeColumns(ref dt, maxCol); sheet.Data = dt; uint maxRow = idx.LastExistingRow; if (idx.LastExistingRow <= idx.FirstExistingRow) { return(true); } dt.BeginLoadData(); for (int i = 0; i < maxRow; i++) { dt.Rows.Add(dt.NewRow()); } uint[] dbCellAddrs = idx.DbCellAddresses; for (int i = 0; i < dbCellAddrs.Length; i++) { XlsBiffDbCell dbCell = (XlsBiffDbCell)m_stream.ReadAt((int)dbCellAddrs[i]); XlsBiffRow row = null; int offs = dbCell.RowAddress; do { row = m_stream.ReadAt(offs) as XlsBiffRow; if (row == null) { break; } offs += row.Size; } while (null != row); while (true) { XlsBiffRecord rec = m_stream.ReadAt(offs); offs += rec.Size; if (rec is XlsBiffDbCell) { break; } if (rec is XlsBiffEOF) { break; } XlsBiffBlankCell cell = rec as XlsBiffBlankCell; if (cell == null) { continue; } if (cell.ColumnIndex >= maxCol) { continue; } if (cell.RowIndex > maxRow) { continue; } string _sValue; double _dValue; switch (cell.ID) { case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: dt.Rows[cell.RowIndex][cell.ColumnIndex] = ((XlsBiffIntegerCell)cell).Value.ToString(); break; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: _dValue = ((XlsBiffNumberCell)cell).Value; if ((_sValue = TryConvertOADate(_dValue, cell.XFormat)) != null) { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue; } else { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _dValue; } break; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: dt.Rows[cell.RowIndex][cell.ColumnIndex] = ((XlsBiffLabelCell)cell).Value; break; case BIFFRECORDTYPE.LABELSST: string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); dt.Rows[cell.RowIndex][cell.ColumnIndex] = tmp; break; case BIFFRECORDTYPE.RK: _dValue = ((XlsBiffRKCell)cell).Value; if ((_sValue = TryConvertOADate(_dValue, cell.XFormat)) != null) { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue; } else { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _dValue; } break; case BIFFRECORDTYPE.MULRK: XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell; for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++) { dt.Rows[cell.RowIndex][j] = _rkCell.GetValue(j); } break; case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: // Skip blank cells break; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: object _oValue = ((XlsBiffFormulaCell)cell).Value; if (null != _oValue && _oValue is FORMULAERROR) { _oValue = null; } if (null != _oValue && (_sValue = TryConvertOADate(_oValue, cell.XFormat)) != null) { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _sValue; } else { dt.Rows[cell.RowIndex][cell.ColumnIndex] = _oValue; } break; default: break; } } } dt.EndLoadData(); if (m_PromoteToColumns) { RemapColumnsNames(ref dt, dt.Rows[0].ItemArray); dt.Rows.RemoveAt(0); dt.AcceptChanges(); } return(true); }
public System.Data.DataSet AsDataSet(bool convertOADateTime) { if (!_isValid) { return(null); } DataSet dataset = new DataSet(); for (int ind = 0; ind < _workbook.Sheets.Count; ind++) { DataTable table = new DataTable(_workbook.Sheets[ind].Name); table.ExtendedProperties.Add("visiblestate", _workbook.Sheets[ind].VisibleState); ReadSheetGlobals(_workbook.Sheets[ind]); if (_workbook.Sheets[ind].Dimension == null) { continue; } _depth = 0; _emptyRowCount = 0; //DataTable columns if (!_isFirstRowAsColumnNames) { for (int i = 0; i < _workbook.Sheets[ind].ColumnsCount; i++) { table.Columns.Add(null, typeof(Object)); } } else if (ReadSheetRow(_workbook.Sheets[ind])) { for (int index = 0; index < _cellsValues.Length; index++) { if (_cellsValues[index] != null && _cellsValues[index].ToString().Length > 0) { Helpers.AddColumnHandleDuplicate(table, _cellsValues[index].ToString()); } else { Helpers.AddColumnHandleDuplicate(table, string.Concat(COLUMN, index)); } } } else { continue; } table.BeginLoadData(); while (ReadSheetRow(_workbook.Sheets[ind])) { table.Rows.Add(_cellsValues); } if (table.Rows.Count > 0) { dataset.Tables.Add(table); } table.EndLoadData(); } dataset.AcceptChanges(); Helpers.FixDataTypes(dataset); return(dataset); }
private DataTable readWholeWorkSheet(XlsWorksheet sheet) { XlsBiffIndex idx; if (!readWorkSheetGlobals(sheet, out idx)) { return(null); } DataTable table = new DataTable(sheet.Name); bool triggerCreateColumns = true; m_dbCellAddrs = idx.DbCellAddresses; for (int index = 0; index < m_dbCellAddrs.Length; index++) { if (m_depht == m_maxRow) { break; } // init reading data m_cellOffset = findFirstDataCellOffset((int)m_dbCellAddrs[index]); //DataTable columns if (triggerCreateColumns) { if (_isFirstRowAsColumnNames && readWorkSheetRow() || (_isFirstRowAsColumnNames && m_maxRow == 1)) { for (int i = 0; i < m_maxCol; i++) { if (m_cellsValues[i] != null && m_cellsValues[i].ToString().Length > 0) { table.Columns.Add(m_cellsValues[i].ToString()); } else { table.Columns.Add(string.Concat(COLUMN, i)); } } } else { for (int i = 0; i < m_maxCol; i++) { table.Columns.Add(); } } triggerCreateColumns = false; table.BeginLoadData(); } while (readWorkSheetRow()) { table.Rows.Add(m_cellsValues); } if (m_depht > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1)) { table.Rows.Add(m_cellsValues); } } table.EndLoadData(); return(table); }