public void TestEvaluateRefOutsideWindowFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(-1, s.LastFlushedRowNumber);
for (int i = 1; i <= 19; i++)
{
s.CreateRow(i);
}
ICell c = s.CreateRow(20).CreateCell(0);
c.CellFormula = (/*setter*/ "A1+100");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(15, s.LastFlushedRowNumber);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateFormulaCell(c);
Assert.Fail("Evaluate shouldn't work, as reference outside the window");
}
catch (RowFlushedException)
{
// Expected
}
wb.Close();
}
public void TestEvaluateAllFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(0).CellFormula = (/*setter*/ "A21");
for (int i = 2; i < 19; i++)
{
s.CreateRow(i);
}
// Cells outside window will fail, whether referenced or not
s.CreateRow(19).CreateCell(0).CellFormula = (/*setter*/ "A1+A2");
s.CreateRow(20).CreateCell(0).CellFormula = (/*setter*/ "A1+A11+100");
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as some cells outside the window");
}
catch (RowFlushedException)
{
// Expected
}
// Inactive sheets will fail
XSSFWorkbook xwb = new XSSFWorkbook();
xwb.CreateSheet("Open");
xwb.CreateSheet("Closed");
wb.Close();
wb = new SXSSFWorkbook(xwb, 5);
s = wb.GetSheet("Closed") as SXSSFSheet;
s.FlushRows();
s = wb.GetSheet("Open") as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as sheets flushed");
}
catch (SheetsFlushedException) { }
wb.Close();
}
public void testBug58175a()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
IDrawing drawing = sheet.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
// Create the comment and set the text+author
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = (str);
comment.Author = ("Apache POI");
/* fixed the problem as well
* comment.setColumn(cell.ColumnIndex);
* comment.setRow(cell.RowIndex);
*/
// Assign the comment to the cell
cell.CellComment = (comment);
FileStream out1 = new FileStream("C:\\temp\\58175.xlsx", FileMode.CreateNew, FileAccess.ReadWrite);
try
{
wb.Write(out1);
}
finally
{
out1.Close();
}
}
finally
{
wb.Close();
}
}
public void TestEvaluateSimple()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "CONCATENATE(\"hello\",\" \",\"world\")");
eval.EvaluateFormulaCell(c);
Assert.AreEqual("hello world", c.StringCellValue);
}
public void TestEvaluateAllInWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(1).CellFormula = (/*setter*/ "A1+10");
s.CreateRow(2).CreateCell(2).CellFormula = (/*setter*/ "B2+100");
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
eval.EvaluateAll();
Assert.AreEqual(3, (int)s.GetRow(0).GetCell(0).NumericCellValue);
Assert.AreEqual(13, (int)s.GetRow(1).GetCell(1).NumericCellValue);
Assert.AreEqual(113, (int)s.GetRow(2).GetCell(2).NumericCellValue);
wb.Close();
}
public void TestEvaluateRefInsideWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.SetCellValue(1.5);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "A1*2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
wb.Close();
}
public void TestBug58175()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
XSSFClientAnchor ca = (XSSFClientAnchor)anchor;
// create comments and vmlDrawing parts if they don't exist
CommentsTable comments = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetCommentsTable(true);
XSSFVMLDrawing vml = (((SXSSFWorkbook)wb).XssfWorkbook
.GetSheetAt(0) as XSSFSheet).GetVMLDrawing(true);
CT_Shape vmlShape1 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape1.GetClientDataArray(0).SetAnchorArray(0, position);
}
// create the comment in two different ways and verify that there is no difference
XSSFComment shape1 = new XSSFComment(comments, comments.NewComment(CellAddress.A1), vmlShape1);
shape1.Column = (ca.Col1);
shape1.Row = (ca.Row1);
CT_Shape vmlShape2 = vml.newCommentShape();
if (ca.IsSet())
{
String position = ca.Col1 + ", 0, " + ca.Row1
+ ", 0, " + ca.Col2 + ", 0, " + ca.Row2
+ ", 0";
vmlShape2.GetClientDataArray(0).SetAnchorArray(0, position);
}
CellAddress ref1 = new CellAddress(ca.Row1, ca.Col1);
XSSFComment shape2 = new XSSFComment(comments, comments.NewComment(ref1), vmlShape2);
Assert.AreEqual(shape1.Author, shape2.Author);
Assert.AreEqual(shape1.ClientAnchor, shape2.ClientAnchor);
Assert.AreEqual(shape1.Column, shape2.Column);
Assert.AreEqual(shape1.Row, shape2.Row);
Assert.AreEqual(shape1.GetCTComment().ToString(), shape2.GetCTComment().ToString());
Assert.AreEqual(shape1.GetCTComment().@ref, shape2.GetCTComment().@ref);
/*CommentsTable table1 = shape1.CommentsTable;
* CommentsTable table2 = shape2.CommentsTable;
* Assert.AreEqual(table1.CTComments.toString(), table2.CTComments.toString());
* Assert.AreEqual(table1.NumberOfComments, table2.NumberOfComments);
* Assert.AreEqual(table1.Relations, table2.Relations);*/
Assert.AreEqual(vmlShape1.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
vmlShape2.ToString().Replace("_x0000_s\\d+", "_x0000_s0000"),
"The vmlShapes should have equal content afterwards");
}
finally
{
wb.Close();
}
}