public void TestReadDBCSHeaderFooter()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("DBCSHeader.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IHeader h = s.Header;
Assert.AreEqual(h.Left, "\u090f\u0915", "Header Left ");
Assert.AreEqual(h.Center, "\u0939\u094b\u0917\u093e", "Header Center ");
Assert.AreEqual(h.Right, "\u091c\u093e", "Header Right ");
IFooter f = s.Footer;
Assert.AreEqual(f.Left, "\u091c\u093e", "Footer Left ");
Assert.AreEqual(f.Center, "\u091c\u093e", "Footer Center ");
Assert.AreEqual(f.Right, "\u091c\u093e", "Footer Right ");
}
public void Test43493()
{
// Has crazy corrupt sub-records on
// a EmbeddedObjectRefSubRecord
try
{
HSSFTestDataSamples.OpenSampleWorkbook("43493.xls");
}
catch (RecordFormatException e)
{
if (e.InnerException.InnerException is IndexOutOfRangeException)
{
throw new AssertFailedException("Identified bug 43493");
}
throw e;
}
}
public void TestExistingFileWithText()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("text") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFTextbox textbox = (HSSFTextbox)Drawing.Children[0];
Assert.AreEqual(HorizontalAlignment.Left, textbox.HorizontalAlignment);
Assert.AreEqual(VerticalAlignment.Center, textbox.VerticalAlignment);
Assert.AreEqual(textbox.MarginTop, 0);
Assert.AreEqual(textbox.MarginBottom, 3600000);
Assert.AreEqual(textbox.MarginLeft, 3600000);
Assert.AreEqual(textbox.MarginRight, 0);
Assert.AreEqual(textbox.String.String, "teeeeesssstttt");
}
public void TestExistingFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("polygon") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFPolygon polygon = (HSSFPolygon)Drawing.Children[0];
Assert.AreEqual(polygon.DrawAreaHeight, 2466975);
Assert.AreEqual(polygon.DrawAreaWidth, 3686175);
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 0, 0, 31479, 16159, 19676, 20502 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 0, 0, 36, 56, 34, 18 }));
wb.Close();
}
public void TestMacroFunction()
{
// testNames.xls contains a VB function called 'myFunc'
HSSFWorkbook w = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(w);
Ptg[] ptg = HSSFFormulaParser.Parse("myFunc()", w);
// the name Gets encoded as the first arg
NamePtg tname = (NamePtg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString(book));
AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[1];
Assert.IsTrue(tfunc.IsExternalFunction);
}
public void TestShiftRows()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("46445.xls");
Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
//verify existing hyperlink in A3
ICell cell1 = sheet.GetRow(2).GetCell(0);
IHyperlink link1 = cell1.Hyperlink;
Assert.IsNotNull(link1);
Assert.AreEqual(2, link1.FirstRow);
Assert.AreEqual(2, link1.LastRow);
//assign a hyperlink to A4
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Document);
link2.Address = ("Sheet2!A2");
ICell cell2 = sheet.GetRow(3).GetCell(0);
cell2.Hyperlink = (link2);
Assert.AreEqual(3, link2.FirstRow);
Assert.AreEqual(3, link2.LastRow);
//move the 3rd row two rows down
sheet.ShiftRows(sheet.FirstRowNum, sheet.LastRowNum, 2);
//cells A3 and A4 don't contain hyperlinks anymore
Assert.IsNull(sheet.GetRow(2).GetCell(0).Hyperlink);
Assert.IsNull(sheet.GetRow(3).GetCell(0).Hyperlink);
//the first hypelink now belongs to A5
IHyperlink link1_shifted = sheet.GetRow(2 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link1_shifted);
Assert.AreEqual(4, link1_shifted.FirstRow);
Assert.AreEqual(4, link1_shifted.LastRow);
//the second hypelink now belongs to A6
IHyperlink link2_shifted = sheet.GetRow(3 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link2_shifted);
Assert.AreEqual(5, link2_shifted.FirstRow);
Assert.AreEqual(5, link2_shifted.LastRow);
}
public void TestCountifBug51498()
{
int REF_COL = 4;
int EVAL_COL = 3;
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("51498.xls");
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = workbook.GetSheetAt(0);
// numeric criteria
for (int i = 0; i < 8; i++)
{
CellValue expected = evaluator.Evaluate(sheet.GetRow(i).GetCell(REF_COL));
CellValue actual = evaluator.Evaluate(sheet.GetRow(i).GetCell(EVAL_COL));
Assert.AreEqual(expected.FormatAsString(), actual.FormatAsString());
}
// boolean criteria
for (int i = 0; i < 8; i++)
{
HSSFCell cellFmla = (HSSFCell)sheet.GetRow(i).GetCell(8);
HSSFCell cellRef = (HSSFCell)sheet.GetRow(i).GetCell(9);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
// string criteria
for (int i = 1; i < 9; i++)
{
ICell cellFmla = sheet.GetRow(i).GetCell(13);
ICell cellRef = sheet.GetRow(i).GetCell(14);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
}
public void TestAll()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("yearfracExamples.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
int nSuccess = 0;
int nFailures = 0;
int nUnexpectedErrors = 0;
IEnumerator rowIterator = sheet.GetRowEnumerator();
while (rowIterator.MoveNext())
{
Row row = (Row)rowIterator.Current;
Cell cell = row.GetCell(SS.YEARFRAC_FORMULA_COLUMN);
if (cell == null || cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
try
{
ProcessRow(row, cell, formulaEvaluator);
nSuccess++;
}
catch (AssertFailedException e)
{
nFailures++;
}
catch (Exception e)
{
nUnexpectedErrors++;
}
}
if (nUnexpectedErrors + nFailures > 0)
{
String msg = nFailures + " failures(s) and " + nUnexpectedErrors
+ " unexpected errors(s) occurred. See stderr for details";
throw new AssertFailedException(msg);
}
if (nSuccess < 1)
{
throw new Exception("No test sample cases found");
}
}
public void TestReadExistingRectangle()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("rectangles") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFSimpleShape shape = (HSSFSimpleShape)Drawing.Children[0];
Assert.AreEqual(shape.IsNoFill, false);
Assert.AreEqual((int)shape.LineStyle, HSSFShape.LINESTYLE_DASHDOTGEL);
Assert.AreEqual(shape.LineStyleColor, 0x616161);
Assert.AreEqual(shape.FillColor, 0x2CE03D, HexDump.ToHex(shape.FillColor));
Assert.AreEqual(shape.LineWidth, HSSFShape.LINEWIDTH_ONE_PT * 2);
Assert.AreEqual(shape.String.String, "POItest");
Assert.AreEqual(shape.RotationDegree, 27);
}
public void TestDrawingGroups()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("groups") as HSSFSheet;
HSSFPatriarch patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFShapeGroup group = (HSSFShapeGroup)patriarch.Children[1];
Assert.AreEqual(3, group.Children.Count);
HSSFShapeGroup group1 = (HSSFShapeGroup)group.Children[0];
Assert.AreEqual(2, group1.Children.Count);
group1 = (HSSFShapeGroup)group.Children[2];
Assert.AreEqual(2, group1.Children.Count);
wb.Close();
}
public void TestReading()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("ReferencePtg.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
// First row
Assert.AreEqual(55.0,
sheet.GetRow(0).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(55.0,
sheet.GetRow(0).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A1",
sheet.GetRow(0).GetCell((short)1).CellFormula, "Wrong formula string for reference");
// Now moving over the 2**15 boundary
// (Remember that excel row (n) is1 poi row (n-1)
Assert.AreEqual(32767.0,
sheet.GetRow(32766).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32767.0,
sheet.GetRow(32766).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32767",
sheet.GetRow(32766).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32768.0,
sheet.GetRow(32767).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32768.0,
sheet.GetRow(32767).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32768",
sheet.GetRow(32767).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32769.0,
sheet.GetRow(32768).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32769.0,
sheet.GetRow(32768).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32769",
sheet.GetRow(32768).GetCell((short)1).CellFormula, "Wrong formula string for reference");
Assert.AreEqual(32770.0,
sheet.GetRow(32769).GetCell((short)0).NumericCellValue, 0.0, "Wrong numeric value for original number");
Assert.AreEqual(32770.0,
sheet.GetRow(32769).GetCell((short)1).NumericCellValue, 0.0, "Wrong numeric value for referemce");
Assert.AreEqual("A32770",
sheet.GetRow(32769).GetCell((short)1).CellFormula, "Wrong formula string for reference");
}
public void TestCountifExamples()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("countifExamples.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet sheet1 = (HSSFSheet)wb.GetSheet("MSDN Example 1");
for (int rowIx = 7; rowIx <= 12; rowIx++)
{
HSSFRow row = (HSSFRow)sheet1.GetRow(rowIx - 1);
HSSFCell cellA = (HSSFCell)row.GetCell(0); // cell containing a formula with COUNTIF
Assert.AreEqual(CellType.Formula, cellA.CellType);
HSSFCell cellC = (HSSFCell)row.GetCell(2); // cell with a reference value
Assert.AreEqual(CellType.Numeric, cellC.CellType);
CellValue cv = fe.Evaluate(cellA);
double actualValue = cv.NumberValue;
double expectedValue = cellC.NumericCellValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " + new CellReference(cellA).FormatAsString()
+ ": " + cellA.CellFormula + " :"
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
HSSFSheet sheet2 = (HSSFSheet)wb.GetSheet("MSDN Example 2");
for (int rowIx = 9; rowIx <= 14; rowIx++)
{
HSSFRow row = (HSSFRow)sheet2.GetRow(rowIx - 1);
HSSFCell cellA = (HSSFCell)row.GetCell(0); // cell containing a formula with COUNTIF
Assert.AreEqual(CellType.Formula, cellA.CellType);
HSSFCell cellC = (HSSFCell)row.GetCell(2); // cell with a reference value
Assert.AreEqual(CellType.Numeric, cellC.CellType);
CellValue cv = fe.Evaluate(cellA);
double actualValue = cv.NumberValue;
double expectedValue = cellC.NumericCellValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellA).FormatAsString() + "[" + cellA.CellFormula + "]: "
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
}
public void TestReadExistingImage()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("pictures") as HSSFSheet;
HSSFPatriarch Drawing = sheet.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(1, Drawing.Children.Count);
HSSFPicture picture = (HSSFPicture)Drawing.Children[0];
Assert.AreEqual(picture.PictureIndex, 2);
Assert.AreEqual(picture.LineStyleColor, HSSFShape.LINESTYLE__COLOR_DEFAULT);
Assert.AreEqual(picture.FillColor, 0x5DC943);
Assert.AreEqual(picture.LineWidth, HSSFShape.LINEWIDTH_DEFAULT);
Assert.AreEqual(picture.LineStyle, HSSFShape.LINESTYLE_DEFAULT);
Assert.AreEqual(picture.IsNoFill, false);
picture.PictureIndex = (2);
Assert.AreEqual(picture.PictureIndex, 2);
}
public void TestBug57231()
{
HSSFWorkbook wb = HSSFTestDataSamples
.OpenSampleWorkbook("57231_MixedGasReport.xls");
HSSFSheet sheet = wb.GetSheet("master") as HSSFSheet;
HSSFSheet newSheet = wb.CloneSheet(wb.GetSheetIndex(sheet)) as HSSFSheet;
int idx = wb.GetSheetIndex(newSheet);
wb.SetSheetName(idx, "newName");
// Write the output to a file
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbBack);
Assert.IsNotNull(wbBack.GetSheet("master"));
Assert.IsNotNull(wbBack.GetSheet("newName"));
}
public void TestMultiSheetAreasHSSFandXSSF()
{
IWorkbook wb1 = HSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xls");
IWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("55906-MultiSheetRefs.xlsx");
foreach (IWorkbook wb in new IWorkbook[] { wb1, wb2 })
{
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet s1 = wb.GetSheetAt(0);
// SUM over a range
ICell sumFA = s1.GetRow(2).GetCell(7);
Assert.IsNotNull(sumFA);
Assert.AreEqual("SUM(Sheet1:Sheet3!A1:B2)", sumFA.CellFormula);
Assert.AreEqual("110", Evaluator.Evaluate(sumFA).FormatAsString(), "Failed for " + wb.GetType());
// Various Stats formulas on ranges of numbers
ICell avgFA = s1.GetRow(2).GetCell(8);
Assert.IsNotNull(avgFA);
Assert.AreEqual("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.CellFormula);
Assert.AreEqual("27.5", Evaluator.Evaluate(avgFA).FormatAsString());
ICell minFA = s1.GetRow(3).GetCell(8);
Assert.IsNotNull(minFA);
Assert.AreEqual("MIN(Sheet1:Sheet3!A$1:B$2)", minFA.CellFormula);
Assert.AreEqual("11", Evaluator.Evaluate(minFA).FormatAsString());
ICell maxFA = s1.GetRow(4).GetCell(8);
Assert.IsNotNull(maxFA);
Assert.AreEqual("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.CellFormula);
Assert.AreEqual("44", Evaluator.Evaluate(maxFA).FormatAsString());
ICell countFA = s1.GetRow(5).GetCell(8);
Assert.IsNotNull(countFA);
Assert.AreEqual("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.CellFormula);
Assert.AreEqual("4", Evaluator.Evaluate(countFA).FormatAsString());
}
wb2.Close();
wb1.Close();
}
public void TestMultipleExternSheetRecords_bug45698()
{
HSSFWorkbook wb;
try
{
wb = HSSFTestDataSamples.OpenSampleWorkbook("ex45698-22488.xls");
}
catch (Exception e)
{
if ("Extern sheet is part of LinkTable".Equals(e.Message))
{
throw new AssertionException("Identified bug 45698");
}
throw e;
}
// some other sanity Checks
Assert.AreEqual(7, wb.NumberOfSheets);
}
public void TestCloneSheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls");
ICell cell;
IHyperlink link;
NPOI.SS.UserModel.ISheet sheet = wb.CloneSheet(0);
cell = sheet.GetRow(4).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(8).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/hssf/", link.Address);
}
public void TestGetDrawings()
{
HSSFWorkbook wb1c = HSSFTestDataSamples.OpenSampleWorkbook("WithChart.xls");
HSSFWorkbook wb2c = HSSFTestDataSamples.OpenSampleWorkbook("WithTwoCharts.xls");
// 1 chart sheet -> data on 1st, chart on 2nd
Assert.IsNotNull(((HSSFSheet)wb1c.GetSheetAt(0)).DrawingPatriarch);
Assert.IsNotNull(((HSSFSheet)wb1c.GetSheetAt(1)).DrawingPatriarch);
Assert.IsFalse((((HSSFSheet)wb1c.GetSheetAt(0)).DrawingPatriarch as HSSFPatriarch).ContainsChart());
Assert.IsTrue((((HSSFSheet)wb1c.GetSheetAt(1)).DrawingPatriarch as HSSFPatriarch).ContainsChart());
// 2 chart sheet -> data on 1st, chart on 2nd+3rd
Assert.IsNotNull(((HSSFSheet)wb2c.GetSheetAt(0)).DrawingPatriarch);
Assert.IsNotNull(((HSSFSheet)wb2c.GetSheetAt(1)).DrawingPatriarch);
Assert.IsNotNull(((HSSFSheet)wb2c.GetSheetAt(2)).DrawingPatriarch);
Assert.IsFalse((((HSSFSheet)wb2c.GetSheetAt(0)).DrawingPatriarch as HSSFPatriarch).ContainsChart());
Assert.IsTrue((((HSSFSheet)wb2c.GetSheetAt(1)).DrawingPatriarch as HSSFPatriarch).ContainsChart());
Assert.IsTrue((((HSSFSheet)wb2c.GetSheetAt(2)).DrawingPatriarch as HSSFPatriarch).ContainsChart());
}
public void TestFromFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("sumifs.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = (HSSFSheet)wb.GetSheet("Example 1");
HSSFCell ex1cell1 = (HSSFCell)example1.GetRow(10).GetCell(2);
fe.Evaluate(ex1cell1);
Assert.AreEqual(20.0, ex1cell1.NumericCellValue);
HSSFCell ex1cell2 = (HSSFCell)example1.GetRow(11).GetCell(2);
fe.Evaluate(ex1cell2);
Assert.AreEqual(30.0, ex1cell2.NumericCellValue);
HSSFSheet example2 = (HSSFSheet)wb.GetSheet("Example 2");
HSSFCell ex2cell1 = (HSSFCell)example2.GetRow(6).GetCell(2);
fe.Evaluate(ex2cell1);
Assert.AreEqual(500.0, ex2cell1.NumericCellValue);
HSSFCell ex2cell2 = (HSSFCell)example2.GetRow(7).GetCell(2);
fe.Evaluate(ex2cell2);
Assert.AreEqual(8711.0, ex2cell2.NumericCellValue);
HSSFSheet example3 = (HSSFSheet)wb.GetSheet("Example 3");
HSSFCell ex3cell = (HSSFCell)example3.GetRow(5).GetCell(2);
fe.Evaluate(ex3cell);
Assert.AreEqual(8, 8, ex3cell.NumericCellValue);
HSSFSheet example4 = (HSSFSheet)wb.GetSheet("Example 4");
HSSFCell ex4cell = (HSSFCell)example4.GetRow(8).GetCell(2);
fe.Evaluate(ex4cell);
Assert.AreEqual(3.5, ex4cell.NumericCellValue);
HSSFSheet example5 = (HSSFSheet)wb.GetSheet("Example 5");
HSSFCell ex5cell = (HSSFCell)example5.GetRow(8).GetCell(2);
fe.Evaluate(ex5cell);
Assert.AreEqual(625000.0, ex5cell.NumericCellValue);
}
public void TestCustomPalette()
{
//reading sample xls
HSSFWorkbook book = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//creating custom palette
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex((short)0x12, (byte)101, (byte)230, (byte)100);
palette.SetColorAtIndex((short)0x3b, (byte)0, (byte)255, (byte)52);
//writing to disk; reading in and verifying palette
string tmppath = TempFile.GetTempFilePath("TestCustomPalette", ".xls");
FileStream fos = new FileStream(tmppath, FileMode.OpenOrCreate);
book.Write(fos);
fos.Close();
FileStream fis = new FileStream(tmppath, FileMode.Open, FileAccess.Read);
book = new HSSFWorkbook(fis);
fis.Close();
palette = book.GetCustomPalette();
HSSFColor color = palette.GetColor(HSSFColor.Coral.Index); //unmodified
Assert.IsNotNull(color, "Unexpected null in custom palette (unmodified index)");
byte[] expectedRGB = HSSFColor.Coral.Triplet;
byte[] actualRGB = color.RGB;
String msg = "Expected palette position to remain unmodified";
Assert.AreEqual(expectedRGB[0], actualRGB[0], msg);
Assert.AreEqual(expectedRGB[1], actualRGB[1], msg);
Assert.AreEqual(expectedRGB[2], actualRGB[2], msg);
color = palette.GetColor((short)0x12);
Assert.IsNotNull(color, "Unexpected null in custom palette (modified)");
actualRGB = color.RGB;
msg = "Expected palette modification to be preserved across save";
Assert.AreEqual((short)101, actualRGB[0], msg);
Assert.AreEqual((short)230, actualRGB[1], msg);
Assert.AreEqual((short)100, actualRGB[2], msg);
}
public void TestAutoSizeColumn()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("43902.xls");
String sheetName = "my sheet";
HSSFSheet sheet = (HSSFSheet)wb.GetSheet(sheetName);
// Can't use literal numbers for column sizes, as
// will come out with different values on different
// machines based on the fonts available.
// So, we use ranges, which are pretty large, but
// thankfully don't overlap!
int minWithRow1And2 = 6400;
int maxWithRow1And2 = 7800;
int minWithRow1Only = 2750;
int maxWithRow1Only = 3300;
// autoSize the first column and check its size before the merged region (1,0,1,1) is set:
// it has to be based on the 2nd row width
sheet.AutoSizeColumn(0);
Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width");
Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width");
//Create a region over the 2nd row and auto size the first column
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.AutoSizeColumn(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Check that the autoSized column width has ignored the 2nd row
// because it is included in a merged region (Excel like behavior)
NPOI.SS.UserModel.ISheet sheet2 = wb2.GetSheet(sheetName);
Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only);
Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only);
// Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width
sheet2.RemoveMergedRegion(1);
sheet2.AutoSizeColumn(0);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
NPOI.SS.UserModel.ISheet sheet3 = wb3.GetSheet(sheetName);
Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2);
Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2);
}
public void TestMirrFromSpreadsheet()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("mirrTest.xls");
ISheet sheet = wb.GetSheet("Mirr");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
StringBuilder failures = new StringBuilder();
int failureCount = 0;
int[] resultRows = { 9, 19, 29, 45 };
foreach (int rowNum in resultRows)
{
IRow row1 = sheet.GetRow(rowNum);
ICell cellA1 = row1.GetCell(0);
try
{
CellValue cv1 = fe.Evaluate(cellA1);
assertFormulaResult(cv1, cellA1);
}
catch (Exception e)
{
if (failures.Length > 0)
{
failures.Append('\n');
}
failures.Append("Row[").Append(cellA1.RowIndex + 1).Append("]: ").Append(cellA1.CellFormula).Append(" ");
failures.Append(e.Message);
failureCount++;
}
}
IRow row = sheet.GetRow(37);
ICell cellA = row.GetCell(0);
CellValue cv = fe.Evaluate(cellA);
Assert.AreEqual(ErrorEval.DIV_ZERO.ErrorCode, cv.ErrorValue);
if (failures.Length > 0)
{
throw new AssertFailedException(failureCount + " IRR assertions failed:\n" + failures.ToString());
}
}
public void TestRecalculateFormulas47747()
{
/*
* ex47747-sharedFormula.xls is a heavily cut-down version of the spreadsheet from
* the attachment (id=24176) in Bugzilla 47747. This was done to make the sample
* file smaller, which hopefully allows the special data encoding condition to be
* seen more easily. Care must be taken when modifying this file since the
* special conditions are easily destroyed (which would make this test useless).
* It seems that removing the worksheet protection has made this more so - if the
* current file is re-saved in Excel(2007) the bug condition disappears.
*
*
* Using BiffViewer, one can see that there are two shared formula groups representing
* the essentially same formula over ~20 cells. The shared group ranges overlap and
* are A12:Q20 and A20:Q27. The locator cell ('first cell') for the second group is
* Q20 which is not the top left cell of the enclosing range. It is this specific
* condition which caused the bug to occur
*/
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex47747-sharedFormula.xls");
// pick out a cell from within the second shared formula group
HSSFCell cell = (HSSFCell)wb.GetSheetAt(0).GetRow(23).GetCell(0);
string formulaText;
try
{
formulaText = cell.CellFormula;
// succeeds if the formula record has been associated
// with the second shared formula group
}
catch (RuntimeException e)
{
// bug occurs if the formula record has been associated
// with the first shared formula group
if ("Shared Formula Conversion: Coding Error".Equals(e.Message))
{
throw new AssertionException("Identified bug 47747");
}
throw e;
}
Assert.AreEqual("$AF24*A$7", formulaText);
}
public new void Test49928()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("49928.xls");
DoTest49928Core(wb);
// an attempt to register an existing format returns its index
int poundFmtIdx = wb.GetSheetAt(0).GetRow(0).GetCell(0).CellStyle.DataFormat;
Assert.AreEqual(poundFmtIdx, wb.CreateDataFormat().GetFormat(poundFmt));
// now create a custom format with Pound (\u00a3)
IDataFormat dataFormat = wb.CreateDataFormat();
short customFmtIdx = dataFormat.GetFormat("\u00a3##.00[Yellow]");
Assert.IsTrue(customFmtIdx >= BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX);
Assert.AreEqual("\u00a3##.00[Yellow]", dataFormat.GetFormat(customFmtIdx));
wb.Close();
}
public void TestComparedToOLE2()
{
// A fairly simple file - ooxml
XSSFExcelExtractor ooxmlExtractor = GetExtractor("SampleSS.xlsx");
ExcelExtractor ole2Extractor =
new ExcelExtractor(HSSFTestDataSamples.OpenSampleWorkbook("SampleSS.xls"));
POITextExtractor[] extractors =
new POITextExtractor[] { ooxmlExtractor, ole2Extractor };
for (int i = 0; i < extractors.Length; i++)
{
POITextExtractor extractor = extractors[i];
String text = Regex.Replace(extractor.Text, "[\r\t]", "");
Assert.IsTrue(text.StartsWith("First Sheet\nTest spreadsheet\n2nd row2nd row 2nd column\n"));
Regex pattern = new Regex(".*13(\\.0+)?\\s+Sheet3.*", RegexOptions.Compiled);
Assert.IsTrue(pattern.IsMatch(text));
}
}
public void TestCorrectOrderInSpContainer()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.GetSheet("pictures") as HSSFSheet;
HSSFPatriarch drawing = sheet.DrawingPatriarch as HSSFPatriarch;
HSSFSimpleShape rectangle = (HSSFSimpleShape)drawing.Children[0];
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(0).RecordId, EscherSpRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(1).RecordId, EscherOptRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(2).RecordId, EscherClientAnchorRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(3).RecordId, EscherClientDataRecord.RECORD_ID);
rectangle.Anchor = (new HSSFClientAnchor());
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(0).RecordId, EscherSpRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(1).RecordId, EscherOptRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(2).RecordId, EscherClientAnchorRecord.RECORD_ID);
Assert.AreEqual(HSSFTestHelper.GetEscherContainer(rectangle).GetChild(3).RecordId, EscherClientDataRecord.RECORD_ID);
}
public void SheetClone()
{
// First up, try a simple file
IWorkbook b = _testDataProvider.CreateWorkbook();
Assert.AreEqual(0, b.NumberOfSheets);
b.CreateSheet("Sheet One");
b.CreateSheet("Sheet Two");
Assert.AreEqual(2, b.NumberOfSheets);
b.CloneSheet(0);
Assert.AreEqual(3, b.NumberOfSheets);
// Now try a problem one with drawing records in it
IWorkbook bBack = HSSFTestDataSamples.OpenSampleWorkbook("SheetWithDrawing.xls");
Assert.AreEqual(1, bBack.NumberOfSheets);
bBack.CloneSheet(0);
Assert.AreEqual(2, bBack.NumberOfSheets);
bBack.Close();
b.Close();
}
public void TestBug52527()
{
HSSFWorkbook wb1 = HSSFTestDataSamples.OpenSampleWorkbook("52527.xls");
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
Assert.AreEqual("IF(H3,LINEST(N9:N14,K9:M14,FALSE),LINEST(N8:N14,K8:M14,FALSE))",
wb1.GetSheetAt(0).GetRow(4).GetCell(11).CellFormula);
Assert.AreEqual("IF(H3,LINEST(N9:N14,K9:M14,FALSE),LINEST(N8:N14,K8:M14,FALSE))",
wb2.GetSheetAt(0).GetRow(4).GetCell(11).CellFormula);
Assert.AreEqual("1/SQRT(J9)",
wb1.GetSheetAt(0).GetRow(8).GetCell(10).CellFormula);
Assert.AreEqual("1/SQRT(J9)",
wb2.GetSheetAt(0).GetRow(8).GetCell(10).CellFormula);
Assert.AreEqual("1/SQRT(J26)",
wb1.GetSheetAt(0).GetRow(25).GetCell(10).CellFormula);
Assert.AreEqual("1/SQRT(J26)",
wb2.GetSheetAt(0).GetRow(25).GetCell(10).CellFormula);
}
public void TestPrintSetupLandscapeExisting()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithPageBreaks.xls");
Assert.AreEqual(3, workbook.NumberOfSheets);
NPOI.SS.UserModel.ISheet sheetL = workbook.GetSheetAt(0);
NPOI.SS.UserModel.ISheet sheetPM = workbook.GetSheetAt(1);
NPOI.SS.UserModel.ISheet sheetLS = workbook.GetSheetAt(2);
// Check two aspects of the print setup
Assert.IsFalse(sheetL.PrintSetup.Landscape);
Assert.IsTrue(sheetPM.PrintSetup.Landscape);
Assert.IsTrue(sheetLS.PrintSetup.Landscape);
Assert.AreEqual(1, sheetL.PrintSetup.Copies);
Assert.AreEqual(1, sheetPM.PrintSetup.Copies);
Assert.AreEqual(1, sheetLS.PrintSetup.Copies);
// Change one on each
sheetL.PrintSetup.Landscape = (true);
sheetPM.PrintSetup.Landscape = (false);
sheetPM.PrintSetup.Copies = ((short)3);
// Check taken
Assert.IsTrue(sheetL.PrintSetup.Landscape);
Assert.IsFalse(sheetPM.PrintSetup.Landscape);
Assert.IsTrue(sheetLS.PrintSetup.Landscape);
Assert.AreEqual(1, sheetL.PrintSetup.Copies);
Assert.AreEqual(3, sheetPM.PrintSetup.Copies);
Assert.AreEqual(1, sheetLS.PrintSetup.Copies);
// Save and re-load, and Check still there
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.IsTrue(sheetL.PrintSetup.Landscape);
Assert.IsFalse(sheetPM.PrintSetup.Landscape);
Assert.IsTrue(sheetLS.PrintSetup.Landscape);
Assert.AreEqual(1, sheetL.PrintSetup.Copies);
Assert.AreEqual(3, sheetPM.PrintSetup.Copies);
Assert.AreEqual(1, sheetLS.PrintSetup.Copies);
}
public void TestMissingRowRecords_bug41187()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex41187-19267.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(0);
if (row == null)
{
throw new AssertionException("Identified bug 41187 a");
}
if (row.Height == 0)
{
throw new AssertionException("Identified bug 41187 b");
}
Assert.AreEqual("Hi Excel!", row.GetCell(0).RichStringCellValue.String);
// Check row height for 'default' flag
Assert.AreEqual((short)0xFF, row.Height);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}