public void TestIndexFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1");
short colB = 1;
sheet.CreateRow(0).CreateCell(colB).SetCellValue(1);
sheet.CreateRow(1).CreateCell(colB).SetCellValue(2);
sheet.CreateRow(2).CreateCell(colB).SetCellValue(3);
Row row4 = sheet.CreateRow(3);
Cell testCell = row4.CreateCell((short)0);
// This formula should evaluate to the contents of B2,
testCell.CellFormula = ("INDEX(A1:B4,2,2)");
// However the range A1:B4 also includes the current cell A4. If the other parameters
// were 4 and 1, this would represent a circular reference. Since POI 'fully' evaluates
// arguments before invoking operators, POI must handle such potential cycles gracefully.
NPOI.SS.UserModel.CellValue cellValue = EvaluateWithCycles(wb, testCell);
Assert.IsTrue(cellValue.CellType == NPOI.SS.UserModel.CellType.NUMERIC);
Assert.AreEqual(2, cellValue.NumberValue, 0);
}
/**
* 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 TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
Row row = sheet.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
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 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 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);
}
private static void floatTest(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = ("" + float.MinValue + operator1 + float.MinValue);
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 = (short)(y + 2))
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + "." + y + operator1 + y + "." + x);
}
}
if (s.LastRowNum < short.MaxValue)
{
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + float.MaxValue + operator1 + float.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
floatVerify(operator1, wb);
}
/**
* All multi-binomial operator Tests use this to Create a worksheet with a
* huge set of x operator y formulas. Next we call BinomialVerify and verify
* that they are all how we expect.
*/
private static void BinomialOperator(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (1 + operator1 + 1);
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++)
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + operator1 + y);
}
}
//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 = ("" + short.MaxValue + operator1 + short.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
BinomialVerify(operator1, wb);
}
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 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 TestShiftRow0()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = b.CreateSheet();
s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
s.ShiftRows(0, 4, 1);
}
/// <summary>
/// 把DataTable 转为Excel 内容
/// </summary>
/// <param name="dt"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="book"></param>
/// <param name="sheetName"></param>
private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, HSSFWorkbook book, string sheetName)
{
//头部样式
CellStyle headstyle = book.CreateCellStyle();
headstyle.Alignment = HorizontalAlignment.CENTER;
headstyle.VerticalAlignment = VerticalAlignment.CENTER;
Font headfont = book.CreateFont();
headfont.Boldweight = 700;
headstyle.SetFont(headfont);
headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
headstyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
headstyle.BorderBottom = CellBorderType.THIN;
headstyle.BorderLeft = CellBorderType.THIN;
headstyle.BorderRight = CellBorderType.THIN;
headstyle.BorderTop = CellBorderType.THIN;
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row header = sheet.CreateRow(0);
header.Height = 20 * 20;
//表格内容样
CellStyle dataStyle = book.CreateCellStyle();
dataStyle.BorderBottom = CellBorderType.THIN;
dataStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
dataStyle.BorderLeft = CellBorderType.THIN;
dataStyle.BorderRight = CellBorderType.THIN;
dataStyle.BorderTop = CellBorderType.THIN;
for (int i = 0; i < dt.Columns.Count; i++)
{
NPOI.SS.UserModel.Cell cell = header.CreateCell(i);
cell.CellStyle = headstyle;
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
NPOI.SS.UserModel.Row excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).CellStyle = dataStyle;
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
public void TestMoveCell()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet();
Row row = sheet.CreateRow(0);
Row rowB = sheet.CreateRow(1);
Cell cellA2 = rowB.CreateCell(0);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
Cell cellB2 = row.CreateCell(1);
Cell cellB3 = row.CreateCell(2);
Cell cellB4 = row.CreateCell(3);
Assert.AreEqual(1, row.FirstCellNum);
Assert.AreEqual(4, row.LastCellNum);
// Try to move to somewhere else that's used
try
{
row.MoveCell(cellB2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException e)
{
// expected during successful Test
}
// Try to move one off a different row
try
{
row.MoveCell(cellA2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException e)
{
// expected during successful Test
}
// Move somewhere spare
Assert.IsNotNull(row.GetCell(1));
row.MoveCell(cellB2, (short)5);
Assert.IsNull(row.GetCell(1));
Assert.IsNotNull(row.GetCell(5));
Assert.AreEqual(5, cellB2.ColumnIndex);
Assert.AreEqual(2, row.FirstCellNum);
Assert.AreEqual(6, row.LastCellNum);
}
public void Test27405()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("input");
// input row 0
Row row = sheet.CreateRow((short)0);
Cell cell = row.CreateCell((short)0);
cell = row.CreateCell((short)1);
cell.SetCellValue(1); // B1
// input row 1
row = sheet.CreateRow((short)1);
cell = row.CreateCell((short)1);
cell.SetCellValue(999); // B2
int rno = 4;
row = sheet.CreateRow(rno);
cell = row.CreateCell((short)1); // B5
cell.CellFormula = ("isnumber(b1)");
cell = row.CreateCell((short)3); // D5
cell.CellFormula = ("IF(ISNUMBER(b1),b1,b2)");
//if (false)
//{ // Set true to check excel file manually
// // bug report mentions 'Editing the formula in excel "fixes" the problem.'
// try
// {
// FileStream fileOut = new FileStream("27405output.xls",FileMode.Open);
// wb.Write(fileOut);
// fileOut.Close();
// }
// catch (IOException)
// {
// throw;
// }
//}
// use POI's evaluator as an extra sanity check
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue cv;
cv = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType);
Assert.AreEqual(1.0, cv.NumberValue, 0.0);
cv = fe.Evaluate(row.GetCell(1));
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, cv.CellType);
Assert.AreEqual(true, cv.BooleanValue);
}
public void CreateDVTypeRow(String strTypeDescription)
{
NPOI.SS.UserModel.Sheet sheet = _currentSheet;
Row row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
sheet.AddMergedRegion(new CellRangeAddress(sheet.PhysicalNumberOfRows - 1, sheet.PhysicalNumberOfRows - 1, 0, 5));
Cell cell = row.CreateCell(0);
SetCellValue(cell, strTypeDescription);
cell.CellStyle = (_style_3);
row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
}
public void Test44636()
{
// Open the existing file, tweak one value and
// re-calculate
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44636.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
Row row = sheet.GetRow(0);
row.GetCell(0).SetCellValue(4.2);
row.GetCell(2).SetCellValue(25);
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(4.2 * 25, row.GetCell(3).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream existing = File.Open(tmpDirName + "44636-existing.xls", FileMode.Open);
existing.Seek(0, SeekOrigin.End);
wb.Write(existing);
existing.Close();
Console.Error.WriteLine("Existing file for bug #44636 written to " + existing.ToString());
}
// Now, do a new file from scratch
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(1.2);
row.CreateCell(1).SetCellValue(4.2);
row = sheet.CreateRow(1);
row.CreateCell(0).CellFormula = ("SUM(A1:B1)");
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(5.4, row.GetCell(0).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream scratch = File.Open(tmpDirName + "44636-scratch.xls", FileMode.Open);
scratch.Seek(0, SeekOrigin.End);
wb.Write(scratch);
scratch.Close();
Console.Error.WriteLine("New file for bug #44636 written to " + scratch.ToString());
}
}
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");
}
/**
* 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 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 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)");
}
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 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 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 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 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 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 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 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 TestFullColumnRefs()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1");
Row row = sheet.CreateRow(0);
Cell cell0 = row.CreateCell(0);
cell0.CellFormula = ("sum(D:D)");
Cell cell1 = row.CreateCell(1);
cell1.CellFormula = ("sum(D:E)");
// some values in column D
setValue(sheet, 1, 3, 5.0);
setValue(sheet, 2, 3, 6.0);
setValue(sheet, 5, 3, 7.0);
setValue(sheet, 50, 3, 8.0);
// some values in column E
setValue(sheet, 1, 4, 9.0);
setValue(sheet, 2, 4, 10.0);
setValue(sheet, 30000, 4, 11.0);
// some other values
setValue(sheet, 1, 2, 100.0);
setValue(sheet, 2, 5, 100.0);
setValue(sheet, 3, 6, 100.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(26.0, fe.Evaluate(cell0).NumberValue, 0.0);
Assert.AreEqual(56.0, fe.Evaluate(cell1).NumberValue, 0.0);
}