public void TestEvaluate() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); HSSFWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); ICell cell1 = sh.CreateRow(0).CreateCell(0); cell1.CellFormula = ("MROUND(10, 3)"); ICell cell2 = sh.CreateRow(0).CreateCell(0); cell2.CellFormula = ("MROUND(-10, -3)"); ICell cell3 = sh.CreateRow(0).CreateCell(0); cell3.CellFormula = ("MROUND(1.3, 0.2)"); ICell cell4 = sh.CreateRow(0).CreateCell(0); cell4.CellFormula = ("MROUND(5, -2)"); ICell cell5 = sh.CreateRow(0).CreateCell(0); cell5.CellFormula = ("MROUND(5, 0)"); double accuracy = 1E-9; IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)"); Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)"); Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)"); Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)"); Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0"); }
public void TestAvg() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.SetCellValue(3); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(1,B2:B3)"); ICell a4 = sh.CreateRow(4).CreateCell(1); a4.SetCellValue(1); ICell a5 = sh.CreateRow(5).CreateCell(1); a5.SetCellValue(7); ICell a6 = sh.CreateRow(6).CreateCell(1); a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2"); ICell a7 = sh.CreateRow(7).CreateCell(1); a7.CellFormula = ("SUBTOTAL(1,B2:B7)"); fe.EvaluateAll(); Assert.AreEqual(2.0, a3.NumericCellValue); Assert.AreEqual(8.0, a6.NumericCellValue); Assert.AreEqual(3.0, a7.NumericCellValue); }
public void TestCallFunction_invalidArgs() { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("test"); IRow row1 = sheet.CreateRow(0); ICell cellA1 = row1.CreateCell(0, CellType.Formula); cellA1.CellFormula = (/*setter*/"COUNTIFS()"); IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); CellValue Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); cellA1.CellFormula = (/*setter*/"COUNTIFS(A1:C1)"); Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); cellA1.SetCellFormula("COUNTIFS(A1:C1,2,2)"); Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(15, Evaluate.ErrorValue); }
public void SetUp() { wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls"); Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sheet = wb.CreateSheet("RandBetweenSheet"); IRow row = sheet.CreateRow(0); bottomValueCell = row.CreateCell(0); topValueCell = row.CreateCell(1); formulaCell = row.CreateCell(2, CellType.FORMULA); }
static void Main(string[] args) { var path = @"d.xls"; using (var fs = File.OpenRead(path)) { //FunctionEval.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу //WorkbookEvaluator.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу var wb = new HSSFWorkbook(fs); #region 1 way //new CustomFunctionEval(); #endregion #region 2 way /* можно добавлять только новые функции. round подменить не выйдет String[] functionNames = { "ROUND2" }; FreeRefFunction[] functionImpls = { new ICDBUDFRound() }; UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls); UDFFinder udfToolpack = new AggregatingUDFFinder(udfs); wb.AddToolPack(udfToolpack); */ #endregion #region 3 way //FunctionEvlEx.RegisterFunction("round", new ICDBRound()); #endregion var eval = wb.GetCreationHelper().CreateFormulaEvaluator(); var sheet = wb.GetSheetAt(0); var en = sheet.GetRowEnumerator(); while (en.MoveNext()) { var row = (IRow)en.Current; var orig = row.GetCell(0); var charp = Math.Round(decimal.Parse(orig.ToString()), 2, MidpointRounding.AwayFromZero); var formula = row.GetCell(2); var npoiRound = eval.Evaluate(formula).NumberValue; var excel = row.GetCell(1); formula.CellFormula = formula.CellFormula.Replace("ROUND(", "ROUNDUP("); eval.ClearAllCachedResultValues(); var npoiRoundUp = eval.Evaluate(formula).NumberValue; Console.WriteLine("orig = {0}; excel = {1}; charp (MidpointRounding.AwayFromZero) = {2}; npoi(ROUND) = {3}; npoi(ROUNDUP) = {4}", orig, excel, charp, npoiRound, npoiRoundUp); } } }
public void TestEvaluate() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); IRow row1 = sh.CreateRow(0); IRow row2 = sh.CreateRow(1); // Create cells row1.CreateCell(0, CellType.Numeric); row1.CreateCell(1, CellType.Numeric); row1.CreateCell(2, CellType.Numeric); row2.CreateCell(0, CellType.Numeric); row2.CreateCell(1, CellType.Numeric); // Create references CellReference a1 = new CellReference("A1"); CellReference a2 = new CellReference("A2"); CellReference b1 = new CellReference("B1"); CellReference b2 = new CellReference("B2"); CellReference c1 = new CellReference("C1"); // Set values sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(210); sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(55); sh.GetRow(b1.Row).GetCell(b1.Col).SetCellValue(35); sh.GetRow(b2.Row).GetCell(b2.Col).SetCellValue(0); sh.GetRow(c1.Row).GetCell(c1.Col).SetCellFormula("A1/B2"); ICell cell1 = sh.CreateRow(3).CreateCell(0); cell1.SetCellFormula("IFERROR(A1/B1,\"Error in calculation\")"); ICell cell2 = sh.CreateRow(3).CreateCell(0); cell2.SetCellFormula("IFERROR(A2/B2,\"Error in calculation\")"); ICell cell3 = sh.CreateRow(3).CreateCell(0); cell3.SetCellFormula("IFERROR(C1,\"error\")"); double accuracy = 1E-9; IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(CellType.Numeric, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric"); Assert.AreEqual(6.0, evaluator.Evaluate(cell1).NumberValue, accuracy, "Divides 210 by 35 and returns 6.0"); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell2).CellType, "Checks that the cell is numeric"); Assert.AreEqual("Error in calculation", evaluator.Evaluate(cell2).StringValue, "Rounds -10 to a nearest multiple of -3 (-9)"); Assert.AreEqual(CellType.String, evaluator.Evaluate(cell3).CellType, "Check that C1 returns string"); Assert.AreEqual("error", evaluator.Evaluate(cell3).StringValue, "Check that C1 returns string \"error\""); }
public void Test50209() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.CellFormula = ("SUBTOTAL(9,B2)"); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(9,B2:B3)"); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); fe.EvaluateAll(); Assert.AreEqual(1.0, a2.NumericCellValue); Assert.AreEqual(1.0, a3.NumericCellValue); }
public void TestCallFunction() { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("test"); IRow row1 = sheet.CreateRow(0); ICell cellA1 = row1.CreateCell(0, CellType.Formula); ICell cellB1 = row1.CreateCell(1, CellType.Numeric); ICell cellC1 = row1.CreateCell(2, CellType.Numeric); ICell cellD1 = row1.CreateCell(3, CellType.Numeric); ICell cellE1 = row1.CreateCell(4, CellType.Numeric); cellB1.SetCellValue(1); cellC1.SetCellValue(1); cellD1.SetCellValue(2); cellE1.SetCellValue(4); cellA1.SetCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)"); IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); CellValue Evaluate = Evaluator.Evaluate(cellA1); Assert.AreEqual(1.0d, Evaluate.NumberValue); }
public void TestMissingWorkbookMissingOverride() { mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME); HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0); HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0); HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0); HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0); Assert.AreEqual(CellType.FORMULA, lA1Cell.CellType); Assert.AreEqual(CellType.FORMULA, lB1Cell.CellType); Assert.AreEqual(CellType.FORMULA, lC1Cell.CellType); HSSFFormulaEvaluator evaluator = (HSSFFormulaEvaluator)mainWorkbook.GetCreationHelper().CreateFormulaEvaluator(); evaluator.IgnoreMissingWorkbooks = (true); Assert.AreEqual(CellType.NUMERIC, evaluator.EvaluateFormulaCell(lA1Cell)); Assert.AreEqual(CellType.STRING, evaluator.EvaluateFormulaCell(lB1Cell)); Assert.AreEqual(CellType.BOOLEAN, evaluator.EvaluateFormulaCell(lC1Cell)); Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d); Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue); Assert.AreEqual(true, lC1Cell.BooleanCellValue); }
public void TestCount() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(1).CreateCell(1); a1.SetCellValue(1); ICell a2 = sh.CreateRow(2).CreateCell(1); a2.SetCellValue(3); ICell a3 = sh.CreateRow(3).CreateCell(1); a3.CellFormula = ("SUBTOTAL(2,B2:B3)"); ICell a4 = sh.CreateRow(4).CreateCell(1); a4.SetCellValue("POI"); // A4 is string and not counted ICell a5 = sh.CreateRow(5).CreateCell(1); // A5 is blank and not counted ICell a6 = sh.CreateRow(6).CreateCell(1); a6.CellFormula = ("SUBTOTAL(2,B2:B6)*2 + 2"); ICell a7 = sh.CreateRow(7).CreateCell(1); a7.CellFormula = ("SUBTOTAL(2,B2:B7)"); fe.EvaluateAll(); Assert.AreEqual(2.0, a3.NumericCellValue); Assert.AreEqual(6.0, a6.NumericCellValue); Assert.AreEqual(2.0, a7.NumericCellValue); }
public void TestStdev() { IWorkbook wb = new HSSFWorkbook(); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); ISheet sh = wb.CreateSheet(); ICell a1 = sh.CreateRow(0).CreateCell(0); a1.SetCellValue(1); ICell a2 = sh.CreateRow(1).CreateCell(0); a2.SetCellValue(3); ICell a3 = sh.CreateRow(2).CreateCell(0); a3.CellFormula = ("SUBTOTAL(7,A1:A2)"); ICell a4 = sh.CreateRow(3).CreateCell(0); a4.SetCellValue(1); ICell a5 = sh.CreateRow(4).CreateCell(0); a5.SetCellValue(7); ICell a6 = sh.CreateRow(5).CreateCell(0); a6.CellFormula = ("SUBTOTAL(7,A1:A5)*2 + 2"); ICell a7 = sh.CreateRow(6).CreateCell(0); a7.CellFormula = ("SUBTOTAL(7,A1:A6)"); fe.EvaluateAll(); Assert.AreEqual(1.41421, a3.NumericCellValue, 0.0001); Assert.AreEqual(7.65685, a6.NumericCellValue, 0.0001); Assert.AreEqual(2.82842, a7.NumericCellValue, 0.0001); }
public void TestAddPictures() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet("Pictures"); IDrawing dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); IPicture p1 = dr.CreatePicture(anchor, idx1); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data)); // register another one byte[] data2 = new byte[] { 4, 5, 6 }; int idx2 = wb.AddPicture(data2, PictureType.JPEG); Assert.AreEqual(2, idx2); IPicture p2 = dr.CreatePicture(anchor, idx2); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data)); // confirm that HSSFPatriarch.Children returns two picture shapes Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // Write, read back and verify that our pictures are there wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst2 = wb.GetAllPictures(); Assert.AreEqual(2, lst2.Count); Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data)); // confirm that the pictures are in the Sheet's Drawing sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // add a third picture byte[] data3 = new byte[] { 7, 8, 9 }; // picture index must increment across Write-read int idx3 = wb.AddPicture(data3, PictureType.JPEG); Assert.AreEqual(3, idx3); IPicture p3 = dr.CreatePicture(anchor, idx3); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data)); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); // write and read again wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst3 = wb.GetAllPictures(); // all three should be there Assert.AreEqual(3, lst3.Count); Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); // forth picture byte[] data4 = new byte[] { 10, 11, 12 }; int idx4 = wb.AddPicture(data4, PictureType.JPEG); Assert.AreEqual(4, idx4); dr.CreatePicture(anchor, idx4); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst4 = wb.GetAllPictures(); Assert.AreEqual(4, lst4.Count); Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); }
public void TestReallyEmbedSomething() { HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); byte[] picturePPT = POIDataSamples.GetSlideShowInstance().ReadFile("clock.jpg"); int imgIdx = wb.AddPicture(pictureData, PictureType.PNG); POIFSFileSystem pptPoifs = GetSamplePPT(); int pptIdx = wb.AddOlePackage(pptPoifs, "Sample-PPT", "sample.ppt", "sample.ppt"); POIFSFileSystem xlsPoifs = GetSampleXLS(); int imgPPT = wb.AddPicture(picturePPT, PictureType.JPEG); int xlsIdx = wb.AddOlePackage(xlsPoifs, "Sample-XLS", "sample.xls", "sample.xls"); int txtIdx = wb.AddOlePackage(GetSampleTXT(), "Sample-TXT", "sample.txt", "sample.txt"); int rowoffset = 5; int coloffset = 5; ICreationHelper ch = wb.GetCreationHelper(); HSSFClientAnchor anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(2 + coloffset), 1 + rowoffset, 0, 0, (short)(3 + coloffset), 5 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, pptIdx, imgPPT); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(5 + coloffset), 1 + rowoffset, 0, 0, (short)(6 + coloffset), 5 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, xlsIdx, imgIdx); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(3 + coloffset), 10 + rowoffset, 0, 0, (short)(5 + coloffset), 11 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); patriarch.CreateObjectData(anchor, txtIdx, imgIdx); anchor = (HSSFClientAnchor)ch.CreateClientAnchor(); anchor.SetAnchor((short)(1 + coloffset), -2 + rowoffset, 0, 0, (short)(7 + coloffset), 14 + rowoffset, 0, 0); anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize); HSSFSimpleShape circle = patriarch.CreateSimpleShape(anchor); circle.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_OVAL); circle.IsNoFill = (/*setter*/true); if (false) { FileStream fos = new FileStream("embed.xls", FileMode.Create); wb.Write(fos); fos.Close(); } wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); MemoryStream bos = new MemoryStream(); HSSFObjectData od = wb.GetAllEmbeddedObjects()[0]; Ole10Native ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); bos = new MemoryStream(); pptPoifs.WriteFileSystem(bos); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray())); od = wb.GetAllEmbeddedObjects()[1]; ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); bos = new MemoryStream(); xlsPoifs.WriteFileSystem(bos); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray())); od = wb.GetAllEmbeddedObjects()[2]; ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory()); Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, GetSampleTXT())); }
public void stackoverflow23114397() { IWorkbook wb = new HSSFWorkbook(); IDataFormat format = wb.GetCreationHelper().CreateDataFormat(); // How close the sizing should be, given that not all // systems will have quite the same fonts on them float fontAccuracy = 0.15f; // x% ICellStyle iPercent = wb.CreateCellStyle(); iPercent.DataFormat = (/*setter*/format.GetFormat("0%")); // x.x% ICellStyle d1Percent = wb.CreateCellStyle(); d1Percent.DataFormat = (/*setter*/format.GetFormat("0.0%")); // x.xx% ICellStyle d2Percent = wb.CreateCellStyle(); d2Percent.DataFormat = (/*setter*/format.GetFormat("0.00%")); ISheet s = wb.CreateSheet(); IRow r1 = s.CreateRow(0); for (int i = 0; i < 3; i++) { r1.CreateCell(i, CellType.Numeric).SetCellValue(0); } for (int i = 3; i < 6; i++) { r1.CreateCell(i, CellType.Numeric).SetCellValue(1); } for (int i = 6; i < 9; i++) { r1.CreateCell(i, CellType.Numeric).SetCellValue(0.12345); } for (int i = 9; i < 12; i++) { r1.CreateCell(i, CellType.Numeric).SetCellValue(1.2345); } for (int i = 0; i < 12; i += 3) { r1.GetCell(i + 0).CellStyle = (/*setter*/iPercent); r1.GetCell(i + 1).CellStyle = (/*setter*/d1Percent); r1.GetCell(i + 2).CellStyle = (/*setter*/d2Percent); } for (int i = 0; i < 12; i++) { s.AutoSizeColumn(i); //System.out.Println(i + " => " + s.GetColumnWidth(i)); } // Check the 0(.00)% ones assertAlmostEquals(980, s.GetColumnWidth(0), fontAccuracy); assertAlmostEquals(1400, s.GetColumnWidth(1), fontAccuracy); assertAlmostEquals(1700, s.GetColumnWidth(2), fontAccuracy); // Check the 100(.00)% ones assertAlmostEquals(1500, s.GetColumnWidth(3), fontAccuracy); assertAlmostEquals(1950, s.GetColumnWidth(4), fontAccuracy); assertAlmostEquals(2225, s.GetColumnWidth(5), fontAccuracy); // Check the 12(.34)% ones assertAlmostEquals(1225, s.GetColumnWidth(6), fontAccuracy); assertAlmostEquals(1650, s.GetColumnWidth(7), fontAccuracy); assertAlmostEquals(1950, s.GetColumnWidth(8), fontAccuracy); // Check the 123(.45)% ones assertAlmostEquals(1500, s.GetColumnWidth(9), fontAccuracy); assertAlmostEquals(1950, s.GetColumnWidth(10), fontAccuracy); assertAlmostEquals(2225, s.GetColumnWidth(11), fontAccuracy); }
public void Bug49658() { // test if inserted EscherMetafileBlip will be read again IWorkbook wb = new HSSFWorkbook(); byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf"); int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF); byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png"); int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG); byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf"); int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; ICreationHelper ch = wb.GetCreationHelper(); IClientAnchor anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/2); anchor.Col2 = (/*setter*/5); anchor.Row1 = (/*setter*/1); anchor.Row2 = (/*setter*/6); patriarch.CreatePicture(anchor, indexEmf); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/2); anchor.Col2 = (/*setter*/5); anchor.Row1 = (/*setter*/10); anchor.Row2 = (/*setter*/16); patriarch.CreatePicture(anchor, indexPng); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/6); anchor.Col2 = (/*setter*/9); anchor.Row1 = (/*setter*/1); anchor.Row2 = (/*setter*/6); patriarch.CreatePicture(anchor, indexWmf); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut)); byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22]; Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22); pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut)); }
public void TestNamesInFormulas() { IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); IName name1 = wb.CreateName(); name1.NameName = ("aConstant"); name1.RefersToFormula = ("3.14"); IName name2 = wb.CreateName(); name2.NameName = ("aFormula"); name2.RefersToFormula = ("SUM(Sheet1!$A$1:$A$3)"); IName name3 = wb.CreateName(); name3.NameName = ("aSet"); name3.RefersToFormula = ("Sheet1!$A$2:$A$4"); IRow row0 = sheet.CreateRow(0); IRow row1 = sheet.CreateRow(1); IRow row2 = sheet.CreateRow(2); IRow row3 = sheet.CreateRow(3); row0.CreateCell(0).SetCellValue(2); row1.CreateCell(0).SetCellValue(5); row2.CreateCell(0).SetCellValue(3); row3.CreateCell(0).SetCellValue(7); row0.CreateCell(2).SetCellFormula("aConstant"); row1.CreateCell(2).SetCellFormula("aFormula"); row2.CreateCell(2).SetCellFormula("SUM(aSet)"); row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)"); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue); Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue); Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue); Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue); }
public void TestNamesInFormulas() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); IName name1 = wb.CreateName(); name1.NameName = "aConstant"; name1.RefersToFormula = "3.14"; IName name2 = wb.CreateName(); name2.NameName = "aFormula"; name2.RefersToFormula = "SUM(Sheet1!$A$1:$A$3)"; IName name3 = wb.CreateName(); name3.NameName = "aSet"; name3.RefersToFormula = "Sheet1!$A$2:$A$4"; IRow row0 = sheet.CreateRow(0); IRow row1 = sheet.CreateRow(1); IRow row2 = sheet.CreateRow(2); IRow row3 = sheet.CreateRow(3); row0.CreateCell(0).SetCellValue(2); row1.CreateCell(0).SetCellValue(5); row2.CreateCell(0).SetCellValue(3); row3.CreateCell(0).SetCellValue(7); row0.CreateCell(2).SetCellFormula("aConstant"); row1.CreateCell(2).SetCellFormula("aFormula"); row2.CreateCell(2).SetCellFormula("SUM(aSet)"); row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)"); IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue); Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue); Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue); Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue); }
public void TestResultOutsideRange() { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); HSSFWorkbook wb = new HSSFWorkbook(); ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0); cell.CellFormula = "D2:D5"; // IF(TRUE,D2:D5,D2) or OFFSET(D2:D5,0,0) would work too IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator(); CellValue cv; try { cv = fe.Evaluate(cell); } catch (ArgumentException e) { if ("Specified row index (0) is outside the allowed range (1..4)".Equals(e.Message)) { throw new AssertionException("Identified bug in result dereferencing"); } throw; } Assert.AreEqual(CellType.Error, cv.CellType); Assert.AreEqual(ErrorConstants.ERROR_VALUE, cv.ErrorValue); // verify circular refs are still detected properly fe.ClearAllCachedResultValues(); cell.CellFormula = "OFFSET(A1,0,0)"; cv = fe.Evaluate(cell); Assert.AreEqual(CellType.Error, cv.CellType); Assert.AreEqual(ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cv.ErrorValue); }