public void CreateFromReadOnlyWorkbook()
{
FileInfo input = XSSFTestDataSamples.GetSampleFile("sample.xlsx");
OPCPackage pkg = OPCPackage.Open(input, PackageAccess.READ);
XSSFWorkbook xssf = new XSSFWorkbook(pkg);
SXSSFWorkbook wb = new SXSSFWorkbook(xssf, 2);
String sheetName = "Test SXSSF";
ISheet s = wb.CreateSheet(sheetName);
for (int i = 0; i < 10; i++)
{
IRow r = s.CreateRow(i);
r.CreateCell(0).SetCellValue(true);
r.CreateCell(1).SetCellValue(2.4);
r.CreateCell(2).SetCellValue("Test Row " + i);
}
Assert.AreEqual(10, s.LastRowNum);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.Write(bos);
wb.Dispose();
wb.Close();
xssf = new XSSFWorkbook(new ByteArrayInputStream(bos.ToByteArray()));
s = xssf.GetSheet(sheetName);
Assert.AreEqual(10, s.LastRowNum);
Assert.AreEqual(true, s.GetRow(0).GetCell(0).BooleanCellValue);
Assert.AreEqual("Test Row 9", s.GetRow(9).GetCell(2).StringCellValue);
}
public void TestZipBombNotTriggeredOnUselessContent()
{
SXSSFWorkbook swb = new SXSSFWorkbook(null, 1, true, true);
SXSSFSheet s = swb.CreateSheet() as SXSSFSheet;
char[] useless = new char[32767];
Arrays.Fill(useless, ' ');
for (int row = 0; row < 1; row++)
{
IRow r = s.CreateRow(row);
for (int col = 0; col < 10; col++)
{
char[] prefix = HexDump.ToHex(row * 1000 + col).ToCharArray();
Arrays.Fill(useless, 0, 10, ' ');
Array.Copy(prefix, 0, useless, 0, prefix.Length);
String ul = new String(useless);
r.CreateCell(col, CellType.String).SetCellValue(ul);
ul = null;
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
swb.Write(bos);
swb.Dispose();
swb.Close();
}
private void WriteFile(string saveAsPath, SXSSFWorkbook wb)
{
//Passing SXSSFWorkbook because IWorkbook does not implement .Dispose which cleans ups temporary files.
using (FileStream fs = new FileStream(saveAsPath, FileMode.Create, FileAccess.ReadWrite))
{
wb.Write(fs);
}
wb.Dispose();
}
public bool Cleanup()
{
bool ok = true;
for (int i = 0; i < instances.Count; i++)
{
SXSSFWorkbook wb = instances[(i)];
ok = ok && wb.Dispose();
}
instances.Clear();
return(ok);
}
public void GzipSheetdataWriter()
{
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.CompressTempFiles = true;
int rowNum = 1000;
int sheetNum = 5;
for (int i = 0; i < sheetNum; i++)
{
ISheet sh = wb.CreateSheet("sheet" + i);
for (int j = 0; j < rowNum; j++)
{
IRow row = sh.CreateRow(j);
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(new CellReference(cell1).FormatAsString());
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(i);
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(j);
}
}
XSSFWorkbook xwb = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb) as XSSFWorkbook;
for (int i = 0; i < sheetNum; i++)
{
ISheet sh = xwb.GetSheetAt(i);
Assert.AreEqual("sheet" + i, sh.SheetName);
for (int j = 0; j < rowNum; j++)
{
IRow row = sh.GetRow(j);
Assert.IsNotNull(row, "row[" + j + "]");
ICell cell1 = row.GetCell(0);
Assert.AreEqual(new CellReference(cell1).FormatAsString(), cell1.StringCellValue);
ICell cell2 = row.GetCell(1);
Assert.AreEqual(i, (int)cell2.NumericCellValue);
ICell cell3 = row.GetCell(2);
Assert.AreEqual(j, (int)cell3.NumericCellValue);
}
}
Assert.IsTrue(wb.Dispose());
xwb.Close();
wb.Close();
}
public void UseSharedStringsTable()
{
SXSSFWorkbook wb = new SXSSFWorkbook(null, 10, false, true);
SharedStringsTable sss = POITestCase.GetFieldValue <SharedStringsTable, SXSSFWorkbook>(typeof(SXSSFWorkbook), wb, typeof(SharedStringsTable), "_sharedStringSource");
Assert.IsNotNull(sss);
IRow row = wb.CreateSheet("S1").CreateRow(0);
row.CreateCell(0).SetCellValue("A");
row.CreateCell(1).SetCellValue("B");
row.CreateCell(2).SetCellValue("A");
XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb) as XSSFWorkbook;
sss = POITestCase.GetFieldValue <SharedStringsTable, SXSSFWorkbook>(typeof(SXSSFWorkbook), wb, typeof(SharedStringsTable), "_sharedStringSource");
Assert.AreEqual(2, sss.UniqueCount);
Assert.IsTrue(wb.Dispose());
ISheet sheet1 = xssfWorkbook.GetSheetAt(0);
Assert.AreEqual("S1", sheet1.SheetName);
Assert.AreEqual(1, sheet1.PhysicalNumberOfRows);
row = sheet1.GetRow(0);
Assert.IsNotNull(row);
ICell cell = row.GetCell(0);
Assert.IsNotNull(cell);
Assert.AreEqual("A", cell.StringCellValue);
cell = row.GetCell(1);
Assert.IsNotNull(cell);
Assert.AreEqual("B", cell.StringCellValue);
cell = row.GetCell(2);
Assert.IsNotNull(cell);
Assert.AreEqual("A", cell.StringCellValue);
xssfWorkbook.Close();
wb.Close();
}
public void SheetdataWriter()
{
SXSSFWorkbook wb = new SXSSFWorkbook();
SXSSFSheet sh = wb.CreateSheet() as SXSSFSheet;
SheetDataWriter wr = sh.SheetDataWriter;
Assert.IsTrue(wr.GetType() == typeof(SheetDataWriter));
FileInfo tmp = wr.TempFileInfo;
Assert.IsTrue(tmp.Name.StartsWith("poi-sxssf-sheet"));
Assert.IsTrue(tmp.Name.EndsWith(".xml"));
Assert.IsTrue(wb.Dispose());
wb.Close();
wb = new SXSSFWorkbook();
wb.CompressTempFiles = (/*setter*/ true);
sh = wb.CreateSheet() as SXSSFSheet;
wr = sh.SheetDataWriter;
Assert.IsTrue(wr.GetType() == typeof(GZIPSheetDataWriter));
tmp = wr.TempFileInfo;
Assert.IsTrue(tmp.Name.StartsWith("poi-sxssf-sheet-xml"));
Assert.IsTrue(tmp.Name.EndsWith(".gz"));
Assert.IsTrue(wb.Dispose());
wb.Close();
//Test escaping of Unicode control characters
wb = new SXSSFWorkbook();
wb.CreateSheet("S1").CreateRow(0).CreateCell(0).SetCellValue("value\u0019");
XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb) as XSSFWorkbook;
ICell cell = xssfWorkbook.GetSheet("S1").GetRow(0).GetCell(0);
Assert.AreEqual("value?", cell.StringCellValue);
Assert.IsTrue(wb.Dispose());
wb.Close();
xssfWorkbook.Close();
}
public void ExistingWorkbook()
{
XSSFWorkbook xssfWb1 = new XSSFWorkbook();
xssfWb1.CreateSheet("S1");
SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1);
XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsTrue(wb1.Dispose());
SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2);
Assert.AreEqual(1, wb2.NumberOfSheets);
ISheet sheet = wb2.GetSheetAt(0);
Assert.IsNotNull(sheet);
Assert.AreEqual("S1", sheet.SheetName);
Assert.IsTrue(wb2.Dispose());
xssfWb2.Close();
xssfWb1.Close();
wb2.Close();
wb1.Close();
}
protected static void assertWorkbookDispose(SXSSFWorkbook wb)
{
int rowNum = 1000;
int sheetNum = 5;
for (int i = 0; i < sheetNum; i++)
{
ISheet sh = wb.CreateSheet("sheet" + i);
for (int j = 0; j < rowNum; j++)
{
IRow row = sh.CreateRow(j);
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(new CellReference(cell1).FormatAsString());
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(i);
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(j);
}
}
foreach (ISheet sheet in wb)
{
SXSSFSheet sxSheet = (SXSSFSheet)sheet;
Assert.IsTrue(sxSheet.SheetDataWriter.TempFileInfo.Exists);
}
Assert.IsTrue(wb.Dispose());
foreach (ISheet sheet in wb)
{
SXSSFSheet sxSheet = (SXSSFSheet)sheet;
Assert.IsFalse(sxSheet.SheetDataWriter.TempFileInfo.Exists);
}
}
public void AddToExistingWorkbook()
{
XSSFWorkbook xssfWb1 = new XSSFWorkbook();
xssfWb1.CreateSheet("S1");
ISheet sheet = xssfWb1.CreateSheet("S2");
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("value 2_1_1");
SXSSFWorkbook wb1 = new SXSSFWorkbook(xssfWb1);
XSSFWorkbook xssfWb2 = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsTrue(wb1.Dispose());
xssfWb1.Close();
SXSSFWorkbook wb2 = new SXSSFWorkbook(xssfWb2);
// Add a row to the existing empty sheet
ISheet sheet1 = wb2.GetSheetAt(0);
IRow row1_1 = sheet1.CreateRow(1);
ICell cell1_1_1 = row1_1.CreateCell(1);
cell1_1_1.SetCellValue("value 1_1_1");
// Add a row to the existing non-empty sheet
ISheet sheet2 = wb2.GetSheetAt(1);
IRow row2_2 = sheet2.CreateRow(2);
ICell cell2_2_1 = row2_2.CreateCell(1);
cell2_2_1.SetCellValue("value 2_2_1");
// Add a sheet with one row
ISheet sheet3 = wb2.CreateSheet("S3");
IRow row3_1 = sheet3.CreateRow(1);
ICell cell3_1_1 = row3_1.CreateCell(1);
cell3_1_1.SetCellValue("value 3_1_1");
XSSFWorkbook xssfWb3 = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb2) as XSSFWorkbook;
wb2.Close();
Assert.AreEqual(3, xssfWb3.NumberOfSheets);
// Verify sheet 1
sheet1 = xssfWb3.GetSheetAt(0);
Assert.AreEqual("S1", sheet1.SheetName);
Assert.AreEqual(1, sheet1.PhysicalNumberOfRows);
row1_1 = sheet1.GetRow(1);
Assert.IsNotNull(row1_1);
cell1_1_1 = row1_1.GetCell(1);
Assert.IsNotNull(cell1_1_1);
Assert.AreEqual("value 1_1_1", cell1_1_1.StringCellValue);
// Verify sheet 2
sheet2 = xssfWb3.GetSheetAt(1);
Assert.AreEqual("S2", sheet2.SheetName);
Assert.AreEqual(2, sheet2.PhysicalNumberOfRows);
IRow row2_1 = sheet2.GetRow(1);
Assert.IsNotNull(row2_1);
ICell cell2_1_1 = row2_1.GetCell(1);
Assert.IsNotNull(cell2_1_1);
Assert.AreEqual("value 2_1_1", cell2_1_1.StringCellValue);
row2_2 = sheet2.GetRow(2);
Assert.IsNotNull(row2_2);
cell2_2_1 = row2_2.GetCell(1);
Assert.IsNotNull(cell2_2_1);
Assert.AreEqual("value 2_2_1", cell2_2_1.StringCellValue);
// Verify sheet 3
sheet3 = xssfWb3.GetSheetAt(2);
Assert.AreEqual("S3", sheet3.SheetName);
Assert.AreEqual(1, sheet3.PhysicalNumberOfRows);
row3_1 = sheet3.GetRow(1);
Assert.IsNotNull(row3_1);
cell3_1_1 = row3_1.GetCell(1);
Assert.IsNotNull(cell3_1_1);
Assert.AreEqual("value 3_1_1", cell3_1_1.StringCellValue);
xssfWb2.Close();
xssfWb3.Close();
wb1.Close();
}
