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 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);
}
/**
* 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 void TestOnARealFile()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("DateFormats.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
NPOI.HSSF.Model.Workbook wb = workbook.Workbook;
Row row;
Cell cell;
NPOI.SS.UserModel.CellStyle style;
double aug_10_2007 = 39304.0;
// Should have dates in 2nd column
// All of them are the 10th of August
// 2 US dates, 3 UK dates
row = sheet.GetRow(0);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.AreEqual("d-mmm-yy", style.GetDataFormatString());
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsInternalDateFormat(style.DataFormat));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(1);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(NPOI.SS.UserModel.DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(2);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(3);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(NPOI.SS.UserModel.DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell));
row = sheet.GetRow(4);
cell = row.GetCell(1);
style = cell.CellStyle;
Assert.AreEqual(aug_10_2007, cell.NumericCellValue, 0.0001);
Assert.IsFalse(NPOI.SS.UserModel.DateUtil.IsInternalDateFormat(cell.CellStyle.DataFormat));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsADateFormat(style.DataFormat, style.GetDataFormatString()));
Assert.IsTrue(NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(cell));
}
public void Test44410()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
// =index(C:C,2,1) -> 2
Row rowIDX = sheet.GetRow(3);
// =sum(C:C) -> 6
Row rowSUM = sheet.GetRow(4);
// =sum(C:D) -> 66
Row rowSUM2D = sheet.GetRow(5);
// Test the sum
Cell cellSUM = rowSUM.GetCell(0);
FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord;
Ptg[] ops = frec.FormulaRecord.ParsedExpression;
Assert.AreEqual(2, ops.Length);
Assert.AreEqual(typeof(AreaPtg), ops[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType());
// Actually stored as C1 to C65536
// (last row is -1 === 65535)
AreaPtg ptg = (AreaPtg)ops[0];
Assert.AreEqual(2, ptg.FirstColumn);
Assert.AreEqual(2, ptg.LastColumn);
Assert.AreEqual(0, ptg.FirstRow);
Assert.AreEqual(65535, ptg.LastRow);
Assert.AreEqual("C:C", ptg.ToFormulaString());
// Will show as C:C, but won't know how many
// rows it covers as we don't have the sheet
// to hand when turning the Ptgs into a string
Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula);
// But the evaluator knows the sheet, so it
// can do it properly
Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0);
// Test the index
// Again, the formula string will be right but
// lacking row count, Evaluated will be right
Cell cellIDX = rowIDX.GetCell(0);
Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula);
Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0);
// Across two colums
Cell cellSUM2D = rowSUM2D.GetCell(0);
Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula);
Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0);
}
public void TestEvaluateMultipleWorkbooks()
{
HSSFWorkbook wbA = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaA.xls");
HSSFWorkbook wbB = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaB.xls");
HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
// Hook up the workbook evaluators to enable evaluation of formulas across books
String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
HSSFFormulaEvaluator.SetupEnvironment(bookNames, evaluators);
Cell cell;
NPOI.SS.UserModel.Sheet aSheet1 = wbA.GetSheetAt(0);
NPOI.SS.UserModel.Sheet bSheet1 = wbB.GetSheetAt(0);
// Simple case - single link from wbA to wbB
ConfirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
cell = aSheet1.GetRow(0).GetCell(0);
ConfirmEvaluation(35, evaluatorA, cell);
// more complex case - back link into wbA
// [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
ConfirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
// [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
ConfirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
cell = aSheet1.GetRow(1).GetCell(0);
ConfirmEvaluation(264, evaluatorA, cell);
// change [wbB]BSheet1!B3 (from 50 to 60)
Cell cellB3 = bSheet1.GetRow(2).GetCell(1);
cellB3.SetCellValue(60);
evaluatorB.NotifyUpdateCell(cellB3);
ConfirmEvaluation(274, evaluatorA, cell);
// change [wbA]ASheet1!A3 (from 100 to 80)
Cell cellA3 = aSheet1.GetRow(2).GetCell(0);
cellA3.SetCellValue(80);
evaluatorA.NotifyUpdateCell(cellA3);
ConfirmEvaluation(234, evaluatorA, cell);
// change [wbA]AnotherSheet!A1 (from 2 to 3)
Cell cellA1 = wbA.GetSheetAt(1).GetRow(0).GetCell(0);
cellA1.SetCellValue(3);
evaluatorA.NotifyUpdateCell(cellA1);
ConfirmEvaluation(235, evaluatorA, cell);
}
public void TestReadEmployeeSimple()
{
HSSFWorkbook workbook = OpenSample("Employee.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String);
Assert.AreEqual(LAST_NAME_KEY, sheet.GetRow(3).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(FIRST_NAME_KEY, sheet.GetRow(4).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(SSN_KEY, sheet.GetRow(5).GetCell(2).RichStringCellValue.String);
}
public DataTable GetData(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
using (NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0))
{
DataTable table = new DataTable();
NPOI.SS.UserModel.Row headerRow = sheet.GetRow(0); //第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
}
table.Rows.Add(dataRow);
}
return(table);
}
}
public void Test44297()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44297.xls");
Row row;
Cell cell;
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
row = sheet.GetRow(0);
cell = row.GetCell(0);
Assert.AreEqual("31+46", cell.CellFormula);
Assert.AreEqual(77, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(1);
cell = row.GetCell(0);
Assert.AreEqual("30+53", cell.CellFormula);
Assert.AreEqual(83, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(2);
cell = row.GetCell(0);
Assert.AreEqual("SUM(A1:A2)", cell.CellFormula);
Assert.AreEqual(160, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(4);
cell = row.GetCell(0);
Assert.AreEqual("32767+32768", cell.CellFormula);
Assert.AreEqual(65535, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(7);
cell = row.GetCell(0);
Assert.AreEqual("32744+42333", cell.CellFormula);
Assert.AreEqual(75077, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(8);
cell = row.GetCell(0);
Assert.AreEqual("327680/32768", cell.CellFormula);
Assert.AreEqual(10, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(9);
cell = row.GetCell(0);
Assert.AreEqual("32767+32769", cell.CellFormula);
Assert.AreEqual(65536, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(10);
cell = row.GetCell(0);
Assert.AreEqual("35000+36000", cell.CellFormula);
Assert.AreEqual(71000, eva.Evaluate(cell).NumberValue, 0);
row = sheet.GetRow(11);
cell = row.GetCell(0);
Assert.AreEqual("-1000000-3000000", cell.CellFormula);
Assert.AreEqual(-4000000, eva.Evaluate(cell).NumberValue, 0);
}
public void TestModifySimple()
{
HSSFWorkbook workbook = OpenSample("Simple.xls");
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
Cell cell = sheet.GetRow(0).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
}
public void TestStyleNames()
{
HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls");
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0);
Cell c1 = s.GetRow(0).GetCell(0);
Cell c2 = s.GetRow(1).GetCell(0);
Cell c3 = s.GetRow(2).GetCell(0);
HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle;
HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle;
HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle;
Assert.IsNotNull(cs1);
Assert.IsNotNull(cs2);
Assert.IsNotNull(cs3);
// Check we got the styles we'd expect
Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(15, cs1.Index);
Assert.AreEqual(23, cs2.Index);
Assert.AreEqual(24, cs3.Index);
Assert.IsNull(cs1.ParentStyle);
Assert.IsNotNull(cs2.ParentStyle);
Assert.IsNotNull(cs3.ParentStyle);
Assert.AreEqual(21, cs2.ParentStyle.Index);
Assert.AreEqual(22, cs3.ParentStyle.Index);
// Now Check we can get style records for
// the parent ones
Assert.IsNull(wb.Workbook.GetStyleRecord(15));
Assert.IsNull(wb.Workbook.GetStyleRecord(23));
Assert.IsNull(wb.Workbook.GetStyleRecord(24));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(21));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(22));
// Now Check the style names
Assert.AreEqual(null, cs1.UserStyleName);
Assert.AreEqual(null, cs2.UserStyleName);
Assert.AreEqual(null, cs3.UserStyleName);
Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName);
Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName);
}
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)");
}
/**
* 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)"
);
}
/**
* 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 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 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 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 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)");
}
/**
* @param startRowIndex row index in the spReadsheet where the first function/operator is1 found
* @param testFocusFunctionName name of a single function/operator to test alone.
* Typically pass <c>null</c> to test all functions
*/
private void processFunctionGroup(int startRowIndex, String testFocusFunctionName)
{
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
int rowIndex = startRowIndex;
while (true)
{
NPOI.SS.UserModel.Row r = sheet.GetRow(rowIndex);
String targetFunctionName = GetTargetFunctionName(r);
if (targetFunctionName == null)
{
throw new AssertFailedException("Test spReadsheet cell empty on row ("
+ (rowIndex + 1) + "). Expected function name or '"
+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
}
if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL))
{
// found end of functions list
break;
}
if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.InvariantCultureIgnoreCase))
{
// expected results are on the row below
NPOI.SS.UserModel.Row expectedValuesRow = sheet.GetRow(rowIndex + 1);
if (expectedValuesRow == null)
{
int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row
throw new AssertFailedException("Missing expected values row for function '"
+ targetFunctionName + " (row " + missingRowNum + ")");
}
switch (ProcessFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow))
{
case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
case Result.NO_EVALUATIONS_FOUND: // do nothing
break;
default:
throw new Exception("unexpected result");
}
}
rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION;
}
}
public void TestSquareMacro()
{
HSSFWorkbook w = OpenSample("SquareMacro.xls");
NPOI.SS.UserModel.Sheet s0 = w.GetSheetAt(0);
Row[] r = { s0.GetRow(0), s0.GetRow(1) };
Cell a1 = r[0].GetCell(0);
Assert.AreEqual("square(1)", a1.CellFormula);
Assert.AreEqual(1d, a1.NumericCellValue, 1e-9);
Cell a2 = r[1].GetCell(0);
Assert.AreEqual("square(2)", a2.CellFormula);
Assert.AreEqual(4d, a2.NumericCellValue, 1e-9);
Cell b1 = r[0].GetCell(1);
Assert.AreEqual("IF(TRUE,square(1))", b1.CellFormula);
Assert.AreEqual(1d, b1.NumericCellValue, 1e-9);
Cell b2 = r[1].GetCell(1);
Assert.AreEqual("IF(TRUE,square(2))", b2.CellFormula);
Assert.AreEqual(4d, b2.NumericCellValue, 1e-9);
Cell c1 = r[0].GetCell(2);
Assert.AreEqual("square(square(1))", c1.CellFormula);
Assert.AreEqual(1d, c1.NumericCellValue, 1e-9);
Cell c2 = r[1].GetCell(2);
Assert.AreEqual("square(square(2))", c2.CellFormula);
Assert.AreEqual(16d, c2.NumericCellValue, 1e-9);
Cell d1 = r[0].GetCell(3);
Assert.AreEqual("square(one())", d1.CellFormula);
Assert.AreEqual(1d, d1.NumericCellValue, 1e-9);
Cell d2 = r[1].GetCell(3);
Assert.AreEqual("square(two())", d2.CellFormula);
Assert.AreEqual(4d, d2.NumericCellValue, 1e-9);
}
private static DateTime ReadCell(HSSFWorkbook workbook, int rowIdx, int colIdx)
{
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
Row row = sheet.GetRow(rowIdx);
Cell cell = row.GetCell(colIdx);
return(cell.DateCellValue);
}
private static void ConfirmCell(NPOI.SS.UserModel.Sheet sheet, int rowIx, int colIx,
double expectedValue, String expectedFormula)
{
Cell cell = sheet.GetRow(rowIx).GetCell(colIx);
Assert.AreEqual(expectedValue, cell.NumericCellValue, 0.0);
Assert.AreEqual(expectedFormula, cell.CellFormula);
}
public void TestShiftRows()
{
// Read initial file in
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(0);
// Shift the second row down 1 and Write to temp file
s.ShiftRows(1, 1, 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Read from temp file and Check the number of cells in each
// row (in original file each row was unique)
s = wb.GetSheetAt(0);
Assert.AreEqual(1, s.GetRow(0).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 1);
Assert.AreEqual(2, s.GetRow(2).PhysicalNumberOfCells);
Assert.AreEqual(4, s.GetRow(3).PhysicalNumberOfCells);
Assert.AreEqual(5, s.GetRow(4).PhysicalNumberOfCells);
// Shift rows 1-3 down 3 in the current one. This Tests when
// 1 row is blank. Write to a another temp file
s.ShiftRows(0, 2, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Read and ensure things are where they should be
s = wb.GetSheetAt(0);
ConfirmEmptyRow(s, 0);
ConfirmEmptyRow(s, 1);
ConfirmEmptyRow(s, 2);
Assert.AreEqual(1, s.GetRow(3).PhysicalNumberOfCells);
ConfirmEmptyRow(s, 4);
Assert.AreEqual(2, s.GetRow(5).PhysicalNumberOfCells);
// Read the first file again
wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleMultiCell.xls");
s = wb.GetSheetAt(0);
// Shift rows 3 and 4 up and Write to temp file
s.ShiftRows(2, 3, -2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3);
Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4);
ConfirmEmptyRow(s, 2);
ConfirmEmptyRow(s, 3);
Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5);
}
private static void WriteOtherSettings(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.CellStyle style,
String strStettings)
{
Row row = sheet.GetRow(sheet.PhysicalNumberOfRows - 1);
Cell cell = row.CreateCell(5);
cell.CellStyle = (style);
SetCellValue(cell, strStettings);
}
public void TestShiftWithFormulas()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ForShifting.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheet("Sheet1");
Assert.AreEqual(20, sheet.LastRowNum);
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
ConfirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B2");
ConfirmCell(sheet, 8, 1, 273, "200+B3");
ConfirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
// -----------
// Row index 1 -> 11 (row "2" -> row "12")
sheet.ShiftRows(1, 1, 10);
// Now Check what sheet looks like after move
// no changes on row "1"
ConfirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
// row "2" is now empty
Assert.AreEqual(0, sheet.GetRow(1).PhysicalNumberOfCells);
// Row "2" moved to row "12", and the formula has been updated.
// note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
ConfirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
// no changes on row "3"
ConfirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
ConfirmCell(sheet, 14, 0, 0.0, "#REF!");
// Formulas on rows that weren't shifted:
ConfirmCell(sheet, 6, 1, 271, "200+B1");
ConfirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
ConfirmCell(sheet, 8, 1, 273, "200+B3");
// Check formulas on other sheets
NPOI.SS.UserModel.Sheet sheet2 = wb.GetSheet("Sheet2");
ConfirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
ConfirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
ConfirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
ConfirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
// Note - named ranges formulas have not been updated
}
public void TestCloneSheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("HyperlinksOnManySheets.xls");
Cell cell;
Hyperlink link;
NPOI.SS.UserModel.Sheet 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 TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
Hyperlink link;
Cell cell;
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
NPOI.SS.UserModel.Sheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
//cell A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Hyperlinks'!A1");
cell.Hyperlink = (link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}