示例#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
        /**
         * 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++;
                }
            }
        }
示例#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);
        }
        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();
            }
        }
示例#5
0
        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);
        }
示例#6
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.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.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());
        }
示例#7
0
        public void UpdateHeaders()
        {
            XSSFSheet     parent             = (XSSFSheet)this.GetParent();
            CellReference startCellReference = this.GetStartCellReference();

            if (startCellReference == null)
            {
                return;
            }
            int     row1 = startCellReference.Row;
            int     col  = (int)startCellReference.Col;
            XSSFRow row2 = parent.GetRow(row1) as XSSFRow;

            if (row2 == null)
            {
                return;
            }
            foreach (CT_TableColumn ctTableColumn in this.GetCTTable().tableColumns.tableColumn)
            {
                int      cellnum = (int)ctTableColumn.id - 1 + col;
                XSSFCell cell    = row2.GetCell(cellnum) as XSSFCell;
                if (cell != null)
                {
                    ctTableColumn.name = cell.StringCellValue;
                }
            }
        }
示例#8
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;
     }
     _sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
     _stylesSource       = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
 }
示例#9
0
        public IEvaluationCell GetCell(int rowIndex, int columnIndex)
        {
            // cache for performance: ~30% speedup due to caching
            if (_cellCache == null)
            {
                _cellCache = new Dictionary <CellKey, IEvaluationCell>(_xs.LastRowNum * 3);
                foreach (IRow row in _xs)
                {
                    int rowNum = row.RowNum;
                    foreach (ICell cell in row)
                    {
                        // cast is safe, the iterator is just defined using the interface
                        CellKey         key1      = new CellKey(rowNum, cell.ColumnIndex);
                        IEvaluationCell evalcell1 = new XSSFEvaluationCell((XSSFCell)cell, this);
                        _cellCache.Add(key1, evalcell1);
                    }
                }
            }

            CellKey key = new CellKey(rowIndex, columnIndex);

            IEvaluationCell evalcell = null;

            if (_cellCache.ContainsKey(key))
            {
                evalcell = _cellCache[key];
            }

            // If cache is stale, update cache with this one cell
            // This is a compromise between rebuilding the entire cache
            // (which would quickly defeat the benefit of the cache)
            // and not caching at all.
            // See bug 59958: Add cells on the fly to the evaluation sheet cache on cache miss
            if (evalcell == null)
            {
                XSSFRow row = _xs.GetRow(rowIndex) as XSSFRow;
                if (row == null)
                {
                    return(null);
                }
                XSSFCell cell = row.GetCell(columnIndex) as XSSFCell;
                if (cell == null)
                {
                    return(null);
                }
                evalcell        = new XSSFEvaluationCell(cell, this);
                _cellCache[key] = evalcell;
            }

            return(evalcell);
        }
示例#10
0
        public void Bug51158()
        {
            // create a workbook
            XSSFWorkbook wb1   = new XSSFWorkbook();
            XSSFSheet    sheet = wb1.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 wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;

            Assert.IsNotNull(wb2);
            sheet = wb2.GetSheetAt(0) as XSSFSheet;
            row   = sheet.GetRow(2) as XSSFRow;
            Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
            Assert.IsNull(row.GetCell(4));

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

            // write the second excel file
            XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;

            Assert.IsNotNull(wb3);
            sheet = wb3.GetSheetAt(0) as XSSFSheet;
            row   = sheet.GetRow(2) as XSSFRow;

            Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
            Assert.AreEqual("test2", row.GetCell(4).StringCellValue);

            wb3.Close();
            wb2.Close();
            wb1.Close();
        }
示例#11
0
        public void TestCopyRowFrom()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet    = workbook.CreateSheet("test") as XSSFSheet;
            XSSFRow      srcRow   = sheet.CreateRow(0) as XSSFRow;

            srcRow.CreateCell(0).SetCellValue("Hello");
            XSSFRow destRow = sheet.CreateRow(1) as XSSFRow;

            destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
            Assert.IsNotNull(destRow.GetCell(0));
            Assert.AreEqual("Hello", destRow.GetCell(0).StringCellValue);

            workbook.Close();
        }
示例#12
0
        private void setUp_testCopyCellFrom_CellCopyPolicy()
        {
            XSSFWorkbook wb  = new XSSFWorkbook();
            XSSFRow      row = wb.CreateSheet().CreateRow(0) as XSSFRow;

            srcCell  = row.CreateCell(0) as XSSFCell;
            destCell = row.CreateCell(1) as XSSFCell;

            srcCell.CellFormula = ("2+3");

            ICellStyle style = wb.CreateCellStyle();

            style.BorderTop           = BorderStyle.Thick;
            style.FillBackgroundColor = ((short)5);
            srcCell.CellStyle         = (style);

            destCell.SetCellValue(true);
        }
示例#13
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);
                    }
                }
            }
        }
示例#14
0
文件: XSSFCell.cs 项目: founshi/npoi
 /**
  * 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();
 }
示例#15
0
        public void TestCopyRowOverwritesExistingRow()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet1   = workbook.CreateSheet("Sheet1") as XSSFSheet;
            ISheet       sheet2   = workbook.CreateSheet("Sheet2");

            IRow    srcRow            = sheet1.CreateRow(0);
            XSSFRow destRow           = sheet1.CreateRow(1) as XSSFRow;
            IRow    observerRow       = sheet1.CreateRow(2);
            IRow    externObserverRow = sheet2.CreateRow(0);

            srcRow.CreateCell(0).SetCellValue("hello");
            srcRow.CreateCell(1).SetCellValue("world");
            destRow.CreateCell(0).SetCellValue(5.0);                     //A2 -> 5.0
            destRow.CreateCell(1).CellFormula           = ("A1");        // B2 -> A1 -> "hello"
            observerRow.CreateCell(0).CellFormula       = ("A2");        // A3 -> A2 -> 5.0
            observerRow.CreateCell(1).CellFormula       = ("B2");        // B3 -> B2 -> A1 -> "hello"
            externObserverRow.CreateCell(0).CellFormula = ("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0

            // overwrite existing destRow with row-copy of srcRow
            destRow.CopyRowFrom(srcRow, new CellCopyPolicy());

            // copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer
            // to the new row (and allow the old row to be garbage collected)
            // this is mostly so existing references to rows that are overwritten are updated
            // rather than allowing users to continue updating rows that are no longer part of the sheet

            Assert.AreSame(srcRow, sheet1.GetRow(0), "existing references to srcRow are still valid");
            Assert.AreSame(destRow, sheet1.GetRow(1), "existing references to destRow are still valid");
            Assert.AreSame(observerRow, sheet1.GetRow(2), "existing references to observerRow are still valid");
            Assert.AreSame(externObserverRow, sheet2.GetRow(0), "existing references to externObserverRow are still valid");

            // Make sure copyRowFrom actually copied row (this is tested elsewhere)
            Assert.AreEqual(CellType.String, destRow.GetCell(0).CellType);
            Assert.AreEqual("hello", destRow.GetCell(0).StringCellValue);

            // We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region
            Assert.AreEqual("A2", observerRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");
            Assert.AreEqual("B2", observerRow.GetCell(1).CellFormula, "references to overwritten cells are unmodified");
            Assert.AreEqual("Sheet1!A2", externObserverRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");

            workbook.Close();
        }
示例#16
0
        public void TestBug57423()
        {
            XSSFWorkbook workbook  = new XSSFWorkbook();
            XSSFSheet    sheet     = (XSSFSheet)workbook.CreateSheet();
            CT_Worksheet wsh       = sheet.GetCTWorksheet();
            CT_SheetData sheetData = wsh.sheetData;

            XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);

            row1.CreateCell(0).SetCellValue("a");

            XSSFRow row2 = (XSSFRow)sheet.CreateRow(1);

            row2.CreateCell(0).SetCellValue("b");

            XSSFRow row3 = (XSSFRow)sheet.CreateRow(2);

            row3.CreateCell(0).SetCellValue("c");

            sheet.ShiftRows(0, 1, 3); //move "a" and "b" 3 rows down
            //      Before:    After:
            //         A        A
            // 1       a        <empty>
            // 2       b        <empty>
            // 3       c        c
            // 4                a
            // 5                b

            List <CT_Row> xrow = sheetData.row;

            Assert.AreEqual(3, xrow.Count);

            // Rows are sorted: [3, 4, 5]
            Assert.AreEqual(3u, xrow[0].r);
            Assert.IsTrue(xrow[0].Equals(row3.GetCTRow()));

            Assert.AreEqual(4u, xrow[1].r);
            Assert.IsTrue(xrow[1].Equals(row1.GetCTRow()));

            Assert.AreEqual(5u, xrow[2].r);
            Assert.IsTrue(xrow[2].Equals(row2.GetCTRow()));
        }
示例#17
0
        private static DataTable GetDataTableByXlsx(string excelFilePath, DataTable dt)
        {
            XSSFWorkbook hssfworkbook;

            using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);

                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                int r = 0;
                while (rows.MoveNext())
                {
                    r++;
                    if (r == 1)
                    {
                        continue;
                    }

                    NPOI.XSSF.UserModel.XSSFRow row = (NPOI.XSSF.UserModel.XSSFRow)rows.Current;
                    DataRow rw = dt.NewRow();
                    for (int i = 1; i <= row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);

                        if (cell == null)
                        {
                            continue;
                        }
                        else
                        {
                            rw[i - 1] = cell.ToString();
                        }
                    }

                    dt.Rows.Add(rw);
                }
                return(dt);
            }
        }
示例#18
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("A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA", cell_0.StringCellValue);

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

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

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

            Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
            Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
            Assert.AreEqual("bar", row.GetCell(2).StringCellValue);
        }
示例#19
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));
                                }
                            }
                        }
                    }
                }
            }
        }
示例#20
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());
                }
            }
        }
示例#21
0
        public void TestCopyRowFromExternalSheet()
        {
            XSSFWorkbook workbook  = new XSSFWorkbook();
            ISheet       srcSheet  = workbook.CreateSheet("src");
            XSSFSheet    destSheet = workbook.CreateSheet("dest") as XSSFSheet;

            workbook.CreateSheet("other");

            IRow srcRow = srcSheet.CreateRow(0);
            int  col    = 0;

            //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
            srcRow.CreateCell(col++).CellFormula = ("B5");
            srcRow.CreateCell(col++).CellFormula = ("src!B5");
            srcRow.CreateCell(col++).CellFormula = ("dest!B5");
            srcRow.CreateCell(col++).CellFormula = ("other!B5");

            //Test 2D and 3D Ref Ptgs with absolute row
            srcRow.CreateCell(col++).CellFormula = ("B$5");
            srcRow.CreateCell(col++).CellFormula = ("src!B$5");
            srcRow.CreateCell(col++).CellFormula = ("dest!B$5");
            srcRow.CreateCell(col++).CellFormula = ("other!B$5");

            //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
            srcRow.CreateCell(col++).CellFormula = ("SUM(B5:D$5)");
            srcRow.CreateCell(col++).CellFormula = ("SUM(src!B5:D$5)");
            srcRow.CreateCell(col++).CellFormula = ("SUM(dest!B5:D$5)");
            srcRow.CreateCell(col++).CellFormula = ("SUM(other!B5:D$5)");
            //////////////////
            XSSFRow destRow = destSheet.CreateRow(1) as XSSFRow;

            destRow.CopyRowFrom(srcRow, new CellCopyPolicy());

            //////////////////

            //Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
            col = 0;
            ICell cell = destRow.GetCell(col++);

            Assert.IsNotNull(cell);
            Assert.AreEqual("B6", cell.CellFormula, "RefPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("src!B6", cell.CellFormula, "Ref3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("dest!B6", cell.CellFormula, "Ref3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("other!B6", cell.CellFormula, "Ref3DPtg");

            /////////////////////////////////////////////

            //Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change)
            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("B$5", cell.CellFormula, "RefPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("src!B$5", cell.CellFormula, "Ref3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("dest!B$5", cell.CellFormula, "Ref3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("other!B$5", cell.CellFormula, "Ref3DPtg");

            //////////////////////////////////////////

            //Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
            // Note: absolute row changes from last cell to first cell in order
            // to maintain topLeft:bottomRight order
            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("SUM(B$5:D6)", cell.CellFormula, "Area2DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(cell);
            Assert.AreEqual("SUM(src!B$5:D6)", cell.CellFormula, "Area3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(destRow.GetCell(6));
            Assert.AreEqual("SUM(dest!B$5:D6)", cell.CellFormula, "Area3DPtg");

            cell = destRow.GetCell(col++);
            Assert.IsNotNull(destRow.GetCell(7));
            Assert.AreEqual("SUM(other!B$5:D6)", cell.CellFormula, "Area3DPtg");

            workbook.Close();
        }