public void TestShapeIds()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch1 = sheet1.CreateDrawingPatriarch() as HSSFPatriarch;
for (int i = 0; i < 2; i++)
{
patriarch1.CreateSimpleShape(new HSSFClientAnchor());
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet1 = wb.GetSheetAt(0) as HSSFSheet;
patriarch1 = sheet1.DrawingPatriarch as HSSFPatriarch;
EscherAggregate agg1 = HSSFTestHelper.GetEscherAggregate(patriarch1);
// last shape ID cached in EscherDgRecord
EscherDgRecord dg1 =
agg1.GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
Assert.AreEqual(1026, dg1.LastMSOSPID);
// iterate over shapes and check shapeId
EscherContainerRecord spgrContainer =
agg1.GetEscherContainer().ChildContainers[0] as EscherContainerRecord;
// root spContainer + 2 spContainers for shapes
Assert.AreEqual(3, spgrContainer.ChildRecords.Count);
EscherSpRecord sp0 =
((EscherContainerRecord)spgrContainer.GetChild(0)).GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
Assert.AreEqual(1024, sp0.ShapeId);
EscherSpRecord sp1 =
((EscherContainerRecord)spgrContainer.GetChild(1)).GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
Assert.AreEqual(1025, sp1.ShapeId);
EscherSpRecord sp2 =
((EscherContainerRecord)spgrContainer.GetChild(2)).GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
Assert.AreEqual(1026, sp2.ShapeId);
}
public void TestMultiNamedRange()
{
// Create a new workbook
HSSFWorkbook wb = new HSSFWorkbook();
// Create a worksheet 'sheet1' in the new workbook
wb.CreateSheet();
wb.SetSheetName(0, "sheet1");
// Create another worksheet 'sheet2' in the new workbook
wb.CreateSheet();
wb.SetSheetName(1, "sheet2");
// Create a new named range for worksheet 'sheet1'
NPOI.SS.UserModel.Name namedRange1 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet1'
namedRange1.NameName = ("RangeTest1");
// Set the reference for the named range for worksheet 'sheet1'
namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41");
// Create a new named range for worksheet 'sheet2'
NPOI.SS.UserModel.Name namedRange2 = wb.CreateName();
// Set the name for the named range for worksheet 'sheet2'
namedRange2.NameName = ("RangeTest2");
// Set the reference for the named range for worksheet 'sheet2'
namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21");
// Write the workbook to a file
// Read the Excel file and verify its content
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1"));
Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName);
Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula);
NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2"));
Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName);
Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula);
}
public void TestWriteWorkbookFromNPOIFS()
{
//throw new NotImplementedException("class NPOIFSFileSystem is not implemented");
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("WithEmbeddedObjects.xls");
NPOIFSFileSystem fs = new NPOIFSFileSystem(is1);
// Start as NPOIFS
HSSFWorkbook wb = new HSSFWorkbook(fs.Root, true);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
// Will switch to POIFS
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
fs.Close();
}
public void TestPrintAreaFile()
{
HSSFWorkbook workbook = new HSSFWorkbook();
workbook.CreateSheet("Test Print Area");
String sheetName = workbook.GetSheetName(0);
String reference = "$A$1:$B$1";
workbook.SetPrintArea(0, reference);
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
String retrievedPrintArea = workbook.GetPrintArea(0);
Assert.IsNotNull(retrievedPrintArea, "Print Area not defined for first sheet");
Assert.AreEqual("'" + sheetName + "'!$A$1:$B$1", retrievedPrintArea, "References Match");
}
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 TestAddToExistingFile()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.PNG);
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
comment.Column = (5);
comment.String = new HSSFRichTextString("comment1");
comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)10, 10)) as HSSFComment;
comment.Row = (5);
comment.String = new HSSFRichTextString("comment2");
comment.SetBackgroundImage(idx);
Assert.AreEqual(comment.GetBackgroundImageId(), idx);
Assert.AreEqual(patriarch.Children.Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
comment = (HSSFComment)patriarch.Children[(1)];
Assert.AreEqual(comment.GetBackgroundImageId(), idx);
comment.ResetBackgroundImage();
Assert.AreEqual(comment.GetBackgroundImageId(), 0);
Assert.AreEqual(patriarch.Children.Count, 2);
comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
comment.String = new HSSFRichTextString("comment3");
Assert.AreEqual(patriarch.Children.Count, 3);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
comment = (HSSFComment)patriarch.Children[1];
Assert.AreEqual(comment.GetBackgroundImageId(), 0);
Assert.AreEqual(patriarch.Children.Count, 3);
Assert.AreEqual(((HSSFComment)patriarch.Children[0]).String.String, "comment1");
Assert.AreEqual(((HSSFComment)patriarch.Children[1]).String.String, "comment2");
Assert.AreEqual(((HSSFComment)patriarch.Children[2]).String.String, "comment3");
}
public void TestReadBooleans()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Test boolean");
IRow row = sheet.CreateRow(2);
ICell cell = row.CreateCell(9);
cell.SetCellValue(true);
cell = row.CreateCell(11);
cell.SetCellValue(true);
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
row = sheet.GetRow(2);
Assert.IsNotNull(row);
Assert.AreEqual(2, row.PhysicalNumberOfCells);
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
NPOI.SS.UserModel.ISheet 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);
}
public void TestWriteModifySheetSimple()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet s = wb1.CreateSheet() as HSSFSheet;
for (int rownum = 0; rownum < 100; rownum++)
{
HSSFRow r = s.CreateRow(rownum) as HSSFRow;
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
HSSFCell c = r.CreateCell(cellnum) as HSSFCell;
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1) as HSSFCell;
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
for (int rownum = 0; rownum < 25; rownum++)
{
HSSFRow r = s.GetRow(rownum) as HSSFRow;
s.RemoveRow(r);
}
for (int rownum = 75; rownum < 100; rownum++)
{
HSSFRow r = s.GetRow(rownum) as HSSFRow;
s.RemoveRow(r);
}
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
sanityChecker.CheckHSSFWorkbook(wb1);
Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74");
Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25");
s = wb2.GetSheetAt(0) as HSSFSheet;
Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74");
Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25");
wb2.Close();
wb1.Close();
}
public void TestTextForSimpleShape()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape shape = patriarch.CreateSimpleShape(new HSSFClientAnchor());
shape.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
shape = (HSSFSimpleShape)patriarch.Children[0];
agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
shape.String = new HSSFRichTextString("string1");
Assert.AreEqual(shape.String.String, "string1");
Assert.IsNotNull(HSSFTestHelper.GetEscherContainer(shape).GetChildById(EscherTextboxRecord.RECORD_ID));
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
shape = (HSSFSimpleShape)patriarch.Children[0];
Assert.IsNotNull(HSSFTestHelper.GetTextObjRecord(shape));
Assert.AreEqual(shape.String.String, "string1");
Assert.IsNotNull(HSSFTestHelper.GetEscherContainer(shape).GetChildById(EscherTextboxRecord.RECORD_ID));
Assert.AreEqual(agg.GetShapeToObjMapping().Count, 2);
}
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 TestSumIf()
{
string function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = OpenSample("sumifformula.xls");
Npoi.Core.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = s.GetRow(0);
ICell c = r.GetCell(2);
Assert.AreEqual(function, c.CellFormula);
wb = new HSSFWorkbook();
s = wb.CreateSheet();
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1000);
c = r.CreateCell(1); c.SetCellValue(1);
r = s.CreateRow(1);
c = r.CreateCell(0); c.SetCellValue(2000);
c = r.CreateCell(1); c.SetCellValue(2);
r = s.CreateRow(2);
c = r.CreateCell(0); c.SetCellValue(3000);
c = r.CreateCell(1); c.SetCellValue(3);
r = s.CreateRow(3);
c = r.CreateCell(0); c.SetCellValue(4000);
c = r.CreateCell(1); c.SetCellValue(4);
r = s.CreateRow(4);
c = r.CreateCell(0); c.SetCellValue(5000);
c = r.CreateCell(1); c.SetCellValue(5);
r = s.GetRow(0);
c = r.CreateCell(2); c.CellFormula = (function);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
public void TestBasicAddIntegers()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(1);
c = r.CreateCell(1);
c.CellFormula = (1 + "+" + 1);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(1);
c = r.GetCell(1);
Assert.IsTrue("1+1".Equals(c.CellFormula), "Formula is as expected");
}
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 TestModifyEmployee()
{
HSSFWorkbook workbook = OpenSample("Employee.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(3).GetCell(2);
cell.SetCellValue(new HSSFRichTextString(LAST_NAME_VALUE));
cell = sheet.GetRow(4).GetCell(2);
cell.SetCellValue(new HSSFRichTextString(FIRST_NAME_VALUE));
cell = sheet.GetRow(5).GetCell(2);
cell.SetCellValue(new HSSFRichTextString(SSN_VALUE));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
Assert.AreEqual(EMPLOYEE_INFORMATION, sheet.GetRow(1).GetCell(1).RichStringCellValue.String);
Assert.AreEqual(LAST_NAME_VALUE, sheet.GetRow(3).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(FIRST_NAME_VALUE, sheet.GetRow(4).GetCell(2).RichStringCellValue.String);
Assert.AreEqual(SSN_VALUE, sheet.GetRow(5).GetCell(2).RichStringCellValue.String);
}
public void TestShapeFlip()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor());
rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
Assert.AreEqual(rectangle.IsFlipVertical, false);
Assert.AreEqual(rectangle.IsFlipHorizontal, false);
rectangle.IsFlipVertical = true;
Assert.AreEqual(rectangle.IsFlipVertical, true);
rectangle.IsFlipHorizontal = true;
Assert.AreEqual(rectangle.IsFlipHorizontal, true);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
rectangle = (HSSFSimpleShape)patriarch.Children[0];
Assert.AreEqual(rectangle.IsFlipHorizontal, true);
rectangle.IsFlipHorizontal = false;
Assert.AreEqual(rectangle.IsFlipHorizontal, false);
Assert.AreEqual(rectangle.IsFlipVertical, true);
rectangle.IsFlipVertical = false;
Assert.AreEqual(rectangle.IsFlipVertical, false);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sheet.DrawingPatriarch as HSSFPatriarch;
rectangle = (HSSFSimpleShape)patriarch.Children[0];
Assert.AreEqual(rectangle.IsFlipVertical, false);
Assert.AreEqual(rectangle.IsFlipHorizontal, false);
}
public void TestAddTextToExistingFile()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sh = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox.String = (new HSSFRichTextString("just for Test"));
HSSFTextbox textbox2 = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox2.String = (new HSSFRichTextString("just for Test2"));
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sh = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFTextbox text3 = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
text3.String = (new HSSFRichTextString("text3"));
Assert.AreEqual(patriarch.Children.Count, 3);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
sh = wb3.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
Assert.AreEqual(((HSSFTextbox)patriarch.Children[0]).String.String, "just for Test");
Assert.AreEqual(((HSSFTextbox)patriarch.Children[1]).String.String, "just for Test2");
Assert.AreEqual(((HSSFTextbox)patriarch.Children[2]).String.String, "text3");
wb3.Close();
}
public void TestGroupRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = workbook.CreateSheet();
HSSFRow r1 = (HSSFRow)s.CreateRow(0);
HSSFRow r2 = (HSSFRow)s.CreateRow(1);
HSSFRow r3 = (HSSFRow)s.CreateRow(2);
HSSFRow r4 = (HSSFRow)s.CreateRow(3);
HSSFRow r5 = (HSSFRow)s.CreateRow(4);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(0, r3.OutlineLevel);
Assert.AreEqual(0, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
s.GroupRow(2, 3);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(1, r3.OutlineLevel);
Assert.AreEqual(1, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
// Save and re-Open
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
s = workbook.GetSheetAt(0);
r1 = (HSSFRow)s.GetRow(0);
r2 = (HSSFRow)s.GetRow(1);
r3 = (HSSFRow)s.GetRow(2);
r4 = (HSSFRow)s.GetRow(3);
r5 = (HSSFRow)s.GetRow(4);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(1, r3.OutlineLevel);
Assert.AreEqual(1, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
}
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 TestStringFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(1); c.CellFormula = ("UPPER(\"abc\")");
c = r.CreateCell(2); c.CellFormula = ("LOWER(\"ABC\")");
c = r.CreateCell(3); c.CellFormula = ("CONCATENATE(\" my \",\" name \")");
HSSFTestDataSamples.WriteOutAndReadBack(wb);
wb = OpenSample("StringFormulas.xls");
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.AreEqual("UPPER(\"xyz\")", c.CellFormula);
}
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);
}
public void TestBug50298a()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("50298.xls");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
ISheet sheet = wb.CloneSheet(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "Invoice (2)");
wb.SetSheetName(wb.GetSheetIndex(sheet), "copy");
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received", "copy");
wb.SetSheetOrder("copy", 0);
assertSheetOrder(wb, "copy", "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(0);
assertSheetOrder(wb, "Invoice", "Invoice1", "Digest", "Deferred", "Received");
wb.RemoveSheetAt(1);
assertSheetOrder(wb, "Invoice", "Digest", "Deferred", "Received");
wb.SetSheetOrder("Digest", 3);
assertSheetOrder(wb, "Invoice", "Deferred", "Received", "Digest");
// check that the overall workbook serializes with its correct size
int expected = wb.Workbook.Size;
int written = wb.Workbook.Serialize(0, new byte[expected * 2]);
Assert.AreEqual(expected, written, "Did not have the expected size when writing the workbook: written: " + written + ", but expected: " + expected);
HSSFWorkbook read = HSSFTestDataSamples.WriteOutAndReadBack(wb);
assertSheetOrder(wb, "Invoice", "Deferred", "Received", "Digest");
}
/**
* Tests Order wrting out == Order writing in for a given formula
*/
private static void OrderTest(string formula)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (formula);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
//get our minimum values
r = s.GetRow(0);
c = r.GetCell(1);
Assert.IsTrue(formula.Equals(c.CellFormula), "minval Formula is as expected"
);
}
/**
* Writes a function then Tests to see if its correct
*/
public static void AreaFunctionTest(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:A3)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue((function + "(A2:A3)").Equals((function + "(A2:A3)")), "function =" + function + "(A2:A3)"
);
}
public void TestRemoveHyperlink()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell1 = row.CreateCell(1) as HSSFCell;
HSSFHyperlink link1 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link1);
cell1.RemoveHyperlink();
Assert.IsNull(cell1.Hyperlink);
HSSFCell cell2 = row.CreateCell(0) as HSSFCell;
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link2);
cell2.Hyperlink = (/*setter*/ null);
Assert.IsNull(cell2.Hyperlink);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
/**
* Writes a function then Tests to see if its correct
*/
public void RefArrayFunctionTest(string function)
{
HSSFWorkbook wb = new HSSFWorkbook();
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = (function + "(A2,A3)");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue((function + "(A2,A3)").Equals(c.CellFormula), "function =" + function + "(A2,A3)"
);
}
public void TestAttemptToSave2CommentsWithSameCoordinates()
{
Object err = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
patriarch.CreateCellComment(new HSSFClientAnchor());
patriarch.CreateCellComment(new HSSFClientAnchor());
try
{
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
catch (InvalidOperationException e)
{
err = 1;
Assert.AreEqual(e.Message, "found multiple cell comments for cell A1");
}
Assert.IsNotNull(err);
}
public void TestPolygonType()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sh = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(102, 101);
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sh = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
HSSFPolygon polygon1 = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon1.SetPolygonDrawArea(102, 101);
polygon1.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
EscherSpRecord spRecord = polygon1.GetEscherContainer().GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
spRecord.ShapeType = ((short)77 /**RANDOM**/);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
sh = wb3.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
Assert.IsTrue(patriarch.Children[0] is HSSFPolygon);
Assert.IsTrue(patriarch.Children[1] is HSSFPolygon);
wb3.Close();
}
public void TestModifySimpleWithStyling()
{
HSSFWorkbook workbook = OpenSample("SimpleWithStyling.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
for (int k = 0; k < 4; k++)
{
ICell cell = sheet.GetRow(k).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
}
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
for (int k = 0; k < 4; k++)
{
ICell cell = sheet.GetRow(k).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
}
}
public void TestModifySimpleWithSkip()
{
HSSFWorkbook workbook = OpenSample("SimpleWithSkip.xls");
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(1);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
cell = sheet.GetRow(1).GetCell(0);
cell.SetCellValue(new HSSFRichTextString(REPLACED));
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
sheet = workbook.GetSheetAt(0);
cell = sheet.GetRow(0).GetCell(1);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual(DO_NOT_REPLACE, cell.RichStringCellValue.String);
cell = sheet.GetRow(1).GetCell(0);
Assert.AreEqual(REPLACED, cell.RichStringCellValue.String);
cell = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(DO_NOT_REPLACE, cell.RichStringCellValue.String);
}