Exemplo n.º 1
0
        public void TestToxyTableToDataTable_withEmptyColumnHeader()
        {
            #region create ToxyTable
            ToxyTable ttable = new ToxyTable();
            ttable.Name = "Test1";
            ttable.HeaderRows.Add(new ToxyRow(0));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, null));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C2"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, null));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(4, "C4"));
            ToxyRow trow1 = new ToxyRow(1);
            trow1.Cells.Add(new ToxyCell(0, "1"));
            trow1.Cells.Add(new ToxyCell(1, "2"));
            trow1.Cells.Add(new ToxyCell(4, "3"));
            trow1.Cells.Add(new ToxyCell(5, "4"));
            trow1.LastCellIndex = 5;
            ttable.Rows.Add(trow1);


            ToxyRow trow2 = new ToxyRow(2);
            trow2.Cells.Add(new ToxyCell(0, "5"));
            trow2.Cells.Add(new ToxyCell(1, "6"));
            trow2.Cells.Add(new ToxyCell(3, "7"));
            trow2.LastCellIndex = 3;
            ttable.Rows.Add(trow2);
            ttable.LastColumnIndex = 3;
            #endregion

            DataTable dt = ttable.ToDataTable();
            Assert.AreEqual("Test1", dt.TableName);
            Assert.AreEqual(2, dt.Rows.Count);
            Assert.AreEqual(6, dt.Columns.Count);

            Assert.AreEqual("C1", dt.Columns[0].Caption);
            Assert.AreEqual("Column1", dt.Columns[1].Caption);
            Assert.AreEqual("C2", dt.Columns[2].Caption);
            Assert.AreEqual("Column2", dt.Columns[3].Caption);
            Assert.AreEqual("C4", dt.Columns[4].Caption);

            Assert.AreEqual("1", dt.Rows[0][0].ToString());
            Assert.AreEqual("2", dt.Rows[0][1].ToString());
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][2].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][3].ToString()));
            Assert.AreEqual("3", dt.Rows[0][4].ToString());
            Assert.AreEqual("4", dt.Rows[0][5].ToString());
            Assert.AreEqual("5", dt.Rows[1][0].ToString());
            Assert.AreEqual("6", dt.Rows[1][1].ToString());
            Assert.AreEqual("7", dt.Rows[1][3].ToString());
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][2].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][4].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][5].ToString()));
        }
Exemplo n.º 2
0
        public void TestToxyTableToDataTable()
        {
            #region create ToxyTable
            ToxyTable ttable = new ToxyTable();
            ttable.Name = "Test1";
            ttable.HeaderRows.Add(new ToxyRow(0));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, "C2"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C3"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, "C4"));
            ToxyRow trow1 = new ToxyRow(1);
            trow1.Cells.Add(new ToxyCell(0, "1"));
            trow1.Cells.Add(new ToxyCell(1, "2"));
            trow1.Cells.Add(new ToxyCell(2, "3"));
            ttable.Rows.Add(trow1);

            ToxyRow trow2 = new ToxyRow(2);
            trow2.Cells.Add(new ToxyCell(0, "4"));
            trow2.Cells.Add(new ToxyCell(1, "5"));
            trow2.Cells.Add(new ToxyCell(3, "6"));
            trow2.LastCellIndex = 3;
            ttable.Rows.Add(trow2);

            ToxyRow trow3 = new ToxyRow(3);
            trow3.LastCellIndex = 3;
            trow3.Cells.Add(new ToxyCell(1, "7"));
            trow3.Cells.Add(new ToxyCell(2, "8"));
            trow3.Cells.Add(new ToxyCell(3, "9"));
            ttable.Rows.Add(trow3);

            ttable.LastColumnIndex = 3;
            #endregion
            DataTable dt = ttable.ToDataTable();
            Assert.AreEqual("Test1", dt.TableName);
            Assert.AreEqual(3, dt.Rows.Count);
            Assert.AreEqual(4, dt.Columns.Count);

            Assert.AreEqual("C1", dt.Columns[0].Caption);
            Assert.AreEqual("C2", dt.Columns[1].Caption);
            Assert.AreEqual("C3", dt.Columns[2].Caption);
            Assert.AreEqual("C4", dt.Columns[3].Caption);

            Assert.AreEqual("1", dt.Rows[0][0].ToString());
            Assert.AreEqual("2", dt.Rows[0][1].ToString());
            Assert.AreEqual("3", dt.Rows[0][2].ToString());
            Assert.AreEqual("4", dt.Rows[1][0].ToString());
            Assert.AreEqual("5", dt.Rows[1][1].ToString());
            Assert.AreEqual("6", dt.Rows[1][3].ToString());
            Assert.AreEqual("7", dt.Rows[2][1].ToString());
            Assert.AreEqual("8", dt.Rows[2][2].ToString());
            Assert.AreEqual("9", dt.Rows[2][3].ToString());
        }
Exemplo n.º 3
0
        public void TestToxyTableToDataTable()
        {
            #region create ToxyTable
            ToxyTable ttable = new ToxyTable();
            ttable.Name = "Test1";
            ttable.HeaderRows.Add(new ToxyRow(0));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(0, "C1"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(1, "C2"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(2, "C3"));
            ttable.HeaderRows[0].Cells.Add(new ToxyCell(3, "C4"));
            ToxyRow trow1=new ToxyRow(1);
            trow1.Cells.Add(new ToxyCell(0,"1"));
            trow1.Cells.Add(new ToxyCell(1,"2"));
            trow1.Cells.Add(new ToxyCell(2,"3"));
            ttable.Rows.Add(trow1);

            ToxyRow trow2 = new ToxyRow(2);
            trow2.Cells.Add(new ToxyCell(0, "4"));
            trow2.Cells.Add(new ToxyCell(1, "5"));
            trow2.Cells.Add(new ToxyCell(3, "6"));
            trow2.LastCellIndex = 3;
            ttable.Rows.Add(trow2);

            ToxyRow trow3 = new ToxyRow(3);
            trow3.LastCellIndex = 3;
            trow3.Cells.Add(new ToxyCell(1, "7"));
            trow3.Cells.Add(new ToxyCell(2, "8"));
            trow3.Cells.Add(new ToxyCell(3, "9"));
            ttable.Rows.Add(trow3);

            ttable.LastColumnIndex = 3;
            #endregion
            DataTable dt = ttable.ToDataTable();
            Assert.AreEqual("Test1",dt.TableName);
            Assert.AreEqual(3, dt.Rows.Count);
            Assert.AreEqual(4, dt.Columns.Count);

            Assert.AreEqual("C1", dt.Columns[0].Caption);
            Assert.AreEqual("C2", dt.Columns[1].Caption);
            Assert.AreEqual("C3", dt.Columns[2].Caption);
            Assert.AreEqual("C4", dt.Columns[3].Caption);

            Assert.AreEqual("1", dt.Rows[0][0].ToString());
            Assert.AreEqual("2", dt.Rows[0][1].ToString());
            Assert.AreEqual("3", dt.Rows[0][2].ToString());
            Assert.AreEqual("4", dt.Rows[1][0].ToString());
            Assert.AreEqual("5", dt.Rows[1][1].ToString());
            Assert.AreEqual("6", dt.Rows[1][3].ToString());
            Assert.AreEqual("7", dt.Rows[2][1].ToString());
            Assert.AreEqual("8", dt.Rows[2][2].ToString());
            Assert.AreEqual("9", dt.Rows[2][3].ToString());
        }
        public ToxySpreadsheet LoadData(DataContext context)
        {
            Database db = DatabaseFactory.CreateDatabase(context.ConnectionStringName);

            DbCommand       cmd = db.GetSqlStringCommand(context.QueryString);
            ToxySpreadsheet ss  = new ToxySpreadsheet();

            ss.Name = context.ConnectionStringName;
            int i = 0;

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                ToxyTable table = new ToxyTable();
                table.Name = "Sheet1";
                ToxyRow row = new ToxyRow(i);
                if (i == 0)
                {
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        row.Cells.Add(new ToxyCell(j, reader.GetName(i)));
                    }
                }
                else
                {
                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        string value = reader.GetString(j);
                        if (!string.IsNullOrEmpty(value))
                        {
                            row.Cells.Add(new ToxyCell(j, value));
                        }
                    }
                }
                table.Rows.Add(row);
                i++;
                ss.Tables.Add(table);
            }
            return(ss);
        }
Exemplo n.º 5
0
        ToxyTable Parse(IWorkbook workbook, int sheetIndex, bool extractHeader, bool extractFooter, bool hasHeader, bool fillBlankCells, bool includeComment, HSSFDataFormatter formatter)
        {
            ToxyTable table = new ToxyTable();

            if (workbook.NumberOfSheets - 1 < sheetIndex)
            {
                throw new ArgumentOutOfRangeException(string.Format("This file only contains {0} sheet(s).", workbook.NumberOfSheets));
            }
            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            table.Name = sheet.SheetName;

            if (extractHeader && sheet.Header != null)
            {
                table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right;
            }

            if (extractFooter && sheet.Footer != null)
            {
                table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right;
            }

            bool firstRow = true;

            table.LastRowIndex = sheet.LastRowNum;
            foreach (IRow row in sheet)
            {
                ToxyRow tr = null;
                if (!hasHeader || !firstRow)
                {
                    tr = new ToxyRow(row.RowNum);
                }
                else if (hasHeader && firstRow)
                {
                    table.HeaderRows.Add(new ToxyRow(row.RowNum));
                }
                foreach (ICell cell in row)
                {
                    if (hasHeader && firstRow)
                    {
                        table.HeaderRows[0].Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString()));
                    }
                    else
                    {
                        if (tr.LastCellIndex < cell.ColumnIndex)
                        {
                            tr.LastCellIndex = cell.ColumnIndex;
                        }
                        ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell));
                        if (!string.IsNullOrEmpty(cell.ToString()))
                        {
                            tr.Cells.Add(c);
                        }
                        else if (fillBlankCells)
                        {
                            tr.Cells.Add(c);
                        }
                        if (includeComment && cell.CellComment != null)
                        {
                            c.Comment = cell.CellComment.String.String;
                        }
                    }
                }
                if (tr != null)
                {
                    tr.RowIndex = row.RowNum;
                    table.Rows.Add(tr);

                    if (table.LastColumnIndex < tr.LastCellIndex)
                    {
                        table.LastColumnIndex = tr.LastCellIndex;
                    }
                }
                if (firstRow)
                {
                    firstRow = false;
                }
            }
            for (int j = 0; j < sheet.NumMergedRegions; j++)
            {
                var range = sheet.GetMergedRegion(j);
                table.MergeCells.Add(new MergeCellRange()
                {
                    FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn
                });
            }
            return(table);
        }
Exemplo n.º 6
0
        public ToxySpreadsheet Parse()
        {
            if (!File.Exists(Context.Path))
            {
                throw new FileNotFoundException("File " + Context.Path + " is not found");
            }

            bool hasHeader = false;

            if (Context.Properties.ContainsKey("HasHeader"))
            {
                string sHasHeader = Context.Properties["HasHeader"].ToLower();
                if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
                {
                    hasHeader = true;
                }
            }
            char delimiter = ',';

            if (Context.Properties.ContainsKey("delimiter"))
            {
                delimiter = Context.Properties["delimiter"][0];
            }


            Encoding encoding = Encoding.UTF8;

            StreamReader sr = null;

            try
            {
                if (Context.Encoding == null)
                {
                    sr = new StreamReader(Context.Path, true);
                }
                else
                {
                    sr = new StreamReader(Context.Path, true);
                }
                CsvReader       reader  = new CsvReader(sr, hasHeader, delimiter);
                string[]        headers = reader.GetFieldHeaders();
                ToxySpreadsheet ss      = new ToxySpreadsheet();
                ToxyTable       t1      = new ToxyTable();
                ss.Tables.Add(t1);

                for (int j = 0; j < headers.Length; j++)
                {
                    t1.ColumnHeaders.Cells.Add(new ToxyCell(j, headers[j]));
                    t1.LastColumnIndex = t1.ColumnHeaders.Cells.Count - 1;
                }
                int i = 0;
                while (reader.ReadNextRecord())
                {
                    ToxyRow tr = new ToxyRow(i);
                    tr.LastCellIndex = reader.FieldCount - 1;
                    if (tr.LastCellIndex > t1.LastColumnIndex)
                    {
                        t1.LastColumnIndex = tr.LastCellIndex;
                    }
                    tr.RowIndex = i;
                    for (int j = 0; j <= tr.LastCellIndex; j++)
                    {
                        if (this.ParseSegment != null)
                        {
                            this.ParseSegment(this, new ParseSegmentEventArgs(reader[j], i, j));
                        }
                        ToxyCell c = new ToxyCell(j, reader[j]);
                        if (tr.LastCellIndex < c.CellIndex)
                        {
                            tr.LastCellIndex = c.CellIndex;
                        }
                        tr.Cells.Add(c);
                    }

                    t1.Rows.Add(tr);
                    i++;
                }
                t1.LastRowIndex = i - 1;
                return(ss);
            }
            finally
            {
                if (sr != null)
                {
                    sr.Close();
                }
            }
        }
Exemplo n.º 7
0
        public new ToxySpreadsheet Parse()
        {
            if (!File.Exists(Context.Path))
                throw new FileNotFoundException("File " + Context.Path + " is not found");

            bool hasHeader=false;
            if (Context.Properties.ContainsKey("HasHeader"))
            {
                string sHasHeader = Context.Properties["HasHeader"].ToLower();
                if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
                    hasHeader = true;
            }
            char delimiter =',';
            if (Context.Properties.ContainsKey("delimiter"))
            {
                delimiter = Context.Properties["delimiter"][0];
            }

            
            Encoding encoding = Encoding.UTF8;

            StreamReader sr = null;
            try
            {
                if (Context.Encoding == null)
                {
                    sr = new StreamReader(Context.Path, true);
                }
                else
                {
                    sr = new StreamReader(Context.Path, true);
                }
                CsvReader reader=new CsvReader(sr, hasHeader,delimiter);
                string[] headers = reader.GetFieldHeaders();
                ToxySpreadsheet ss = new ToxySpreadsheet();
                ToxyTable t1 = new ToxyTable();
                ss.Tables.Add(t1);

                for (int j = 0; j < headers.Length;j++ )
                {
                    t1.ColumnHeaders.Cells.Add(new ToxyCell(j, headers[j]));
                }
                int i=0;
                while(reader.ReadNextRecord())
                {
                    ToxyRow tr=new ToxyRow(i);
                    tr.LastCellIndex = t1.ColumnHeaders.Cells.Count;
                    for (int j = 0; j < tr.LastCellIndex; j++)
                    {
                        ToxyCell c = new ToxyCell(j, reader[j]);
                        if (tr.LastCellIndex < c.CellIndex)
                        {
                            tr.LastCellIndex = c.CellIndex;
                        }
                        tr.Cells.Add(c);
                    }
                    
                    t1.Rows.Add(tr);
                }
                return ss;
            }
            finally
            {
                if (sr != null)
                    sr.Close();
            }
        }
Exemplo n.º 8
0
        ToxyTable Parse(IWorkbook workbook, int sheetIndex, bool extractHeader, bool extractFooter, bool hasHeader, bool fillBlankCells, bool includeComment, HSSFDataFormatter formatter)
        {
            ToxyTable table = new ToxyTable();
            if (workbook.NumberOfSheets - 1 < sheetIndex)
            {
                throw new ArgumentOutOfRangeException(string.Format("This file only contains {0} sheet(s).", workbook.NumberOfSheets));
            }
            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            table.Name = sheet.SheetName;

            if (extractHeader && sheet.Header != null)
            {
                table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right;
            }

            if (extractFooter && sheet.Footer != null)
            {
                table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right;
            }

            bool firstRow = true;
            table.LastRowIndex = sheet.LastRowNum;
            foreach (IRow row in sheet)
            {
                ToxyRow tr = null;
                if (!hasHeader || !firstRow)
                {
                    tr = new ToxyRow(row.RowNum);
                }
                else if (hasHeader && firstRow)
                {
                    table.HeaderRows.Add(new ToxyRow(row.RowNum));
                }
                foreach (ICell cell in row)
                {
                    if (hasHeader && firstRow)
                    {
                        table.HeaderRows[0].Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString()));
                    }
                    else
                    {
                        if (tr.LastCellIndex < cell.ColumnIndex)
                        {
                            tr.LastCellIndex = cell.ColumnIndex;
                        }
                        ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell));
                        if (!string.IsNullOrEmpty(cell.ToString()))
                        {
                            tr.Cells.Add(c);
                        }
                        else if (fillBlankCells)
                        {
                            tr.Cells.Add(c);
                        }
                        if (includeComment && cell.CellComment != null)
                        {
                            c.Comment = cell.CellComment.String.String;
                        }
                    }
                }
                if (tr != null)
                {
                    tr.RowIndex = row.RowNum;
                    table.Rows.Add(tr);

                    if (table.LastColumnIndex < tr.LastCellIndex)
                        table.LastColumnIndex = tr.LastCellIndex;
                }
                if (firstRow)
                {
                    firstRow = false;
                }
            }
            for (int j = 0; j < sheet.NumMergedRegions; j++)
            {
                var range = sheet.GetMergedRegion(j);
                table.MergeCells.Add(new MergeCellRange() { FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn });
            }
            return table;
        }
Exemplo n.º 9
0
        public void TestToxyTableToDataTable_withEmptyColumnHeader()
        {
            #region create ToxyTable
            ToxyTable ttable = new ToxyTable();
            ttable.Name = "Test1";
            ttable.ColumnHeaders.Cells.Add(new ToxyCell(0, "C1"));
            ttable.ColumnHeaders.Cells.Add(new ToxyCell(1, null));
            ttable.ColumnHeaders.Cells.Add(new ToxyCell(2, "C2"));
            ttable.ColumnHeaders.Cells.Add(new ToxyCell(3, null));
            ttable.ColumnHeaders.Cells.Add(new ToxyCell(4, "C4"));
            ToxyRow trow1 = new ToxyRow(0);
            trow1.Cells.Add(new ToxyCell(0, "1"));
            trow1.Cells.Add(new ToxyCell(1, "2"));
            trow1.Cells.Add(new ToxyCell(4, "3"));
            trow1.Cells.Add(new ToxyCell(5, "4"));
            trow1.LastCellIndex = 5;
            ttable.Rows.Add(trow1);

            ToxyRow trow2 = new ToxyRow(1);
            trow2.LastCellIndex = 3;
            trow2.Cells.Add(new ToxyCell(0, "5"));
            trow2.Cells.Add(new ToxyCell(1, "6"));
            trow2.Cells.Add(new ToxyCell(3, "7"));
            ttable.Rows.Add(trow2);

            ttable.LastColumnIndex = 5;
            #endregion

            DataTable dt = ttable.ToDataTable();
            Assert.AreEqual("Test1", dt.TableName);
            Assert.AreEqual(2, dt.Rows.Count);
            Assert.AreEqual(6, dt.Columns.Count);

            Assert.AreEqual("C1", dt.Columns[0].Caption);
            Assert.AreEqual("Column1", dt.Columns[1].Caption);
            Assert.AreEqual("C2", dt.Columns[2].Caption);
            Assert.AreEqual("Column2", dt.Columns[3].Caption);
            Assert.AreEqual("C4", dt.Columns[4].Caption);

            Assert.AreEqual("1", dt.Rows[0][0].ToString());
            Assert.AreEqual("2", dt.Rows[0][1].ToString());
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][2].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[0][3].ToString()));
            Assert.AreEqual("3", dt.Rows[0][4].ToString());
            Assert.AreEqual("4", dt.Rows[0][5].ToString());
            Assert.AreEqual("5", dt.Rows[1][0].ToString());
            Assert.AreEqual("6", dt.Rows[1][1].ToString());
            Assert.AreEqual("7", dt.Rows[1][3].ToString());
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][2].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][4].ToString()));
            Assert.IsTrue(string.IsNullOrEmpty(dt.Rows[1][5].ToString()));
        }
Exemplo n.º 10
0
        public ToxySpreadsheet Parse()
        {
            if (!File.Exists(Context.Path))
                throw new FileNotFoundException("File " + Context.Path + " is not found");

            bool hasHeader = false;
            if (Context.Properties.ContainsKey("GenerateColumnHeader"))
            {
                hasHeader = Utility.IsTrue(Context.Properties["GenerateColumnHeader"]);
            }
            bool extractHeader = false;
            if (Context.Properties.ContainsKey("ExtractSheetHeader"))
            {
                extractHeader = Utility.IsTrue(Context.Properties["ExtractSheetHeader"]);
            }
            bool extractFooter = false;
            if (Context.Properties.ContainsKey("ExtractSheetFooter"))
            {
                extractFooter = Utility.IsTrue(Context.Properties["ExtractSheetFooter"]);
            }
            bool showCalculatedResult = false;
            if (Context.Properties.ContainsKey("ShowCalculatedResult"))
            {
                showCalculatedResult = Utility.IsTrue(Context.Properties["ShowCalculatedResult"]);
            }
            bool fillBlankCells = false;
            if (Context.Properties.ContainsKey("FillBlankCells"))
            {
                fillBlankCells = Utility.IsTrue(Context.Properties["FillBlankCells"]);
            }
            bool includeComment = true;
            if (Context.Properties.ContainsKey("IncludeComments"))
            {
                includeComment = Utility.IsTrue(Context.Properties["IncludeComments"]);
            }
            ToxySpreadsheet ss = new ToxySpreadsheet();
            ss.Name = Context.Path;
            IWorkbook workbook = WorkbookFactory.Create(Context.Path);
           
            HSSFDataFormatter formatter = new HSSFDataFormatter();
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ToxyTable table=new ToxyTable();
                ISheet sheet = workbook.GetSheetAt(i);
                table.Name = sheet.SheetName;
                
                if (extractHeader && sheet.Header != null)
                {
                    table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right;
                }

                if (extractFooter && sheet.Footer != null)
                {
                    table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right;
                }

                bool firstRow = true;
                table.LastRowIndex = sheet.LastRowNum;
                foreach (IRow row in sheet)
                {
                    ToxyRow tr=null;
                    if (!hasHeader || !firstRow)
                    {
                        tr=new ToxyRow(row.RowNum);
                    }
                    foreach (ICell cell in row)
                    {
                        if (hasHeader&& firstRow)
                        {
                            table.ColumnHeaders.Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString()));
                        }
                        else 
                        {
                            if (tr.LastCellIndex < cell.ColumnIndex)
                            {
                                tr.LastCellIndex = cell.ColumnIndex;
                            }
                            ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell));
                            if (!string.IsNullOrEmpty(cell.ToString()))
                            {
                                tr.Cells.Add(c);
                            }
                            else if (fillBlankCells)
                            {
                                tr.Cells.Add(c);
                            }
                            if (cell.CellComment != null)
                            {
                                c.Comment = cell.CellComment.String.String;
                            }
                        }
                    }
                    if (tr != null)
                    {
                        tr.RowIndex = row.RowNum;
                        table.Rows.Add(tr);
                    }
                    if (firstRow)
                    {
                        firstRow = false;
                    }
                    if(table.LastColumnIndex<tr.LastCellIndex)
                        table.LastColumnIndex=tr.LastCellIndex;
                }
                for (int j = 0; j < sheet.NumMergedRegions; j++)
                { 
                    var range = sheet.GetMergedRegion(j);
                    table.MergeCells.Add(new MergeCellRange() { FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn });
                }
                ss.Tables.Add(table);
            }
            if (workbook is XSSFWorkbook)
            {
                var props= ((XSSFWorkbook)workbook).GetProperties();

                if (props.CoreProperties != null)
                {
                    if (props.CoreProperties.Title != null)
                    {
                        ss.Properties.Add("Title", props.CoreProperties.Title );
                    }
                    else if (props.CoreProperties.Identifier != null)
                    {
                        ss.Properties.Add("Identifier", props.CoreProperties.Identifier );
                    }
                    else if (props.CoreProperties.Keywords != null)
                    {
                        ss.Properties.Add("Keywords", props.CoreProperties.Keywords);
                    }
                    else if (props.CoreProperties.Revision != null)
                    {
                        ss.Properties.Add("Revision", props.CoreProperties.Revision);
                    }
                    else if (props.CoreProperties.Subject != null)
                    {
                        ss.Properties.Add("Subject", props.CoreProperties.Subject);
                    }
                    else if (props.CoreProperties.Modified != null)
                    {
                        ss.Properties.Add("Modified", props.CoreProperties.Modified);
                    }
                    else if (props.CoreProperties.LastPrinted != null)
                    {
                        ss.Properties.Add("LastPrinted", props.CoreProperties.LastPrinted);
                    }
                    else if (props.CoreProperties.Created != null)
                    {
                        ss.Properties.Add("Created", props.CoreProperties.Created);
                    }
                    else if (props.CoreProperties.Creator != null)
                    {
                        ss.Properties.Add("Creator", props.CoreProperties.Creator);
                    }
                    else if (props.CoreProperties.Description != null)
                    {
                        ss.Properties.Add("Description", props.CoreProperties.Description);
                    }
                }
                if (props.ExtendedProperties != null && props.ExtendedProperties.props!=null)
                {
                    var extProps = props.ExtendedProperties.props.GetProperties();
                    if (extProps.Application != null)
                    {
                        ss.Properties.Add("Application", extProps.Application);
                    }
                    if (extProps.AppVersion != null)
                    {
                        ss.Properties.Add("AppVersion", extProps.AppVersion);
                    }
                    if (extProps.Characters>0)
                    {
                        ss.Properties.Add("Characters", extProps.Characters);
                    }
                    if (extProps.CharactersWithSpaces>0)
                    {
                        ss.Properties.Add("CharactersWithSpaces", extProps.CharactersWithSpaces);
                    }
                    if (extProps.Company != null)
                    {
                        ss.Properties.Add("Company", extProps.Company);
                    }
                    if (extProps.Lines > 0)
                    {
                        ss.Properties.Add("Lines", extProps.Lines);
                    }
                    if (extProps.Manager != null)
                    {
                        ss.Properties.Add("Manager", extProps.Manager);
                    }
                    if (extProps.Notes> 0)
                    {
                        ss.Properties.Add("Notes", extProps.Notes);
                    }
                    if (extProps.Pages>0)
                    {
                        ss.Properties.Add("Pages", extProps.Pages);
                    }
                    if (extProps.Paragraphs>0)
                    {
                        ss.Properties.Add("Paragraphs", extProps.Paragraphs);
                    }
                    if (extProps.Words>0)
                    {
                        ss.Properties.Add("Words", extProps.Words);
                    }
                    if (extProps.TotalTime>0)
                    {
                        ss.Properties.Add("TotalTime", extProps.TotalTime);
                    }
                }
            }
            else
            {
                //HSSFWorkbook
                var si = ((HSSFWorkbook)workbook).SummaryInformation;
                if (si != null)
                {
                    if (si.Title != null)
                    {
                        ss.Properties.Add("Title", si.Title);
                    }
                    else if (si.LastSaveDateTime != null)
                    {
                        ss.Properties.Add("LastSaveDateTime", si.LastSaveDateTime);
                    }
                    else if (si.PageCount > 0)
                    {
                        ss.Properties.Add("PageCount", si.PageCount);
                    }
                    else if (si.OSVersion > 0)
                    {
                        ss.Properties.Add("OSVersion", si.OSVersion);
                    }
                    else if (si.Security > 0)
                    {
                        ss.Properties.Add("Security", si.Security);
                    }
                    else if (si.Keywords != null)
                    {
                        ss.Properties.Add("Keywords", si.Keywords);
                    }
                    else if (si.EditTime > 0)
                    {
                        ss.Properties.Add("EditTime", si.EditTime);
                    }
                    else if (si.Subject != null)
                    {
                        ss.Properties.Add("Subject", si.Subject);
                    }
                    else if (si.CreateDateTime != null)
                    {
                        ss.Properties.Add("CreateDateTime", si.CreateDateTime);
                    }
                    else if (si.LastPrinted != null)
                    {
                        ss.Properties.Add("LastPrinted", si.LastPrinted);
                    }
                    else if (si.CharCount != null)
                    {
                        ss.Properties.Add("CharCount", si.CharCount);
                    }
                    else if (si.Author != null)
                    {
                        ss.Properties.Add("Author", si.Author);
                    }
                    else if (si.LastAuthor != null)
                    {
                        ss.Properties.Add("LastAuthor", si.LastAuthor);
                    }
                    else if (si.ApplicationName != null)
                    {
                        ss.Properties.Add("ApplicationName", si.ApplicationName);
                    }
                    else if (si.RevNumber != null)
                    {
                        ss.Properties.Add("RevNumber", si.RevNumber);
                    }
                    else if (si.Template != null)
                    {
                        ss.Properties.Add("Template", si.Template);
                    }
                }
                var dsi = ((HSSFWorkbook)workbook).DocumentSummaryInformation;
                if(dsi!=null)
                {
                    if (dsi.ByteCount > 0)
                    {
                        ss.Properties.Add("ByteCount", dsi.ByteCount);
                    }
                    else if (dsi.Company !=null)
                    {
                        ss.Properties.Add("Company", dsi.Company);
                    }
                    else if (dsi.Format>0)
                    {
                        ss.Properties.Add("Format", dsi.Format);
                    }
                    else if (dsi.LineCount!= null)
                    {
                        ss.Properties.Add("LineCount", dsi.Company);
                    }
                    else if (dsi.LinksDirty)
                    {
                        ss.Properties.Add("LinksDirty", true);
                    }
                    else if (dsi.Manager!=null)
                    {
                        ss.Properties.Add("Manager", dsi.Manager);
                    }
                    else if (dsi.NoteCount != null)
                    {
                        ss.Properties.Add("NoteCount", dsi.NoteCount);
                    }
                    else if (dsi.Scale)
                    {
                        ss.Properties.Add("Scale", dsi.Scale);
                    }
                    else if (dsi.Company != null)
                    {
                        ss.Properties.Add("Company", dsi.Company);
                    }
                    else if (dsi.MMClipCount != null)
                    {
                        ss.Properties.Add("MMClipCount", dsi.MMClipCount);
                    }
                    else if (dsi.ParCount != null)
                    {
                        ss.Properties.Add("ParCount", dsi.ParCount);
                    }
                }
            }
            return ss;
        }
Exemplo n.º 11
0
        public ToxySpreadsheet Parse()
        {
            if (!File.Exists(Context.Path))
                throw new FileNotFoundException("File " + Context.Path + " is not found");

            bool extractHeader=false;
            if (Context.Properties.ContainsKey("ExtractHeader"))
            {
                string sHasHeader = Context.Properties["ExtractHeader"].ToLower();
                if (sHasHeader == "1" || sHasHeader == "on" || sHasHeader == "true")
                    extractHeader = true;
            }
            char delimiter =',';
            if (Context.Properties.ContainsKey("delimiter"))
            {
                delimiter = Context.Properties["delimiter"][0];
            }

            
            Encoding encoding = Encoding.UTF8;

            StreamReader sr = null;
            try
            {
                if (Context.Encoding == null)
                {
                    sr = new StreamReader(Context.Path, true);
                }
                else
                {
                    sr = new StreamReader(Context.Path, true);
                }
                CsvReader reader=new CsvReader(sr, extractHeader,delimiter);
                string[] headers = reader.GetFieldHeaders();
                ToxySpreadsheet ss = new ToxySpreadsheet();
                ToxyTable t1 = new ToxyTable();
                ss.Tables.Add(t1);

                int i = 0;
                if (headers.Length > 0)
                {
                    t1.HeaderRows.Add(new ToxyRow(i));
                    i++;
                }
                for (int j = 0; j < headers.Length;j++ )
                {
                    t1.HeaderRows[0].Cells.Add(new ToxyCell(j, headers[j]));
                    t1.LastColumnIndex = t1.HeaderRows[0].Cells.Count-1;
                }
                while(reader.ReadNextRecord())
                {
                    ToxyRow tr=new ToxyRow(i);
                    tr.LastCellIndex = reader.FieldCount-1;
                    if (tr.LastCellIndex > t1.LastColumnIndex)
                    {
                        t1.LastColumnIndex = tr.LastCellIndex;
                    }
                    tr.RowIndex = i;
                    for (int j = 0; j <= tr.LastCellIndex; j++)
                    {
                        if (this.ParseSegment != null)
                        {
                            this.ParseSegment(this, new ParseSegmentEventArgs(reader[j], i, j));
                        }
                        ToxyCell c = new ToxyCell(j, reader[j]);
                        if (tr.LastCellIndex < c.CellIndex)
                        {
                            tr.LastCellIndex = c.CellIndex;
                        }
                        tr.Cells.Add(c);
                    }
                    
                    t1.Rows.Add(tr);
                    i++;
                }
                t1.LastRowIndex = i - 1;
                return ss;
            }
            finally
            {
                if (sr != null)
                    sr.Close();
            }
        }
Exemplo n.º 12
0
        public ToxySpreadsheet Parse()
        {
            if (!File.Exists(Context.Path))
            {
                throw new FileNotFoundException("File " + Context.Path + " is not found");
            }

            bool hasHeader = false;

            if (Context.Properties.ContainsKey("GenerateColumnHeader"))
            {
                hasHeader = Utility.IsTrue(Context.Properties["GenerateColumnHeader"]);
            }
            bool extractHeader = false;

            if (Context.Properties.ContainsKey("ExtractSheetHeader"))
            {
                extractHeader = Utility.IsTrue(Context.Properties["ExtractSheetHeader"]);
            }
            bool extractFooter = false;

            if (Context.Properties.ContainsKey("ExtractSheetFooter"))
            {
                extractFooter = Utility.IsTrue(Context.Properties["ExtractSheetFooter"]);
            }
            bool showCalculatedResult = false;

            if (Context.Properties.ContainsKey("ShowCalculatedResult"))
            {
                showCalculatedResult = Utility.IsTrue(Context.Properties["ShowCalculatedResult"]);
            }
            bool fillBlankCells = false;

            if (Context.Properties.ContainsKey("FillBlankCells"))
            {
                fillBlankCells = Utility.IsTrue(Context.Properties["FillBlankCells"]);
            }
            bool includeComment = true;

            if (Context.Properties.ContainsKey("IncludeComments"))
            {
                includeComment = Utility.IsTrue(Context.Properties["IncludeComments"]);
            }
            ToxySpreadsheet ss = new ToxySpreadsheet();

            ss.Name = Context.Path;
            IWorkbook workbook = WorkbookFactory.Create(Context.Path);

            HSSFDataFormatter formatter = new HSSFDataFormatter();

            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ToxyTable table = new ToxyTable();
                ISheet    sheet = workbook.GetSheetAt(i);
                table.Name = sheet.SheetName;

                if (extractHeader && sheet.Header != null)
                {
                    table.PageHeader = sheet.Header.Left + "|" + sheet.Header.Center + "|" + sheet.Header.Right;
                }

                if (extractFooter && sheet.Footer != null)
                {
                    table.PageFooter = sheet.Footer.Left + "|" + sheet.Footer.Center + "|" + sheet.Footer.Right;
                }

                bool firstRow = true;
                table.LastRowIndex = sheet.LastRowNum;
                foreach (IRow row in sheet)
                {
                    ToxyRow tr = null;
                    if (!hasHeader || !firstRow)
                    {
                        tr = new ToxyRow(row.RowNum);
                    }
                    foreach (ICell cell in row)
                    {
                        if (hasHeader && firstRow)
                        {
                            table.ColumnHeaders.Cells.Add(new ToxyCell(cell.ColumnIndex, cell.ToString()));
                        }
                        else
                        {
                            if (tr.LastCellIndex < cell.ColumnIndex)
                            {
                                tr.LastCellIndex = cell.ColumnIndex;
                            }
                            ToxyCell c = new ToxyCell(cell.ColumnIndex, formatter.FormatCellValue(cell));
                            if (!string.IsNullOrEmpty(cell.ToString()))
                            {
                                tr.Cells.Add(c);
                            }
                            else if (fillBlankCells)
                            {
                                tr.Cells.Add(c);
                            }
                            if (includeComment && cell.CellComment != null)
                            {
                                c.Comment = cell.CellComment.String.String;
                            }
                        }
                    }
                    if (tr != null)
                    {
                        tr.RowIndex = row.RowNum;
                        table.Rows.Add(tr);
                    }
                    if (firstRow)
                    {
                        firstRow = false;
                    }
                    if (table.LastColumnIndex < tr.LastCellIndex)
                    {
                        table.LastColumnIndex = tr.LastCellIndex;
                    }
                }
                for (int j = 0; j < sheet.NumMergedRegions; j++)
                {
                    var range = sheet.GetMergedRegion(j);
                    table.MergeCells.Add(new MergeCellRange()
                    {
                        FirstRow = range.FirstRow, FirstColumn = range.FirstColumn, LastRow = range.LastRow, LastColumn = range.LastColumn
                    });
                }
                ss.Tables.Add(table);
            }
            return(ss);
        }