コード例 #1
0
        /// <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);
        }
コード例 #2
0
        /// <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());
                }
            }
        }
コード例 #3
0
        private void readWorkBookGlobals()
        {
            //Read Header
            try
            {
                m_hdr = XlsHeader.ReadHeader(m_file);
            }
            catch (ArgumentException 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;

                    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:
                    ////Console.WriteLine("UnKnown Id:{0}",rec.ID);
                    continue;
                }
            }
        }
コード例 #4
0
        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
            //
            XlsBiffBOF bof = m_stream.Read() as XlsBiffBOF;

            if (bof == null || bof.Type != BIFFTYPE.Worksheet)
            {
                return(false);
            }

            //DumpBiffRecords();

            //
            // 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 (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();
            }

            //
            // Read Demension
            //
            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);

            //
            // 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;
                }
                var thisRec = m_stream.Read();

                if (thisRec is XlsBiffEOF)
                {
                    break;
                }
                rowRecord = thisRec as XlsBiffRow;
            }

            if (rowRecord != null)
            {
                ////Console.WriteLine("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;

            //
            // Read Hyper Link
            //
            bool hasFound = false;

            while (true)
            {
                if (m_stream.Position >= m_stream.Size)
                {
                    break;
                }
                var thisRecord = m_stream.Read();

                if (thisRecord is XlsBiffEOF)
                {
                    break;
                }

                XlsBiffHyperLink hyperLink = thisRecord as XlsBiffHyperLink;
                if (hyperLink != null)
                {
                    hasFound = true;
                    ////Console.WriteLine("Read HyperLink");
                    //Console.WriteLine("Url:{0},{1}",hyperLink.Url,hyperLink.CellRangeAddress.ToString());
                    m_globals.AddHyperLink(hyperLink);
                }

                if (hasFound == true && hyperLink == null)
                {
                    break;
                }
            }

            return(true);
        }
コード例 #5
0
        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);
        }