public void TestWithoutFeatRecord()
{
HSSFWorkbook hssf =
HSSFTestDataSamples.OpenSampleWorkbook("46136-WithWarnings.xls");
InternalWorkbook wb = HSSFTestHelper.GetWorkbookForTest(hssf);
Assert.AreEqual(1, hssf.NumberOfSheets);
int countFR = 0;
int countFRH = 0;
// Check on the workbook, but shouldn't be there!
foreach (Record r in wb.Records)
{
if (r is FeatRecord)
{
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
// Now check on the sheet
HSSFSheet s = (HSSFSheet)hssf.GetSheetAt(0);
InternalSheet sheet = HSSFTestHelper.GetSheetForTest(s);
foreach (RecordBase rb in sheet.Records)
{
if (rb is Record)
{
Record r = (Record)rb;
if (r is FeatRecord)
{
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
}
public void AddPictures()
{
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 TestIfFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("TestSheet1");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(1); c.SetCellValue(1);
c = r.CreateCell(2); c.SetCellValue(2);
c = r.CreateCell(3); c.CellFormula = ("MAX(A1:B1)");
c = r.CreateCell(4); c.CellFormula = ("IF(A1=D1,\"A1\",\"B1\")");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(4);
Assert.IsTrue(("IF(A1=D1,\"A1\",\"B1\")").Equals(c.CellFormula), "expected: IF(A1=D1,\"A1\",\"B1\") got " + c.CellFormula);
wb = OpenSample("IfFormulaTest.xls");
s = wb.GetSheetAt(0);
r = s.GetRow(3);
c = r.GetCell(0);
Assert.IsTrue(("IF(A3=A1,\"A1\",\"A2\")").Equals(c.CellFormula), "expected: IF(A3=A1,\"A1\",\"A2\") got " + c.CellFormula);
//c = r.GetCell((short)1);
//Assert.IsTrue("expected: A!A1+A!B1 got: "+c.CellFormula, ("A!A1+A!B1").Equals(c.CellFormula));
wb = new HSSFWorkbook();
s = wb.CreateSheet("TestSheet1");
r = null;
c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("IF(1=1,0,1)");
HSSFTestDataSamples.WriteOutAndReadBack(wb);
wb = new HSSFWorkbook();
s = wb.CreateSheet("TestSheet1");
r = null;
c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.SetCellValue(1);
c = r.CreateCell(1);
c.SetCellValue(3);
ICell formulaCell = r.CreateCell(3);
r = s.CreateRow(1);
c = r.CreateCell(0);
c.SetCellValue(3);
c = r.CreateCell(1);
c.SetCellValue(7);
formulaCell.CellFormula = ("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void MultiSheetReferencesHSSFandXSSF()
{
IWorkbook[] wbs = new IWorkbook[] {
HSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xls"),
XSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xlsx")
};
foreach (IWorkbook wb in wbs)
{
ISheet s1 = wb.GetSheetAt(0);
Ptg[] ptgs;
// Check the contents
ICell sumF = s1.GetRow(2).GetCell(0);
Assert.IsNotNull(sumF);
Assert.AreEqual("SUM(Sheet1:Sheet3!A1)", sumF.CellFormula);
ICell avgF = s1.GetRow(2).GetCell(1);
Assert.IsNotNull(avgF);
Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1)", avgF.CellFormula);
ICell countAF = s1.GetRow(2).GetCell(2);
Assert.IsNotNull(countAF);
Assert.AreEqual("COUNTA(Sheet1:Sheet3!C1)", countAF.CellFormula);
ICell maxF = s1.GetRow(4).GetCell(1);
Assert.IsNotNull(maxF);
Assert.AreEqual("MAX(Sheet1:Sheet3!A$1)", maxF.CellFormula);
ICell sumFA = s1.GetRow(2).GetCell(7);
Assert.IsNotNull(sumFA);
Assert.AreEqual("SUM(Sheet1:Sheet3!A1:B2)", sumFA.CellFormula);
ICell avgFA = s1.GetRow(2).GetCell(8);
Assert.IsNotNull(avgFA);
Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.CellFormula);
ICell maxFA = s1.GetRow(4).GetCell(8);
Assert.IsNotNull(maxFA);
Assert.AreEqual("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.CellFormula);
ICell countFA = s1.GetRow(5).GetCell(8);
Assert.IsNotNull(countFA);
Assert.AreEqual("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.CellFormula);
// Create a formula Parser
IFormulaParsingWorkbook fpb = null;
if (wb is HSSFWorkbook)
{
fpb = HSSFEvaluationWorkbook.Create((HSSFWorkbook)wb);
}
else
{
fpb = XSSFEvaluationWorkbook.Create((XSSFWorkbook)wb);
}
// Check things parse as expected:
// SUM to one cell over 3 workbooks, relative reference
ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!A1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb));
// MAX to one cell over 3 workbooks, absolute row reference
ptgs = Parse(fpb, "MAX(Sheet1:Sheet3!A$1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!A$1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MAX", ToFormulaString(ptgs[1], fpb));
// MIN to one cell over 3 workbooks, absolute reference
ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Ref3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
}
Assert.AreEqual("Sheet1:Sheet3!$A$1", ToFormulaString(ptgs[0], fpb));
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb));
// SUM to a range of cells over 3 workbooks
ptgs = Parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
}
Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!A1:B2");
Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
Assert.AreEqual("SUM", ToFormulaString(ptgs[1], fpb));
// MIN to a range of cells over 3 workbooks, absolute reference
ptgs = Parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)");
Assert.AreEqual(2, ptgs.Length);
if (wb is HSSFWorkbook)
{
Assert.AreEqual(typeof(Area3DPtg), ptgs[0].GetType());
}
else
{
Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
}
Assert.AreEqual(ToFormulaString(ptgs[0], fpb), "Sheet1:Sheet3!$A$1:$B$2");
Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
Assert.AreEqual("MIN", ToFormulaString(ptgs[1], fpb));
// Check we can round-trip - try to Set a new one to a new single cell
ICell newF = s1.GetRow(0).CreateCell(10, CellType.Formula);
newF.CellFormula = (/*setter*/ "SUM(Sheet2:Sheet3!A1)");
Assert.AreEqual("SUM(Sheet2:Sheet3!A1)", newF.CellFormula);
// Check we can round-trip - try to Set a new one to a cell range
newF = s1.GetRow(0).CreateCell(11, CellType.Formula);
newF.CellFormula = (/*setter*/ "MIN(Sheet1:Sheet2!A1:B2)");
Assert.AreEqual("MIN(Sheet1:Sheet2!A1:B2)", newF.CellFormula);
}
}
public void Test55747_55324()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator ev = wb.GetCreationHelper().CreateFormulaEvaluator() as HSSFFormulaEvaluator;
HSSFSheet ws = wb.CreateSheet() as HSSFSheet;
HSSFRow row = ws.CreateRow(0) as HSSFRow;
HSSFCell cell;
// Our test value
cell = row.CreateCell(0) as HSSFCell;
cell.SetCellValue("abc");
// Lots of IF cases
cell = row.CreateCell(1) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",MID(A1,1,2),\"X\")");//if(expr,func,val)
cell = row.CreateCell(2) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",\"A\",\"B\")");// if(expr,val,val)
cell = row.CreateCell(3) as HSSFCell;
cell.SetCellFormula("IF(A1=\"\",\"X\",MID(A1,1,2))");//if(expr,val,func),
cell = row.CreateCell(4) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",\"X\",MID(A1,1,2))");//if(expr,val,func),
cell = row.CreateCell(5) as HSSFCell;
cell.SetCellFormula("IF(A1=\"\",MID(A1,1,2),MID(A1,2,2))"); //if(exp,func,func)
cell = row.CreateCell(6) as HSSFCell;
cell.SetCellFormula("IF(A1<>\"\",MID(A1,1,2),MID(A1,2,2))"); //if(exp,func,func)
cell = row.CreateCell(7) as HSSFCell;
cell.SetCellFormula("IF(MID(A1,1,2)<>\"\",\"A\",\"B\")");//if(func_expr,val,val)
// And some MID ones just to check
row = ws.CreateRow(1) as HSSFRow;
cell = row.CreateCell(1) as HSSFCell;
cell.SetCellFormula("MID(A1,1,2)");
cell = row.CreateCell(2) as HSSFCell;
cell.SetCellFormula("MID(A1,2,2)");
cell = row.CreateCell(3) as HSSFCell;
cell.SetCellFormula("MID(A1,2,1)");
cell = row.CreateCell(4) as HSSFCell;
cell.SetCellFormula("MID(A1,3,1)");
// Evaluate
ev.EvaluateAll();
// Save and re-load
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb) as HSSFWorkbook;
ws = wb.GetSheetAt(0) as HSSFSheet;
// Check the MID Ptgs in Row 2 have V RefPtgs for A1
row = ws.GetRow(1) as HSSFRow;
for (int i = 1; i <= 4; i++)
{
cell = row.GetCell(i) as HSSFCell;
Ptg[] ptgs = getPtgs(cell);
Assert.AreEqual(4, ptgs.Length);
Assert.AreEqual(typeof(FuncPtg), ptgs[3].GetType());
Assert.AreEqual("MID", ((FuncPtg)ptgs[3]).Name);
assertRefPtgA1('V', ptgs, 0);
}
// Now check the IF formulas
row = ws.GetRow(0) as HSSFRow;
// H1, MID is used in the expression IF checks, so A1 should be V
cell = row.GetCell(CellReference.ConvertColStringToIndex("H")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// E1, MID is used in the FALSE route, so A1 should be V
// A1 should be V in the IF check
// A1 should be R in the FALSE route
cell = row.GetCell(CellReference.ConvertColStringToIndex("E")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
assertRefPtgA1('R', getPtgs(cell), 6);
// Check that, for B1, D1, F1 and G1, the references to A1
// from all of IF check, True and False are V
cell = row.GetCell(CellReference.ConvertColStringToIndex("B")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("D")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 6); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("F")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
// assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
cell = row.GetCell(CellReference.ConvertColStringToIndex("G")) as HSSFCell;
assertRefPtgA1('V', getPtgs(cell), 0);
// assertRefPtgA1('V', getPtgs(cell), 4); // FIXME!
// assertRefPtgA1('V', getPtgs(cell), 9); // FIXME!
// Check our cached values were correctly evaluated
cell = row.GetCell(CellReference.ConvertColStringToIndex("A")) as HSSFCell;
Assert.AreEqual("abc", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("B")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("C")) as HSSFCell;
Assert.AreEqual("A", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("D")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("E")) as HSSFCell;
Assert.AreEqual("X", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("F")) as HSSFCell;
Assert.AreEqual("bc", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("G")) as HSSFCell;
Assert.AreEqual("ab", cell.StringCellValue);
cell = row.GetCell(CellReference.ConvertColStringToIndex("H")) as HSSFCell;
Assert.AreEqual("A", cell.StringCellValue);
// Enable this to write out + check in Excel
if (OUTPUT_TEST_FILES)
{
FileStream out1 = new FileStream("/tmp/test.xls", FileMode.Create, FileAccess.ReadWrite);
wb.Write(out1);
out1.Close();
}
}
private static Stream OpenSampleStream(String sampleFileName)
{
return(HSSFTestDataSamples.OpenSampleFileStream(sampleFileName));
}
public void TestRemoveShapes()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor());
rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG);
patriarch.CreatePicture(new HSSFClientAnchor(), idx);
patriarch.CreateCellComment(new HSSFClientAnchor());
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 });
patriarch.CreateTextbox(new HSSFClientAnchor());
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
group.CreateTextbox(new HSSFChildAnchor());
group.CreatePicture(new HSSFChildAnchor(), idx);
Assert.AreEqual(patriarch.Children.Count, 6);
Assert.AreEqual(group.Children.Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 6);
group = (HSSFShapeGroup)patriarch.Children[5];
group.RemoveShape(group.Children[0]);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
group = (HSSFShapeGroup)patriarch.Children[(5)];
patriarch.RemoveShape(group);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 5);
HSSFShape shape = patriarch.Children[0];
patriarch.RemoveShape(shape);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 4);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 4);
HSSFPicture picture = (HSSFPicture)patriarch.Children[0];
patriarch.RemoveShape(picture);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
Assert.AreEqual(patriarch.Children.Count, 3);
HSSFComment comment = (HSSFComment)patriarch.Children[0];
patriarch.RemoveShape(comment);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 2);
polygon = (HSSFPolygon)patriarch.Children[0];
patriarch.RemoveShape(polygon);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 2);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 1);
HSSFTextbox textbox = (HSSFTextbox)patriarch.Children[0];
patriarch.RemoveShape(textbox);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 0);
Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
Assert.AreEqual(patriarch.Children.Count, 0);
}
public static OPCPackage OpenSamplePackage(String sampleName)
{
return(OPCPackage.Open(
HSSFTestDataSamples.OpenSampleFileStream(sampleName)
));
}
public static XSSFWorkbook OpenSampleWorkbook(String sampleName)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(sampleName);
return(new XSSFWorkbook(is1));
}
/**
* Loads a workbook from the given filename in the Test data dir.
*
* @param sampleFileName the filename.
* @return the loaded workbook.
*/
protected static HSSFWorkbook LoadWorkbook(String sampleFileName)
{
return(HSSFTestDataSamples.OpenSampleWorkbook(sampleFileName));
}
public static FileInfo GetSampleFile(String sampleFileName)
{
return(HSSFTestDataSamples.GetSampleFile(sampleFileName));
}
public new void TestColumnWidth()
{
//check we can correctly read column widths from a reference workbook
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("colwidth.xls");
//reference values
int[] ref1 = { 365, 548, 731, 914, 1097, 1280, 1462, 1645, 1828, 2011, 2194, 2377, 2560, 2742, 2925, 3108, 3291, 3474, 3657 };
ISheet sh = wb.GetSheetAt(0);
for (char i = 'A'; i <= 'S'; i++)
{
int idx = i - 'A';
int w = sh.GetColumnWidth(idx);
Assert.AreEqual(ref1[idx], w);
}
//the second sheet doesn't have overridden column widths
sh = wb.GetSheetAt(1);
int def_width = sh.DefaultColumnWidth;
for (char i = 'A'; i <= 'S'; i++)
{
int idx = i - 'A';
int w = sh.GetColumnWidth(idx);
//getDefaultColumnWidth returns width measured in characters
//getColumnWidth returns width measured in 1/256th units
Assert.AreEqual(def_width * 256, w);
}
//test new workbook
wb = new HSSFWorkbook();
sh = wb.CreateSheet();
sh.DefaultColumnWidth = (/*setter*/ 10);
Assert.AreEqual(10, sh.DefaultColumnWidth);
Assert.AreEqual(256 * 10, sh.GetColumnWidth(0));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(1));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(2));
for (char i = 'D'; i <= 'F'; i++)
{
short w = (256 * 12);
sh.SetColumnWidth(i, w);
Assert.AreEqual(w, sh.GetColumnWidth(i));
}
//serialize and read again
wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb);
sh = wb.GetSheetAt(0);
Assert.AreEqual(10, sh.DefaultColumnWidth);
//columns A-C have default width
Assert.AreEqual(256 * 10, sh.GetColumnWidth(0));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(1));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(2));
//columns D-F have custom width
for (char i = 'D'; i <= 'F'; i++)
{
short w = (256 * 12);
Assert.AreEqual(w, sh.GetColumnWidth(i));
}
// check for 16-bit signed/unsigned error:
sh.SetColumnWidth(0, 40000);
Assert.AreEqual(40000, sh.GetColumnWidth(0));
}
public void TestForceRecalculation()
{
IWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("UncalcedRecord.xls");
ISheet sheet = workbook.GetSheetAt(0);
ISheet sheet2 = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellValue(8);
Assert.IsFalse(sheet.ForceFormulaRecalculation);
Assert.IsFalse(sheet2.ForceFormulaRecalculation);
// Save and manually verify that on column C we have 0, value in template
FileInfo tempFile = TempFile.CreateTempFile("uncalced_err", ".xls");
FileStream fout = new FileStream(tempFile.FullName, FileMode.OpenOrCreate);
workbook.Write(fout);
fout.Close();
sheet.ForceFormulaRecalculation = (/*setter*/ true);
Assert.IsTrue(sheet.ForceFormulaRecalculation);
// Save and manually verify that on column C we have now 13, calculated value
tempFile = TempFile.CreateTempFile("uncalced_succ", ".xls");
tempFile.Delete();
fout = new FileStream(tempFile.FullName, FileMode.OpenOrCreate);
workbook.Write(fout);
fout.Close();
// Try it can be opened
IWorkbook wb2 = new HSSFWorkbook(new FileStream(tempFile.FullName, FileMode.Open));
// And check correct sheet Settings found
sheet = wb2.GetSheetAt(0);
sheet2 = wb2.GetSheetAt(1);
Assert.IsTrue(sheet.ForceFormulaRecalculation);
Assert.IsFalse(sheet2.ForceFormulaRecalculation);
// Now turn if back off again
sheet.ForceFormulaRecalculation = (/*setter*/ false);
fout = new FileStream(tempFile.FullName, FileMode.Open);
wb2.Write(fout);
fout.Close();
wb2 = new HSSFWorkbook(new FileStream(tempFile.FullName, FileMode.Open));
Assert.IsFalse(wb2.GetSheetAt(0).ForceFormulaRecalculation);
Assert.IsFalse(wb2.GetSheetAt(1).ForceFormulaRecalculation);
Assert.IsFalse(wb2.GetSheetAt(2).ForceFormulaRecalculation);
// Now add a new sheet, and check things work
// with old ones unset, new one Set
ISheet s4 = wb2.CreateSheet();
s4.ForceFormulaRecalculation = (/*setter*/ true);
Assert.IsFalse(sheet.ForceFormulaRecalculation);
Assert.IsFalse(sheet2.ForceFormulaRecalculation);
Assert.IsTrue(s4.ForceFormulaRecalculation);
fout = new FileStream(tempFile.FullName, FileMode.Open);
wb2.Write(fout);
fout.Close();
IWorkbook wb3 = new HSSFWorkbook(new FileStream(tempFile.FullName, FileMode.Open));
Assert.IsFalse(wb3.GetSheetAt(0).ForceFormulaRecalculation);
Assert.IsFalse(wb3.GetSheetAt(1).ForceFormulaRecalculation);
Assert.IsFalse(wb3.GetSheetAt(2).ForceFormulaRecalculation);
Assert.IsTrue(wb3.GetSheetAt(3).ForceFormulaRecalculation);
}
public void TestShiftWithComments()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("comments.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(3, sheet.LastRowNum);
// Verify comments are in the position expected
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNotNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
String comment1 = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, "comment top row1 (index0)\n");
String comment3 = sheet.GetCellComment(2, 0).String.String;
Assert.AreEqual(comment3, "comment top row3 (index2)\n");
String comment4 = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment4, "comment top row4 (index3)\n");
// Shifting all but first line down to Test comments shifting
sheet.ShiftRows(1, sheet.LastRowNum, 1, true, true);
MemoryStream outputStream = new MemoryStream();
wb.Write(outputStream);
// Test that comments were shifted as expected
Assert.AreEqual(4, sheet.LastRowNum);
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
String comment1_shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_shifted);
String comment3_shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_shifted);
String comment4_shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_shifted);
// Write out and read back in again
// Ensure that the changes were persisted
wb = new HSSFWorkbook(new MemoryStream(outputStream.ToArray()));
sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(4, sheet.LastRowNum);
// Verify comments are in the position expected after the shift
Assert.IsNotNull(sheet.GetCellComment(0, 0));
Assert.IsNull(sheet.GetCellComment(1, 0));
Assert.IsNull(sheet.GetCellComment(2, 0));
Assert.IsNotNull(sheet.GetCellComment(3, 0));
Assert.IsNotNull(sheet.GetCellComment(4, 0));
comment1_shifted = sheet.GetCellComment(0, 0).String.String;
Assert.AreEqual(comment1, comment1_shifted);
comment3_shifted = sheet.GetCellComment(3, 0).String.String;
Assert.AreEqual(comment3, comment3_shifted);
comment4_shifted = sheet.GetCellComment(4, 0).String.String;
Assert.AreEqual(comment4, comment4_shifted);
}
public void TestFormulas()
{
FormulaRecord[] fRecs = mockListen.GetFormulaRecords();
// Check our formula records
Assert.AreEqual(6, fRecs.Length);
InternalWorkbook stubWB = listener.GetStubWorkbook();
Assert.IsNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.GetStubHSSFWorkbook();
Assert.IsNotNull(stubHSSF);
// Check these stubs have the right stuff on them
Assert.AreEqual("Sheet1", stubWB.GetSheetName(0));
Assert.AreEqual("S2", stubWB.GetSheetName(1));
Assert.AreEqual("Sh3", stubWB.GetSheetName(2));
// Check we can Get the formula without breaking
for (int i = 0; i < fRecs.Length; i++)
{
HSSFFormulaParser.ToFormulaString(stubHSSF, fRecs[i].ParsedExpression);
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].ParsedExpression;
Assert.AreEqual(1, ptgs.Length);
Assert.IsTrue(ptgs[0] is Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg)ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(stubHSSF);
Assert.AreEqual("Sheet1!A1", ptg.ToFormulaString(book));
// Now check we Get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
Assert.AreEqual(1, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("Sheet1!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
Assert.AreEqual(4, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("'S2'!A1", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
Assert.AreEqual(6, fr.Row);
Assert.AreEqual(0, fr.Column);
Assert.AreEqual("SUM(Sh3!A1:A4)", HSSFFormulaParser.ToFormulaString(stubHSSF, fr.ParsedExpression));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("3dFormulas.xls");
Assert.AreEqual("Sheet1!A1", wb.GetSheetAt(0).GetRow(1).GetCell(0).CellFormula);
Assert.AreEqual("SUM(Sh3!A1:A4)", wb.GetSheetAt(0).GetRow(6).GetCell(0).CellFormula);
}
public void TestXRefs()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
ICell cell;
// VLookup on a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
// Simple reference to a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
// Evaluate the cells
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData)
}
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(2)
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(4)
);
// Re-check VLOOKUP one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// Re-check ref one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// Add a formula that refers to one of the existing external workbooks
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(40);
cell.CellFormula = (/*setter*/ "Cost*[XRefCalcData.xls]MarkupSheet!$B$1");
// Check is was stored correctly
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
// Check it Evaluates correctly
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Try to add a formula for a new external workbook, won't be allowed to start
try
{
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(42);
cell.CellFormula = (/*setter*/ "[alt.xls]Sheet0!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception e) { }
// Link our new workbook
HSSFWorkbook alt = new HSSFWorkbook();
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
wb.LinkExternalWorkbook("alt.xls", alt);
// Now add a formula that refers to our new workbook
cell.CellFormula = (/*setter*/ "[alt.xls]Sheet0!$A$1");
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
// Evaluate it, without a link to that workbook
try
{
eval.Evaluate(cell);
Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
}
catch (Exception e) { }
// Add a link, check it does
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
// Save and re-load
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);
eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
// Check the one referring to the previously existing workbook behaves
cell = wb.GetSheetAt(0).GetRow(1).GetCell(40);
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Now check the newly Added reference
cell = wb.GetSheetAt(0).GetRow(1).GetCell(42);
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
}
public void TestWriteSheetStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (BorderStyle.Thin);
cs.BorderLeft = (BorderStyle.Thin);
cs.BorderRight = (BorderStyle.Thin);
cs.BorderTop = (BorderStyle.Thin);
cs.FillForegroundColor = ((short)0xA);
cs.FillPattern = FillPatternType.SolidForeground;
fnt.Color = ((short)0xf);
fnt.IsItalic = (true);
cs2.FillForegroundColor = ((short)0x0);
cs2.FillPattern = FillPatternType.SolidForeground;
cs2.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.CreateRow(rownum);
r.RowStyle = (cs2);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
s = wb.GetSheetAt(0);
Assert.IsNotNull(s, "Sheet is not null");
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs = r.RowStyle;
Assert.AreEqual(cs.BorderBottom, BorderStyle.Thin, "Bottom Border Style for row: ");
Assert.AreEqual(cs.BorderLeft, BorderStyle.Thin, "Left Border Style for row: ");
Assert.AreEqual(cs.BorderRight, BorderStyle.Thin, "Right Border Style for row: ");
Assert.AreEqual(cs.BorderTop, BorderStyle.Thin, "Top Border Style for row: ");
Assert.AreEqual(0xA, cs.FillForegroundColor, "FillForegroundColor for row: ");
Assert.AreEqual((short)0x1, (short)cs.FillPattern, "FillPattern for row: ");
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs2 = r.RowStyle;
Assert.AreEqual(cs2.FillForegroundColor, (short)0x0, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs2.FillPattern, (short)0x1, "FillPattern for row: ");
}
}
public void TestReadNaN()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("49761.xls");
Assert.IsNotNull(wb);
}
/**
* create a rectangle, save the workbook, read back and verify that all shape properties are there
*/
public void TestReadWriteRectangle()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch drawing = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short)2, 2, (short)4, 4);
anchor.AnchorType = (AnchorType)(2);
Assert.AreEqual(anchor.AnchorType, 2);
HSSFSimpleShape rectangle = drawing.CreateSimpleShape(anchor);
rectangle.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
rectangle.LineWidth = (10000);
rectangle.FillColor = (777);
Assert.AreEqual(rectangle.FillColor, 777);
Assert.AreEqual(10000, rectangle.LineWidth);
rectangle.LineStyle = (LineStyle)(10);
Assert.AreEqual(10, rectangle.LineStyle);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_SQUARE);
rectangle.LineStyleColor = (1111);
rectangle.IsNoFill = (true);
rectangle.WrapText = (HSSFSimpleShape.WRAP_NONE);
rectangle.String = (new HSSFRichTextString("teeeest"));
Assert.AreEqual(rectangle.LineStyleColor, 1111);
Assert.AreEqual(((EscherSimpleProperty)((EscherOptRecord)HSSFTestHelper.GetEscherContainer(rectangle).GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.TEXT__TEXTID)).PropertyValue, "teeeest".GetHashCode());
Assert.AreEqual(rectangle.IsNoFill, true);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
Assert.AreEqual(rectangle.String.String, "teeeest");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, drawing.Children.Count);
HSSFSimpleShape rectangle2 =
(HSSFSimpleShape)drawing.Children[0];
Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE,
rectangle2.ShapeType);
Assert.AreEqual(10000, rectangle2.LineWidth);
Assert.AreEqual(10, (int)rectangle2.LineStyle);
Assert.AreEqual(anchor, rectangle2.Anchor);
Assert.AreEqual(rectangle2.LineStyleColor, 1111);
Assert.AreEqual(rectangle2.FillColor, 777);
Assert.AreEqual(rectangle2.IsNoFill, true);
Assert.AreEqual(rectangle2.String.String, "teeeest");
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
rectangle2.FillColor = (3333);
rectangle2.LineStyle = (LineStyle)(9);
rectangle2.LineStyleColor = (4444);
rectangle2.IsNoFill = (false);
rectangle2.LineWidth = (77);
rectangle2.Anchor.Dx1 = 2;
rectangle2.Anchor.Dx2 = 3;
rectangle2.Anchor.Dy1 = (4);
rectangle2.Anchor.Dy2 = (5);
rectangle.WrapText = (HSSFSimpleShape.WRAP_BY_POINTS);
rectangle2.String = (new HSSFRichTextString("test22"));
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, drawing.Children.Count);
rectangle2 = (HSSFSimpleShape)drawing.Children[0];
Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.ShapeType);
Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_BY_POINTS);
Assert.AreEqual(77, rectangle2.LineWidth);
Assert.AreEqual(9, rectangle2.LineStyle);
Assert.AreEqual(rectangle2.LineStyleColor, 4444);
Assert.AreEqual(rectangle2.FillColor, 3333);
Assert.AreEqual(rectangle2.Anchor.Dx1, 2);
Assert.AreEqual(rectangle2.Anchor.Dx2, 3);
Assert.AreEqual(rectangle2.Anchor.Dy1, 4);
Assert.AreEqual(rectangle2.Anchor.Dy2, 5);
Assert.AreEqual(rectangle2.IsNoFill, false);
Assert.AreEqual(rectangle2.String.String, "test22");
HSSFSimpleShape rect3 = drawing.CreateSimpleShape(new HSSFClientAnchor());
rect3.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
drawing = (wb.GetSheetAt(0) as HSSFSheet).DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(drawing.Children.Count, 2);
}
private static HSSFWorkbook WriteOutAndReadBack(HSSFWorkbook original)
{
return(HSSFTestDataSamples.WriteOutAndReadBack(original));
}
private void CheckWorkbookBack(HSSFWorkbook wb)
{
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
HSSFSheet sheetBack = wbBack.GetSheetAt(0) as HSSFSheet;
Assert.IsNotNull(sheetBack);
HSSFPatriarch patriarchBack = sheetBack.DrawingPatriarch as HSSFPatriarch;
Assert.IsNotNull(patriarchBack);
IList <HSSFShape> children = patriarchBack.Children;
Assert.AreEqual(4, children.Count);
HSSFShape hssfShape = children[(0)];
Assert.IsTrue(hssfShape is HSSFSimpleShape);
HSSFAnchor anchor = hssfShape.Anchor;
Assert.IsTrue(anchor is HSSFClientAnchor);
Assert.AreEqual(0, anchor.Dx1);
Assert.AreEqual(512, anchor.Dx2);
Assert.AreEqual(0, anchor.Dy1);
Assert.AreEqual(100, anchor.Dy2);
HSSFClientAnchor cAnchor = (HSSFClientAnchor)anchor;
Assert.AreEqual(1, cAnchor.Col1);
Assert.AreEqual(1, cAnchor.Col2);
Assert.AreEqual(1, cAnchor.Row1);
Assert.AreEqual(1, cAnchor.Row2);
hssfShape = children[(1)];
Assert.IsTrue(hssfShape is HSSFSimpleShape);
anchor = hssfShape.Anchor;
Assert.IsTrue(anchor is HSSFClientAnchor);
Assert.AreEqual(512, anchor.Dx1);
Assert.AreEqual(1023, anchor.Dx2);
Assert.AreEqual(0, anchor.Dy1);
Assert.AreEqual(100, anchor.Dy2);
cAnchor = (HSSFClientAnchor)anchor;
Assert.AreEqual(1, cAnchor.Col1);
Assert.AreEqual(1, cAnchor.Col2);
Assert.AreEqual(1, cAnchor.Row1);
Assert.AreEqual(1, cAnchor.Row2);
hssfShape = children[(2)];
Assert.IsTrue(hssfShape is HSSFSimpleShape);
anchor = hssfShape.Anchor;
Assert.IsTrue(anchor is HSSFClientAnchor);
Assert.AreEqual(0, anchor.Dx1);
Assert.AreEqual(512, anchor.Dx2);
Assert.AreEqual(0, anchor.Dy1);
Assert.AreEqual(100, anchor.Dy2);
cAnchor = (HSSFClientAnchor)anchor;
Assert.AreEqual(2, cAnchor.Col1);
Assert.AreEqual(2, cAnchor.Col2);
Assert.AreEqual(2, cAnchor.Row1);
Assert.AreEqual(2, cAnchor.Row2);
hssfShape = children[(3)];
Assert.IsTrue(hssfShape is HSSFSimpleShape);
anchor = hssfShape.Anchor;
Assert.IsTrue(anchor is HSSFClientAnchor);
Assert.AreEqual(0, anchor.Dx1);
Assert.AreEqual(512, anchor.Dx2);
Assert.AreEqual(100, anchor.Dy1);
Assert.AreEqual(200, anchor.Dy2);
cAnchor = (HSSFClientAnchor)anchor;
Assert.AreEqual(2, cAnchor.Col1);
Assert.AreEqual(2, cAnchor.Col2);
Assert.AreEqual(2, cAnchor.Row1);
Assert.AreEqual(2, cAnchor.Row2);
}
private static HSSFWorkbook WriteRead(HSSFWorkbook b)
{
return(HSSFTestDataSamples.WriteOutAndReadBack(b));
}
public void TestAddToExistingSheet()
{
// dvEmpty.xls is a simple one sheet workbook. With a DataValidations header record but no
// DataValidations. It's important that the example has one SHEETPROTECTION record.
// Such a workbook can be Created in Excel (2007) by Adding datavalidation for one cell
// and then deleting the row that Contains the cell.
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
ISheet sheet = wb.GetSheetAt(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
dv.EmptyCellAllowed = (/*setter*/false);
dv.ErrorStyle = (/*setter*/ERRORSTYLE.STOP);
dv.ShowPromptBox = (/*setter*/true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] wbData = baos.ToArray();
#if !HIDE_UNREACHABLE_CODE
if (false)
{ // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)
ERFListener erfListener = null; // new MyERFListener();
EventRecordFactory erf = new EventRecordFactory(erfListener, null);
try
{
POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray()));
throw new NotImplementedException("The method CreateDocumentInputStream of POIFSFileSystem is not implemented.");
//erf.ProcessRecords(fs.CreateDocumentInputStream("Workbook"));
}
catch (RecordFormatException e)
{
throw new RuntimeException(e);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
}
// else verify record ordering by navigating the raw bytes
#endif
byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, };
int dvHeaderOffset = FindIndex(wbData, dvHeaderRecStart);
Assert.IsTrue(dvHeaderOffset > 0);
int nextRecIndex = dvHeaderOffset + 22;
int nextSid
= ((wbData[nextRecIndex + 0] << 0) & 0x00FF)
+ ((wbData[nextRecIndex + 1] << 8) & 0xFF00)
;
// nextSid should be for a DVRecord. If anything comes between the DV header record
// and the DV records, Excel will not be able to open the workbook without error.
if (nextSid == 0x0867)
{
throw new AssertionException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
public void TestOpensProperly()
{
OPCPackage.Open(HSSFTestDataSamples.OpenSampleFileStream("sample.xlsx"));
}
public void TestSetGetProperties()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox.String = (new HSSFRichTextString("test"));
Assert.AreEqual(textbox.String.String, "test");
textbox.HorizontalAlignment = ((short)5);
Assert.AreEqual(textbox.HorizontalAlignment, 5);
textbox.VerticalAlignment = ((short)6);
Assert.AreEqual(textbox.VerticalAlignment, (short)6);
textbox.MarginBottom = (7);
Assert.AreEqual(textbox.MarginBottom, 7);
textbox.MarginLeft = (8);
Assert.AreEqual(textbox.MarginLeft, 8);
textbox.MarginRight = (9);
Assert.AreEqual(textbox.MarginRight, 9);
textbox.MarginTop = (10);
Assert.AreEqual(textbox.MarginTop, 10);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test");
Assert.AreEqual(textbox.HorizontalAlignment, 5);
Assert.AreEqual(textbox.VerticalAlignment, (short)6);
Assert.AreEqual(textbox.MarginBottom, 7);
Assert.AreEqual(textbox.MarginLeft, 8);
Assert.AreEqual(textbox.MarginRight, 9);
Assert.AreEqual(textbox.MarginTop, 10);
textbox.String = (new HSSFRichTextString("test1"));
textbox.HorizontalAlignment = (HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED);
textbox.VerticalAlignment = (HSSFTextbox.VERTICAL_ALIGNMENT_TOP);
textbox.MarginBottom = (71);
textbox.MarginLeft = (81);
textbox.MarginRight = (91);
textbox.MarginTop = (101);
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED);
Assert.AreEqual(textbox.VerticalAlignment, HSSFTextbox.VERTICAL_ALIGNMENT_TOP);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HSSFTextbox.HORIZONTAL_ALIGNMENT_CENTERED);
Assert.AreEqual(textbox.VerticalAlignment, HSSFTextbox.VERTICAL_ALIGNMENT_TOP);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
}
private static OldExcelExtractor CreateExtractor(String sampleFileName)
{
FileInfo file = HSSFTestDataSamples.GetSampleFile(sampleFileName);
return(new OldExcelExtractor(file));
}
private static void OperationRefTest(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = ("A2" + operator1 + "A3");
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
String ref1 = null;
String ref2 = null;
short refx1 = 0;
short refy1 = 0;
short refx2 = 0;
short refy2 = 0;
if (x + 50 < short.MaxValue)
{
refx1 = (short)(x + 50);
refx2 = (short)(x + 46);
}
else
{
refx1 = (short)(x - 4);
refx2 = (short)(x - 3);
}
if (y + 50 < 255)
{
refy1 = (short)(y + 50);
refy2 = (short)(y + 49);
}
else
{
refy1 = (short)(y - 4);
refy2 = (short)(y - 3);
}
c = r.GetCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
ref1 = cr.FormatAsString();
cr = new CellReference(refx2, refy2, false, false);
ref2 = cr.FormatAsString();
c = r.CreateCell(y);
c.CellFormula = ("" + ref1 + operator1 + ref2);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + "B1" + operator1 + "IV255");
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
OperationalRefVerify(operator1, wb);
}
public void SetUp()
{
wbH = HSSFTestDataSamples.OpenSampleWorkbook("TwoSheetsOneHidden.xls");
wbU = HSSFTestDataSamples.OpenSampleWorkbook("TwoSheetsNoneHidden.xls");
}
private static HSSFWorkbook OpenSample(String sampleFileName)
{
return(HSSFTestDataSamples.OpenSampleWorkbook(sampleFileName));
}
public void TestReadFeatRecord()
{
HSSFWorkbook hssf =
HSSFTestDataSamples.OpenSampleWorkbook("46136-NoWarnings.xls");
InternalWorkbook wb = HSSFTestHelper.GetWorkbookForTest(hssf);
FeatRecord fr = null;
FeatHdrRecord fhr = null;
Assert.AreEqual(1, hssf.NumberOfSheets);
// First check it isn't on the Workbook
int countFR = 0;
int countFRH = 0;
foreach (Record r in wb.Records)
{
if (r is FeatRecord)
{
fr = (FeatRecord)r;
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
Assert.Fail("FeatRecord SID found but not Created correctly!");
}
if (r is FeatHdrRecord)
{
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
Assert.Fail("FeatHdrRecord SID found but not Created correctly!");
}
}
Assert.AreEqual(0, countFR);
Assert.AreEqual(0, countFRH);
// Now find it on our sheet
HSSFSheet s = (HSSFSheet)hssf.GetSheetAt(0);
InternalSheet sheet = HSSFTestHelper.GetSheetForTest(s);
foreach (RecordBase rb in sheet.Records)
{
if (rb is Record)
{
Record r = (Record)rb;
if (r is FeatRecord)
{
fr = (FeatRecord)r;
countFR++;
}
else if (r.Sid == FeatRecord.sid)
{
countFR++;
}
if (r is FeatHdrRecord)
{
fhr = (FeatHdrRecord)r;
countFRH++;
}
else if (r.Sid == FeatHdrRecord.sid)
{
countFRH++;
}
}
}
Assert.AreEqual(1, countFR);
Assert.AreEqual(1, countFRH);
Assert.IsNotNull(fr);
Assert.IsNotNull(fhr);
// Now check the contents are as expected
Assert.AreEqual(
FeatHdrRecord.SHAREDFEATURES_ISFFEC2,
fr.Isf_sharedFeatureType
);
// Applies to one cell only
Assert.AreEqual(1, fr.CellRefs.Length);
Assert.AreEqual(0, fr.CellRefs[0].FirstRow);
Assert.AreEqual(0, fr.CellRefs[0].LastRow);
Assert.AreEqual(0, fr.CellRefs[0].FirstColumn);
Assert.AreEqual(0, fr.CellRefs[0].LastColumn);
// More Checking of shared features stuff
Assert.AreEqual(4, fr.CbFeatData);
Assert.AreEqual(4, fr.SharedFeature.DataSize);
Assert.AreEqual(typeof(FeatFormulaErr2), fr.SharedFeature.GetType());
FeatFormulaErr2 fferr2 = (FeatFormulaErr2)fr.SharedFeature;
Assert.AreEqual(0x04, fferr2.RawErrorCheckValue);
Assert.IsFalse(fferr2.CheckCalculationErrors);
Assert.IsFalse(fferr2.CheckDateTimeFormats);
Assert.IsFalse(fferr2.CheckEmptyCellRef);
Assert.IsFalse(fferr2.CheckInconsistentFormulas);
Assert.IsFalse(fferr2.CheckInconsistentRanges);
Assert.IsTrue(fferr2.CheckNumbersAsText);
Assert.IsFalse(fferr2.CheckUnprotectedFormulas);
Assert.IsFalse(fferr2.PerformDataValidation);
}
