private int findFirstDataCellOffset(int startOffset) { XlsBiffRecord record = this.m_stream.ReadAt(startOffset); while (!(record is XlsBiffDbCell)) { if (this.m_stream.Position >= this.m_stream.Size) { return(-1); } if (record is XlsBiffEOF) { return(-1); } record = this.m_stream.Read(); } XlsBiffDbCell cell = (XlsBiffDbCell)record; XlsBiffRow row = null; int rowAddress = cell.RowAddress; do { row = this.m_stream.ReadAt(rowAddress) as XlsBiffRow; if (row == null) { return(rowAddress); } rowAddress += row.Size; }while (row != null); return(rowAddress); }
private bool readWorkSheetRow() { this.m_cellsValues = new object[this.m_maxCol]; while (this.m_cellOffset < this.m_stream.Size) { XlsBiffRecord record = this.m_stream.ReadAt(this.m_cellOffset); this.m_cellOffset += record.Size; if (record is XlsBiffDbCell) { break; } if (record is XlsBiffEOF) { return(false); } XlsBiffBlankCell cell = record as XlsBiffBlankCell; if ((cell != null) && (cell.ColumnIndex < this.m_maxCol)) { if (cell.RowIndex != this.m_depth) { this.m_cellOffset -= record.Size; break; } this.pushCellValue(cell); } } this.m_depth++; return(this.m_depth < this.m_maxRow); }
private void DumpBiffRecords() { XlsBiffRecord record = null; int position = this.m_stream.Position; do { record = this.m_stream.Read(); Console.WriteLine(record.ID.ToString()); }while ((record != null) && (this.m_stream.Position < this.m_stream.Size)); this.m_stream.Seek(position, SeekOrigin.Begin); }
private void DumpBiffRecords() { XlsBiffRecord rec = null; int startPos = m_stream.Position; do { rec = m_stream.Read(); } while (rec != null && m_stream.Position < m_stream.Size); m_stream.Seek(startPos, SeekOrigin.Begin); }
private bool moveToNextRecordNoIndex() { XlsBiffRow currentRowRecord = this.m_currentRowRecord; if (currentRowRecord == null) { return(false); } if (currentRowRecord.RowIndex < this.m_depth) { this.m_stream.Seek(currentRowRecord.Offset + currentRowRecord.Size, SeekOrigin.Begin); do { if (this.m_stream.Position >= this.m_stream.Size) { return(false); } XlsBiffRecord record = this.m_stream.Read(); if (record is XlsBiffEOF) { return(false); } currentRowRecord = record as XlsBiffRow; }while ((currentRowRecord == null) || (currentRowRecord.RowIndex < this.m_depth)); } this.m_currentRowRecord = currentRowRecord; XlsBiffBlankCell cell = null; do { if (this.m_stream.Position >= this.m_stream.Size) { return(false); } XlsBiffRecord record2 = this.m_stream.Read(); if (record2 is XlsBiffEOF) { return(false); } if (record2.IsCell) { XlsBiffBlankCell cell2 = record2 as XlsBiffBlankCell; if ((cell2 != null) && (cell2.RowIndex == this.m_currentRowRecord.RowIndex)) { cell = cell2; } } }while (cell == null); this.m_cellOffset = cell.Offset; this.m_canRead = this.readWorkSheetRow(); return(this.m_canRead); }
private void DumpBiffRecords() { XlsBiffRecord rec = null; var startPos = m_stream.Position; do { rec = m_stream.Read(); LogManager.Log(this).Debug(rec.ID.ToString()); } while (rec != null && m_stream.Position < m_stream.Size); m_stream.Seek(startPos, SeekOrigin.Begin); }
private void DumpBiffRecords() { XlsBiffRecord rec = null; var startPos = m_stream.Position; do { rec = m_stream.Read(); Console.WriteLine(rec.ID.ToString()); } while (rec != null && m_stream.Position < m_stream.Size); m_stream.Seek(startPos, SeekOrigin.Begin); }
private int findFirstDataCellOffset(int startOffset) { //seek to the first dbcell record XlsBiffRecord record = m_stream.ReadAt(startOffset); while (!(record is XlsBiffDbCell)) { if (m_stream.Position >= m_stream.Size) { return(-1); } if (record is XlsBiffEOF) { return(-1); } try { record = m_stream.Read(); } catch { return(-2); } } var startCell = (XlsBiffDbCell)record; XlsBiffRow row = null; int offs = startCell.RowAddress; do { row = m_stream.ReadAt(offs) as XlsBiffRow; if (row == null) { break; } offs += row.Size; } while (true); return(offs); }
private bool readWorkSheetRow() { m_cellsValues = new XlsCell[m_maxCol]; while (m_cellOffset < m_stream.Size) { XlsBiffRecord rec = m_stream.ReadAt(m_cellOffset); m_cellOffset += rec.Size; if ((rec is XlsBiffDbCell)) { break; } ; //break; if (rec is XlsBiffEOF) { return(false); } ; XlsBiffBlankCell cell = rec as XlsBiffBlankCell; if ((null == cell) || (cell.ColumnIndex >= m_maxCol)) { continue; } if (cell.RowIndex != m_depth) { m_cellOffset -= rec.Size; break; } ; pushCellValue(cell); } m_depth++; return(m_depth < m_maxRow); }
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 void readWorkBookGlobals() { //Read Header try { m_hdr = XlsHeader.ReadHeader(m_file); } catch (Exceptions.HeaderException ex) { fail(ex.Message); return; } catch (FormatException ex) { fail(ex.Message); return; } XlsRootDirectory dir = new XlsRootDirectory(m_hdr); XlsDirectoryEntry workbookEntry = dir.FindEntry(WORKBOOK) ?? dir.FindEntry(BOOK); if (workbookEntry == null) { fail(Errors.ErrorStreamWorkbookNotFound); return; } if (workbookEntry.EntryType != STGTY.STGTY_STREAM) { fail(Errors.ErrorWorkbookIsNotStream); return; } m_stream = new XlsBiffStream(m_hdr, workbookEntry.StreamFirstSector, workbookEntry.IsEntryMiniStream, dir, this); m_globals = new XlsWorkbookGlobals(); m_stream.Seek(0, SeekOrigin.Begin); XlsBiffRecord rec = m_stream.Read(); XlsBiffBOF bof = rec as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.WorkbookGlobals) { fail(Errors.ErrorWorkbookGlobalsInvalidData); return; } bool sst = false; m_version = bof.Version; m_sheets = new List <XlsWorksheet>(); while (null != (rec = m_stream.Read())) { switch (rec.ID) { case BIFFRECORDTYPE.INTERFACEHDR: m_globals.InterfaceHdr = (XlsBiffInterfaceHdr)rec; break; case BIFFRECORDTYPE.BOUNDSHEET: XlsBiffBoundSheet sheet = (XlsBiffBoundSheet)rec; if (sheet.Type != XlsBiffBoundSheet.SheetType.Worksheet) { break; } sheet.IsV8 = isV8(); sheet.UseEncoding = m_encoding; LogManager.Log(this).Debug("BOUNDSHEET IsV8={0}", sheet.IsV8); m_sheets.Add(new XlsWorksheet(m_globals.Sheets.Count, sheet)); m_globals.Sheets.Add(sheet); break; case BIFFRECORDTYPE.MMS: m_globals.MMS = rec; break; case BIFFRECORDTYPE.COUNTRY: m_globals.Country = rec; break; case BIFFRECORDTYPE.CODEPAGE: m_globals.CodePage = (XlsBiffSimpleValueRecord)rec; try { m_encoding = Encoding.GetEncoding(m_globals.CodePage.Value); } catch (ArgumentException) { // Warning - Password protection // TODO: Attach to ILog } break; case BIFFRECORDTYPE.FONT: case BIFFRECORDTYPE.FONT_V34: m_globals.Fonts.Add(rec); break; case BIFFRECORDTYPE.FORMAT_V23: { var fmt = (XlsBiffFormatString)rec; fmt.UseEncoding = m_encoding; m_globals.Formats.Add((ushort)m_globals.Formats.Count, fmt); } break; case BIFFRECORDTYPE.FORMAT: { var fmt = (XlsBiffFormatString)rec; m_globals.Formats.Add(fmt.Index, fmt); } break; case BIFFRECORDTYPE.XF: case BIFFRECORDTYPE.XF_V4: case BIFFRECORDTYPE.XF_V3: case BIFFRECORDTYPE.XF_V2: m_globals.ExtendedFormats.Add(rec); break; case BIFFRECORDTYPE.SST: m_globals.SST = (XlsBiffSST)rec; sst = true; break; case BIFFRECORDTYPE.CONTINUE: if (!sst) { break; } XlsBiffContinue contSST = (XlsBiffContinue)rec; m_globals.SST.Append(contSST); break; case BIFFRECORDTYPE.EXTSST: m_globals.ExtSST = rec; sst = false; break; case BIFFRECORDTYPE.PROTECT: case BIFFRECORDTYPE.PASSWORD: case BIFFRECORDTYPE.PROT4REVPASSWORD: //IsProtected break; case BIFFRECORDTYPE.EOF: if (m_globals.SST != null) { m_globals.SST.ReadStrings(); } return; default: continue; } } }
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 void ReadWorkbookGlobals() { m_globals = new XlsWorkbookGlobals(); m_stream.Seek(0, SeekOrigin.Begin); XlsBiffRecord rec = m_stream.Read(); XlsBiffBOF bof = rec as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.WorkbookGlobals) { throw new ArgumentException(Errors.ErrorWorkbookGlobalsInvalidData); } m_version = bof.Version; bool sst = false; while (null != (rec = m_stream.Read())) { switch (rec.ID) { case BIFFRECORDTYPE.INTERFACEHDR: m_globals.InterfaceHdr = (XlsBiffInterfaceHdr)rec; break; case BIFFRECORDTYPE.BOUNDSHEET: XlsBiffBoundSheet sheet = (XlsBiffBoundSheet)rec; if (sheet.Type != XlsBiffBoundSheet.SheetType.Worksheet) { break; } sheet.IsV8 = IsV8(); sheet.UseEncoding = m_encoding; m_sheets.Add(new XlsWorksheet(m_globals.Sheets.Count, sheet)); m_globals.Sheets.Add(sheet); break; case BIFFRECORDTYPE.MMS: m_globals.MMS = rec; break; case BIFFRECORDTYPE.COUNTRY: m_globals.Country = rec; break; case BIFFRECORDTYPE.CODEPAGE: m_globals.CodePage = (XlsBiffSimpleValueRecord)rec; try { m_encoding = Encoding.GetEncoding(m_globals.CodePage.Value); } catch { // Warning - Password protection // TODO: Attach to ILog } break; case BIFFRECORDTYPE.FONT: case BIFFRECORDTYPE.FONT_V34: m_globals.Fonts.Add(rec); break; case BIFFRECORDTYPE.FORMAT: case BIFFRECORDTYPE.FORMAT_V23: m_globals.Formats.Add(rec); break; case BIFFRECORDTYPE.XF: case BIFFRECORDTYPE.XF_V4: case BIFFRECORDTYPE.XF_V3: case BIFFRECORDTYPE.XF_V2: m_globals.ExtendedFormats.Add(rec); break; case BIFFRECORDTYPE.SST: m_globals.SST = (XlsBiffSST)rec; sst = true; break; case BIFFRECORDTYPE.CONTINUE: if (!sst) { break; } XlsBiffContinue contSST = (XlsBiffContinue)rec; m_globals.SST.Append(contSST); break; case BIFFRECORDTYPE.EXTSST: m_globals.ExtSST = rec; sst = false; break; case BIFFRECORDTYPE.PROTECT: case BIFFRECORDTYPE.PASSWORD: case BIFFRECORDTYPE.PROT4REVPASSWORD: m_IsProtected = true; break; case BIFFRECORDTYPE.EOF: if (m_globals.SST != null) { m_globals.SST.ReadStrings(); } return; default: continue; } } }
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 moveToNextRecordNoIndex() { //seek from current row record to start of cell data where that cell relates to the next row record XlsBiffRow rowRecord = m_currentRowRecord; if (rowRecord == null) { return(false); } if (rowRecord.RowIndex < m_depth) { m_stream.Seek(rowRecord.Offset + rowRecord.Size, SeekOrigin.Begin); do { if (m_stream.Position >= m_stream.Size) { return(false); } XlsBiffRecord record = m_stream.Read(); if (record is XlsBiffEOF) { return(false); } rowRecord = record as XlsBiffRow; } while (rowRecord == null || rowRecord.RowIndex < m_depth); } m_currentRowRecord = rowRecord; //we have now found the row record for the new row, the we need to seek forward to the first cell record XlsBiffBlankCell cell = null; do { if (m_stream.Position >= m_stream.Size) { return(false); } XlsBiffRecord record = m_stream.Read(); if (record is XlsBiffEOF) { return(false); } if (record.IsCell) { var candidateCell = record as XlsBiffBlankCell; if (candidateCell != null) { if (candidateCell.RowIndex == m_currentRowRecord.RowIndex) { cell = candidateCell; } } } } while (cell == null); m_cellOffset = cell.Offset; m_canRead = readWorkSheetRow(); return(m_canRead); }
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); }
/// <summary> /// Private method, reads Workbook Globals section /// </summary> private void ReadWorkbookGlobals() { m_globals = new XlsWorkbookGlobals(); m_stream.Seek(0, SeekOrigin.Begin); XlsBiffRecord rec = m_stream.Read(); XlsBiffBOF bof = rec as XlsBiffBOF; if (bof == null || bof.Type != BIFFTYPE.WorkbookGlobals) { throw new InvalidDataException("Oops! Stream has invalid data"); } m_version = bof.Version; m_encoding = Encoding.Unicode; bool isV8 = (m_version >= 0x600); bool sst = false; while ((rec = m_stream.Read()) != null) { try { switch (rec.ID) { case BIFFRECORDTYPE.INTERFACEHDR: m_globals.InterfaceHdr = (XlsBiffInterfaceHdr)rec; break; case BIFFRECORDTYPE.BOUNDSHEET: XlsBiffBoundSheet sheet = (XlsBiffBoundSheet)rec; if (sheet.Type != XlsBiffBoundSheet.SheetType.Worksheet) { break; } sheet.IsV8 = isV8; sheet.UseEncoding = m_encoding; m_sheets.Add(new XlsWorksheet(m_globals.Sheets.Count, sheet)); m_globals.Sheets.Add(sheet); break; case BIFFRECORDTYPE.MMS: m_globals.MMS = rec; break; case BIFFRECORDTYPE.COUNTRY: m_globals.Country = rec; break; case BIFFRECORDTYPE.CODEPAGE: m_globals.CodePage = (XlsBiffSimpleValueRecord)rec; m_encoding = Encoding.GetEncoding(m_globals.CodePage.Value); break; case BIFFRECORDTYPE.FONT: case BIFFRECORDTYPE.FONT_V34: m_globals.Fonts.Add(rec); break; case BIFFRECORDTYPE.FORMAT: case BIFFRECORDTYPE.FORMAT_V23: m_globals.Formats.Add(rec); break; case BIFFRECORDTYPE.XF: case BIFFRECORDTYPE.XF_V4: case BIFFRECORDTYPE.XF_V3: case BIFFRECORDTYPE.XF_V2: m_globals.ExtendedFormats.Add(rec); break; case BIFFRECORDTYPE.SST: m_globals.SST = (XlsBiffSST)rec; sst = true; break; case BIFFRECORDTYPE.CONTINUE: if (!sst) { break; } XlsBiffContinue contSST = (XlsBiffContinue)rec; m_globals.SST.Append(contSST); break; case BIFFRECORDTYPE.EXTSST: m_globals.ExtSST = rec; sst = false; break; case BIFFRECORDTYPE.EOF: if (m_globals.SST != null) { m_globals.SST.ReadStrings(); } return; default: continue; } } catch (Exception ex) { _log.Error(ex.ToString()); } } }
private object tryConvertOADateTime(double value, ushort XFormat) { ushort format = 0; if (XFormat < m_globals.ExtendedFormats.Count) { XlsBiffRecord rec = m_globals.ExtendedFormats[XFormat]; switch (rec.ID) { case BIFFRECORDTYPE.XF_V2: format = (ushort)(rec.ReadByte(2) & 0x3F); break; case BIFFRECORDTYPE.XF_V3: if ((rec.ReadByte(3) & 4) == 0) { return(value); } format = rec.ReadByte(1); break; case BIFFRECORDTYPE.XF_V4: if ((rec.ReadByte(5) & 4) == 0) { return(value); } format = rec.ReadByte(1); break; default: if ((rec.ReadByte(m_globals.Sheets[m_globals.Sheets.Count - 1].IsV8 ? 9 : 7) & 4) == 0) { return(value); } format = rec.ReadUInt16(2); break; } } else { format = XFormat; } switch (format) { // numeric built in formats case 0: //"General"; case 1: //"0"; case 2: //"0.00"; case 3: //"#,##0"; case 4: //"#,##0.00"; case 5: //"\"$\"#,##0_);(\"$\"#,##0)"; case 6: //"\"$\"#,##0_);[Red](\"$\"#,##0)"; case 7: //"\"$\"#,##0.00_);(\"$\"#,##0.00)"; case 8: //"\"$\"#,##0.00_);[Red](\"$\"#,##0.00)"; case 9: //"0%"; case 10: //"0.00%"; case 11: //"0.00E+00"; case 12: //"# ?/?"; case 13: //"# ??/??"; case 0x30: // "##0.0E+0"; case 0x25: // "_(#,##0_);(#,##0)"; case 0x26: // "_(#,##0_);[Red](#,##0)"; case 0x27: // "_(#,##0.00_);(#,##0.00)"; case 40: // "_(#,##0.00_);[Red](#,##0.00)"; case 0x29: // "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)"; case 0x2a: // "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)"; case 0x2b: // "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)"; case 0x2c: // "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)"; return(value); // date formats case 14: //this.GetDefaultDateFormat(); case 15: //"D-MM-YY"; case 0x10: // "D-MMM"; case 0x11: // "MMM-YY"; case 0x12: // "h:mm AM/PM"; case 0x13: // "h:mm:ss AM/PM"; case 20: // "h:mm"; case 0x15: // "h:mm:ss"; case 0x16: // string.Format("{0} {1}", this.GetDefaultDateFormat(), this.GetDefaultTimeFormat()); case 0x2d: // "mm:ss"; case 0x2e: // "[h]:mm:ss"; case 0x2f: // "mm:ss.0"; return(value.ConvertFromOATime()); case 0x31: // "@"; return(value.ToString(CultureInfo.InvariantCulture)); default: XlsBiffFormatString fmtString; if (m_globals.Formats.TryGetValue(format, out fmtString)) { string fmt = fmtString.Value; var formatReader = new FormatReader { FormatString = fmt }; if (formatReader.IsDateFormatString()) { return(value.ConvertFromOATime()); } } return(value); } }
private bool readWorkSheetGlobals(XlsWorksheet sheet, out XlsBiffIndex idx) { idx = 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); } 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); } 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 object tryConvertOADateTime(double value, ushort XFormat) { XlsBiffFormatString str; ushort key = 0; if ((XFormat < 0) || (XFormat >= this.m_globals.ExtendedFormats.Count)) { key = XFormat; } else { XlsBiffRecord record = this.m_globals.ExtendedFormats[XFormat]; BIFFRECORDTYPE iD = record.ID; if (iD == BIFFRECORDTYPE.XF_V2) { key = (ushort)(record.ReadByte(2) & 0x3f); } else if (iD == BIFFRECORDTYPE.XF_V3) { if ((record.ReadByte(3) & 4) == 0) { return(value); } key = record.ReadByte(1); } else if (iD == BIFFRECORDTYPE.XF_V4) { if ((record.ReadByte(5) & 4) == 0) { return(value); } key = record.ReadByte(1); } else { if ((record.ReadByte(this.m_globals.Sheets[this.m_globals.Sheets.Count - 1].IsV8 ? 9 : 7) & 4) == 0) { return(value); } key = record.ReadUInt16(2); } } switch (key) { case 0: case 1: case 2: case 3: case 4: case 5: case 6: case 7: case 8: case 9: case 10: case 11: case 12: case 13: case 0x25: case 0x26: case 0x27: case 40: case 0x29: case 0x2a: case 0x2b: case 0x2c: case 0x30: return(value); case 14: case 15: case 0x10: case 0x11: case 0x12: case 0x13: case 20: case 0x15: case 0x16: case 0x2d: case 0x2e: case 0x2f: return(Helpers.ConvertFromOATime(value)); case 0x31: return(value.ToString()); } if (this.m_globals.Formats.TryGetValue(key, out str)) { string str2 = str.Value; FormatReader reader = new FormatReader { FormatString = str2 }; if (reader.IsDateFormatString()) { return(Helpers.ConvertFromOATime(value)); } } return(value); }
/// <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 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); }