示例#1
0
        private void assertCellsWithMissingR(XSSFRow row)
        {
            XSSFCell a1 = (XSSFCell)row.GetCell(0);

            Assert.IsNotNull(a1);
            XSSFCell a2 = (XSSFCell)row.GetCell(1);

            Assert.IsNotNull(a2);
            XSSFCell a5 = (XSSFCell)row.GetCell(4);

            Assert.IsNotNull(a5);
            XSSFCell a6 = (XSSFCell)row.GetCell(5);

            Assert.IsNotNull(a6);

            Assert.AreEqual(6, row.LastCellNum);
            Assert.AreEqual(4, row.PhysicalNumberOfCells);

            Assert.AreEqual(a1.StringCellValue, "A1");
            Assert.AreEqual(a2.StringCellValue, "B1");
            Assert.AreEqual(a5.StringCellValue, "E1");
            Assert.AreEqual(a6.StringCellValue, "F1");

            // even if R attribute is not set,
            // POI is able to re-construct it from column and row indexes
            Assert.AreEqual(a1.GetReference(), "A1");
            Assert.AreEqual(a2.GetReference(), "B1");
            Assert.AreEqual(a5.GetReference(), "E1");
            Assert.AreEqual(a6.GetReference(), "F1");
        }
示例#2
0
        public void Bug56274()
        {
            // read sample file
            XSSFWorkbook inputWorkbook = XSSFTestDataSamples.OpenSampleWorkbook("56274.xlsx");

            // read the original sheet header order
            XSSFRow       row     = inputWorkbook.GetSheetAt(0).GetRow(0) as XSSFRow;
            List <String> headers = new List <String>();

            foreach (ICell cell in row)
            {
                headers.Add(cell.StringCellValue);
            }

            // no SXSSF class
            // save the worksheet as-is using SXSSF
            //File outputFile = File.CreateTempFile("poi-56274", ".xlsx");
            //SXSSFWorkbook outputWorkbook = new Npoi.Core.XSSF.streaming.SXSSFWorkbook(inputWorkbook);
            //outputWorkbook.Write(new FileOutputStream(outputFile));

            // re-read the saved file and make sure headers in the xml are in the original order
            //inputWorkbook = new Npoi.Core.XSSF.UserModel.XSSFWorkbook(new FileStream(outputFile));
            inputWorkbook = XSSFTestDataSamples.WriteOutAndReadBack(inputWorkbook) as XSSFWorkbook;
            CT_Table ctTable = (inputWorkbook.GetSheetAt(0) as XSSFSheet).GetTables()[0].GetCTTable();
            List <CT_TableColumn> ctTableColumnList = ctTable.tableColumns.tableColumn;

            Assert.AreEqual(headers.Count, ctTableColumnList.Count,
                            "number of headers in xml table should match number of header cells in worksheet");
            for (int i = 0; i < headers.Count; i++)
            {
                Assert.AreEqual(headers[i], ctTableColumnList[i].name,
                                "header name in xml table should match number of header cells in worksheet");
            }
            //Assert.IsTrue(outputFile.Delete());
        }
示例#3
0
        public void TestMissingRAttribute()
        {
            XSSFWorkbook wb    = new XSSFWorkbook();
            XSSFSheet    sheet = (XSSFSheet)wb.CreateSheet();
            XSSFRow      row   = (XSSFRow)sheet.CreateRow(0);
            XSSFCell     a1    = (XSSFCell)row.CreateCell(0);

            a1.SetCellValue("A1");
            XSSFCell a2 = (XSSFCell)row.CreateCell(1);

            a2.SetCellValue("B1");
            XSSFCell a4 = (XSSFCell)row.CreateCell(4);

            a4.SetCellValue("E1");
            XSSFCell a6 = (XSSFCell)row.CreateCell(5);

            a6.SetCellValue("F1");

            assertCellsWithMissingR(row);

            a2.GetCTCell().unsetR();
            a6.GetCTCell().unsetR();

            assertCellsWithMissingR(row);

            wb  = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
            row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
            assertCellsWithMissingR(row);
        }
        private void CheckWorkbookGrouping(IWorkbook wb, bool?[] collapsed, bool[] hidden, int[] outlineLevel)
        {
            printWorkbook(wb);
            ISheet sheet = wb.GetSheetAt(0);

            Assert.AreEqual(collapsed.Length, hidden.Length);
            Assert.AreEqual(collapsed.Length, outlineLevel.Length);
            Assert.AreEqual(collapsed.Length, sheet.LastRowNum - sheet.FirstRowNum + 1,
                            "Expected " + collapsed.Length + " rows with collapsed state, but had " + (sheet.LastRowNum - sheet.FirstRowNum + 1) + " rows ("
                            + sheet.FirstRowNum + "-" + sheet.LastRowNum + ")");
            for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
            {
                if (collapsed[i - sheet.FirstRowNum] == null)
                {
                    continue;
                }
                XSSFRow row = (XSSFRow)sheet.GetRow(i);
                Assert.IsNotNull(row, "Could not read row " + i);
                Assert.IsNotNull(row.GetCTRow(), "Could not read row " + i);
                Assert.AreEqual(collapsed[i - sheet.FirstRowNum], row.GetCTRow().collapsed, "Row: " + i + ": collapsed");
                Assert.AreEqual(hidden[i - sheet.FirstRowNum], row.GetCTRow().hidden, "Row: " + i + ": hidden");

                Assert.AreEqual(outlineLevel[i - sheet.FirstRowNum], row.GetCTRow().outlineLevel, "Row: " + i + ": level");
            }

            WriteToFile(wb);
        }
示例#5
0
        /**
         * Synchronize table headers with cell values in the parent sheet.
         * Headers <em>must</em> be in sync, otherwise Excel will display a
         * "Found unreadable content" message on startup.
         */
        public void UpdateHeaders()
        {
            XSSFSheet     sheet = (XSSFSheet)GetParent();
            CellReference ref1  = GetStartCellReference() as CellReference;

            if (ref1 == null)
            {
                return;
            }

            int     headerRow         = ref1.Row;
            int     firstHeaderColumn = ref1.Col;
            XSSFRow row = sheet.GetRow(headerRow) as XSSFRow;

            if (row != null && row.GetCTRow() != null)
            {
                int cellnum = firstHeaderColumn;
                foreach (CT_TableColumn col in GetCTTable().tableColumns.tableColumn)
                {
                    XSSFCell cell = row.GetCell(cellnum) as XSSFCell;
                    if (cell != null)
                    {
                        col.name = (cell.StringCellValue);
                    }
                    cellnum++;
                }
            }
        }
        public void TestBug55843b()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet  = wb.CreateSheet("test") as XSSFSheet;
                XSSFRow   row    = sheet.CreateRow(0) as XSSFRow;
                XSSFRow   row2   = sheet.CreateRow(1) as XSSFRow;
                XSSFCell  cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
                XSSFCell  cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
                cellB1.SetCellValue(10);
                XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;

                cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
                CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(Evaluate);
                Assert.AreEqual("2", Evaluate.FormatAsString());

                cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
                CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(EvaluateN);

                Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
                Assert.AreEqual("2", EvaluateN.FormatAsString());
            }
            finally
            {
                wb.Close();
            }
        }
示例#7
0
        /**
         * Construct a XSSFCell.
         *
         * @param row the parent row.
         * @param cell the xml bean Containing information about the cell.
         */

        public XSSFCell(XSSFRow row, CT_Cell cell)
        {
            _cell = cell;
            _row  = row;
            if (cell.r != null)
            {
                _cellNum = new CellReference(cell.r).Col;
            }
            else
            {
                int prevNum = row.LastCellNum;
                if (prevNum != -1)
                {
                    _cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1;
                }
            }
            _sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
            _stylesSource       = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
        }
示例#8
0
        public void TestMissingRAttributeBug54288()
        {
            // workbook with cells missing the R attribute
            XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx");
            // same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
            XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx");

            XSSFSheet sheet    = (XSSFSheet)wb.GetSheetAt(0);
            XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0);

            Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows);

            // Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
            // For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
            // and assert that POI reads them equally:
            DataFormatter formater = new DataFormatter();

            foreach (IRow r in sheetRef)
            {
                XSSFRow rowRef = (XSSFRow)r;
                XSSFRow row    = (XSSFRow)sheet.GetRow(rowRef.RowNum);

                Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]");

                foreach (ICell c in rowRef.Cells)
                {
                    XSSFCell cellRef = (XSSFCell)c;
                    XSSFCell cell    = (XSSFCell)row.GetCell(cellRef.ColumnIndex);

                    Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex);
                    Assert.AreEqual(cellRef.GetReference(), cell.GetReference());

                    if (!cell.GetCTCell().IsSetR())
                    {
                        Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef");

                        String valRef = formater.FormatCellValue(cellRef);
                        String val    = formater.FormatCellValue(cell);
                        Assert.AreEqual(valRef, val);
                    }
                }
            }
        }
示例#9
0
        public void Bug51158()
        {
            // create a workbook
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet    = workbook.CreateSheet("Test Sheet") as XSSFSheet;
            XSSFRow      row      = sheet.CreateRow(2) as XSSFRow;
            XSSFCell     cell     = row.CreateCell(3) as XSSFCell;

            cell.SetCellValue("test1");

            //XSSFCreationHelper helper = workbook.GetCreationHelper();
            //cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));

            XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;

            Assert.IsNotNull(comment);
            comment.SetString("some comment");

            //        ICellStyle cs = workbook.CreateCellStyle();
            //        cs.ShrinkToFit=(/*setter*/false);
            //        row.CreateCell(0).CellStyle=(/*setter*/cs);

            // write the first excel file
            XSSFWorkbook readBack = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;

            Assert.IsNotNull(readBack);
            Assert.AreEqual("test1", readBack.GetSheetAt(0).GetRow(2).GetCell(3).StringCellValue);
            Assert.IsNull(readBack.GetSheetAt(0).GetRow(2).GetCell(4));

            // add a new cell to the sheet
            cell = row.CreateCell(4) as XSSFCell;
            cell.SetCellValue("test2");

            // write the second excel file
            readBack = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            Assert.IsNotNull(readBack);
            Assert.AreEqual("test1", readBack.GetSheetAt(0).GetRow(2).GetCell(3).StringCellValue);
            Assert.AreEqual("test2", readBack.GetSheetAt(0).GetRow(2).GetCell(4).StringCellValue);
        }
示例#10
0
        public void TestInlineString()
        {
            XSSFWorkbook wb    = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("xlsx-jdbc.xlsx");
            XSSFSheet    sheet = (XSSFSheet)wb.GetSheetAt(0);
            XSSFRow      row   = (XSSFRow)sheet.GetRow(1);

            XSSFCell cell_0 = (XSSFCell)row.GetCell(0);

            Assert.AreEqual(ST_CellType.inlineStr, cell_0.GetCTCell().t);
            Assert.IsTrue(cell_0.GetCTCell().IsSetIs());
            Assert.AreEqual(cell_0.StringCellValue, "A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA");

            XSSFCell cell_1 = (XSSFCell)row.GetCell(1);

            Assert.AreEqual(ST_CellType.inlineStr, cell_1.GetCTCell().t);
            Assert.IsTrue(cell_1.GetCTCell().IsSetIs());
            Assert.AreEqual(cell_1.StringCellValue, "foo");

            XSSFCell cell_2 = (XSSFCell)row.GetCell(2);

            Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
            Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
            Assert.AreEqual(row.GetCell(2).StringCellValue, "bar");
        }
示例#11
0
        public void TestBug56511()
        {
            XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56511.xlsx");

            foreach (XSSFSheet sheet in wb)
            {
                int lastRow = sheet.LastRowNum;
                for (int rowIdx = sheet.FirstRowNum; rowIdx <= lastRow; rowIdx++)
                {
                    XSSFRow row = sheet.GetRow(rowIdx) as XSSFRow;
                    if (row != null)
                    {
                        int lastCell = row.LastCellNum;

                        for (int cellIdx = row.FirstCellNum; cellIdx <= lastCell; cellIdx++)
                        {
                            XSSFCell cell = row.GetCell(cellIdx) as XSSFCell;
                            if (cell != null)
                            {
                                //System.out.Println("row " + rowIdx + " column " + cellIdx + ": " + cell.CellType + ": " + cell.ToString());

                                XSSFRichTextString richText = cell.RichStringCellValue as XSSFRichTextString;
                                int anzFormattingRuns       = richText.NumFormattingRuns;
                                for (int run = 0; run < anzFormattingRuns; run++)
                                {
                                    /*XSSFFont font =*/
                                    richText.GetFontOfFormattingRun(run);
                                    //System.out.Println("  run " + run
                                    //        + " font " + (font == null ? "<null>" : font.FontName));
                                }
                            }
                        }
                    }
                }
            }
        }
示例#12
0
        public void TestCreate()
        {
            XSSFWorkbook       workbook     = new XSSFWorkbook();
            XSSFSheet          sheet        = workbook.CreateSheet() as XSSFSheet;
            XSSFRow            row          = sheet.CreateRow(0) as XSSFRow;
            XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;

            String[] urls =
            {
                "http://apache.org/",
                "www.apache.org",
                "/temp",
                "file:///c:/temp",
                "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
            };
            for (int i = 0; i < urls.Length; i++)
            {
                String        s    = urls[i];
                XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
                link.Address = (s);

                XSSFCell cell = row.CreateCell(i) as XSSFCell;
                cell.Hyperlink = (link);
            }
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet    = workbook.GetSheetAt(0) as XSSFSheet;
            PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;

            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
                {
                    Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
                }
                else
                {
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
                }
            }

            // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet    = workbook.GetSheetAt(0) as XSSFSheet;
            rels     = sheet.GetPackagePart().Relationships;
            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
                {
                    Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
                }
                else
                {
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
                }
            }
        }