/**
* Writes a function then Tests to see if its correct
*
*/
public void RefAreaArrayFunctionTest(String function)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = (function + "(A2:A4,B2:B4)");
c = r.CreateCell(1);
c.CellFormula = (function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
(function + "(A2:A4,B2:B4)").Equals(c.CellFormula), "function =" + function + "(A2:A4,B2:B4)"
);
c = r.GetCell(1);
Assert.IsTrue((function + "($A$2:$A4,B$2:B4)").Equals(c.CellFormula),
"function =" + function + "($A$2:$A4,B$2:B4)"
);
}
public void TestNamedCell_2()
{
// setup for this Testcase
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sname);
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cvalue));
// Create named range for a single cell using cellreference
NPOI.SS.UserModel.Name namedCell = wb.CreateName();
namedCell.NameName = (cname);
String reference = sname + "!A1";
namedCell.RefersToFormula = (reference);
// retrieve the newly Created named range
int namedCellIdx = wb.GetNameIndex(cname);
NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx);
Assert.IsNotNull(aNamedCell);
// retrieve the cell at the named range and Test its contents
CellReference cref = new CellReference(aNamedCell.RefersToFormula);
Assert.IsNotNull(cref);
NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName);
Row r = sheet.GetRow(cref.Row);
Cell c = r.GetCell(cref.Col);
String contents = c.RichStringCellValue.String;
Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference");
}
public void TestStripFields()
{
String simple = "I am a Test header";
String withPage = "I am a&P Test header";
String withLots = "I&A am&N a&P Test&T header&U";
String withFont = "I&22 am a&\"Arial,bold\" Test header";
String withOtherAnds = "I am a&P Test header&&";
String withOtherAnds2 = "I am a&P Test header&a&b";
Assert.AreEqual(simple, HSSFHeader.StripFields(simple));
Assert.AreEqual(simple, HSSFHeader.StripFields(withPage));
Assert.AreEqual(simple, HSSFHeader.StripFields(withLots));
Assert.AreEqual(simple, HSSFHeader.StripFields(withFont));
Assert.AreEqual(simple + "&&", HSSFHeader.StripFields(withOtherAnds));
Assert.AreEqual(simple + "&a&b", HSSFHeader.StripFields(withOtherAnds2));
// Now Test the default Strip flag
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("EmbeddedChartHeaderTest.xls");
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0);
Header head = s.Header;
Assert.AreEqual("Top Left", head.Left);
Assert.AreEqual("Top Center", head.Center);
Assert.AreEqual("Top Right", head.Right);
head.Left = ("Top &P&F&D Left");
Assert.AreEqual("Top &P&F&D Left", head.Left);
Assert.AreEqual("Top Left", NPOI.HSSF.UserModel.HeaderFooter.StripFields(head.Left));
// Now even more complex
head.Center = ("HEADER TEXT &P&N&D&T&Z&F&F&A&G&X END");
Assert.AreEqual("HEADER TEXT END", NPOI.HSSF.UserModel.HeaderFooter.StripFields(head.Center));
}
public void Test27349()
{
// 27349-vlookupAcrossSheets.xls is1 bugzilla/attachment.cgi?id=10622
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vlookupAcrossSheets.xls");
HSSFWorkbook wb;
try
{
// original bug may have thrown exception here, or output warning to
// stderr
wb = new HSSFWorkbook(is1);
}
catch (IOException)
{
throw;
}
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
Row row = sheet.GetRow(1);
Cell cell = row.GetCell(0);
// this definitely would have failed due to 27349
Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell.CellFormula);
// We might as well evaluate the formula
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}
public void TestDataStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Row row = s.CreateRow((short)0);
// with Date:
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
// with Calendar:
row = s.CreateRow((short)1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestPrintSetupLandscapeNew()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheetL = workbook.CreateSheet("LandscapeS");
NPOI.SS.UserModel.Sheet sheetP = workbook.CreateSheet("LandscapeP");
// Check two aspects of the print setup
Assert.IsFalse(sheetL.PrintSetup.Landscape);
Assert.IsFalse(sheetP.PrintSetup.Landscape);
Assert.AreEqual(0, sheetL.PrintSetup.Copies);
Assert.AreEqual(0, sheetP.PrintSetup.Copies);
// Change one on each
sheetL.PrintSetup.Landscape = (true);
sheetP.PrintSetup.Copies = ((short)3);
// Check taken
Assert.IsTrue(sheetL.PrintSetup.Landscape);
Assert.IsFalse(sheetP.PrintSetup.Landscape);
Assert.AreEqual(0, sheetL.PrintSetup.Copies);
Assert.AreEqual(3, sheetP.PrintSetup.Copies);
// Save and re-load, and Check still there
MemoryStream baos = new MemoryStream();
workbook.Write(baos);
workbook = new HSSFWorkbook(new MemoryStream(baos.ToArray()));
Assert.IsTrue(sheetL.PrintSetup.Landscape);
Assert.IsFalse(sheetP.PrintSetup.Landscape);
Assert.AreEqual(0, sheetL.PrintSetup.Copies);
Assert.AreEqual(3, sheetP.PrintSetup.Copies);
}
private static void ConfirmRow(NPOI.SS.UserModel.Sheet sheet, int rowIx, double valA, double valB, double valC,
String formulaA, String formulaB, String formulaC)
{
ConfirmCell(sheet, rowIx, 4, valA, formulaA);
ConfirmCell(sheet, rowIx, 5, valB, formulaB);
ConfirmCell(sheet, rowIx, 6, valC, formulaC);
}
public void TestRowBounds()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
//Test low row bound
sheet.CreateRow(0);
//Test low row bound exception
try
{
sheet.CreateRow(-1);
Assert.Fail("IndexOutOfBoundsException should have been thrown");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("Invalid row number (-1) outside allowable range (0..65535)", e.Message);
}
//Test high row bound
sheet.CreateRow(65535);
//Test high row bound exception
try
{
sheet.CreateRow(65536);
Assert.Fail("IndexOutOfBoundsException should have been thrown");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("Invalid row number (65536) outside allowable range (0..65535)", e.Message);
}
}
public void TestRowHeight()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
Row row1 = sheet.CreateRow(0);
Assert.AreEqual(0xFF, row1.Height);
Assert.AreEqual(sheet.DefaultRowHeight, row1.Height);
Row row2 = sheet.CreateRow(1);
row2.Height = ((short)400);
Assert.AreEqual(400, row2.Height);
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
row1 = sheet.GetRow(0);
Assert.AreEqual(0xFF, row1.Height);
Assert.AreEqual(sheet.DefaultRowHeight, row1.Height);
row2 = sheet.GetRow(1);
Assert.AreEqual(400, row2.Height);
}
public void TestDataStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Row row = s.CreateRow(0);
// with Date:
Cell cell = row.CreateCell(1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
// with Calendar:
cell = row.CreateCell(2);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(0, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestHashEquals()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
NPOI.SS.UserModel.CellStyle cs1 = wb.CreateCellStyle();
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
Row row = s.CreateRow(0);
Cell cell1 = row.CreateCell(1);
Cell cell2 = row.CreateCell(2);
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cs2.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
cell1.CellStyle = (cs1);
cell1.SetCellValue(DateTime.Now);
cell2.CellStyle = (cs2);
cell2.SetCellValue(DateTime.Now);
Assert.AreEqual(cs1.GetHashCode(), cs1.GetHashCode());
Assert.AreEqual(cs2.GetHashCode(), cs2.GetHashCode());
Assert.IsTrue(cs1.Equals(cs1));
Assert.IsTrue(cs2.Equals(cs2));
// Change cs1, hash will alter
int hash1 = cs1.GetHashCode();
cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
Assert.IsFalse(hash1 == cs1.GetHashCode());
}
public void TestDateFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
Row r = null;
Cell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
c.SetCellValue(new DateTime());
c.CellStyle = (cellStyle);
// Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
// NPOI.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());
for (int k = 1; k < 100; k++)
{
r = s.CreateRow(k);
c = r.CreateCell(0);
c.CellFormula = ("A" + (k) + "+1");
c.CellStyle = cellStyle;
}
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestSheetFunctions()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet("A");
Row r = null;
Cell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1);
c = r.CreateCell(1); c.SetCellValue(2);
s = wb.CreateSheet("B");
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheet("B");
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
c = r.GetCell(1);
Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
}
public void TestAbsRefs()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r;
Cell c;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("A3+A2");
c = r.CreateCell(1);
c.CellFormula = ("$A3+$A2");
c = r.CreateCell(2);
c.CellFormula = ("A$3+A$2");
c = r.CreateCell(3);
c.CellFormula = ("$A$3+$A$2");
c = r.CreateCell(4);
c.CellFormula = ("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("A3+A2").Equals(c.CellFormula), "A3+A2");
c = r.GetCell(1);
Assert.IsTrue(("$A3+$A2").Equals(c.CellFormula), "$A3+$A2");
c = r.GetCell(2);
Assert.IsTrue(("A$3+A$2").Equals(c.CellFormula), "A$3+A$2");
c = r.GetCell(3);
Assert.IsTrue(("$A$3+$A$2").Equals(c.CellFormula), "$A$3+$A$2");
c = r.GetCell(4);
Assert.IsTrue(("SUM($A$3,$A$2)").Equals(c.CellFormula), "SUM($A$3,$A$2)");
}
public void TestCloneSheet()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
Cell cell2 = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("Clone_Test"));
cell2.CellFormula = ("sin(1)");
NPOI.SS.UserModel.Sheet ClonedSheet = workbook.CloneSheet(0);
Row ClonedRow = ClonedSheet.GetRow(0);
//Check for a good Clone
Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test");
//Check that the cells are not somehow linked
cell.SetCellValue(new HSSFRichTextString("Difference Check"));
cell2.CellFormula = ("cos(2)");
if ("Difference Check".Equals(ClonedRow.GetCell(0).RichStringCellValue.String))
{
Assert.Fail("string cell not properly Cloned");
}
if ("COS(2)".Equals(ClonedRow.GetCell(1).CellFormula))
{
Assert.Fail("formula cell not properly Cloned");
}
Assert.AreEqual(ClonedRow.GetCell(0).RichStringCellValue.String, "Clone_Test");
Assert.AreEqual(ClonedRow.GetCell(1).CellFormula, "SIN(1)");
}
/**
* Make sure that there is no cross-talk between rows especially with getFirstCellNum and getLastCellNum
* This Test was Added in response to bug report 44987.
*/
public void TestBoundsInMultipleRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
Row rowA = sheet.CreateRow(0);
rowA.CreateCell(10);
rowA.CreateCell(5);
Assert.AreEqual(5, rowA.FirstCellNum);
Assert.AreEqual(11, rowA.LastCellNum);
Row rowB = sheet.CreateRow(1);
rowB.CreateCell(15);
rowB.CreateCell(30);
Assert.AreEqual(15, rowB.FirstCellNum);
Assert.AreEqual(31, rowB.LastCellNum);
Assert.AreEqual(5, rowA.FirstCellNum);
Assert.AreEqual(11, rowA.LastCellNum);
rowA.CreateCell(50);
Assert.AreEqual(51, rowA.LastCellNum);
Assert.AreEqual(31, rowB.LastCellNum);
}
public void TestCloneSheetMultipleTimes()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("Test Clone");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell(0);
cell.SetCellValue(new HSSFRichTextString("Clone_Test"));
//Clone the sheet multiple times
workbook.CloneSheet(0);
workbook.CloneSheet(0);
Assert.IsNotNull(workbook.GetSheet("Test Clone"));
Assert.IsNotNull(workbook.GetSheet("Test Clone (2)"));
Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2));
Assert.IsNotNull(workbook.GetSheet("Test Clone (3)"));
workbook.RemoveSheetAt(0);
workbook.RemoveSheetAt(0);
workbook.RemoveSheetAt(0);
workbook.CreateSheet("abc ( 123)");
workbook.CloneSheet(0);
Assert.AreEqual("abc (124)", workbook.GetSheetName(1));
}
public void TestReMoveCell()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
row.CreateCell(1);
Assert.AreEqual(2, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.CreateCell(3);
Assert.AreEqual(4, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.RemoveCell(row.GetCell(3));
Assert.AreEqual(2, row.LastCellNum);
Assert.AreEqual(1, row.FirstCellNum);
row.RemoveCell(row.GetCell(1));
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
// all cells on this row have been Removed
// so Check the row record actually Writes it out as 0's
byte[] data = new byte[100];
row.RowRecord.Serialize(0, data);
Assert.AreEqual(0, data[6]);
Assert.AreEqual(0, data[8]);
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
Assert.AreEqual(-1, sheet.GetRow(0).LastCellNum);
Assert.AreEqual(-1, sheet.GetRow(0).FirstCellNum);
}
public void TestPageBreakFiles()
{
HSSFWorkbook wb = OpenSample("SimpleWithPageBreaks.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
Assert.IsNotNull(sheet);
Assert.AreEqual(1, sheet.RowBreaks.Length, "1 row page break");
Assert.AreEqual(1, sheet.ColumnBreaks.Length, "1 column page break");
Assert.IsTrue(sheet.IsRowBroken(22), "No row page break");
Assert.IsTrue(sheet.IsColumnBroken((short)4), "No column page break");
sheet.SetRowBreak(10);
sheet.SetColumnBreak((short)13);
Assert.AreEqual(2, sheet.RowBreaks.Length, "row breaks number");
Assert.AreEqual(2, sheet.ColumnBreaks.Length, "column breaks number");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0);
Assert.IsTrue(sheet.IsRowBroken(22), "No row page break");
Assert.IsTrue(sheet.IsColumnBroken((short)4), "No column page break");
Assert.AreEqual(2, sheet.RowBreaks.Length, "row breaks number");
Assert.AreEqual(2, sheet.ColumnBreaks.Length, "column breaks number");
}
public void TestWriteModifySheetMerged()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
for (int rownum = 0; rownum < 100; rownum++)
{
Row r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
Cell c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
s.AddMergedRegion(new CellRangeAddress(0, 10, 0, 10));
s.AddMergedRegion(new CellRangeAddress(30, 40, 5, 15));
sanityChecker.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
CellRangeAddress r1 = s.GetMergedRegion(0);
CellRangeAddress r2 = s.GetMergedRegion(1);
ConfirmRegion(new CellRangeAddress(0, 10, 0, 10), r1);
ConfirmRegion(new CellRangeAddress(30, 40, 5, 15), r2);
}
public void TestClassCast_bug44861()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");
// Check direct
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
// And via calls
int numSheets = wb.NumberOfSheets;
for (int i = 0; i < numSheets; i++)
{
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(i);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext();)
{
Row r = (Row)rows.Current;
for (IEnumerator cells = r.GetCellEnumerator(); cells.MoveNext();)
{
Cell c = (Cell)cells.Current;
eval.EvaluateFormulaCell(c);
}
}
}
}
public void TestManyRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
Row row;
Cell cell;
int i, j;
for (i = 0, j = 32771; j > 0; i++, j--)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(i);
}
sanityChecker.CheckHSSFWorkbook(workbook);
Assert.AreEqual(32770, sheet.LastRowNum, "LAST ROW == 32770");
cell = sheet.GetRow(32770).GetCell(0);
double lastVal = cell.NumericCellValue;
HSSFWorkbook wb = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0);
row = s.GetRow(32770);
cell = row.GetCell(0);
Assert.AreEqual(lastVal, cell.NumericCellValue, 0, "Value from last row == 32770");
Assert.AreEqual(32770, s.LastRowNum, "LAST ROW == 32770");
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
fnt.Color = (NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight = (short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
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");
}
public void TestWriteSheetSimple()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
for (int rownum = 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double )rownum / 1000)
+ (( double )cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
wb.Write(out1);
out1.Close();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void Test44916()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet();
// 1. Create drawing patriarch
Drawing patr = sheet.CreateDrawingPatriarch();
// 2. Try to re-get the patriarch
Drawing existingPatr;
try
{
existingPatr = sheet.DrawingPatriarch;
}
catch (NullReferenceException e)
{
throw new AssertFailedException("Identified bug 44916");
}
// 3. Use patriarch
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2);
anchor.AnchorType = (3);
byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
patr.CreatePicture(anchor, idx1);
// 4. Try to re-use patriarch later
existingPatr = sheet.DrawingPatriarch;
Assert.IsNotNull(existingPatr);
}
public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
Row row = sheet1.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula = ("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
public void TestReadComments()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithComments.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
Cell cell;
Row row;
Comment comment;
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(0);
comment = cell.CellComment;
Assert.IsNull(comment, "Cells in the first column are not commented");
Assert.IsNull(sheet.GetCellComment(rownum, 0));
}
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(1);
comment = cell.CellComment;
Assert.IsNotNull(comment, "Cells in the second column have comments");
Assert.IsNotNull(sheet.GetCellComment(rownum, 1), "Cells in the second column have comments");
Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_COMMENT, ((HSSFSimpleShape)comment).ShapeType);
Assert.AreEqual("Yegor Kozlov", comment.Author);
Assert.IsFalse(comment.String.String == string.Empty, "cells in the second column have not empyy notes");
Assert.AreEqual(rownum, comment.Row);
Assert.AreEqual(cell.ColumnIndex, comment.Column);
}
}
/**
* Opens the sheet we wrote out by BinomialOperator and makes sure the formulas
* all Match what we expect (x operator y)
*/
private static void BinomialVerify(String operator1, HSSFWorkbook wb)
{
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0);
Row r = null;
Cell c = null;
//get our minimum values
r = s.GetRow(0);
c = r.GetCell(1);
Assert.IsTrue(("1" + operator1 + "1").Equals(c.CellFormula),
"minval Formula is as expected 1" + operator1 + "1 != " + c.CellFormula);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.GetRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.GetCell(y);
Assert.IsTrue(("" + x + operator1 + y).Equals(c.CellFormula),
"loop Formula is as expected " + x + operator1 + y + "!=" + c.CellFormula
);
}
}
//Test our maximum values
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(
("" + short.MaxValue + operator1 + short.MaxValue).Equals(c.CellFormula), "maxval Formula is as expected"
);
}
public MySheet()
{
_wb = new HSSFWorkbook();
_evalListener = new EvalListener(_wb);
_evaluator = WorkbookEvaluatorTestHelper.CreateEvaluator(_wb, _evalListener);
_sheet = _wb.CreateSheet("Sheet1");
}
public void TestDeleteComments()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithComments.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
// Zap from rows 1 and 3
Assert.IsNotNull(sheet.GetRow(0).GetCell(1).CellComment);
Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment);
Assert.IsNotNull(sheet.GetRow(2).GetCell(1).CellComment);
sheet.GetRow(0).GetCell(1).RemoveCellComment();
sheet.GetRow(2).GetCell(1).CellComment = (null);
// Check gone so far
Assert.IsNull(sheet.GetRow(0).GetCell(1).CellComment);
Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment);
Assert.IsNull(sheet.GetRow(2).GetCell(1).CellComment);
// Save and re-load
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
out1.Close();
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
// Check
Assert.IsNull(sheet.GetRow(0).GetCell(1).CellComment);
Assert.IsNotNull(sheet.GetRow(1).GetCell(1).CellComment);
Assert.IsNull(sheet.GetRow(2).GetCell(1).CellComment);
// FileOutputStream fout = new FileOutputStream("/tmp/c.xls");
// wb.Write(fout);
// fout.Close();
}
public HSSFFormulaEvaluator(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.Workbook workbook)
: this(workbook)
{
this.sheet = sheet;
this.workbook = workbook;
}
