private void PushCellValue(XlsBiffBlankCell cell) { double dValue; switch (cell.ID) { case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: _CellsValues[cell.ColumnIndex] = ((XlsBiffIntegerCell)cell).Value; break; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: dValue = ((XlsBiffNumberCell)cell).Value; _CellsValues[cell.ColumnIndex] = !_ConvertOaDate ? dValue : TryConvertOaDateTime(dValue, cell.XFormat); break; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: _CellsValues[cell.ColumnIndex] = ((XlsBiffLabelCell)cell).Value; break; case BIFFRECORDTYPE.LABELSST: var tmp = _WorkbookGlobals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); _CellsValues[cell.ColumnIndex] = tmp; break; case BIFFRECORDTYPE.RK: dValue = ((XlsBiffRKCell)cell).Value; _CellsValues[cell.ColumnIndex] = !_ConvertOaDate ? dValue : TryConvertOaDateTime(dValue, cell.XFormat); break; case BIFFRECORDTYPE.MULRK: var rkCell = (XlsBiffMulRKCell)cell; for (var j = cell.ColumnIndex; j <= rkCell.LastColumnIndex; j++) { _CellsValues[j] = !_ConvertOaDate ? rkCell.GetValue(j) : TryConvertOaDateTime(rkCell.GetValue(j), rkCell.GetXF(j)); } break; case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: // Skip blank cells break; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: var oValue = ((XlsBiffFormulaCell)cell).Value; if (null != oValue && oValue is FORMULAERROR) { oValue = null; } else { _CellsValues[cell.ColumnIndex] = !_ConvertOaDate ? oValue : TryConvertOaDateTime(oValue, (ushort)(cell.XFormat + 75));//date time offset } break; default: break; } }
private void pushCellValue(XlsBiffBlankCell cell) { double _dValue; switch (cell.ID) { case BIFFRECORDTYPE.BOOLERR: if (cell.ReadByte(7) == 0) { m_cellsValues[cell.ColumnIndex] = cell.ReadByte(6) != 0; } break; case BIFFRECORDTYPE.BOOLERR_OLD: if (cell.ReadByte(8) == 0) { m_cellsValues[cell.ColumnIndex] = cell.ReadByte(7) != 0; } break; case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: m_cellsValues[cell.ColumnIndex] = ((XlsBiffIntegerCell)cell).Value; break; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: _dValue = ((XlsBiffNumberCell)cell).Value; m_cellsValues[cell.ColumnIndex] = !m_ConvertOADate ? _dValue : tryConvertOADateTime(_dValue, cell.XFormat); break; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: m_cellsValues[cell.ColumnIndex] = ((XlsBiffLabelCell)cell).Value; break; case BIFFRECORDTYPE.LABELSST: string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); m_cellsValues[cell.ColumnIndex] = tmp; break; case BIFFRECORDTYPE.RK: _dValue = ((XlsBiffRKCell)cell).Value; m_cellsValues[cell.ColumnIndex] = !m_ConvertOADate ? _dValue : tryConvertOADateTime(_dValue, cell.XFormat); break; case BIFFRECORDTYPE.MULRK: XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell; for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++) { _dValue = _rkCell.GetValue(j); m_cellsValues[j] = !m_ConvertOADate ? _dValue : tryConvertOADateTime(_dValue, _rkCell.GetXF(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; } else { m_cellsValues[cell.ColumnIndex] = !m_ConvertOADate ? _oValue : tryConvertOADateTime(_oValue, (ushort)(cell.XFormat));//date time offset } break; default: break; } }
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); } var record = m_stream.Read(); if (record is XlsBiffEOF) { return(false); } rowRecord = record as XlsBiffRow; } while (rowRecord == null || rowRecord.RowIndex < m_depth); } m_currentRowRecord = rowRecord; //m_depth = m_currentRowRecord.RowIndex; //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); } var 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(); //read last row //if (!m_canRead && m_depth > 0) m_canRead = true; //if (!m_canRead && m_dbCellAddrsIndex < (m_dbCellAddrs.Length - 1)) //{ // m_dbCellAddrsIndex++; // m_cellOffset = findFirstDataCellOffset((int)m_dbCellAddrs[m_dbCellAddrsIndex]); // m_canRead = readWorkSheetRow(); //} return(m_canRead); }
private void pushCellValue(XlsBiffBlankCell cell) { double num; switch (cell.ID) { case BIFFRECORDTYPE.RK: num = ((XlsBiffRKCell)cell).Value; this.m_cellsValues[cell.ColumnIndex] = !this.m_ConvertOADate ? num : this.tryConvertOADateTime(num, cell.XFormat); return; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: { object obj2 = ((XlsBiffFormulaCell)cell).Value; if ((obj2 != null) && (obj2 is FORMULAERROR)) { obj2 = null; return; } this.m_cellsValues[cell.ColumnIndex] = !this.m_ConvertOADate ? obj2 : this.tryConvertOADateTime(obj2, cell.XFormat); break; } case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: break; case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: this.m_cellsValues[cell.ColumnIndex] = ((XlsBiffIntegerCell)cell).Value; return; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: num = ((XlsBiffNumberCell)cell).Value; this.m_cellsValues[cell.ColumnIndex] = !this.m_ConvertOADate ? num : this.tryConvertOADateTime(num, cell.XFormat); return; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: this.m_cellsValues[cell.ColumnIndex] = ((XlsBiffLabelCell)cell).Value; return; case BIFFRECORDTYPE.BOOLERR: if (cell.ReadByte(7) != 0) { break; } this.m_cellsValues[cell.ColumnIndex] = cell.ReadByte(6) != 0; return; case BIFFRECORDTYPE.LABELSST: { string str = this.m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); this.m_cellsValues[cell.ColumnIndex] = str; return; } case BIFFRECORDTYPE.BOOLERR_OLD: if (cell.ReadByte(8) != 0) { break; } this.m_cellsValues[cell.ColumnIndex] = cell.ReadByte(7) != 0; return; case BIFFRECORDTYPE.MULRK: { XlsBiffMulRKCell cell2 = (XlsBiffMulRKCell)cell; for (ushort i = cell.ColumnIndex; i <= cell2.LastColumnIndex; i = (ushort)(i + 1)) { num = cell2.GetValue(i); this.m_cellsValues[i] = !this.m_ConvertOADate ? num : this.tryConvertOADateTime(num, cell2.GetXF(i)); } return; } default: return; } }
/// <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 void pushCellValue(XlsBiffBlankCell cell) { double _dValue; //LogManager.Log(this).Debug("pushCellValue {0}", cell.ID); switch (cell.ID) { case BIFFRECORDTYPE.BOOLERR: if (cell.ReadByte(7) == 0) { m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(6) != 0); } break; case BIFFRECORDTYPE.BOOLERR_OLD: if (cell.ReadByte(8) == 0) { m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(7) != 0); } break; case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffIntegerCell)cell).Value); break; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: _dValue = ((XlsBiffNumberCell)cell).Value; m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ?_dValue : tryConvertOADateTime(_dValue, cell.XFormat)); //LogManager.Log(this).Debug("VALUE: {0}", _dValue); break; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffLabelCell)cell).Value); //LogManager.Log(this).Debug("VALUE: {0}", m_cellsValues[cell.ColumnIndex]); break; case BIFFRECORDTYPE.LABELSST: string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); //LogManager.Log(this).Debug("VALUE: {0}", tmp); ////Console.WriteLine("SST Index:{0}", ((XlsBiffLabelSSTCell)cell).SSTIndex); m_cellsValues[cell.ColumnIndex] = new XlsCell(tmp); break; case BIFFRECORDTYPE.RK: _dValue = ((XlsBiffRKCell)cell).Value; m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ? _dValue : tryConvertOADateTime(_dValue, cell.XFormat)); //LogManager.Log(this).Debug("VALUE: {0}", _dValue); break; case BIFFRECORDTYPE.MULRK: XlsBiffMulRKCell _rkCell = (XlsBiffMulRKCell)cell; for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++) { _dValue = _rkCell.GetValue(j); //LogManager.Log(this).Debug("VALUE[{1}]: {0}", _dValue, j); m_cellsValues[j] = new XlsCell(!ConvertOaDate ? _dValue : tryConvertOADateTime(_dValue, _rkCell.GetXF(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; } else { m_cellsValues[cell.ColumnIndex] = new XlsCell(!ConvertOaDate ? _oValue : tryConvertOADateTime(_oValue, (ushort)(cell.XFormat)));//date time offset } break; default: //Console.WriteLine("CellId:",cell.ID); break; } ////Console.WriteLine("CellId:{0},Value:{1}", cell.ID, m_cellsValues[cell.ColumnIndex]); var hyperLink = m_globals.GetHyperLink(cell.RowIndex, cell.ColumnIndex); if (hyperLink != null) { m_cellsValues[cell.ColumnIndex].SetHyperLink(hyperLink.Url); } }
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 void pushCellValue(XlsBiffBlankCell cell) { double _dValue; bool hasValue = true; switch (cell.ID) { case BIFFRECORDTYPE.BOOLERR: if (cell.ReadByte(7) == 0) { m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(6) != 0); } else { hasValue = false; } break; case BIFFRECORDTYPE.BOOLERR_OLD: if (cell.ReadByte(8) == 0) { m_cellsValues[cell.ColumnIndex] = new XlsCell(cell.ReadByte(7) != 0); } else { hasValue = false; } break; case BIFFRECORDTYPE.INTEGER: case BIFFRECORDTYPE.INTEGER_OLD: m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffIntegerCell)cell).Value); break; case BIFFRECORDTYPE.NUMBER: case BIFFRECORDTYPE.NUMBER_OLD: _dValue = ((XlsBiffNumberCell)cell).Value; m_cellsValues[cell.ColumnIndex] = new XlsCell(_dValue); break; case BIFFRECORDTYPE.LABEL: case BIFFRECORDTYPE.LABEL_OLD: case BIFFRECORDTYPE.RSTRING: m_cellsValues[cell.ColumnIndex] = new XlsCell(((XlsBiffLabelCell)cell).Value); break; case BIFFRECORDTYPE.LABELSST: string tmp = m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell).SSTIndex); m_cellsValues[cell.ColumnIndex] = new XlsCell(tmp); break; case BIFFRECORDTYPE.RK: _dValue = ((XlsBiffRKCell)cell).Value; m_cellsValues[cell.ColumnIndex] = new XlsCell(_dValue); break; case BIFFRECORDTYPE.MULRK: var _rkCell = (XlsBiffMulRKCell)cell; bool hasSet = false; for (ushort j = cell.ColumnIndex; j <= _rkCell.LastColumnIndex; j++) { _dValue = _rkCell.GetValue(j); m_cellsValues[j] = new XlsCell(_dValue); hasSet = true; } hasValue = hasSet; break; case BIFFRECORDTYPE.BLANK: case BIFFRECORDTYPE.BLANK_OLD: case BIFFRECORDTYPE.MULBLANK: // Skip blank cells hasValue = false; break; case BIFFRECORDTYPE.FORMULA: case BIFFRECORDTYPE.FORMULA_OLD: object _oValue = ((XlsBiffFormulaCell)cell).Value; if (!(_oValue is FORMULAERROR)) { m_cellsValues[cell.ColumnIndex] = new XlsCell(_oValue); } else { hasValue = false; } break; default: hasValue = false; break; } if (hasValue) { XlsBiffHyperLink hyperLink = m_globals.GetHyperLink(cell.RowIndex, cell.ColumnIndex); if (hyperLink != null) { m_cellsValues[cell.ColumnIndex].SetHyperLink(hyperLink.Url); } } }