public void TestZipEntityExpansionSharedStringTable() { IWorkbook wb = WorkbookFactory.Create(XSSFTestDataSamples.OpenSamplePackage("poc-shared-strings.xlsx")); wb.Close(); //POITextExtractor extractor = ExtractorFactory.CreateExtractor(HSSFTestDataSamples.GetSampleFile("poc-shared-strings.xlsx")); //try //{ // Assert.IsNotNull(extractor); // try // { // string tmp = extractor.Text; // } // catch (InvalidOperationException e) // { // // expected due to shared strings expansion // } //} //finally //{ // extractor.Close(); //} }
public void TestRemoveHyperlink() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell1 = row.CreateCell(1); XSSFHyperlink link1 = new XSSFHyperlink(HyperlinkType.Url); cell1.Hyperlink = (/*setter*/ link1); Assert.IsNotNull(cell1.Hyperlink); cell1.RemoveHyperlink(); Assert.IsNull(cell1.Hyperlink); ICell cell2 = row.CreateCell(0); XSSFHyperlink link2 = new XSSFHyperlink(HyperlinkType.Url); cell2.Hyperlink = (/*setter*/ link2); Assert.IsNotNull(cell2.Hyperlink); cell2.Hyperlink = (/*setter*/ null); Assert.IsNull(cell2.Hyperlink); XSSFTestDataSamples.WriteOutAndReadBack(wb); }
public void TestReadWrite() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx"); SharedStringsTable sst1 = wb.GetSharedStringSource(); //Serialize, read back and compare with the original SharedStringsTable sst2 = XSSFTestDataSamples.WriteOutAndReadBack(wb).GetSharedStringSource(); Assert.AreEqual(sst1.Count, sst2.Count); Assert.AreEqual(sst1.UniqueCount, sst2.UniqueCount); IList <CT_Rst> items1 = sst1.Items; IList <CT_Rst> items2 = sst2.Items; Assert.AreEqual(items1.Count, items2.Count); for (int i = 0; i < items1.Count; i++) { CT_Rst st1 = items1[i]; CT_Rst st2 = items2[i]; Assert.AreEqual(st1.ToString(), st2.ToString()); } Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb)); }
public void BasicReadWriteRead() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref-56737.xlsx"); IName name = wb.ExternalLinksTable[0].DefinedNames[(1)]; name.NameName = (/*setter*/ "Testing"); name.RefersToFormula = (/*setter*/ "$A$1"); wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; Assert.AreEqual(1, wb.ExternalLinksTable.Count); ExternalLinksTable links = wb.ExternalLinksTable[(0)]; name = links.DefinedNames[(0)]; Assert.AreEqual("NR_Global_B2", name.NameName); Assert.AreEqual(-1, name.SheetIndex); Assert.AreEqual(null, name.SheetName); Assert.AreEqual("'Defines'!$B$2", name.RefersToFormula); name = links.DefinedNames[(1)]; Assert.AreEqual("Testing", name.NameName); Assert.AreEqual(1, name.SheetIndex); Assert.AreEqual("Defines", name.SheetName); Assert.AreEqual("$A$1", name.RefersToFormula); }
public void Bug47813() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("47813.xlsx"); Assert.AreEqual(3, wb.NumberOfSheets); Assert.IsNotNull(wb.GetCalculationChain()); Assert.AreEqual("Numbers", wb.GetSheetName(0)); //the second sheet is of type 'chartsheet' Assert.AreEqual("Chart", wb.GetSheetName(1)); Assert.IsTrue(wb.GetSheetAt(1) is XSSFChartSheet); Assert.AreEqual("SomeJunk", wb.GetSheetName(2)); wb.RemoveSheetAt(2); Assert.AreEqual(2, wb.NumberOfSheets); Assert.IsNull(wb.GetCalculationChain()); wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(2, wb.NumberOfSheets); Assert.IsNull(wb.GetCalculationChain()); Assert.AreEqual("Numbers", wb.GetSheetName(0)); Assert.AreEqual("Chart", wb.GetSheetName(1)); }
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)); } } } } } } }
public void TestCloneStyleSameWB() { XSSFWorkbook wb = new XSSFWorkbook(); Assert.AreEqual(1, wb.NumberOfFonts); XSSFFont fnt = (XSSFFont)wb.CreateFont(); fnt.FontName = ("TestingFont"); Assert.AreEqual(2, wb.NumberOfFonts); XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle(); orig.Alignment = (HorizontalAlignment.Right); orig.SetFont(fnt); orig.DataFormat = (short)18; Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment); Assert.AreEqual(fnt, orig.GetFont()); Assert.AreEqual(18, orig.DataFormat); XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle(); Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment); Assert.AreNotEqual(fnt, clone.GetFont()); Assert.AreNotEqual(18, clone.DataFormat); clone.CloneStyleFrom(orig); Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment); Assert.AreEqual(fnt, clone.GetFont()); Assert.AreEqual(18, clone.DataFormat); Assert.AreEqual(2, wb.NumberOfFonts); clone.Alignment = HorizontalAlignment.Left; clone.DataFormat = 17; Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment); Assert.AreEqual(18, orig.DataFormat); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb)); }
public void TestCloneStyleDiffWB() { XSSFWorkbook wbOrig = new XSSFWorkbook(); Assert.AreEqual(1, wbOrig.NumberOfFonts); Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count); XSSFFont fnt = (XSSFFont)wbOrig.CreateFont(); fnt.FontName = ("TestingFont"); Assert.AreEqual(2, wbOrig.NumberOfFonts); Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count); XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat(); fmt.GetFormat("MadeUpOne"); fmt.GetFormat("MadeUpTwo"); XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle(); orig.Alignment = (HorizontalAlignment.Right); orig.SetFont(fnt); orig.DataFormat = (fmt.GetFormat("Test##")); Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment); Assert.IsTrue(fnt == orig.GetFont()); Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat); Assert.AreEqual(2, wbOrig.NumberOfFonts); Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count); // Now a style on another workbook XSSFWorkbook wbClone = new XSSFWorkbook(); Assert.AreEqual(1, wbClone.NumberOfFonts); Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.AreEqual(1, wbClone.NumCellStyles); XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat(); XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle(); Assert.AreEqual(1, wbClone.NumberOfFonts); Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment); Assert.IsFalse("TestingFont" == clone.GetFont().FontName); clone.CloneStyleFrom(orig); Assert.AreEqual(2, wbClone.NumberOfFonts); Assert.AreEqual(2, wbClone.NumCellStyles); Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count); Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment); Assert.AreEqual("TestingFont", clone.GetFont().FontName); Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat); Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##")); // Save it and re-check XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone); Assert.AreEqual(2, wbReload.NumberOfFonts); Assert.AreEqual(2, wbReload.NumCellStyles); Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count); XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1); Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment); Assert.AreEqual("TestingFont", reload.GetFont().FontName); Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat); Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##")); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig)); Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone)); }
protected XSSFExcelExtractor GetExtractor(string sampleName) { return(new XSSFExcelExtractor(XSSFTestDataSamples.OpenSampleWorkbook(sampleName))); }
public void test58325_three() { check58325(XSSFTestDataSamples.OpenSampleWorkbook("58325_db.xlsx"), 3); }
public void Bug49702() { // First try with a new file XSSFWorkbook wb1 = new XSSFWorkbook(); // Should have one style Assert.AreEqual(1, wb1.NumCellStyles); wb1.GetCellStyleAt((short)0); try { wb1.GetCellStyleAt((short)1); Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Add another one ICellStyle cs = wb1.CreateCellStyle(); cs.DataFormat = ((short)11); // Re-check Assert.AreEqual(2, wb1.NumCellStyles); wb1.GetCellStyleAt((short)0); wb1.GetCellStyleAt((short)1); try { wb1.GetCellStyleAt((short)2); Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Save and reload XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb1); Assert.AreEqual(2, nwb.NumCellStyles); nwb.GetCellStyleAt((short)0); nwb.GetCellStyleAt((short)1); try { nwb.GetCellStyleAt((short)2); Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } // Now with an existing file XSSFWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx"); Assert.AreEqual(3, wb2.NumCellStyles); wb2.GetCellStyleAt((short)0); wb2.GetCellStyleAt((short)1); wb2.GetCellStyleAt((short)2); try { wb2.GetCellStyleAt((short)3); Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist"); } catch (ArgumentOutOfRangeException) { } wb2.Close(); wb1.Close(); nwb.Close(); }
public void TestLoadSave() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx"); ICreationHelper CreateHelper = workbook.GetCreationHelper(); Assert.AreEqual(3, workbook.NumberOfSheets); XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); // Check hyperlinks Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Write out, and check // Load up again, check all links still there XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.IsNotNull(wb2.GetSheetAt(0)); Assert.IsNotNull(wb2.GetSheetAt(1)); Assert.IsNotNull(wb2.GetSheetAt(2)); sheet = (XSSFSheet)wb2.GetSheetAt(0); // Check hyperlinks again Assert.AreEqual(4, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); // Add one more, and re-check IRow r17 = sheet.CreateRow(17); ICell r17c = r17.CreateCell(2); IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url); hyperlink.Address = ("http://poi.apache.org/spreadsheet/"); hyperlink.Label = "POI SS Link"; r17c.Hyperlink = (hyperlink); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); // Save and re-load once more XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2); Assert.AreEqual(3, wb3.NumberOfSheets); Assert.IsNotNull(wb3.GetSheetAt(0)); Assert.IsNotNull(wb3.GetSheetAt(1)); Assert.IsNotNull(wb3.GetSheetAt(2)); sheet = (XSSFSheet)wb3.GetSheetAt(0); Assert.AreEqual(5, sheet.NumHyperlinks); doTestHyperlinkContents(sheet); Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(17).GetCell(2).Hyperlink.Type); Assert.AreEqual("POI SS Link", sheet.GetRow(17).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/spreadsheet/", sheet.GetRow(17).GetCell(2).Hyperlink.Address); }
public void TestAddNewTextParagraphWithRTS() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet; XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String"); XSSFFont font = wb1.CreateFont() as XSSFFont; font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255))); font.FontName = ("Arial"); rt.ApplyFont(font); XSSFFont midfont = wb1.CreateFont() as XSSFFont; midfont.SetColor(new XSSFColor(Color.FromRgb(0, 255, 0))); rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font XSSFTextParagraph para = shape.AddNewTextParagraph(rt); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook; wb1.Close(); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = sshape.TextParagraphs; Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that. List <XSSFTextRun> runs = para.TextRuns; Assert.AreEqual(3, runs.Count); // first run properties Assert.AreEqual("Test ", runs[0].Text); Assert.AreEqual("Arial", runs[0].FontFamily); var clr = runs[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); // second run properties Assert.AreEqual("Rich Text", runs[1].Text); Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily); clr = runs[1].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); // third run properties Assert.AreEqual(" String", runs[2].Text); Assert.AreEqual("Arial", runs[2].FontFamily); clr = runs[2].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb2); wb2.Close(); }
public void ThemedAndNonThemedColours() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; XSSFCellStyle style; XSSFColor color; XSSFCell cell; String[] names = { "White", "Black", "Grey", "Dark Blue", "Blue", "Red", "Green" }; String[] explicitFHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0070C0", "FFFF0000", "FF00B050" }; String[] explicitBHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0000FF", "FFFF0000", "FF00FF00" }; Assert.AreEqual(7, names.Length); // Check the non-CF colours in Columns A, B, C and E for (int rn = 1; rn < 8; rn++) { int idx = rn - 1; XSSFRow row = sheet.GetRow(rn) as XSSFRow; Assert.IsNotNull(row, "Missing row " + rn); // Theme cells come first XSSFCell themeCell = row.GetCell(0) as XSSFCell; ThemeElement themeElem = ThemeElement.ById(idx); assertCellContents(themeElem.name, themeCell); // Sanity check names assertCellContents(names[idx], row.GetCell(1)); assertCellContents(names[idx], row.GetCell(2)); assertCellContents(names[idx], row.GetCell(4)); // Check the colours // A: Theme Based, Foreground style = themeCell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); Assert.AreEqual(idx, color.Theme); Assert.AreEqual(rgbExpected[idx], HexDump.EncodeHexString(color.RGB)); // B: Theme Based, Foreground cell = row.GetCell(1) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); // TODO Fix the grey theme color in Column B if (idx != 2) { Assert.AreEqual(true, color.IsThemed); Assert.AreEqual(idx, color.Theme); Assert.AreEqual(rgbExpected[idx], HexDump.EncodeHexString(color.RGB)); } else { Assert.AreEqual(1, color.Theme); Assert.AreEqual(0.50, color.Tint, 0.001); } // C: Explicit, Foreground cell = row.GetCell(2) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(explicitFHexes[idx], color.ARGBHex); // E: Explicit Background, Foreground all Black cell = row.GetCell(4) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); Assert.AreEqual("FF000000", color.ARGBHex); color = style.FillForegroundXSSFColor; Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(explicitBHexes[idx], color.ARGBHex); color = style.FillBackgroundColorColor as XSSFColor; Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(null, color.ARGBHex); } // Check the CF colours // TODO }
public void MultiSheetReferencesHSSFandXSSF() { IWorkbook[] wbs = new IWorkbook[] { HSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xls"), XSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xlsx") }; foreach (IWorkbook wb in wbs) { ISheet s1 = wb.GetSheetAt(0); Ptg[] ptgs; // Check the contents ICell sumF = s1.GetRow(2).GetCell(0); Assert.IsNotNull(sumF); Assert.AreEqual("SUM(Sheet1:Sheet3!A1)", sumF.CellFormula); ICell avgF = s1.GetRow(2).GetCell(1); Assert.IsNotNull(avgF); Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1)", avgF.CellFormula); ICell countAF = s1.GetRow(2).GetCell(2); Assert.IsNotNull(countAF); Assert.AreEqual("COUNTA(Sheet1:Sheet3!C1)", countAF.CellFormula); ICell maxF = s1.GetRow(4).GetCell(1); Assert.IsNotNull(maxF); Assert.AreEqual("MAX(Sheet1:Sheet3!A$1)", maxF.CellFormula); ICell sumFA = s1.GetRow(2).GetCell(7); Assert.IsNotNull(sumFA); Assert.AreEqual("SUM(Sheet1:Sheet3!A1:B2)", sumFA.CellFormula); ICell avgFA = s1.GetRow(2).GetCell(8); Assert.IsNotNull(avgFA); Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.CellFormula); ICell maxFA = s1.GetRow(4).GetCell(8); Assert.IsNotNull(maxFA); Assert.AreEqual("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.CellFormula); ICell countFA = s1.GetRow(5).GetCell(8); Assert.IsNotNull(countFA); Assert.AreEqual("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.CellFormula); // Create a formula Parser IFormulaParsingWorkbook fpb = null; if (wb is HSSFWorkbook) { fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb); } else { fpb = XSSFEvaluationWorkbook.Create((XSSFWorkbook)wb); } // Check things parse as expected: // SUM to one cell over 3 workbooks, relative reference ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!A1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb)); // MAX to one cell over 3 workbooks, absolute row reference ptgs = Parse(fpb, "MAX(Sheet1:Sheet3!A$1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!A$1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MAX", ToFormulaString(ptgs[1], fpb)); // MIN to one cell over 3 workbooks, absolute reference ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); } Assert.AreEqual("Sheet1:Sheet3!$A$1", ToFormulaString(ptgs[0], fpb)); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb)); // SUM to a range of cells over 3 workbooks ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); } Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!A1:B2"); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb)); // MIN to a range of cells over 3 workbooks, absolute reference ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)"); Assert.AreEqual(2, ptgs.Length); if (wb is HSSFWorkbook) { Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType()); } else { Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); } Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!$A$1:$B$2"); Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType()); Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb)); // Check we can round-trip - try to Set a new one to a new single cell ICell newF = s1.GetRow(0).CreateCell(10, CellType.Formula); newF.CellFormula = (/*setter*/ "SUM(Sheet2:Sheet3!A1)"); Assert.AreEqual("SUM(Sheet2:Sheet3!A1)", newF.CellFormula); // Check we can round-trip - try to Set a new one to a cell range newF = s1.GetRow(0).CreateCell(11, CellType.Formula); newF.CellFormula = (/*setter*/ "MIN(Sheet1:Sheet2!A1:B2)"); Assert.AreEqual("MIN(Sheet1:Sheet2!A1:B2)", newF.CellFormula); } }
public void TestThemesTableColors(){ XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile); String rgbExpected[] = {
public void ParseStructuredReferences() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx"); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; /* * The following cases are tested (copied from FormulaParser.parseStructuredReference) * 1 Table1[col] * 2 Table1[[#Totals],[col]] * 3 Table1[#Totals] * 4 Table1[#All] * 5 Table1[#Data] * 6 Table1[#Headers] * 7 Table1[#Totals] * 8 Table1[#This Row] * 9 Table1[[#All],[col]] * 10 Table1[[#Headers],[col]] * 11 Table1[[#Totals],[col]] * 12 Table1[[#All],[col1]:[col2]] * 13 Table1[[#Data],[col1]:[col2]] * 14 Table1[[#Headers],[col1]:[col2]] * 15 Table1[[#Totals],[col1]:[col2]] * 16 Table1[[#Headers],[#Data],[col2]] * 17 Table1[[#This Row], [col1]] * 18 Table1[ [col1]:[col2] ] */ String tbl = "\\_Prime.1"; String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]"; ////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs //////// ptgs = Parse(fpb, "SUM(" + tbl + "[calc='#*'#])"); Assert.AreEqual(2, ptgs.Length); // Area3DPxg [sheet=Table ! A2:A7] Assert.IsTrue(ptgs[0] is Area3DPxg); Area3DPxg ptg0 = (Area3DPxg)ptgs[0]; Assert.AreEqual("Table", ptg0.SheetName); Assert.AreEqual("A2:A7", ptg0.Format2DRefAsString()); // Note: structured references are evaluated and resolved to regular 3D area references. Assert.AreEqual("Table!A2:A7", ptg0.ToFormulaString()); // AttrPtg [sum ] Assert.IsTrue(ptgs[1] is AttrPtg); AttrPtg ptg1 = (AttrPtg)ptgs[1]; Assert.IsTrue(ptg1.IsSum); ////// Case 1: Evaluate "Table1[col]" //////// ptgs = Parse(fpb, tbl + "[Name]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!B2:B7", ptgs[0].ToFormulaString(), "Table1[col]"); ////// Case 2: Evaluate "Table1[[#Totals],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Totals],[col]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason); ////// Case 3: Evaluate "Table1[#Totals]" //////// ptgs = Parse(fpb, tbl + "[#Totals]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason); ////// Case 4: Evaluate "Table1[#All]" //////// ptgs = Parse(fpb, tbl + "[#All]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!A1:C7", ptgs[0].ToFormulaString(), "Table1[#All]"); ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) //////// ptgs = Parse(fpb, tbl + "[#Data]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!A2:C7", ptgs[0].ToFormulaString(), "Table1[#Data]"); ////// Case 6: Evaluate "Table1[#Headers]" //////// ptgs = Parse(fpb, tbl + "[#Headers]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!A1:C1", ptgs[0].ToFormulaString(), "Table1[#Headers]"); ////// Case 7: Evaluate "Table1[#Totals]" //////// ptgs = Parse(fpb, tbl + "[#Totals]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason); ////// Case 8: Evaluate "Table1[#This Row]" //////// ptgs = Parse(fpb, tbl + "[#This Row]", 2); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString(), "Table1[#This Row]"); ////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") //////// ptgs = Parse(fpb, tbl + "[@]", 2); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString()); ////// Evaluate "Table1[#This Row]" when rowIndex is outside Table //////// ptgs = Parse(fpb, tbl + "[#This Row]", 10); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[#This Row]"); ////// Evaluate "Table1[@]" when rowIndex is outside Table //////// ptgs = Parse(fpb, tbl + "[@]", 10); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[@]"); ////// Evaluate "Table1[[#Data],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Data], [Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!C2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]]"); ////// Case 9: Evaluate "Table1[[#All],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#All], [Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col]]"); ////// Case 10: Evaluate "Table1[[#Headers],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Headers], [Number]]"); Assert.AreEqual(1, ptgs.Length); // also acceptable: Table1!B1 Assert.AreEqual("Table!C1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col]]"); ////// Case 11: Evaluate "Table1[[#Totals],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Totals],[Name]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason); ////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#All], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!B1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col1]:[col2]]"); ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#Data], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]:[col2]]"); ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#Headers], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!B1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col1]:[col2]]"); ////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#Totals], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason); ////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Headers],[#Data],[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#Headers],[#Data],[col]]"); ////// Case 17: Evaluate "Table1[[#This Row], [col1]]" //////// ptgs = Parse(fpb, tbl + "[[#This Row], [Number]]", 2); Assert.AreEqual(1, ptgs.Length); // also acceptable: Table!C3 Assert.AreEqual("Table!C3:C3", ptgs[0].ToFormulaString(), "Table1[[#This Row], [col1]]"); ////// Case 18: Evaluate "Table1[[col]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[col]:[col2]]"); wb.Close(); }
return new XSSFEventBasedExcelExtractor(XSSFTestDataSamples. openSamplePackage(sampleName));
public void TestAddBulletParagraphs() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet; XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 10, 20)); String paraString1 = "A normal paragraph"; String paraString2 = "First bullet"; String paraString3 = "Second bullet (level 1)"; String paraString4 = "Third bullet"; String paraString5 = "Another normal paragraph"; String paraString6 = "First numbered bullet"; String paraString7 = "Second bullet (level 1)"; String paraString8 = "Third bullet (level 1)"; String paraString9 = "Fourth bullet (level 1)"; String paraString10 = "Fifth Bullet"; XSSFTextParagraph para = shape.AddNewTextParagraph(paraString1); para = shape.AddNewTextParagraph(paraString2); para.SetBullet(true); para = shape.AddNewTextParagraph(paraString3); para.SetBullet(true); para.Level = (1); para = shape.AddNewTextParagraph(paraString4); para.SetBullet(true); para = shape.AddNewTextParagraph(paraString5); para = shape.AddNewTextParagraph(paraString6); para.SetBullet(ListAutoNumber.ARABIC_PERIOD); para = shape.AddNewTextParagraph(paraString7); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString8); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(""); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString9); para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3); para.Level = (1); para = shape.AddNewTextParagraph(paraString10); para.SetBullet(ListAutoNumber.ARABIC_PERIOD); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = sshape.TextParagraphs; Assert.AreEqual(12, paras.Count); // this should be 12 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added to that StringBuilder builder = new StringBuilder(); builder.Append(paraString1); builder.Append("\n"); builder.Append("\u2022 "); builder.Append(paraString2); builder.Append("\n"); builder.Append("\t\u2022 "); builder.Append(paraString3); builder.Append("\n"); builder.Append("\u2022 "); builder.Append(paraString4); builder.Append("\n"); builder.Append(paraString5); builder.Append("\n"); builder.Append("1. "); builder.Append(paraString6); builder.Append("\n"); builder.Append("\t3. "); builder.Append(paraString7); builder.Append("\n"); builder.Append("\t4. "); builder.Append(paraString8); builder.Append("\n"); builder.Append("\t"); // should be empty builder.Append("\n"); builder.Append("\t5. "); builder.Append(paraString9); builder.Append("\n"); builder.Append("2. "); builder.Append(paraString10); Assert.AreEqual(builder.ToString(), sshape.Text); checkRewrite(wb2); wb2.Close(); }
public void TestAddValidations() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx"); ISheet sheet = workbook.GetSheetAt(0); List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations(); /** * For each validation type, there are two cells with the same validation. This Tests * application of a single validation defInition to multiple cells. * * For list ( 3 validations for explicit and 3 for formula ) * - one validation that allows blank. * - one that does not allow blank. * - one that does not show the drop down arrow. * = 2 * * For number validations ( integer/decimal and text length ) with 8 different types of operators. * = 50 * * = 52 ( Total ) */ Assert.AreEqual(52, dataValidations.Count); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH }; int[] SingleOperandOperatorTypes = new int[] { OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL, OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL, OperatorType.EQUAL, OperatorType.NOT_EQUAL }; int[] doubleOperandOperatorTypes = new int[] { OperatorType.BETWEEN, OperatorType.NOT_BETWEEN }; decimal value = (decimal)10, value2 = (decimal)20; double dvalue = (double)10.001, dvalue2 = (double)19.999; int lastRow = sheet.LastRowNum; int offset = lastRow + 3; int lastKnownNumValidations = dataValidations.Count; IRow row = sheet.CreateRow(offset++); ICell cell = row.CreateCell(0); IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" }); CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; row = sheet.CreateRow(offset++); cell = row.CreateCell(0); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex); ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT"); ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN"); ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL"); int rowNum = row.RowNum + 1; String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString(); IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula); dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList); SetOtherValidationParameters(dataValidation); sheet.AddValidationData(dataValidation); lastKnownNumValidations++; offset++; offset++; for (int i = 0; i < validationTypes.Length; i++) { int validationType = validationTypes[i]; offset = offset + 2; IRow row0 = sheet.CreateRow(offset++); ICell cell_10 = row0.CreateCell(0); cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length"); offset++; for (int j = 0; j < SingleOperandOperatorTypes.Length; j++) { int operatorType = SingleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); //For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells. IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null; cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null; ICell cell_13 = row1.CreateCell(3); cell_13.SetCellType(CellType.Numeric); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null); if (i == 0 && j == 0) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else if (i == 0 && j == 1) { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } else { cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } for (int j = 0; j < doubleOperandOperatorTypes.Length; j++) { int operatorType = doubleOperandOperatorTypes[j]; IRow row1 = sheet.CreateRow(offset++); cell_10 = row1.CreateCell(0); cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString()); ICell cell_11 = row1.CreateCell(1); ICell cell_21 = row1.CreateCell(2); ICell cell_13 = row1.CreateCell(3); ICell cell_14 = row1.CreateCell(4); String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString(); cell_13.SetCellType(CellType.Numeric); cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value); String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString(); cell_14.SetCellType(CellType.Numeric); cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2); //First create value based validation; IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex)); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); //Now create real formula based validation. String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString(); String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString(); constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2); cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex)); validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList); SetOtherValidationParameters(validation); sheet.AddValidationData(validation); Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count); } } workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); ISheet sheetAt = workbook.GetSheetAt(0); Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count); }
private static XSSFExcelExtractor GetExtractor(String sampleName) { return(new XSSFExcelExtractor(XSSFTestDataSamples.OpenSampleWorkbook(sampleName))); }
public void TestThemesTableColors() { // Load our two test workbooks XSSFWorkbook simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple); XSSFWorkbook complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); // Save and re-load them, to check for stability across that XSSFWorkbook simpleRS = XSSFTestDataSamples.WriteOutAndReadBack(simple) as XSSFWorkbook; XSSFWorkbook complexRS = XSSFTestDataSamples.WriteOutAndReadBack(complex) as XSSFWorkbook; // Fetch fresh copies to test with simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple); complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); // Files and descriptions Dictionary <String, XSSFWorkbook> workbooks = new Dictionary <String, XSSFWorkbook>(); workbooks.Add(testFileSimple, simple); workbooks.Add("Re-Saved_" + testFileSimple, simpleRS); workbooks.Add(testFileComplex, complex); workbooks.Add("Re-Saved_" + testFileComplex, complexRS); // Sanity check //Assert.AreEqual(rgbExpected.Length, rgbExpected.Length); // For offline testing bool createFiles = false; // Check each workbook in turn, and verify that the colours // for the theme-applied cells in Column A are correct foreach (String whatWorkbook in workbooks.Keys) { XSSFWorkbook workbook = workbooks[whatWorkbook]; XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet; int startRN = 0; if (whatWorkbook.EndsWith(testFileComplex)) { startRN++; } for (int rn = startRN; rn < rgbExpected.Length + startRN; rn++) { XSSFRow row = sheet.GetRow(rn) as XSSFRow; Assert.IsNotNull(row, "Missing row " + rn + " in " + whatWorkbook); String ref1 = (new CellReference(rn, 0)).FormatAsString(); XSSFCell cell = row.GetCell(0) as XSSFCell; Assert.IsNotNull(cell, "Missing cell " + ref1 + " in " + whatWorkbook); int expectedThemeIdx = rn - startRN; ThemeElement themeElem = ThemeElement.ById(expectedThemeIdx); Assert.AreEqual(themeElem.name.ToLower(), cell.StringCellValue, "Wrong theme at " + ref1 + " in " + whatWorkbook); // Fonts are theme-based in their colours XSSFFont font = (cell.CellStyle as XSSFCellStyle).GetFont(); CT_Color ctColor = font.GetCTFont().GetColorArray(0); Assert.IsNotNull(ctColor); Assert.AreEqual(true, ctColor.IsSetTheme()); Assert.AreEqual(themeElem.idx, ctColor.theme); // Get the colour, via the theme XSSFColor color = font.GetXSSFColor(); // Theme colours aren't tinted Assert.AreEqual(color.HasTint, false); // Check the RGB part (no tint) Assert.AreEqual(rgbExpected[expectedThemeIdx], HexDump.EncodeHexString(color.RGB), "Wrong theme colour " + themeElem.name + " on " + whatWorkbook); long themeIdx = font.GetCTFont().GetColorArray(0).theme; Assert.AreEqual(expectedThemeIdx, themeIdx, "Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook ); if (createFiles) { XSSFCellStyle cs = row.Sheet.Workbook.CreateCellStyle() as XSSFCellStyle; cs.SetFillForegroundColor(color); cs.FillPattern = FillPatternType.SolidForeground; row.CreateCell(1).CellStyle = (cs); } } if (createFiles) { FileStream fos = new FileStream("Generated_" + whatWorkbook, FileMode.Create, FileAccess.ReadWrite); workbook.Write(fos); fos.Close(); } } }
protected void SetUp() { XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("sheetProtection_not_protected.xlsx"); sheet = workbook.GetSheetAt(0); }
public void TestReadTextBoxParagraphs() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx"); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; //the sheet has one relationship and it is XSSFDrawing List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts; Assert.AreEqual(1, rels.Count); POIXMLDocumentPart.RelationPart rp = rels[0]; Assert.IsTrue(rp.DocumentPart is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart; //sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing Assert.AreSame(drawing, sheet.CreateDrawingPatriarch()); String drawingId = rp.Relationship.Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = textbox.TextParagraphs; Assert.AreEqual(3, paras.Count); Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox // check attributes of paragraphs Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign); Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign); Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign); var clr = paras[0].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 255, 0, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[1].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[2].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 0, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb); wb.Close(); }
public void TestRepeatingRowsAndColums() { // First Test that Setting RR&C for same sheet more than once only Creates a // single Print_Titles built-in record XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = (XSSFSheet)wb.CreateSheet("First Sheet"); sheet1.RepeatingRows = (null); sheet1.RepeatingColumns = (null); // Set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4")); sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A")); //sheet.CreateFreezePane(0, 3); } Assert.AreEqual(1, wb.NumberOfNames); IName nr1 = wb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); //remove the columns part sheet1.RepeatingColumns = (null); Assert.AreEqual("'First Sheet'!$1:$4", nr1.RefersToFormula); //revert sheet1.RepeatingColumns = (CellRangeAddress.ValueOf("A:A")); //remove the rows part sheet1.RepeatingRows = (null); Assert.AreEqual("'First Sheet'!$A:$A", nr1.RefersToFormula); //revert sheet1.RepeatingRows = (CellRangeAddress.ValueOf("1:4")); // Save and re-open IWorkbook nwb = XSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(1, nwb.NumberOfNames); nr1 = nwb.GetNameAt(0); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr1.NameName); Assert.AreEqual("'First Sheet'!$A:$A,'First Sheet'!$1:$4", nr1.RefersToFormula); // check that Setting RR&C on a second sheet causes a new Print_Titles built-in // name to be Created XSSFSheet sheet2 = (XSSFSheet)nwb.CreateSheet("SecondSheet"); sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:1")); sheet2.RepeatingColumns = (CellRangeAddress.ValueOf("B:C")); Assert.AreEqual(2, nwb.NumberOfNames); IName nr2 = nwb.GetNameAt(1); Assert.AreEqual(XSSFName.BUILTIN_PRINT_TITLE, nr2.NameName); Assert.AreEqual("SecondSheet!$B:$C,SecondSheet!$1:$1", nr2.RefersToFormula); sheet2.RepeatingRows = (null); sheet2.RepeatingColumns = (null); }
public void TestNew() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)wb1.CreateSheet(); //multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch(); Assert.AreSame(dr1, dr2); List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts; Assert.AreEqual(1, rels.Count); POIXMLDocumentPart.RelationPart rp = rels[0]; Assert.IsTrue(rp.DocumentPart is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart; String drawingId = rp.Relationship.Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); //XSSFClientAnchor anchor = new XSSFClientAnchor(); XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2)); c1.LineWidth = 2.5; c1.LineStyle = LineStyle.DashDotSys; XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5)); Assert.IsNotNull(c2); XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); c3.SetText(new XSSFRichTextString("Test String")); c3.SetFillColor(128, 128, 128); XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6)); XSSFRichTextString rt = new XSSFRichTextString("Test String"); rt.ApplyFont(0, 5, wb1.CreateFont()); rt.ApplyFont(5, 6, wb1.CreateFont()); c4.SetText(rt); c4.IsNoFill = (true); Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray()); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[(0)] is XSSFConnector); Assert.IsTrue(shapes[(1)] is XSSFShapeGroup); Assert.IsTrue(shapes[(2)] is XSSFSimpleShape); Assert.IsTrue(shapes[(3)] is XSSFSimpleShape); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook; wb1.Close(); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing; CT_Drawing ctDrawing = dr1.GetCTDrawing(); // Connector, shapes and text boxes are all two cell anchors Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray()); Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray()); Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray()); shapes = dr1.GetShapes(); Assert.AreEqual(4, shapes.Count); Assert.IsTrue(shapes[0] is XSSFConnector); Assert.IsTrue(shapes[1] is XSSFShapeGroup); Assert.IsTrue(shapes[2] is XSSFSimpleShape); Assert.IsTrue(shapes[3] is XSSFSimpleShape); // // Ensure it got the right namespaces //String xml = ctDrawing.ToString(); //Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\"")); //Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\"")); checkRewrite(wb2); wb2.Close(); }
public void TestGroupingTest() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GroupTest.xlsx"); Assert.AreEqual(31, wb.GetSheetAt(0).LastRowNum); // NOTE: This is currently based on current behavior of POI, somehow // what POI returns in the calls to collapsed/hidden is not fully matching // the examples in the spec or I did not fully understand how POI stores the data internally... CheckWorkbookGrouping(wb, new bool?[] { // 0-4 false, false, false, false, false, null, null, // 7-11 false, false, true, true, true, null, null, // 14-18 false, false, true, false, false, null, // 20-24 false, false, true, true, false, null, null, // 27-31 false, false, false, true, false }, new bool[] { // 0-4 false, false, false, false, false, false, false, // 7-11 true, true, true, true, false, false, false, // 14-18 true, true, false, false, false, false, // 20-24 true, true, true, false, false, false, false, // 27-31 true, true, true, true, false }, // outlineLevel new int[] { // 0-4 3, 3, 2, 1, 0, 0, 0, // 7-11 3, 3, 2, 1, 0, 0, 0, // 14-18 3, 3, 2, 1, 0, 0, // 20-24 3, 3, 2, 1, 0, 0, 0, // 27-31 3, 3, 2, 1, 0, } ); /* * Row: 0: Level: 3 Collapsed: false Hidden: false * Row: 1: Level: 3 Collapsed: false Hidden: false * Row: 2: Level: 2 Collapsed: false Hidden: false * Row: 3: Level: 1 Collapsed: false Hidden: false * Row: 4: Level: 0 Collapsed: false Hidden: false * Row: 7: Level: 3 Collapsed: false Hidden: true * Row: 8: Level: 3 Collapsed: false Hidden: true * Row: 9: Level: 2 Collapsed: true Hidden: true * Row: 10: Level: 1 Collapsed: true Hidden: true * Row: 11: Level: 0 Collapsed: true Hidden: false * Row: 14: Level: 3 Collapsed: false Hidden: true * Row: 15: Level: 3 Collapsed: false Hidden: true * Row: 16: Level: 2 Collapsed: true Hidden: false * Row: 17: Level: 1 Collapsed: false Hidden: false * Row: 18: Level: 0 Collapsed: false Hidden: false * Row: 20: Level: 3 Collapsed: false Hidden: true * Row: 21: Level: 3 Collapsed: false Hidden: true * Row: 22: Level: 2 Collapsed: true Hidden: true * Row: 23: Level: 1 Collapsed: true Hidden: false * Row: 24: Level: 0 Collapsed: false Hidden: false * Row: 27: Level: 3 Collapsed: false Hidden: true * Row: 28: Level: 3 Collapsed: false Hidden: true * Row: 29: Level: 2 Collapsed: false Hidden: true * Row: 30: Level: 1 Collapsed: true Hidden: true * Row: 31: Level: 0 Collapsed: true Hidden: false */ }
public void TestCustomProperties() { POIXMLDocument wb1 = new XSSFWorkbook(); CustomProperties customProps = wb1.GetProperties().CustomProperties; customProps.AddProperty("test-1", "string val"); customProps.AddProperty("test-2", 1974); customProps.AddProperty("test-3", 36.6); //Adding a duplicate try { customProps.AddProperty("test-3", 36.6); Assert.Fail("expected exception"); } catch (ArgumentException e) { Assert.AreEqual("A property with this name already exists in the custom properties", e.Message); } customProps.AddProperty("test-4", true); POIXMLDocument wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack((XSSFWorkbook)wb1); wb1.Close(); CT_CustomProperties ctProps = wb2.GetProperties().CustomProperties.GetUnderlyingProperties(); Assert.AreEqual(6, ctProps.sizeOfPropertyArray()); CT_Property p; p = ctProps.GetPropertyArray(0); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-1", p.name); Assert.AreEqual("string val", p.Item.ToString()); Assert.AreEqual(2, p.pid); p = ctProps.GetPropertyArray(1); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-2", p.name); Assert.AreEqual(1974, p.Item); Assert.AreEqual(3, p.pid); p = ctProps.GetPropertyArray(2); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-3", p.name); Assert.AreEqual(36.6, p.Item); Assert.AreEqual(4, p.pid); p = ctProps.GetPropertyArray(3); Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid); Assert.AreEqual("test-4", p.name); Assert.AreEqual(true, p.Item); Assert.AreEqual(5, p.pid); p = ctProps.GetPropertyArray(4); Assert.AreEqual("Generator", p.name); Assert.AreEqual("NPOI", p.Item); Assert.AreEqual(6, p.pid); //p = ctProps.GetPropertyArray(5); //Assert.AreEqual("Generator Version", p.name); //Assert.AreEqual("2.0.9", p.Item); //Assert.AreEqual(7, p.pid); wb2.Close(); }
public void test58325_one() { check58325(XSSFTestDataSamples.OpenSampleWorkbook("58325_lt.xlsx"), 1); }
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()); } } }
// Disabled because shift rows is not yet implemented for SXSSFWorkbook public void disabled_testBug53798XLSXStream() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53798_ShiftNegative_TMPL.xlsx"); FileInfo xlsOutput = TempFile.CreateTempFile("testBug53798", ".xlsx"); //bug53798Work(new SXSSFWorkbook(wb), xlsOutput); }