Example #1
0
        public static DataTable RemoveColumnsByRow(this DataTable dataTable, int rowIndex, Func<XlsCell, bool> filter) {
            if (rowIndex>=dataTable.Rows.Count) {
                throw new ArgumentOutOfRangeException(string.Format("行下标超出范围,最大行数为: {0}", dataTable.Rows.Count));
            }
            DataRow row=dataTable.Rows[rowIndex];
            int index=0;
            var removeIndexs=new List<int>();
            foreach (object cell in row.ItemArray) {
                XlsCell value=null;
                var xlsCell=cell as XlsCell;
                if (xlsCell!=null) {
                    value=xlsCell;
                } else {
                    value=new XlsCell(cell);
                }

                if (filter(value)) {
                    removeIndexs.Add(index);
                }

                index++;
            }

            for (int i=removeIndexs.Count-1; i>=0; i--) {
                dataTable.Columns.RemoveAt(removeIndexs[i]);
            }

            return dataTable;
        }
Example #2
0
        private static string ToMd(this DataTable table)
        {
            table.Shrink();
            //table.RemoveColumnsByRow(0, string.IsNullOrEmpty);
            var sb = new StringBuilder();

            int i = 0;

            foreach (DataRow row in table.Rows)
            {
                //if (row.IsEmpty())
                //{
                //    continue;
                //}

                sb.Append("|");
                foreach (var cell in row.ItemArray)
                {
                    string  value   = "";
                    XlsCell xlsCell = cell as XlsCell;
                    if (xlsCell != null)
                    {
                        value = xlsCell.MarkDownText;
                    }
                    else
                    {
                        value = cell.ToString();
                    }

                    sb.Append(value).Append("|");
                }
                sb.Append("\r\n");
                if (i == 0)
                {
                    sb.Append("|");
                    foreach (DataColumn col in table.Columns)
                    {
                        sb.Append(":--|");
                    }
                    sb.Append("\r\n");
                }
                i++;
            }
            return(sb.ToString());
        }
Example #3
0
        public static bool IsEmpty(this DataRow row)
        {
            foreach (var cell in row.ItemArray)
            {
                string  value   = "";
                XlsCell xlsCell = cell as XlsCell;
                if (xlsCell != null)
                {
                    value = xlsCell.Value.ToString();
                }
                else
                {
                    value = cell.ToString();
                }

                if (value != null && !string.IsNullOrEmpty(value.Trim()))
                {
                    return(false);
                }
            }
            return(true);
        }
Example #4
0
        public static DataTable RemoveColumnsByRow(this DataTable dataTable, int rowIndex, Func <XlsCell, bool> filter)
        {
            if (rowIndex >= dataTable.Rows.Count)
            {
                throw new ArgumentOutOfRangeException(string.Format("行下标超出范围,最大行数为: {0}", dataTable.Rows.Count));
            }
            var row          = dataTable.Rows[rowIndex];
            int index        = 0;
            var removeIndexs = new List <int>();

            foreach (var cell in row.ItemArray)
            {
                XlsCell value   = null;
                XlsCell xlsCell = cell as XlsCell;
                if (xlsCell != null)
                {
                    value = xlsCell;
                }
                else
                {
                    value = new XlsCell(cell);
                }

                if (filter(value))
                {
                    removeIndexs.Add(index);
                }

                index++;
            }

            for (int i = removeIndexs.Count - 1; i >= 0; i--)
            {
                dataTable.Columns.RemoveAt(removeIndexs[i]);
            }

            return(dataTable);
        }
Example #5
0
        private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
        {
            // ReadTo HyperLinks Node
            if (m_xmlReader == null)
            {
                //Console.WriteLine("m_xmlReader is null");
                return(false);
            }

            //Console.WriteLine(m_xmlReader.Depth.ToString());

            m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
            if (m_xmlReader.IsEmptyElement)
            {
                //Console.WriteLine("not find hyperlink");
                return(false);
            }

            // Read Realtionship Table
            //Console.WriteLine("sheetrel:{0}", sheet.Path);
            var sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
            var hyperDict      = new Dictionary <string, string>();

            if (sheetRelStream != null)
            {
                using (var reader = XmlReader.Create(sheetRelStream))
                {
                    while (reader.Read())
                    {
                        if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel)
                        {
                            string rid = reader.GetAttribute(XlsxWorkbook.A_id);
                            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);
                ////Console.WriteLine("{0}:{1}", aref.Substring(1), display);

                //Console.WriteLine("hyperlink:{0}",hyperDict[rid]);
                var hyperlink = display;
                if (hyperDict.ContainsKey(rid))
                {
                    hyperlink = hyperDict[rid];
                }

                int col = -1;
                int row = -1;
                XlsxDimension.XlsxDim(aref, out col, out row);
                //Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
                if (col >= 1 && row >= 1)
                {
                    row = row - 1;
                    col = col - 1;
                    if (row == 0 && m_isFirstRowAsColumnNames)
                    {
                        // TODO(fanfeilong):
                        var     value = table.Columns[col].ColumnName;
                        XlsCell cell  = new XlsCell(value);
                        cell.SetHyperLink(hyperlink);
                        table.Columns[col].DefaultValue = cell;
                    }
                    else
                    {
                        var value = table.Rows[row][col];
                        var cell  = new XlsCell(value);
                        cell.SetHyperLink(hyperlink);
                        //Console.WriteLine(cell.MarkDownText);
                        table.Rows[row][col] = cell;
                    }
                }
            }

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

            return(true);
        }
Example #6
0
        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);
            }
        }
Example #7
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 == 0 && m_isFirstRowAsColumnNames) {
                        // TODO(fanfeilong):
                        string value = table.Columns[col].ColumnName;
                        var cell = new XlsCell(value);
                        cell.SetHyperLink(hyperlink);
                        table.Columns[col].DefaultValue = cell;
                    } else {
                        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;
        }
Example #8
0
        /// <summary>
        /// Add a row to the input DataTable for each row match in the input MatchCollection
        /// </summary>
        /// <param name="rowMatches">A collection of all the rows to add to the DataTable</param>
        /// <param name="dataTable">The DataTable to which we add rows</param>
        private static void ParseRows(IEnumerable rowMatches, DataTable dataTable) {
            foreach (Match rowMatch in rowMatches) {
                // if the row contains header tags don't use it - it is a header not a row
                if (!rowMatch.Value.Contains("<th")) {
                    DataRow dataRow = dataTable.NewRow();
                    var rowArray = new List<XlsCell>();
                    MatchCollection cellMatches = Regex.Matches(
                        rowMatch.Value,
                        CellPattern,
                        ExpressionOptions);

                    for (int columnIndex = 0; columnIndex < cellMatches.Count; columnIndex++) {
                        var cellValue = cellMatches[columnIndex].Groups[1].ToString();
                        cellValue = cellValue.RemoveSpan();

                        var hyperLinkMach = Regex.Match(cellValue, HyperLinkPattern);
                        if (hyperLinkMach.Captures.Count >0) {
                            var xlsCell = new XlsCell(hyperLinkMach.Groups[2].Value);
                            xlsCell.SetHyperLink(hyperLinkMach.Groups[1].Value);
                            rowArray.Add(xlsCell);
                            Console.WriteLine("have hyperlink");
                        } else {
                            rowArray.Add(new XlsCell(cellValue));
                        }
                    }
                    XlsCell[] cells = rowArray.ToArray();
                    dataRow.ItemArray = cells;
                    dataTable.Rows.Add(dataRow);
                }
            }
        }
Example #9
0
        private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
        {
            // ReadTo HyperLinks Node
            if (m_xmlReader == null)
            {
                //Console.WriteLine("m_xmlReader is null");
                return false;
            }

            //Console.WriteLine(m_xmlReader.Depth.ToString());

            m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
            if (m_xmlReader.IsEmptyElement)
            {
                //Console.WriteLine("not find hyperlink");
                return false;
            }

            // 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);
                ////Console.WriteLine("{0}:{1}", aref.Substring(1), display);

                int col = -1;
                int row = -1;
                XlsxDimension.XlsxDim(aref, out col,out row);
                //Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
                if (col >=1 && row >=1)
                {
                    row = row - 1;
                    col = col - 1;
                    if (row==0 &&m_isFirstRowAsColumnNames)
                    {
                        // TODO(fanfeilong):
                        var value = table.Columns[col].ColumnName;
                        XlsCell cell = new XlsCell(value);
                        cell.SetHyperLink(display);
                        table.Columns[col].DefaultValue = cell;
                    }
                    else
                    {
                        var value = table.Rows[row][col];
                        var cell = new XlsCell(value);
                        cell.SetHyperLink(display);
                        //Console.WriteLine(cell.MarkDownText);
                        table.Rows[row][col] = cell;
                    }
                }
            }

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

            return true;
        }
Example #10
0
        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);
            }
        }
Example #11
0
        private bool ReadHyperLinks(XlsxWorksheet sheet, DataTable table)
        {
            // ReadTo HyperLinks Node
            if (m_xmlReader == null)
            {
                //Console.WriteLine("m_xmlReader is null");
                return false;
            }

            //Console.WriteLine(m_xmlReader.Depth.ToString());

            m_xmlReader.ReadToFollowing(XlsxWorksheet.N_hyperlinks);
            if (m_xmlReader.IsEmptyElement)
            {
                //Console.WriteLine("not find hyperlink");
                return false;
            }

            // Read Realtionship Table
            //Console.WriteLine("sheetrel:{0}", sheet.Path);
            var sheetRelStream = m_zipWorker.GetWorksheetRelsStream(sheet.Path);
            var hyperDict = new Dictionary<string, string>();
            if (sheetRelStream != null)
            {
                using (var reader = XmlReader.Create(sheetRelStream))
                {
                    while (reader.Read())
                    {
                        if (reader.NodeType == XmlNodeType.Element && reader.LocalName == XlsxWorkbook.N_rel)
                        {
                            string rid = reader.GetAttribute(XlsxWorkbook.A_id);
                            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);
                ////Console.WriteLine("{0}:{1}", aref.Substring(1), display);

                //Console.WriteLine("hyperlink:{0}",hyperDict[rid]);
                var hyperlink = display;
                if (hyperDict.ContainsKey(rid))
                {
                    hyperlink = hyperDict[rid];
                }

                int col = -1;
                int row = -1;
                XlsxDimension.XlsxDim(aref, out col,out row);
                //Console.WriteLine("{0}:[{1},{2}]",aref, row, col);
                if (col >=1 && row >=1)
                {
                    row = row - 1;
                    col = col - 1;
                    if (row==0 &&m_isFirstRowAsColumnNames)
                    {
                        // TODO(fanfeilong):
                        var value = table.Columns[col].ColumnName;
                        XlsCell cell = new XlsCell(value);
                        cell.SetHyperLink(hyperlink);
                        table.Columns[col].DefaultValue = cell;
                    }
                    else
                    {
                        var value = table.Rows[row][col];
                        var cell = new XlsCell(value);
                        cell.SetHyperLink(hyperlink);
                        //Console.WriteLine(cell.MarkDownText);
                        table.Rows[row][col] = cell;
                    }
                }
            }

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

            return true;
        }