public void AddPivotCache() { XSSFWorkbook wb = new XSSFWorkbook(); try { CT_Workbook ctWb = wb.GetCTWorkbook(); CT_PivotCache pivotCache = wb.AddPivotCache("0"); //Ensures that pivotCaches is Initiated Assert.IsTrue(ctWb.IsSetPivotCaches()); Assert.AreSame(pivotCache, ctWb.pivotCaches.GetPivotCacheArray(0)); Assert.AreEqual("0", pivotCache.id); } finally { wb.Close(); } }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFTextParagraph para = shape.AddNewTextParagraph(); para.AddNewTextRun().Text=("Line 1"); List<XSSFTextRun> Runs = para.TextRuns; Assert.AreEqual(1, Runs.Count); XSSFTextRun run = Runs[0]; Assert.AreEqual("Line 1", run.Text); //Assert.IsNotNull(run.ParentParagraph); //Assert.IsNotNull(run.XmlObject); Assert.IsNotNull(run.GetRPr()); Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor); Color color = Color.FromArgb(0, 255, 255); run.FontColor = (/*setter*/color); Assert.AreEqual(color, run.FontColor); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/12.32); Assert.AreEqual(12.32, run.FontSize, 0.01); run.FontSize = (/*setter*/-1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/-1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); try { run.FontSize = (/*setter*/0.9); Assert.Fail("Should fail"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.Contains("0.9")); } Assert.AreEqual(11.0, run.FontSize, 0.01); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/12.31); Assert.AreEqual(12.31, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", unchecked((byte)-1), (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, unchecked((byte)-1), false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, true); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFont("Arial"); Assert.AreEqual("Arial", run.FontFamily); Assert.AreEqual((byte)0, run.PitchAndFamily); run.SetFont(null); Assert.AreEqual((byte)0, run.PitchAndFamily); Assert.IsFalse(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/true); Assert.IsTrue(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/false); Assert.IsFalse(run.IsStrikethrough); Assert.IsFalse(run.IsSuperscript); run.IsSuperscript = (/*setter*/true); Assert.IsTrue(run.IsSuperscript); run.IsSuperscript = (/*setter*/false); Assert.IsFalse(run.IsSuperscript); Assert.IsFalse(run.IsSubscript); run.IsSubscript = (/*setter*/true); Assert.IsTrue(run.IsSubscript); run.IsSubscript = (/*setter*/false); Assert.IsFalse(run.IsSubscript); Assert.AreEqual(TextCap.NONE, run.TextCap); Assert.IsFalse(run.IsBold); run.IsBold = (/*setter*/true); Assert.IsTrue(run.IsBold); run.IsBold = (/*setter*/false); Assert.IsFalse(run.IsBold); Assert.IsFalse(run.IsItalic); run.IsItalic = (/*setter*/true); Assert.IsTrue(run.IsItalic); run.IsItalic = (/*setter*/false); Assert.IsFalse(run.IsItalic); Assert.IsFalse(run.IsUnderline); run.IsUnderline = (/*setter*/true); Assert.IsTrue(run.IsUnderline); run.IsUnderline = (/*setter*/false); Assert.IsFalse(run.IsUnderline); Assert.IsNotNull(run.ToString()); } finally { wb.Close(); } }
public void XSSFTextParagraph_() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/"Arial"); rt.ApplyFont(font); shape.SetText(rt); List<XSSFTextParagraph> paras = shape.TextParagraphs; Assert.AreEqual(1, paras.Count); XSSFTextParagraph text = paras[(0)]; Assert.AreEqual("Test String", text.Text); Assert.IsFalse(text.IsBullet); Assert.IsNotNull(text.GetXmlObject()); Assert.AreEqual(shape.GetCTShape(), text.ParentShape); Assert.IsNotNull(text.GetEnumerator()); Assert.IsNotNull(text.AddLineBreak()); Assert.IsNotNull(text.TextRuns); Assert.AreEqual(2, text.TextRuns.Count); text.AddNewTextRun(); Assert.AreEqual(3, text.TextRuns.Count); Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = (/*setter*/TextAlign.CENTER); Assert.AreEqual(TextAlign.CENTER, text.TextAlign); text.TextAlign = (/*setter*/TextAlign.RIGHT); Assert.AreEqual(TextAlign.RIGHT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextFontAlign = (/*setter*/TextFontAlign.BASELINE); Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = (/*setter*/TextFontAlign.BOTTOM); Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); Assert.IsNull(text.BulletFont); text.BulletFont = (/*setter*/"Arial"); Assert.AreEqual("Arial", text.BulletFont); Assert.IsNull(text.BulletCharacter); text.BulletCharacter = (/*setter*/"."); Assert.AreEqual(".", text.BulletCharacter); //Assert.IsNull(text.BulletFontColor); Assert.AreEqual(Color.Empty, text.BulletFontColor); text.BulletFontColor = (/*setter*/color); Assert.AreEqual(color, text.BulletFontColor); Assert.AreEqual(100.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/-9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/-9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/-9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/2.0); Assert.AreEqual(2.0, text.Indent, 0.01); text.Indent = (/*setter*/-1.0); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/-1.0); Assert.AreEqual(0.0, text.Indent, 0.01); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/3.0); Assert.AreEqual(3.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/-1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/-1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/4.5); Assert.AreEqual(4.5, text.RightMargin, 0.01); text.RightMargin = (/*setter*/-1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/-1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); Assert.AreEqual(0.0, text.DefaultTabSize, 0.01); Assert.AreEqual(0.0, text.GetTabStop(0), 0.01); text.AddTabStop(3.14); Assert.AreEqual(3.14, text.GetTabStop(0), 0.01); Assert.AreEqual(100.0, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/3.15); Assert.AreEqual(3.15, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/-2.13); Assert.AreEqual(-2.13, text.LineSpacing, 0.01); Assert.AreEqual(0.0, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/3.17); Assert.AreEqual(3.17, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/-4.7); Assert.AreEqual(-4.7, text.SpaceBefore, 0.01); Assert.AreEqual(0.0, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/6.17); Assert.AreEqual(6.17, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/-8.17); Assert.AreEqual(-8.17, text.SpaceAfter, 0.01); Assert.AreEqual(0, text.Level); text.Level = (/*setter*/1); Assert.AreEqual(1, text.Level); text.Level = (/*setter*/4); Assert.AreEqual(4, text.Level); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (false); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (true); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); Assert.AreEqual(0, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); //Assert.AreEqual(0, text.BulletAutoNumberStart); //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1; Assert.AreEqual(1, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); Assert.AreEqual(10, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme); Assert.IsNotNull(text.ToString()); new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape()); } finally { wb.Close(); } }
public void IncrementSheetId() { XSSFWorkbook wb = new XSSFWorkbook(); try { int sheetId = (int)(wb.CreateSheet() as XSSFSheet).sheet.sheetId; Assert.AreEqual(1, sheetId); sheetId = (int)(wb.CreateSheet() as XSSFSheet).sheet.sheetId; Assert.AreEqual(2, sheetId); //test file with gaps in the sheetId sequence XSSFWorkbook wbBack = XSSFTestDataSamples.OpenSampleWorkbook("47089.xlsm"); try { int lastSheetId = (int)(wbBack.GetSheetAt(wbBack.NumberOfSheets - 1) as XSSFSheet).sheet.sheetId; sheetId = (int)(wbBack.CreateSheet() as XSSFSheet).sheet.sheetId; Assert.AreEqual(lastSheetId + 1, sheetId); } finally { wbBack.Close(); } } finally { wb.Close(); } }
public void Test49941() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet s = wb.CreateSheet() as XSSFSheet; XSSFRow r = s.CreateRow(0) as XSSFRow; XSSFCell c = r.CreateCell(0) as XSSFCell; // First without fonts c.SetCellValue( new XSSFRichTextString(" with spaces ") ); Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString()); Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray()); Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT()); // Should have the preserve Set //Assert.AreEqual( // 1, // (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength() //); //Assert.AreEqual( // "preserve", // (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue() //); // Save and check wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; s = wb.GetSheetAt(0) as XSSFSheet; r = s.GetRow(0) as XSSFRow; c = r.GetCell(0) as XSSFCell; Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString()); Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray()); Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT()); // Change the string c.SetCellValue( new XSSFRichTextString("hello world") ); Assert.AreEqual("hello world", c.RichStringCellValue.ToString()); // Won't have preserve //Assert.AreEqual( // 0, // c.RichStringCellValue.GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength() //); // Apply a font XSSFFont f = wb.CreateFont() as XSSFFont; f.IsBold = (true); c.RichStringCellValue.ApplyFont(0, 5, f); Assert.AreEqual("hello world", c.RichStringCellValue.ToString()); // Does need preserving on the 2nd part Assert.AreEqual(2, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray()); //Assert.AreEqual( // 0, // c.RichStringCellValue.GetCTRst().GetRArray(0).xgetT().GetDomNode().GetAttributes().GetLength() //); //Assert.AreEqual( // 1, // c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().GetLength() //); //Assert.AreEqual( // "preserve", // c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue() //); // Save and check wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; s = wb.GetSheetAt(0) as XSSFSheet; r = s.GetRow(0) as XSSFRow; c = r.GetCell(0) as XSSFCell; Assert.AreEqual("hello world", c.RichStringCellValue.ToString()); wb.Close(); }
public void TestBug55280() { XSSFWorkbook w = new XSSFWorkbook(); try { ISheet s = w.CreateSheet(); for (int row = 0; row < 5000; ++row) s.AddMergedRegion(new CellRangeAddress(row, row, 0, 3)); s.ShiftRows(0, 4999, 1); // takes a long time... } finally { w.Close(); } }
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("Table1[col]", "Table!B2:B7", ptgs[0].ToFormulaString()); ////// 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("Table1[#All]", "Table!A1:C7", ptgs[0].ToFormulaString()); ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) //////// ptgs = Parse(fpb, tbl + "[#Data]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[#Data]", "Table!A2:C7", ptgs[0].ToFormulaString()); ////// Case 6: Evaluate "Table1[#Headers]" //////// ptgs = Parse(fpb, tbl + "[#Headers]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[#Headers]", "Table!A1:C1", ptgs[0].ToFormulaString()); ////// 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("Table1[#This Row]", "Table!A3:C3", ptgs[0].ToFormulaString()); ////// 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("Table1[[#Data],[col]]", "Table!C2:C7", ptgs[0].ToFormulaString()); ////// Case 9: Evaluate "Table1[[#All],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#All], [Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[[#All],[col]]", "Table!C1:C7", ptgs[0].ToFormulaString()); ////// Case 10: Evaluate "Table1[[#Headers],[col]]" //////// ptgs = Parse(fpb, tbl + "[[#Headers], [Number]]"); Assert.AreEqual(1, ptgs.Length); // also acceptable: Table1!B1 Assert.AreEqual("Table1[[#Headers],[col]]", "Table!C1:C1", ptgs[0].ToFormulaString()); ////// 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("Table1[[#All],[col1]:[col2]]", "Table!B1:C7", ptgs[0].ToFormulaString()); ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#Data], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[[#Data],[col]:[col2]]", "Table!B2:C7", ptgs[0].ToFormulaString()); ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[#Headers], [Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[[#Headers],[col1]:[col2]]", "Table!B1:C1", ptgs[0].ToFormulaString()); ////// 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("Table1[[#Headers],[#Data],[col]]", "Table!C1:C7", ptgs[0].ToFormulaString()); ////// 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("Table1[[#This Row], [col1]]", "Table!C3:C3", ptgs[0].ToFormulaString()); ////// Case 18: Evaluate "Table1[[col]:[col2]]" //////// ptgs = Parse(fpb, tbl + "[[Name]:[Number]]"); Assert.AreEqual(1, ptgs.Length); Assert.AreEqual("Table1[[col]:[col2]]", "Table!B2:C7", ptgs[0].ToFormulaString()); wb.Close(); }
public void Bug56468() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCell cell = row.CreateCell(0) as XSSFCell; cell.SetCellValue("Hi"); sheet.RepeatingRows = (new CellRangeAddress(0, 0, 0, 0)); MemoryStream bos = new MemoryStream(8096); wb.Write(bos); byte[] firstSave = bos.ToArray(); //using (FileStream fs = new FileStream("d:\\save1.xlsx", FileMode.Create, FileAccess.ReadWrite)) //{ // fs.Write(firstSave, 0, firstSave.Length); // fs.Flush(); //} MemoryStream bos2 = new MemoryStream(8096); wb.Write(bos2); byte[] secondSave = bos2.ToArray(); //using (FileStream fs2 = new FileStream("d:\\save2.xlsx", FileMode.Create, FileAccess.ReadWrite)) //{ // fs2.Write(secondSave, 0, secondSave.Length); // fs2.Flush(); //} Assert.That(firstSave, new EqualConstraint(secondSave), "Had: \n" + Arrays.ToString(firstSave) + " and \n" + Arrays.ToString(secondSave)); wb.Close(); }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFTextParagraph para = shape.AddNewTextParagraph(); para.AddNewTextRun().Text = ("Line 1"); List <XSSFTextRun> Runs = para.TextRuns; Assert.AreEqual(1, Runs.Count); XSSFTextRun run = Runs[0]; Assert.AreEqual("Line 1", run.Text); //Assert.IsNotNull(run.ParentParagraph); //Assert.IsNotNull(run.XmlObject); Assert.IsNotNull(run.GetRPr()); Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor); Color color = Color.FromArgb(0, 255, 255); run.FontColor = (/*setter*/ color); Assert.AreEqual(color, run.FontColor); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/ 12.32); Assert.AreEqual(12.32, run.FontSize, 0.01); run.FontSize = (/*setter*/ -1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); run.FontSize = (/*setter*/ -1.0); Assert.AreEqual(11.0, run.FontSize, 0.01); try { run.FontSize = (/*setter*/ 0.9); Assert.Fail("Should fail"); } catch (ArgumentException e) { Assert.IsTrue(e.Message.Contains("0.9")); } Assert.AreEqual(11.0, run.FontSize, 0.01); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 12.31); Assert.AreEqual(12.31, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); run.CharacterSpacing = (/*setter*/ 0.0); Assert.AreEqual(0.0, run.CharacterSpacing, 0.01); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily("Arial", (byte)1, (byte)1, true); Assert.AreEqual("Arial", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFontFamily(null, (byte)1, (byte)1, false); Assert.AreEqual("Calibri", run.FontFamily); run.SetFont("Arial"); Assert.AreEqual("Arial", run.FontFamily); Assert.AreEqual((byte)0, run.PitchAndFamily); run.SetFont(null); Assert.AreEqual((byte)0, run.PitchAndFamily); Assert.IsFalse(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/ true); Assert.IsTrue(run.IsStrikethrough); run.IsStrikethrough = (/*setter*/ false); Assert.IsFalse(run.IsStrikethrough); Assert.IsFalse(run.IsSuperscript); run.IsSuperscript = (/*setter*/ true); Assert.IsTrue(run.IsSuperscript); run.IsSuperscript = (/*setter*/ false); Assert.IsFalse(run.IsSuperscript); Assert.IsFalse(run.IsSubscript); run.IsSubscript = (/*setter*/ true); Assert.IsTrue(run.IsSubscript); run.IsSubscript = (/*setter*/ false); Assert.IsFalse(run.IsSubscript); Assert.AreEqual(TextCap.NONE, run.TextCap); Assert.IsFalse(run.IsBold); run.IsBold = (/*setter*/ true); Assert.IsTrue(run.IsBold); run.IsBold = (/*setter*/ false); Assert.IsFalse(run.IsBold); Assert.IsFalse(run.IsItalic); run.IsItalic = (/*setter*/ true); Assert.IsTrue(run.IsItalic); run.IsItalic = (/*setter*/ false); Assert.IsFalse(run.IsItalic); Assert.IsFalse(run.IsUnderline); run.IsUnderline = (/*setter*/ true); Assert.IsTrue(run.IsUnderline); run.IsUnderline = (/*setter*/ false); Assert.IsFalse(run.IsUnderline); Assert.IsNotNull(run.ToString()); } finally { wb.Close(); } }
public void BasicParse() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); Ptg[] ptgs; ptgs = Parse(fpb, "ABC10"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs)); ptgs = Parse(fpb, "A500000"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs)); ptgs = Parse(fpb, "ABC500000"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs)); //highest allowed rows and column (XFD and 0x100000) ptgs = Parse(fpb, "XFD1048576"); Assert.AreEqual(1, ptgs.Length); Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs)); //column greater than XFD try { ptgs = Parse(fpb, "XFE10"); Assert.Fail("expected exception"); } catch (FormulaParseException e) { Assert.AreEqual("Specified named range 'XFE10' does not exist in the current workbook.", e.Message); } //row greater than 0x100000 try { ptgs = Parse(fpb, "XFD1048577"); Assert.Fail("expected exception"); } catch (FormulaParseException e) { Assert.AreEqual("Specified named range 'XFD1048577' does not exist in the current workbook.", e.Message); } // Formula referencing one cell ptgs = Parse(fpb, "ISEVEN(A1)"); Assert.AreEqual(3, ptgs.Length); Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(RefPtg), ptgs[1].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ptgs[2].GetType()); Assert.AreEqual("ISEVEN", ptgs[0].ToFormulaString()); Assert.AreEqual("A1", ptgs[1].ToFormulaString()); Assert.AreEqual("#external#", ptgs[2].ToFormulaString()); // Formula referencing an area ptgs = Parse(fpb, "SUM(A1:B3)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("A1:B3", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); // Formula referencing one cell in a different sheet ptgs = Parse(fpb, "SUM(Sheet1!A1)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("Sheet1!A1", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); // Formula referencing an area in a different sheet ptgs = Parse(fpb, "SUM(Sheet1!A1:B3)"); Assert.AreEqual(2, ptgs.Length); Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType()); Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType()); Assert.AreEqual("Sheet1!A1:B3", ptgs[0].ToFormulaString()); Assert.AreEqual("SUM", ptgs[1].ToFormulaString()); wb.Close(); }
public void TestSetAllowBlankToTrue() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDataValidation validation = CreateValidation(sheet); validation.GetCTDataValidation().allowBlank = (/*setter*/true); sheet.AddValidationData(validation); List<IDataValidation> dataValidations = sheet.GetDataValidations(); Assert.AreEqual(true, (dataValidations[0] as XSSFDataValidation).GetCTDataValidation().allowBlank); } finally { wb.Close(); } }
public void Test53965() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; List<IDataValidation> lst = sheet.GetDataValidations(); //<-- works Assert.AreEqual(0, lst.Count); //create the cell that will have the validation applied sheet.CreateRow(0).CreateCell(0); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500"); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList); sheet.AddValidationData(validation); // this line caused XmlValueOutOfRangeException , see Bugzilla 3965 lst = sheet.GetDataValidations(); Assert.AreEqual(1, lst.Count); } finally { wb.Close(); } }
public void Bug57880() { Console.WriteLine("long time test, run over 1 minute."); int numStyles = 33000; XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet s = wb.CreateSheet("TestSheet") as XSSFSheet; XSSFDataFormat fmt = wb.GetCreationHelper().CreateDataFormat() as XSSFDataFormat; for (int i = 1; i < numStyles; i++) { short df = fmt.GetFormat("test" + i); // Format indexes will be wrapped beyond 32,676 Assert.AreEqual(164 + i, df & 0xffff); // Create a style and use it XSSFCellStyle style = wb.CreateCellStyle() as XSSFCellStyle; Assert.AreEqual(i, style.UIndex); style.DataFormat = (/*setter*/df); XSSFCell c = s.CreateRow(i).CreateCell(0, CellType.Numeric) as XSSFCell; c.CellStyle = (/*setter*/style); c.SetCellValue(i); } //wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; // using temp file instead of ByteArrayOutputStream because of OOM in gump run FileInfo tmp = TempFile.CreateTempFile("poi-test", ".bug57880"); FileStream fos = new FileStream(tmp.FullName, FileMode.Create, FileAccess.ReadWrite); wb.Write(fos); fos.Close(); wb.Close(); fmt = null; s = null; wb = null; // System.gc(); wb = new XSSFWorkbook(tmp.FullName); fmt = wb.GetCreationHelper().CreateDataFormat() as XSSFDataFormat; s = wb.GetSheetAt(0) as XSSFSheet; for (int i = 1; i < numStyles; i++) { XSSFCellStyle style = wb.GetCellStyleAt((short)i) as XSSFCellStyle; Assert.IsNotNull(style); Assert.AreEqual(i, style.UIndex); Assert.AreEqual(164 + i, style.DataFormat & 0xffff); Assert.AreEqual("test" + i, style.GetDataFormatString()); } wb.Close(); tmp.Delete(); }
public void TestBug55843() { 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()-ROW(A$1))*12))"); CellValue Evaluate = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(Evaluate); Assert.AreEqual("12", Evaluate.FormatAsString()); cellA2.CellFormula = (/*setter*/"IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")"); CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(EvaluateN); Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString()); Assert.AreEqual("12", EvaluateN.FormatAsString()); } finally { wb.Close(); } }
public void TestBug55752() { IWorkbook wb = new XSSFWorkbook(); try { ISheet sheet = wb.CreateSheet("test"); for (int i = 0; i < 4; i++) { IRow row = sheet.CreateRow(i); for (int j = 0; j < 2; j++) { ICell cell = row.CreateCell(j); cell.CellStyle = (wb.CreateCellStyle()); } } // set content IRow row1 = sheet.GetRow(0); row1.GetCell(0).SetCellValue("AAA"); IRow row2 = sheet.GetRow(1); row2.GetCell(0).SetCellValue("BBB"); IRow row3 = sheet.GetRow(2); row3.GetCell(0).SetCellValue("CCC"); IRow row4 = sheet.GetRow(3); row4.GetCell(0).SetCellValue("DDD"); // merge cells CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1); sheet.AddMergedRegion(range1); CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1); sheet.AddMergedRegion(range2); CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1); sheet.AddMergedRegion(range3); Assert.AreEqual(0, range3.FirstColumn); Assert.AreEqual(1, range3.LastColumn); Assert.AreEqual(2, range3.LastRow); CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1); sheet.AddMergedRegion(range4); // set border RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb); row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin; row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin; ICell cell0 = CellUtil.GetCell(row3, 0); CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); ICell cell1 = CellUtil.GetCell(row3, 1); CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin); RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb); // write to file Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite); try { wb.Write(stream); } finally { stream.Close(); } } finally { wb.Close(); } }
public void TestListOfCustomProperties() { FileInfo inp = POIDataSamples.GetSpreadSheetInstance().GetFileInfo("ExcelWithAttachments.xlsm"); OPCPackage pkg = OPCPackage.Open(inp, PackageAccess.READ); XSSFWorkbook wb = new XSSFWorkbook(pkg); Assert.IsNotNull(wb.GetProperties()); Assert.IsNotNull(wb.GetProperties().CustomProperties); foreach (CT_Property prop in wb.GetProperties().CustomProperties.GetUnderlyingProperties().GetPropertyList()) { Assert.IsNotNull(prop); } wb.Close(); pkg.Close(); }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/ "Arial"); rt.ApplyFont(font); shape.SetText(/*setter*/ rt); Assert.IsNotNull(shape.GetCTShape()); Assert.IsNotNull(shape.GetEnumerator()); Assert.IsNotNull(XSSFSimpleShape.GetPrototype()); foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber))) { shape.TextParagraphs[(0)].SetBullet(nr); Assert.IsNotNull(shape.Text); } shape.TextParagraphs[(0)].SetBullet(false); Assert.IsNotNull(shape.Text); shape.SetText("testtext"); Assert.AreEqual("testtext", shape.Text); shape.SetText(new XSSFRichTextString()); Assert.AreEqual("null", shape.Text); shape.AddNewTextParagraph(); shape.AddNewTextParagraph("test-other-text"); shape.AddNewTextParagraph(new XSSFRichTextString("rtstring")); shape.AddNewTextParagraph(new XSSFRichTextString()); Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP); Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP); Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Bottom; Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.Top; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = (short)VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.STACKED); Assert.AreEqual(TextDirection.STACKED, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/ TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ 12.32); Assert.AreEqual(12.32, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ 12.31); Assert.AreEqual(12.31, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ 13.31); Assert.AreEqual(13.31, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.RightInset, 0.01); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ 23.31); Assert.AreEqual(23.31, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/ -1); Assert.AreEqual(3.6, shape.TopInset, 0.01); Assert.IsTrue(shape.WordWrap); shape.WordWrap = (/*setter*/ false); Assert.IsFalse(shape.WordWrap); shape.WordWrap = (/*setter*/ true); Assert.IsTrue(shape.WordWrap); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE); Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit); shape.TextAutofit = (/*setter*/ TextAutofit.NONE); Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit); Assert.AreEqual(5, shape.ShapeType); shape.ShapeType = (/*setter*/ 23); Assert.AreEqual(23, shape.ShapeType); // TODO: should this be supported? // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); Assert.IsNotNull(shape.GetShapeProperties()); } finally { wb.Close(); } }
public void TestBug56820_Formula2() { IWorkbook wb = new XSSFWorkbook(); try { IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); sh.CreateRow(0).CreateCell(0).SetCellValue(0.0d); ICell formulaCell1 = sh.CreateRow(1).CreateCell(0); ICell formulaCell2 = sh.CreateRow(2).CreateCell(0); formulaCell1.CellFormula = (/*setter*/FORMULA2); formulaCell2.CellFormula = (/*setter*/FORMULA2); double A1 = Evaluator.Evaluate(formulaCell1).NumberValue; double A2 = Evaluator.Evaluate(formulaCell2).NumberValue; //<-- FAILS EVALUATION Assert.AreEqual(2, A1, 0); Assert.AreEqual(4, A2, 0); } finally { wb.Close(); } }
public void Bug58245_XSSFSheetIterator() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet(); // ===================================================================== // Case 1: Existing code uses XSSFSheet for-each loop // ===================================================================== // Original code (no longer valid) /* * for (XSSFSheet sh : wb) { * sh.createRow(0); * } */ // Option A: foreach (XSSFSheet sh in wb) { sh.CreateRow(0); } // Option B (preferred for new code): foreach (ISheet sh in wb) { sh.CreateRow(0); } // ===================================================================== // Case 2: Existing code creates an iterator variable // ===================================================================== // Original code (no longer valid) /* * Iterator<XSSFSheet> it = wb.iterator(); * XSSFSheet sh = it.next(); * sh.createRow(0); */ // Option A: { IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>; XSSFSheet sh = it.Current; sh.CreateRow(0); } // Option B: { //IEnumerator<XSSFSheet> it = wb.XssfSheetIterator(); //XSSFSheet sh = it.Current; //sh.CreateRow(0); } // Option C (preferred for new code): { IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>; ISheet sh = it.Current; sh.CreateRow(0); } wb.Close(); }
public void Test48877() { String text = "Use \n with word wrap on to create a new line.\n" + "This line finishes with two trailing spaces. "; XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; IFont font1 = wb.CreateFont(); font1.Color = ((short)20); IFont font2 = wb.CreateFont(); font2.Color = (short)(FontColor.Red); IFont font3 = wb.GetFontAt((short)0); XSSFRow row = sheet.CreateRow(2) as XSSFRow; XSSFCell cell = row.CreateCell(2) as XSSFCell; XSSFRichTextString richTextString = wb.GetCreationHelper().CreateRichTextString(text) as XSSFRichTextString; // Check the text has the newline Assert.AreEqual(text, richTextString.String); // Apply the font richTextString.ApplyFont(font3); richTextString.ApplyFont(0, 3, font1); cell.SetCellValue(richTextString); // To enable newlines you need Set a cell styles with wrap=true ICellStyle cs = wb.CreateCellStyle(); cs.WrapText = (true); cell.CellStyle = (cs); // Check the text has the Assert.AreEqual(text, cell.StringCellValue); // Save the file and re-read it wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; row = sheet.GetRow(2) as XSSFRow; cell = row.GetCell(2) as XSSFCell; Assert.AreEqual(text, cell.StringCellValue); // Now add a 2nd, and check again int fontAt = text.IndexOf("\n", 6); cell.RichStringCellValue.ApplyFont(10, fontAt + 1, font2); Assert.AreEqual(text, cell.StringCellValue); Assert.AreEqual(4, (cell.RichStringCellValue as XSSFRichTextString).NumFormattingRuns); Assert.AreEqual("Use", (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[0].t); String r3 = (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[2].t; Assert.AreEqual("line.\n", r3.Substring(r3.Length - 6)); // Save and re-check wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; sheet = wb.GetSheetAt(0) as XSSFSheet; row = sheet.GetRow(2) as XSSFRow; cell = row.GetCell(2) as XSSFCell; Assert.AreEqual(text, cell.StringCellValue); wb.Close(); // FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx"); // wb.Write(out); // out.Close(); }
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 TestSetColor() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IRow row = sheet.CreateRow(0); //CreationHelper ch = wb.GetCreationHelper(); IDataFormat format = wb.CreateDataFormat(); ICell cell = row.CreateCell(1); cell.SetCellValue("somEvalue"); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/format.GetFormat("###0")); cellStyle.FillBackgroundColor = (/*setter*/IndexedColors.DarkBlue.Index); cellStyle.FillForegroundColor = (/*setter*/IndexedColors.DarkBlue.Index); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.Alignment = HorizontalAlignment.Right; cellStyle.VerticalAlignment = VerticalAlignment.Top; cell.CellStyle = (/*setter*/cellStyle); /*OutputStream stream = new FileOutputStream("C:\\temp\\CellColor.xlsx"); try { wb.Write(stream); } finally { stream.Close(); }*/ IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb); ICell cellBack = wbBack.GetSheetAt(0).GetRow(0).GetCell(1); Assert.IsNotNull(cellBack); ICellStyle styleBack = cellBack.CellStyle; Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillBackgroundColor); Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillForegroundColor); Assert.AreEqual(HorizontalAlignment.Right, styleBack.Alignment); Assert.AreEqual(VerticalAlignment.Top, styleBack.VerticalAlignment); Assert.AreEqual(FillPattern.SolidForeground, styleBack.FillPattern); wbBack.Close(); wb.Close(); }
public void SaveLoadNew() { XSSFWorkbook wb1 = new XSSFWorkbook(); //check that the default date system is Set to 1900 CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr; Assert.IsNotNull(pr); Assert.IsTrue(pr.IsSetDate1904()); Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system"); ISheet sheet1 = wb1.CreateSheet("sheet1"); ISheet sheet2 = wb1.CreateSheet("sheet2"); wb1.CreateSheet("sheet3"); IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world"); sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2); sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts); sheet2.CreateRow(0); Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum); FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx"); Stream out1 = File.OpenWrite(file.FullName); wb1.Write(out1); out1.Close(); // Check the namespace Contains what we'd expect it to OPCPackage pkg = OPCPackage.Open(file.ToString()); PackagePart wbRelPart = pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels")); Assert.IsNotNull(wbRelPart); Assert.IsTrue(wbRelPart.IsRelationshipPart); Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType); PackagePart wbPart = pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml")); // Links to the three sheets, shared strings and styles Assert.IsTrue(wbPart.HasRelationships); Assert.AreEqual(5, wbPart.Relationships.Size); wb1.Close(); // Load back the XSSFWorkbook XSSFWorkbook wb2 = new XSSFWorkbook(pkg); Assert.AreEqual(3, wb2.NumberOfSheets); Assert.IsNotNull(wb2.GetSheetAt(0)); Assert.IsNotNull(wb2.GetSheetAt(1)); Assert.IsNotNull(wb2.GetSheetAt(2)); Assert.IsNotNull(wb2.GetSharedStringSource()); Assert.IsNotNull(wb2.GetStylesSource()); Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum); Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum); Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum); sheet1 = wb2.GetSheetAt(0); Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001); Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String); pkg.Close(); Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files."); }
public void TestBug56835CellComment() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; // first comment works IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4); XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment; Assert.IsNotNull(comment); try { Drawing.CreateCellComment(anchor); Assert.Fail("Should fail if we try to add the same comment for the same cell"); } catch (ArgumentException e) { // expected } } finally { wb.Close(); } }
public void TestReferencesToOtherWorkbooks() { XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx"); XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet; // References to a .xlsx file IRow rXSLX = s.GetRow(2); ICell cXSLX_cell = rXSLX.GetCell(4); ICell cXSLX_sNR = rXSLX.GetCell(6); ICell cXSLX_gNR = rXSLX.GetCell(8); Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula); Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula); Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula); Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue); Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue); Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue, 0); // References to a .xls file IRow rXSL = s.GetRow(4); ICell cXSL_cell = rXSL.GetCell(4); ICell cXSL_sNR = rXSL.GetCell(6); ICell cXSL_gNR = rXSL.GetCell(8); Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula); Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula); Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula); Assert.AreEqual("Hello!", cXSL_cell.StringCellValue); Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue); Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue, 0); // Try to Evaluate without references, won't work // (At least, not unit we fix bug #56752 that is1) try { evaluator.Evaluate(cXSL_cell); Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " + "reference to a non-provided linked workbook"); } catch (Exception) { } // Setup the environment Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>(); evaluators.Add("ref2-56737.xlsx", evaluator); evaluators.Add("56737.xlsx", _testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator()); evaluators.Add("56737.xls", HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator()); evaluator.SetupReferencedWorkbooks(evaluators); // Try Evaluating all of them, ensure we don't blow up foreach (IRow r in s) { foreach (ICell c in r) { // TODO Fix and enable evaluator.Evaluate(c); } } // And evaluate the other way too evaluator.EvaluateAll(); // Static evaluator won't work, as no references passed in try { XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); Assert.Fail("Static method lacks references, shouldn't work"); } catch (Exception) { // expected here } // Evaluate specific cells and check results Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString()); // Add another formula referencing these workbooks ICell cXSL_cell2 = rXSL.CreateCell(40); cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1"); // TODO Shouldn't it become [2] like the others? Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString()); // Now add a formula that refers to yet another (different) workbook // Won't work without the workbook being linked ICell cXSLX_nw_cell = rXSLX.CreateCell(42); try { cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1"); Assert.Fail("New workbook not linked, shouldn't be able to Add"); } catch (Exception) { } // Link and re-try IWorkbook alt = new XSSFWorkbook(); try { alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook"); // TODO Implement the rest of this, see bug #57184 /* * wb.linkExternalWorkbook("alt.xlsx", alt); * * cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1"); * // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ? * Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula()); * * // Evaluate it, without a link to that workbook * try { * evaluator.evaluate(cXSLX_nw_cell); * fail("No cached value and no link to workbook, shouldn't evaluate"); * } catch(Exception e) {} * * // Add a link, check it does * evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator()); * evaluator.setupReferencedWorkbooks(evaluators); * * evaluator.evaluate(cXSLX_nw_cell); * Assert.AreEqual("In another workbook", cXSLX_nw_cell.getStringCellValue()); */ } finally { alt.Close(); } wb.Close(); }
public void WorkbookProperties() { XSSFWorkbook workbook = new XSSFWorkbook(); try { POIXMLProperties props = workbook.GetProperties(); Assert.IsNotNull(props); //the Application property must be set for new workbooks, see Bugzilla #47559 Assert.AreEqual("NPOI", props.ExtendedProperties.GetUnderlyingProperties().Application); PackagePropertiesPart opcProps = props.CoreProperties.GetUnderlyingProperties(); Assert.IsNotNull(opcProps); opcProps.SetTitleProperty("Testing Bugzilla #47460"); Assert.AreEqual("NPOI", opcProps.GetCreatorProperty()); opcProps.SetCreatorProperty("*****@*****.**"); XSSFWorkbook wbBack = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook); Assert.AreEqual("NPOI", wbBack.GetProperties().ExtendedProperties.GetUnderlyingProperties().Application); opcProps = wbBack.GetProperties().CoreProperties.GetUnderlyingProperties(); Assert.AreEqual("Testing Bugzilla #47460", opcProps.GetTitleProperty()); Assert.AreEqual("*****@*****.**", opcProps.GetCreatorProperty()); } finally { workbook.Close(); } }
public void Test49253() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFWorkbook wb2 = new XSSFWorkbook(); // No print Settings before repeating XSSFSheet s1 = wb1.CreateSheet() as XSSFSheet; Assert.AreEqual(false, s1.GetCTWorksheet().IsSetPageSetup()); Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins()); wb1.SetRepeatingRowsAndColumns(0, 2, 3, 1, 2); Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageSetup()); Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins()); XSSFPrintSetup ps1 = s1.PrintSetup as XSSFPrintSetup; Assert.AreEqual(false, ps1.ValidSettings); Assert.AreEqual(false, ps1.Landscape); // Had valid print Settings before repeating XSSFSheet s2 = wb2.CreateSheet() as XSSFSheet; XSSFPrintSetup ps2 = s2.PrintSetup as XSSFPrintSetup; Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageSetup()); Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins()); ps2.Landscape = (false); Assert.AreEqual(true, ps2.ValidSettings); Assert.AreEqual(false, ps2.Landscape); wb2.SetRepeatingRowsAndColumns(0, 2, 3, 1, 2); ps2 = s2.PrintSetup as XSSFPrintSetup; Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageSetup()); Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins()); Assert.AreEqual(true, ps2.ValidSettings); Assert.AreEqual(false, ps2.Landscape); wb1.Close(); wb2.Close(); }
public void SetFirstVisibleTab_57373() { XSSFWorkbook wb = new XSSFWorkbook(); try { /*Sheet sheet1 =*/ wb.CreateSheet(); ISheet sheet2 = wb.CreateSheet(); int idx2 = wb.GetSheetIndex(sheet2); ISheet sheet3 = wb.CreateSheet(); int idx3 = wb.GetSheetIndex(sheet3); // add many sheets so "first visible" is relevant for (int i = 0; i < 30; i++) { wb.CreateSheet(); } wb.FirstVisibleTab = (/*setter*/idx2); wb.SetActiveSheet(idx3); //wb.Write(new FileOutputStream(new File("C:\\temp\\test.xlsx"))); Assert.AreEqual(idx2, wb.FirstVisibleTab); Assert.AreEqual(idx3, wb.ActiveSheetIndex); IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb); sheet2 = wbBack.GetSheetAt(idx2); sheet3 = wbBack.GetSheetAt(idx3); Assert.AreEqual(idx2, wb.FirstVisibleTab); Assert.AreEqual(idx3, wb.ActiveSheetIndex); } finally { wb.Close(); } }
public void Test51037() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet s = wb.CreateSheet() as XSSFSheet; ICellStyle defaultStyle = wb.GetCellStyleAt((short)0); Assert.AreEqual(0, defaultStyle.Index); ICellStyle blueStyle = wb.CreateCellStyle(); blueStyle.FillForegroundColor = (IndexedColors.Aqua.Index); blueStyle.FillPattern = (FillPattern.SolidForeground); Assert.AreEqual(1, blueStyle.Index); ICellStyle pinkStyle = wb.CreateCellStyle(); pinkStyle.FillForegroundColor = (IndexedColors.Pink.Index); pinkStyle.FillPattern = (FillPattern.SolidForeground); Assert.AreEqual(2, pinkStyle.Index); // Starts empty Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray()); CT_Cols cols = s.GetCTWorksheet().GetColsArray(0); Assert.AreEqual(0, cols.sizeOfColArray()); // Add some rows and columns XSSFRow r1 = s.CreateRow(0) as XSSFRow; XSSFRow r2 = s.CreateRow(1) as XSSFRow; r1.CreateCell(0); r1.CreateCell(2); r2.CreateCell(0); r2.CreateCell(3); // Check no style is there Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray()); Assert.AreEqual(0, cols.sizeOfColArray()); Assert.AreEqual(defaultStyle, s.GetColumnStyle(0)); Assert.AreEqual(defaultStyle, s.GetColumnStyle(2)); Assert.AreEqual(defaultStyle, s.GetColumnStyle(3)); // Apply the styles s.SetDefaultColumnStyle(0, pinkStyle); s.SetDefaultColumnStyle(3, blueStyle); // Check Assert.AreEqual(pinkStyle, s.GetColumnStyle(0)); Assert.AreEqual(defaultStyle, s.GetColumnStyle(2)); Assert.AreEqual(blueStyle, s.GetColumnStyle(3)); Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray()); Assert.AreEqual(2, cols.sizeOfColArray()); Assert.AreEqual(1, cols.GetColArray(0).min); Assert.AreEqual(1, cols.GetColArray(0).max); Assert.AreEqual(pinkStyle.Index, cols.GetColArray(0).style); Assert.AreEqual(4, cols.GetColArray(1).min); Assert.AreEqual(4, cols.GetColArray(1).max); Assert.AreEqual(blueStyle.Index, cols.GetColArray(1).style); // Save, re-load and re-check wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook; s = wb.GetSheetAt(0) as XSSFSheet; defaultStyle = wb.GetCellStyleAt(defaultStyle.Index); blueStyle = wb.GetCellStyleAt(blueStyle.Index); pinkStyle = wb.GetCellStyleAt(pinkStyle.Index); Assert.AreEqual(pinkStyle, s.GetColumnStyle(0)); Assert.AreEqual(defaultStyle, s.GetColumnStyle(2)); Assert.AreEqual(blueStyle, s.GetColumnStyle(3)); wb.Close(); }
public void TestMultisheetFormulaEval() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet; XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet; XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet; // sheet1 A1 XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet2 A1 cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet2 B1 cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet3 A1 cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Numeric); cell.SetCellValue(1.0); // sheet1 A2 formulae cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Formula); cell.CellFormula = (/*setter*/"SUM(Sheet1:Sheet3!A1)"); // sheet1 A3 formulae cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell; cell.SetCellType(CellType.Formula); cell.CellFormula = (/*setter*/"SUM(Sheet1:Sheet3!A1:B1)"); wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll(); cell = sheet1.GetRow(1).GetCell(0) as XSSFCell; Assert.AreEqual(3.0, cell.NumericCellValue); cell = sheet1.GetRow(2).GetCell(0) as XSSFCell; Assert.AreEqual(4.0, cell.NumericCellValue); } finally { wb.Close(); } }
public void Bug56468() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFCell cell = row.CreateCell(0) as XSSFCell; cell.SetCellValue("Hi"); sheet.RepeatingRows = (new CellRangeAddress(0, 0, 0, 0)); MemoryStream bos = new MemoryStream(8096); wb.Write(bos); byte[] firstSave = bos.ToArray(); MemoryStream bos2 = new MemoryStream(8096); wb.Write(bos2); byte[] secondSave = bos2.ToArray(); Assert.That(firstSave, new EqualConstraint(secondSave)); wb.Close(); }
public void TestBug55843f() { 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,\"\",IF(B1=10,3,4))"); CellValue Evaluate = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(Evaluate); Assert.AreEqual("3", Evaluate.FormatAsString()); } finally { wb.Close(); } }
public void Bug57430() { XSSFWorkbook wb = new XSSFWorkbook(); try { wb.CreateSheet("Sheet1"); XSSFName name1 = wb.CreateName() as XSSFName; name1.NameName = (/*setter*/"FMLA"); name1.RefersToFormula = (/*setter*/"Sheet1!$B$3"); } finally { wb.Close(); } }
public void TestXSSFTextParagraph() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/"Arial"); rt.ApplyFont(font); shape.SetText(/*setter*/rt); Assert.IsNotNull(shape.GetCTShape()); Assert.IsNotNull(shape.GetEnumerator()); Assert.IsNotNull(XSSFSimpleShape.GetPrototype()); foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber))) { shape.TextParagraphs[(0)].SetBullet(nr); Assert.IsNotNull(shape.Text); } shape.TextParagraphs[(0)].IsBullet = (false); Assert.IsNotNull(shape.Text); shape.SetText("testtext"); Assert.AreEqual("testtext", shape.Text); shape.SetText(new XSSFRichTextString()); //Assert.AreEqual("null", shape.Text); Assert.AreEqual(String.Empty, shape.Text); shape.AddNewTextParagraph(); shape.AddNewTextParagraph("test-other-text"); shape.AddNewTextParagraph(new XSSFRichTextString("rtstring")); shape.AddNewTextParagraph(new XSSFRichTextString()); //Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text); Assert.AreEqual("test-other-text\nrtstring\n", shape.Text); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.CLIP); Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.OVERFLOW); Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); shape.TextHorizontalOverflow = TextHorizontalOverflow.None; Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.CLIP); Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow); shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.OVERFLOW); Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); shape.TextVerticalOverflow = TextVerticalOverflow.None; Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow); Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = VerticalAlignment.Bottom; Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment); shape.VerticalAlignment = VerticalAlignment.Top; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); shape.VerticalAlignment = VerticalAlignment.None; Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/TextDirection.STACKED); Assert.AreEqual(TextDirection.STACKED, shape.TextDirection); shape.TextDirection = (/*setter*/TextDirection.HORIZONTAL); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); shape.TextDirection = (/*setter*/TextDirection.None); Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/12.32); Assert.AreEqual(12.32, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); shape.BottomInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.BottomInset, 0.01); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/12.31); Assert.AreEqual(12.31, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); shape.LeftInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.LeftInset, 0.01); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/13.31); Assert.AreEqual(13.31, shape.RightInset, 0.01); shape.RightInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.RightInset, 0.01); shape.RightInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.RightInset, 0.01); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/23.31); Assert.AreEqual(23.31, shape.TopInset, 0.01); shape.TopInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.TopInset, 0.01); shape.TopInset = (/*setter*/-1); Assert.AreEqual(3.6, shape.TopInset, 0.01); Assert.IsTrue(shape.WordWrap); shape.WordWrap = (/*setter*/false); Assert.IsFalse(shape.WordWrap); shape.WordWrap = (/*setter*/true); Assert.IsTrue(shape.WordWrap); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/TextAutofit.NORMAL); Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit); shape.TextAutofit = (/*setter*/TextAutofit.SHAPE); Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit); shape.TextAutofit = (/*setter*/TextAutofit.NONE); Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit); Assert.AreEqual(5, shape.ShapeType); shape.ShapeType = (/*setter*/23); Assert.AreEqual(23, shape.ShapeType); // TODO: should this be supported? // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); // shape.ShapeType=(/*setter*/-1); // Assert.AreEqual(-1, shape.ShapeType); Assert.IsNotNull(shape.GetShapeProperties()); } finally { wb.Close(); } }
public void XSSFTextParagraph_() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox; XSSFRichTextString rt = new XSSFRichTextString("Test String"); XSSFFont font = wb.CreateFont() as XSSFFont; Color color = Color.FromArgb(0, 255, 255); font.SetColor(new XSSFColor(color)); font.FontName = (/*setter*/ "Arial"); rt.ApplyFont(font); shape.SetText(rt); List <XSSFTextParagraph> paras = shape.TextParagraphs; Assert.AreEqual(1, paras.Count); XSSFTextParagraph text = paras[(0)]; Assert.AreEqual("Test String", text.Text); Assert.IsFalse(text.IsBullet); Assert.IsNotNull(text.GetXmlObject()); Assert.AreEqual(shape.GetCTShape(), text.ParentShape); Assert.IsNotNull(text.GetEnumerator()); Assert.IsNotNull(text.AddLineBreak()); Assert.IsNotNull(text.TextRuns); Assert.AreEqual(2, text.TextRuns.Count); text.AddNewTextRun(); Assert.AreEqual(3, text.TextRuns.Count); Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextAlign = (/*setter*/ TextAlign.CENTER); Assert.AreEqual(TextAlign.CENTER, text.TextAlign); text.TextAlign = (/*setter*/ TextAlign.RIGHT); Assert.AreEqual(TextAlign.RIGHT, text.TextAlign); text.TextAlign = TextAlign.None; Assert.AreEqual(TextAlign.LEFT, text.TextAlign); text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE); Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM); Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); text.TextFontAlign = TextFontAlign.None; Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign); Assert.IsNull(text.BulletFont); text.BulletFont = (/*setter*/ "Arial"); Assert.AreEqual("Arial", text.BulletFont); Assert.IsNull(text.BulletCharacter); text.BulletCharacter = (/*setter*/ "."); Assert.AreEqual(".", text.BulletCharacter); //Assert.IsNull(text.BulletFontColor); Assert.AreEqual(Color.Empty, text.BulletFontColor); text.BulletFontColor = (/*setter*/ color); Assert.AreEqual(color, text.BulletFontColor); Assert.AreEqual(100.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ 1.0); Assert.AreEqual(1.0, text.BulletFontSize, 0.01); text.BulletFontSize = (/*setter*/ -9.0); Assert.AreEqual(-9.0, text.BulletFontSize, 0.01); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/ 2.0); Assert.AreEqual(2.0, text.Indent, 0.01); text.Indent = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.Indent, 0.01); text.Indent = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.Indent, 0.01); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ 3.0); Assert.AreEqual(3.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); text.LeftMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.LeftMargin, 0.01); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ 4.5); Assert.AreEqual(4.5, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); text.RightMargin = (/*setter*/ -1.0); Assert.AreEqual(0.0, text.RightMargin, 0.01); Assert.AreEqual(0.0, text.DefaultTabSize, 0.01); Assert.AreEqual(0.0, text.GetTabStop(0), 0.01); text.AddTabStop(3.14); Assert.AreEqual(3.14, text.GetTabStop(0), 0.01); Assert.AreEqual(100.0, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/ 3.15); Assert.AreEqual(3.15, text.LineSpacing, 0.01); text.LineSpacing = (/*setter*/ -2.13); Assert.AreEqual(-2.13, text.LineSpacing, 0.01); Assert.AreEqual(0.0, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/ 3.17); Assert.AreEqual(3.17, text.SpaceBefore, 0.01); text.SpaceBefore = (/*setter*/ -4.7); Assert.AreEqual(-4.7, text.SpaceBefore, 0.01); Assert.AreEqual(0.0, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/ 6.17); Assert.AreEqual(6.17, text.SpaceAfter, 0.01); text.SpaceAfter = (/*setter*/ -8.17); Assert.AreEqual(-8.17, text.SpaceAfter, 0.01); Assert.AreEqual(0, text.Level); text.Level = (/*setter*/ 1); Assert.AreEqual(1, text.Level); text.Level = (/*setter*/ 4); Assert.AreEqual(4, text.Level); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (false); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.IsBullet = (true); Assert.IsTrue(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); Assert.AreEqual(0, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); //Assert.AreEqual(0, text.BulletAutoNumberStart); //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1; Assert.AreEqual(1, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme); text.IsBullet = (false); Assert.IsFalse(text.IsBullet); Assert.IsFalse(text.IsBulletAutoNumber); text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10); Assert.IsTrue(text.IsBullet); Assert.IsTrue(text.IsBulletAutoNumber); Assert.AreEqual(10, text.BulletAutoNumberStart); Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme); Assert.IsNotNull(text.ToString()); new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape()); } finally { wb.Close(); } }