private DataTable readWholeWorkSheet(XlsWorksheet sheet) { XlsBiffIndex idx; if (!readWorkSheetGlobals(sheet, out idx, out m_currentRowRecord)) { return(null); } DataTable table = new DataTable(sheet.Name); bool triggerCreateColumns = true; if (idx != null) { readWholeWorkSheetWithIndex(idx, triggerCreateColumns, table); } else { readWholeWorkSheetNoIndex(triggerCreateColumns, table); } table.EndLoadData(); return(table); }
private bool ReadWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx) { _BiffStream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); var bof = _BiffStream.Read() as XlsBiffBOF; idx = null; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } idx = _BiffStream.Read() as XlsBiffIndex; if (null == idx) { return(false); } idx.IsV8 = IsV8(); XlsBiffRecord trec; XlsBiffDimensions dims = null; do { trec = _BiffStream.Read(); if (trec.ID != BIFFRECORDTYPE.DIMENSIONS) { continue; } dims = (XlsBiffDimensions)trec; break; } while (trec.ID != BIFFRECORDTYPE.ROW); FieldCount = 256; if (dims != null) { dims.IsV8 = IsV8(); FieldCount = dims.LastColumn - 1; sheet.Dimensions = dims; } _MaxRowIndex = (int)idx.LastExistingRow; if (idx.LastExistingRow <= idx.FirstExistingRow) { return(false); } Depth = 0; return(true); }
/// <summary> /// private method, reads sheet data /// </summary> /// <param name="sheet">Sheet object, whose data to read</param> /// <returns>True if sheet was read successfully, otherwise False</returns> 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; bool isV8 = (m_version >= 0x600); if (idx != null) { 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.ID != BIFFRECORDTYPE.ROW); int maxCol = 256; if (dims != null) { dims.IsV8 = isV8; maxCol = dims.LastColumn; sheet.Dimensions = dims; } for (int i = 0; i < maxCol; i++) { dt.Columns.Add("Column" + (i + 1).ToString(), typeof(string)); } 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 = (int)dbCell.RowAddress; do { row = m_stream.ReadAt(offs) as XlsBiffRow; if (row == null) { break; } offs += row.Size; }while (row != null); 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; } 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: dt.Rows[cell.RowIndex][cell.ColumnIndex] = FormatNumber(((XlsBiffNumberCell)cell).Value); 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: dt.Rows[cell.RowIndex][cell.ColumnIndex] = FormatNumber(((XlsBiffRKCell)cell).Value); break; case BIFFRECORDTYPE.MULRK: for (ushort j = cell.ColumnIndex; j <= ((XlsBiffMulRKCell)cell).LastColumnIndex; j++) { dt.Rows[cell.RowIndex][j] = FormatNumber(((XlsBiffMulRKCell)cell).GetValue(j)); } break; case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: // Skip blank cells break; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: ((XlsBiffFormulaCell)cell).UseEncoding = m_encoding; object val = ((XlsBiffFormulaCell)cell).Value; if (val == null) { val = string.Empty; } else if (val is FORMULAERROR) { val = "#" + ((FORMULAERROR)val).ToString(); } else if (val is double) { val = FormatNumber((double)val); } dt.Rows[cell.RowIndex][cell.ColumnIndex] = val.ToString(); break; default: break; } } } dt.EndLoadData(); } else { return(false); } return(true); }
private IWorkSheet ReadWholeWorkSheet(XlsWorksheet sheet, IWorkBook workBook) { XlsBiffIndex idx; if (!ReadWorkSheetGlobals(sheet, out idx)) { return(null); } var workSheet = workBook.CreateWorkSheet(); workSheet.Name = sheet.Name; var triggerCreateColumns = true; _DatabaseCellAddresses = idx.DbCellAddresses; for (var index = 0; index < _DatabaseCellAddresses.Length; index++) { if (Depth == _MaxRowIndex) { break; } // init reading data _CellOffset = FindFirstDataCellOffset((int)_DatabaseCellAddresses[index]); //DataTable columns if (triggerCreateColumns) { if (IsFirstRowAsColumnNames && ReadWorkSheetRow()) { for (var i = 0; i < FieldCount; i++) { if (_CellsValues[i] != null && _CellsValues[i].ToString().Length > 0) { var column = workSheet.CreateDataColumn(); column.ColumnName = _CellsValues[i].ToString(); workSheet.Columns.Add(column); } else { var column = workSheet.CreateDataColumn(); column.ColumnName = String.Concat(Column, i); workSheet.Columns.Add(column); } } } else { for (var i = 0; i < FieldCount; i++) { workSheet.Columns.Add(workSheet.CreateDataColumn()); } } triggerCreateColumns = false; //table.BeginLoadData(); } while (ReadWorkSheetRow()) { var dataRow = workSheet.CreateDataRow(); dataRow.Values = _CellsValues; workSheet.Rows.Add(dataRow); } if (Depth > 0) { var dataRow = workSheet.CreateDataRow(); dataRow.Values = _CellsValues; workSheet.Rows.Add(dataRow); } } //table.EndLoadData(); return(workSheet); }
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; bool isV8 = (m_version >= 0x600); if (idx != null) { 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.ID != BIFFRECORDTYPE.ROW); int maxCol = 256; if (dims != null) { dims.IsV8 = isV8; maxCol = dims.LastColumn; sheet.Dimensions = dims; } for (int i = 0; i < maxCol; i++) dt.Columns.Add("Column" + (i + 1).ToString(), typeof (string)); 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 = (int) dbCell.RowAddress; do { row = m_stream.ReadAt(offs) as XlsBiffRow; if (row == null) break; offs += row.Size; } while (row != null); 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; 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: dt.Rows[cell.RowIndex][cell.ColumnIndex] = formatNumber(((XlsBiffNumberCell) cell).Value); 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: dt.Rows[cell.RowIndex][cell.ColumnIndex] = formatNumber(((XlsBiffRKCell) cell).Value); break; case BIFFRECORDTYPE.MULRK: for (ushort j = cell.ColumnIndex; j <= ((XlsBiffMulRKCell) cell).LastColumnIndex; j++) dt.Rows[cell.RowIndex][j] = formatNumber(((XlsBiffMulRKCell) cell).GetValue(j)); break; case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: // Skip blank cells break; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: //Turned of unicode encoding //((XlsBiffFormulaCell)cell).UseEncoding = m_encoding; ((XlsBiffFormulaCell) cell).UseEncoding = Encoding.Default; object val = ((XlsBiffFormulaCell) cell).Value; if (val == null) val = string.Empty; else if (val is FORMULAERROR) val = "#" + ((FORMULAERROR) val).ToString(); else if (val is double) val = formatNumber((double) val); dt.Rows[cell.RowIndex][cell.ColumnIndex] = val.ToString(); break; case BIFFRECORDTYPE.BOOLERR: dt.Rows[cell.RowIndex][cell.ColumnIndex] = ((XlsBiffBoolErrCell) cell).Value; break; default: break; } } } dt.EndLoadData(); } else { return false; } return true; }
private DataTable readWholeWorkSheet(XlsWorksheet sheet) { XlsBiffIndex idx; if (!readWorkSheetGlobals(sheet, out idx)) { return(null); } var table = new DataTable(sheet.Name); var triggerCreateColumns = true; m_dbCellAddrs = idx.DbCellAddresses; for (var 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 (var 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 (var 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); }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx, out XlsBiffRow row) { idx = null; row = null; m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); XlsBiffBOF bof = m_stream.Read() as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } //DumpBiffRecords(); XlsBiffRecord rec = m_stream.Read(); if (rec == null) { return(false); } if (rec is XlsBiffIndex) { idx = rec as XlsBiffIndex; } else if (rec is XlsBiffUncalced) { // Sometimes this come before the index... idx = m_stream.Read() as XlsBiffIndex; } //if (null == idx) //{ // // There is a record before the index! Chech his type and see the MS Biff Documentation // return false; //} if (idx != null) { idx.IsV8 = isV8(); LogManager.Log(this).Debug("INDEX IsV8={0}", idx.IsV8); } 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); //if we are already on row record then set that as the row, otherwise step forward till we get to a row record if (trec.ID == BIFFRECORDTYPE.ROW) { row = (XlsBiffRow)trec; } XlsBiffRow rowRecord = null; while (rowRecord == null) { if (m_stream.Position >= m_stream.Size) { break; } var thisRec = m_stream.Read(); LogManager.Log(this).Debug("finding rowRecord offset {0}, rec: {1}", thisRec.Offset, thisRec.ID); if (thisRec is XlsBiffEOF) { break; } rowRecord = thisRec as XlsBiffRow; } if (rowRecord != null) { LogManager.Log(this).Debug("Got row {0}, rec: id={1},rowindex={2}, rowColumnStart={3}, rowColumnEnd={4}", rowRecord.Offset, rowRecord.ID, rowRecord.RowIndex, rowRecord.FirstDefinedColumn, rowRecord.LastDefinedColumn); } row = rowRecord; if (dims != null) { dims.IsV8 = isV8(); LogManager.Log(this).Debug("dims IsV8={0}", dims.IsV8); m_maxCol = dims.LastColumn - 1; //handle case where sheet reports last column is 1 but there are actually more if (m_maxCol <= 0 && rowRecord != null) { m_maxCol = rowRecord.LastDefinedColumn; } m_maxRow = (int)dims.LastRow; sheet.Dimensions = dims; } else { m_maxCol = 256; m_maxRow = (int)idx.LastExistingRow; } if (idx != null && idx.LastExistingRow <= idx.FirstExistingRow) { return(false); } else if (row == null) { return(false); } m_depth = 0; return(true); }
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); }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx) { idx = null; m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); var bof = m_stream.Read() as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } var rec = m_stream.Read(); if (rec == null) { return(false); } if (rec is XlsBiffIndex) { idx = rec as XlsBiffIndex; } else if (rec is XlsBiffUncalced) { // Sometimes this come before the index... idx = m_stream.Read() as XlsBiffIndex; } if (null == idx) { // There is a record before the index! Chech his type and see the MS Biff Documentation return(false); } idx.IsV8 = isV8(); 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); m_maxCol = 256; if (dims != null) { dims.IsV8 = isV8(); m_maxCol = dims.LastColumn - 1; sheet.Dimensions = dims; } m_maxRow = (int)idx.LastExistingRow; if (idx.LastExistingRow <= idx.FirstExistingRow) { return(false); } m_depht = 0; return(true); }
private bool ReadWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx) { _BiffStream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); var bof = _BiffStream.Read() as XlsBiffBOF; idx = null; if (bof == null || bof.Type != BIFFTYPE.Worksheet) return false; idx = _BiffStream.Read() as XlsBiffIndex; if (null == idx) return false; idx.IsV8 = IsV8(); XlsBiffRecord trec; XlsBiffDimensions dims = null; do { trec = _BiffStream.Read(); if (trec.ID != BIFFRECORDTYPE.DIMENSIONS) continue; dims = (XlsBiffDimensions)trec; break; } while (trec.ID != BIFFRECORDTYPE.ROW); FieldCount = 256; if (dims != null) { dims.IsV8 = IsV8(); FieldCount = dims.LastColumn - 1; sheet.Dimensions = dims; } _MaxRowIndex = (int)idx.LastExistingRow; if (idx.LastExistingRow <= idx.FirstExistingRow) { return false; } Depth = 0; return true; }
private IWorkSheet ReadWholeWorkSheet(XlsWorksheet sheet, IWorkBook workBook) { XlsBiffIndex idx; if (!ReadWorkSheetGlobals(sheet, out idx)) return null; var workSheet = workBook.CreateWorkSheet(); workSheet.Name = sheet.Name; var triggerCreateColumns = true; _DatabaseCellAddresses = idx.DbCellAddresses; for (var index = 0; index < _DatabaseCellAddresses.Length; index++) { if (Depth == _MaxRowIndex) break; // init reading data _CellOffset = FindFirstDataCellOffset((int)_DatabaseCellAddresses[index]); //DataTable columns if (triggerCreateColumns) { if (IsFirstRowAsColumnNames && ReadWorkSheetRow()) { for (var i = 0; i < FieldCount; i++) { if (_CellsValues[i] != null && _CellsValues[i].ToString().Length > 0) { var column = workSheet.CreateDataColumn(); column.ColumnName = _CellsValues[i].ToString(); workSheet.Columns.Add(column); } else { var column = workSheet.CreateDataColumn(); column.ColumnName = String.Concat(Column, i); workSheet.Columns.Add(column); } } } else { for (var i = 0; i < FieldCount; i++) { workSheet.Columns.Add(workSheet.CreateDataColumn()); } } triggerCreateColumns = false; //table.BeginLoadData(); } while (ReadWorkSheetRow()) { var dataRow = workSheet.CreateDataRow(); dataRow.Values = _CellsValues; workSheet.Rows.Add(dataRow); } if (Depth > 0) { var dataRow = workSheet.CreateDataRow(); dataRow.Values = _CellsValues; workSheet.Rows.Add(dataRow); } } //table.EndLoadData(); return workSheet; }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx, out XlsBiffRow row) { idx = null; row = null; m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); XlsBiffBOF bof = m_stream.Read() as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } //DumpBiffRecords(); XlsBiffRecord rec = m_stream.Read(); if (rec == null) { return(false); } if (rec is XlsBiffIndex) { idx = rec as XlsBiffIndex; } else if (rec is XlsBiffUncalced) { // Sometimes this come before the index... idx = m_stream.Read() as XlsBiffIndex; } //if (null == idx) //{ // // There is a record before the index! Chech his type and see the MS Biff Documentation // return false; //} if (idx != null) { idx.IsV8 = isV8(); } 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); //if we are already on row record then set that as the row, otherwise step forward till we get to a row record if (trec.ID == BIFFRECORDTYPE.ROW) { row = (XlsBiffRow)trec; } XlsBiffRow rowRecord = null; while (rowRecord == null) { if (m_stream.Position >= m_stream.Size) { break; } var thisRec = m_stream.Read(); if (thisRec is XlsBiffEOF) { break; } rowRecord = thisRec as XlsBiffRow; } row = rowRecord; m_maxCol = 256; if (dims != null) { dims.IsV8 = isV8(); m_maxCol = dims.LastColumn - 1; sheet.Dimensions = dims; } m_maxRow = idx == null ? (int)dims.LastRow : (int)idx.LastExistingRow; if (idx != null && idx.LastExistingRow <= idx.FirstExistingRow) { return(false); } else if (row == null) { return(false); } m_depth = 0; return(true); }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx, out XlsBiffRow row) { XlsBiffRecord record2; idx = null; row = null; this.m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); XlsBiffBOF fbof = this.m_stream.Read() as XlsBiffBOF; if ((fbof == null) || (fbof.Type != BIFFTYPE.Worksheet)) { return(false); } XlsBiffRecord record = this.m_stream.Read(); if (record == null) { return(false); } if (record is XlsBiffIndex) { idx = record as XlsBiffIndex; } else if (record is XlsBiffUncalced) { idx = this.m_stream.Read() as XlsBiffIndex; } if (idx != null) { idx.IsV8 = this.isV8(); } XlsBiffDimensions dimensions = null; do { record2 = this.m_stream.Read(); if (record2.ID == BIFFRECORDTYPE.DIMENSIONS) { dimensions = (XlsBiffDimensions)record2; break; } }while ((record2 != null) && (record2.ID != BIFFRECORDTYPE.ROW)); if (record2.ID == BIFFRECORDTYPE.ROW) { row = (XlsBiffRow)record2; } XlsBiffRow row2 = null; while (row2 == null) { if (this.m_stream.Position >= this.m_stream.Size) { break; } XlsBiffRecord record3 = this.m_stream.Read(); if (record3 is XlsBiffEOF) { break; } row2 = record3 as XlsBiffRow; } row = row2; this.m_maxCol = 0x100; if (dimensions != null) { dimensions.IsV8 = this.isV8(); this.m_maxCol = dimensions.LastColumn - 1; sheet.Dimensions = dimensions; } this.m_maxRow = (idx == null) ? ((int)dimensions.LastRow) : ((int)idx.LastExistingRow); if ((idx != null) && (idx.LastExistingRow <= idx.FirstExistingRow)) { return(false); } if (row == null) { return(false); } this.m_depth = 0; return(true); }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx, out XlsBiffRow row) { idx = null; row = null; m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin); // Read BOF var bof = m_stream.Read() as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.Worksheet) { return(false); } // Read Index XlsBiffRecord rec = m_stream.Read(); if (rec == null) { return(false); } if (rec is XlsBiffIndex) { idx = rec as XlsBiffIndex; } else if (rec is XlsBiffUncalced) { // Sometimes this come before the index... idx = m_stream.Read() as XlsBiffIndex; } if (idx != null) { idx.IsV8 = isV8(); } // Read Demension XlsBiffRecord trec; XlsBiffDimensions dims = null; do { trec = m_stream.Read(); if (trec.ID == BIFFRECORDTYPE.DIMENSIONS) { dims = (XlsBiffDimensions)trec; break; } } while (trec.ID != BIFFRECORDTYPE.ROW); // Read Row // if we are already on row record then set that as the row, // otherwise step forward till we get to a row record if (trec.ID == BIFFRECORDTYPE.ROW) { row = (XlsBiffRow)trec; } XlsBiffRow rowRecord = null; while (rowRecord == null) { if (m_stream.Position >= m_stream.Size) { break; } XlsBiffRecord thisRec = m_stream.Read(); if (thisRec is XlsBiffEOF) { break; } rowRecord = thisRec as XlsBiffRow; } row = rowRecord; if (dims != null) { dims.IsV8 = isV8(); m_maxCol = dims.LastColumn - 1; //handle case where sheet reports last column is 1 but there are actually more if (m_maxCol <= 0 && rowRecord != null) { m_maxCol = rowRecord.LastDefinedColumn; } m_maxRow = (int)dims.LastRow; sheet.Dimensions = dims; } else { Debug.Assert(idx != null); m_maxCol = 256; m_maxRow = (int)idx.LastExistingRow; } if (idx != null && idx.LastExistingRow <= idx.FirstExistingRow) { return(false); } else if (row == null) { return(false); } m_depth = 0; // Read Hyper Link bool hasFound = false; while (true) { if (m_stream.Position >= m_stream.Size) { break; } XlsBiffRecord thisRecord = m_stream.Read(); if (thisRecord is XlsBiffEOF) { break; } var hyperLink = thisRecord as XlsBiffHyperLink; if (hyperLink != null) { hasFound = true; m_globals.AddHyperLink(hyperLink); } if (hasFound && hyperLink == null) { break; } } return(true); }