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);
}