/// <summary> /// Default constructor /// </summary> /// <param name="file">Stream with source data</param> public ExcelDataReader(Stream file) { m_file = file; // new BufferedStream(file); m_hdr = XlsHeader.ReadHeader(m_file); XlsRootDirectory dir = new XlsRootDirectory(m_hdr); XlsDirectoryEntry workbookEntry = dir.FindEntry("Workbook"); if (workbookEntry == null) workbookEntry = dir.FindEntry("Book"); if (workbookEntry == null) throw new FileNotFoundException("Oops! Neither stream 'Workbook' nor 'Book' was found in file"); if (workbookEntry.EntryType != STGTY.STGTY_STREAM) throw new FormatException("Oops! Workbook directory entry is not a Stream"); m_stream = new XlsBiffStream(m_hdr, workbookEntry.StreamFirstSector); ReadWorkbookGlobals(); GC.Collect(); m_workbookData = new DataSet(); for (int i = 0; i < m_sheets.Count; i++) if (ReadWorksheet(m_sheets[i])) m_workbookData.Tables.Add(m_sheets[i].Data); m_globals.SST = null; m_globals = null; m_sheets = null; m_stream = null; m_hdr = null; GC.Collect(); }
/// <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) { 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; } } }
/// <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) { 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; // 05/01/2008 Allow load of hidden worksheets. // http://www.codeproject.com/KB/office/Excel_DataReader.aspx?display=PrintAll&fid=320417&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26&select=2513532#xx0xx if (!(sheet.Type == XlsBiffBoundSheet.SheetType.Worksheet || sheet.Type == XlsBiffBoundSheet.SheetType.MacroSheet)) 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; } } }
public ExcelDataReader(Stream file, ref List<DataItemINTEX> listItems, ref List<DataPalletINTEX> listPallets, ref List<DataCaseINTEX> listCases) { XlsHeader hdr = XlsHeader.ReadHeader(file); XlsRootDirectory dir = new XlsRootDirectory(hdr); XlsDirectoryEntry workbookEntry = dir.FindEntry("Workbook"); if (workbookEntry == null) workbookEntry = dir.FindEntry("Book"); if (workbookEntry == null) throw new FileNotFoundException("Oops! Neither stream 'Workbook' nor 'Book' was found in file"); if (workbookEntry.EntryType != STGTY.STGTY_STREAM) throw new FormatException("Oops! Workbook directory entry is not a Stream"); m_stream = new XlsBiffStream(hdr, workbookEntry.StreamFirstSector); ReadWorkbookGlobals(); GC.Collect(); m_workbookData = new DataSet(); // first sheet : boxes for (int iSheet = 0; iSheet < m_sheets.Count; ++iSheet) { if (string.Equals(m_sheets[iSheet].Name, "Articles", StringComparison.CurrentCultureIgnoreCase)) { listItems = new List<DataItemINTEX>(); if (ReadWorksheet(m_sheets[iSheet])) { DataTable dt = m_sheets[iSheet].Data; for (int iRow = 4; iRow < dt.Rows.Count; ++iRow) { try { DataItemINTEX item = new DataItemINTEX(); item._ref = (string)dt.Rows[iRow][0]; item._description = (string)dt.Rows[iRow][1]; if (DBNull.Value != dt.Rows[iRow][2]) item._UPC = (string)dt.Rows[iRow][2]; if (DBNull.Value != dt.Rows[iRow][3]) item._PCB = Convert.ToInt32((string)dt.Rows[iRow][3]); if (DBNull.Value != dt.Rows[iRow][4]) item._gencode = (string)dt.Rows[iRow][4]; item._weight = double.Parse((string)dt.Rows[iRow][5], System.Globalization.CultureInfo.InvariantCulture); item._length = double.Parse((string)dt.Rows[iRow][6], System.Globalization.CultureInfo.InvariantCulture); item._width = double.Parse((string)dt.Rows[iRow][7], System.Globalization.CultureInfo.InvariantCulture); item._height = double.Parse((string)dt.Rows[iRow][8], System.Globalization.CultureInfo.InvariantCulture); listItems.Add(item); } catch (Exception /*ex*/) { } } } } // Pallets else if (string.Equals(m_sheets[iSheet].Name, "Palettes", StringComparison.CurrentCultureIgnoreCase)) { listPallets = new List<DataPalletINTEX>(); if (ReadWorksheet(m_sheets[iSheet])) { DataTable dt = m_sheets[iSheet].Data; for (int iRow = 4; iRow < dt.Rows.Count; ++iRow) { try { DataPalletINTEX pallet = new DataPalletINTEX(); pallet._type = (string)dt.Rows[iRow][0]; pallet._length = double.Parse((string)dt.Rows[iRow][1], System.Globalization.CultureInfo.InvariantCulture); pallet._width = double.Parse((string)dt.Rows[iRow][2], System.Globalization.CultureInfo.InvariantCulture); pallet._height = double.Parse((string)dt.Rows[iRow][3], System.Globalization.CultureInfo.InvariantCulture); if (!DBNull.Value.Equals(dt.Rows[iRow][4])) pallet._weight = double.Parse((string)dt.Rows[iRow][4], System.Globalization.CultureInfo.InvariantCulture); listPallets.Add(pallet); } catch (Exception /*ex*/) { } } } } // Caisses else if (string.Equals(m_sheets[iSheet].Name, "Caisses", StringComparison.CurrentCultureIgnoreCase)) { listCases = new List<DataCaseINTEX>(); if (ReadWorksheet(m_sheets[iSheet])) { DataTable dt = m_sheets[iSheet].Data; for (int iRow = 4; iRow < dt.Rows.Count; ++iRow) { try { DataCaseINTEX caseItem = new DataCaseINTEX(); caseItem._ref = (string)dt.Rows[iRow][0]; caseItem._lengthExt = double.Parse((string)dt.Rows[iRow][1], System.Globalization.CultureInfo.InvariantCulture); caseItem._widthExt = double.Parse((string)dt.Rows[iRow][2], System.Globalization.CultureInfo.InvariantCulture); caseItem._heightExt = double.Parse((string)dt.Rows[iRow][3], System.Globalization.CultureInfo.InvariantCulture); if (!DBNull.Value.Equals(dt.Rows[iRow][4])) caseItem._lengthInt = double.Parse((string)dt.Rows[iRow][4], System.Globalization.CultureInfo.InvariantCulture); if (!DBNull.Value.Equals(dt.Rows[iRow][5])) caseItem._widthInt = double.Parse((string)dt.Rows[iRow][5], System.Globalization.CultureInfo.InvariantCulture); if (!DBNull.Value.Equals(dt.Rows[iRow][6])) caseItem._heightInt = double.Parse((string)dt.Rows[iRow][6], System.Globalization.CultureInfo.InvariantCulture); if (!DBNull.Value.Equals(dt.Rows[iRow][7])) caseItem._weight = double.Parse((string)dt.Rows[iRow][7], System.Globalization.CultureInfo.InvariantCulture); listCases.Add(caseItem); } catch (Exception /*ex*/) { } } } } } m_globals.SST = null; m_globals = null; m_sheets = null; m_stream = null; hdr = null; GC.Collect(); }