Пример #1
0
        private bool ReadSheetRow(XlsxWorksheet sheet)
        {
            if (sheet.ColumnsCount < 0)
            {
                return(false);
            }

            if (null == m_xmlReader)
            {
                return(false);
            }

            if (m_emptyRowCount != 0)
            {
                m_cellsValues = new object[sheet.ColumnsCount];
                m_emptyRowCount--;
                m_depth++;

                return(true);
            }

            if (m_savedCellsValues != null)
            {
                m_cellsValues      = m_savedCellsValues;
                m_savedCellsValues = null;
                m_depth++;

                return(true);
            }

            bool isRow       = false;
            bool isSheetData = (m_xmlReader.NodeType == XmlNodeType.Element &&
                                m_xmlReader.LocalName == XlsxWorksheet.N_sheetData);

            if (isSheetData)
            {
                isRow = m_xmlReader.ReadToFollowing(XlsxWorksheet.N_row, m_namespaceUri);
            }
            else
            {
                if (m_xmlReader.LocalName == XlsxWorksheet.N_row && m_xmlReader.NodeType == XmlNodeType.EndElement)
                {
                    //Console.WriteLine("read");
                    m_xmlReader.Read();
                }
                isRow = (m_xmlReader.NodeType == XmlNodeType.Element && m_xmlReader.LocalName == XlsxWorksheet.N_row);
            }

            if (!isRow)
            {
                return(false);
            }

            //Console.WriteLine("New Row");

            m_cellsValues = new object[sheet.ColumnsCount];
            if (sheet.ColumnsCount > 13)
            {
                int i = sheet.ColumnsCount;
            }

            var rowIndexText = m_xmlReader.GetAttribute(XlsxWorksheet.A_r);

            Debug.Assert(rowIndexText != null);
            int rowIndex = int.Parse(rowIndexText);

            if (rowIndex != (m_depth + 1))
            {
                m_emptyRowCount = rowIndex - m_depth - 1;
            }

            bool           hasValue       = false;
            bool           hasFormula     = false;
            HyperLinkIndex hyperlinkIndex = null;
            string         a_s            = String.Empty;
            string         a_t            = String.Empty;
            string         a_r            = String.Empty;
            string         f   = String.Empty;
            int            col = 0;
            int            row = 0;

            while (m_xmlReader.Read())
            {
                //Console.WriteLine("m_xmlReader.LocalName:{0}",m_xmlReader.LocalName);
                //Console.WriteLine("m_xmlReader.Value:{0}",m_xmlReader.Value);
                if (m_xmlReader.Depth == 2)
                {
                    break;
                }

                if (m_xmlReader.NodeType == XmlNodeType.Element)
                {
                    hasValue = false;

                    if (m_xmlReader.LocalName == XlsxWorksheet.N_c)
                    {
                        a_s = m_xmlReader.GetAttribute(XlsxWorksheet.A_s);
                        a_t = m_xmlReader.GetAttribute(XlsxWorksheet.A_t);
                        a_r = m_xmlReader.GetAttribute(XlsxWorksheet.A_r);
                        XlsxDimension.XlsxDim(a_r, out col, out row);
                    }
                    else if (m_xmlReader.LocalName == XlsxWorksheet.N_f)
                    {
                        hasFormula = true;
                    }
                    else if (m_xmlReader.LocalName == XlsxWorksheet.N_v || m_xmlReader.LocalName == XlsxWorksheet.N_t)
                    {
                        hasValue   = true;
                        hasFormula = false;
                    }
                    else
                    {
                        //Console.WriteLine("m_xmlReader.LocalName:{0}",m_xmlReader.LocalName);
                        // Ignore
                    }
                }

                bool hasHyperLinkFormula = false;
                if (m_xmlReader.NodeType == XmlNodeType.Text && hasFormula)
                {
                    string formula = m_xmlReader.Value.ToString();
                    if (formula.StartsWith("HYPERLINK("))
                    {
                        hyperlinkIndex = this.ReadHyperLinkFormula(sheet.Name, formula);
                    }
                }


                if (m_xmlReader.NodeType == XmlNodeType.Text && hasValue)
                {
                    double number;
                    object o = m_xmlReader.Value;

                    //Console.WriteLine("O:{0}", o);

                    if (double.TryParse(o.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out number))
                    {
                        // numeric
                        o = number;
                    }

                    if (null != a_t && a_t == XlsxWorksheet.A_s)
                    {
                        // string
                        var sstStr = m_workbook.SST[int.Parse(o.ToString())];
                        //Console.WriteLine(sstStr);
                        o = sstStr.ConvertEscapeChars();
                    }
                    else if (null != a_t && a_t == XlsxWorksheet.N_inlineStr)
                    {
                        // string inline
                        o = o.ToString().ConvertEscapeChars();
                    }
                    else if (a_t == "b")
                    {
                        // boolean
                        o = m_xmlReader.Value == "1";
                    }
                    else if (a_t == "str")
                    {
                        // string
                        o = m_xmlReader.Value;
                    }
                    else if (null != a_s)
                    {
                        //something else
                        XlsxXf xf = m_workbook.Styles.CellXfs[int.Parse(a_s)];
                        if (xf.ApplyNumberFormat && o != null && o.ToString() != string.Empty &&
                            IsDateTimeStyle(xf.NumFmtId))
                        {
                            o = number.ConvertFromOATime();
                        }
                        else if (xf.NumFmtId == 49)
                        {
                            o = o.ToString();
                        }
                    }

                    //Console.WriteLine(o);

                    if (col - 1 < m_cellsValues.Length)
                    {
                        if (hyperlinkIndex != null)
                        {
                            var co = new XlsCell(o);
                            co.HyperLinkIndex      = hyperlinkIndex;
                            m_cellsValues[col - 1] = co;
                            hyperlinkIndex         = null;
                        }
                        else
                        {
                            m_cellsValues[col - 1] = o;
                        }
                    }
                }
                else
                {
                    //Console.WriteLine(m_xmlReader.Value.ToString());
                }
            }

            if (m_emptyRowCount > 0)
            {
                //Console.WriteLine("Again");
                m_savedCellsValues = m_cellsValues;
                return(ReadSheetRow(sheet));
            }
            m_depth++;

            return(true);
        }
Пример #2
0
        private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
        {
            // ReadTo HyperLinks Node
            if (m_xmlReader == null)
            {
                return(false);
            }

            m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
            if (m_xmlReader.IsEmptyElement)
            {
                return(false);
            }

            // Read Realtionship Table
            Stream sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
            var    hyperDict      = new Dictionary <string, string>();

            if (sheetRelStream != null)
            {
                using (XmlReader reader = XmlReader.Create(sheetRelStream)) {
                    while (reader.Read())
                    {
                        if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel)
                        {
                            string rid = reader.GetAttribute(XlsxWorkbook.A_id);
                            Debug.Assert(rid != null);
                            hyperDict[rid] = reader.GetAttribute(XlsxWorkbook.A_target);
                        }
                    }
                    sheetRelStream.Close();
                }
            }


            // Read All HyperLink Node
            while (m_xmlReader.Read())
            {
                if (m_xmlReader.NodeType != XmlNodeType.Element)
                {
                    break;
                }

                if (m_xmlReader.LocalName != XlsxWorksheet.N_hyperlink)
                {
                    break;
                }

                string aref      = m_xmlReader.GetAttribute(XlsxWorksheet.A_ref);
                string display   = m_xmlReader.GetAttribute(XlsxWorksheet.A_display);
                string rid       = m_xmlReader.GetAttribute(XlsxWorksheet.A_rid);
                string hyperlink = display;

                Debug.Assert(rid != null);
                if (hyperDict.ContainsKey(rid))
                {
                    hyperlink = hyperDict[rid];
                }

                int col = -1;
                int row = -1;
                XlsxDimension.XlsxDim(aref, out col, out row);
                if (col >= 1 && row >= 1)
                {
                    row = row - 1;
                    col = col - 1;
                    if (row < table.Rows.Count)
                    {
                        if (col < table.Rows[row].Count)
                        {
                            object value = table.Rows[row][col];
                            var    cell  = new XlsCell(value);
                            cell.SetHyperLink(hyperlink);
                            table.Rows[row][col] = cell;
                        }
                    }
                }
            }

            // Close
            m_xmlReader.Close();
            if (m_sheetStream != null)
            {
                m_sheetStream.Close();
            }

            return(true);
        }
Пример #3
0
        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);
                }
            }
        }