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();
}
public void TestEvaluateRefOutsideWindowFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(-1, s.LastFlushedRowNum);
for (int i = 1; i <= 19; i++)
{
s.CreateRow(i);
}
ICell c = s.CreateRow(20).CreateCell(0);
c.CellFormula = (/*setter*/ "A1+100");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(15, s.LastFlushedRowNum);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateFormulaCell(c);
Assert.Fail("Evaluate shouldn't work, as reference outside the window");
}
catch (RowFlushedException)
{
// Expected
}
wb.Close();
}
private XSSFSheet GetXSSFSheet(SXSSFSheet sheet)
{
if (sheet != null && _sxFromXHash.ContainsKey(sheet))
{
return(_sxFromXHash[sheet]);
}
else
{
return(null);
}
}
public void Test_EmptySheet_NoException()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackAllColumnsForAutoSizing();
for (int i = 0; i < 10; i++)
{
sheet.AutoSizeColumn(i, useMergedCells);
}
}
public void TestEvaluateAllFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(0).CellFormula = (/*setter*/ "A21");
for (int i = 2; i < 19; i++)
{
s.CreateRow(i);
}
// Cells outside window will fail, whether referenced or not
s.CreateRow(19).CreateCell(0).CellFormula = (/*setter*/ "A1+A2");
s.CreateRow(20).CreateCell(0).CellFormula = (/*setter*/ "A1+A11+100");
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as some cells outside the window");
}
catch (RowFlushedException)
{
// Expected
}
// Inactive sheets will fail
XSSFWorkbook xwb = new XSSFWorkbook();
xwb.CreateSheet("Open");
xwb.CreateSheet("Closed");
wb.Close();
wb = new SXSSFWorkbook(xwb, 5);
s = wb.GetSheet("Closed") as SXSSFSheet;
s.FlushRows();
s = wb.GetSheet("Open") as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateAll();
Assert.Fail("Evaluate All shouldn't work, as sheets flushed");
}
catch (SheetsFlushedException) { }
wb.Close();
}
public void AutoSizeColumn_isColumnTrackedForAutoSizing()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackColumnsForAutoSizing(columns);
foreach (int column in columns)
{
Assert.IsTrue(sheet.IsColumnTrackedForAutoSizing(column));
Assume.That(!columns.Contains(column + 10));
Assert.IsFalse(sheet.IsColumnTrackedForAutoSizing(column + 10));
}
}
private void InjectData(FileInfo zipfile, Stream outStream)
{
// don't use ZipHelper.openZipFile here - see #59743
ZipFile zip = new ZipFile(zipfile.FullName);
try
{
ZipOutputStream zos = new ZipOutputStream(outStream);
try
{
//ZipEntrySource zipEntrySource = new ZipFileZipEntrySource(zip);
//var en = zipEntrySource.Entries;
var en = zip.GetEnumerator();
while (en.MoveNext())
{
var ze = (ZipEntry)en.Current;
zos.PutNextEntry(new ZipEntry(ze.Name));
var inputStream = zip.GetInputStream(ze);
XSSFSheet xSheet = GetSheetFromZipEntryName(ze.Name);
if (xSheet != null)
{
SXSSFSheet sxSheet = GetSXSSFSheet(xSheet);
var xis = sxSheet.GetWorksheetXMLInputStream();
try
{
CopyStreamAndInjectWorksheet(inputStream, zos, xis);
}
finally
{
xis.Close();
}
}
else
{
CopyStream(inputStream, zos);
}
inputStream.Close();
}
}
finally
{
zos.Close();
}
}
finally
{
zip.Close();
}
}
private SXSSFSheet CreateAndRegisterSXSSFSheet(ISheet xSheet)
{
SXSSFSheet sxSheet;
try
{
sxSheet = new SXSSFSheet(this, (XSSFSheet)xSheet);
}
catch (IOException ioe)
{
throw new RuntimeException(ioe);
}
RegisterSheetMapping(sxSheet, (XSSFSheet)xSheet);
return(sxSheet);
}
public void Test_WindowSizeEqualsOne_lastRowIsWidest()
{
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackAllColumnsForAutoSizing();
ICell cellRow0 = CreateRowWithCellValues(sheet, 0, SHORT_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
CreateRowWithCellValues(sheet, 1, LONG_CELL_VALUE);
sheet.AutoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
public void Test_WindowSizeDefault_AllRowsFitIntoWindowSize()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackAllColumnsForAutoSizing();
ICell cellRow0 = CreateRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, cellRow0);
CreateRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
sheet.AutoSizeColumn(0, useMergedCells);
assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
}
private void DeregisterSheetMapping(XSSFSheet xSheet)
{
SXSSFSheet sxSheet = GetSXSSFSheet(xSheet);
try
{
sxSheet._writer.Close();
}
catch (IOException e)
{
// ignore exception here
}
_sxFromXHash.Remove(sxSheet);
_xFromSxHash.Remove(xSheet);
}
private void DeregisterSheetMapping(XSSFSheet xSheet)
{
SXSSFSheet sxSheet = GetSXSSFSheet(xSheet);
try
{
sxSheet._writer.Close();
}
catch (IOException) // ElectricSquare: The variable 'e' is declared but never used
{
// ignore exception here
}
_sxFromXHash.Remove(sxSheet);
_xFromSxHash.Remove(xSheet);
}
private void InjectData(ZipEntrySource zipEntrySource, Stream outStream)
{
try
{
ZipOutputStream zos = new ZipOutputStream(outStream);
try
{
var en = zipEntrySource.Entries;
while (en.MoveNext())
{
var ze = (ZipEntry)en.Current;
zos.PutNextEntry(new ZipEntry(ze.Name));
var inputStream = zipEntrySource.GetInputStream(ze);
XSSFSheet xSheet = GetSheetFromZipEntryName(ze.Name);
if (xSheet != null)
{
SXSSFSheet sxSheet = GetSXSSFSheet(xSheet);
var xis = sxSheet.getWorksheetXMLInputStream();
try
{
CopyStreamAndInjectWorksheet(inputStream, zos, xis);
}
finally
{
xis.Close();
}
}
else
{
CopyStream(inputStream, zos);
}
inputStream.Close();
}
}
finally
{
zos.Close();
}
}
finally
{
zipEntrySource.Close();
}
}
public void AutoSizeColumn_trackAllColumns_explicitUntrackColumn()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackColumnsForAutoSizing(columns);
sheet.TrackAllColumnsForAutoSizing();
sheet.UntrackColumnForAutoSizing(0);
try
{
sheet.AutoSizeColumn(0, useMergedCells);
Assert.Fail("Should not be able to auto-size an explicitly untracked column");
}
catch (InvalidOperationException)
{
// expected
}
}
public void TestEvaluateSimple()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "CONCATENATE(\"hello\",\" \",\"world\")");
eval.EvaluateFormulaCell(c);
Assert.AreEqual("hello world", c.StringCellValue);
}
public void TestEvaluateAllInWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(1).CellFormula = (/*setter*/ "A1+10");
s.CreateRow(2).CreateCell(2).CellFormula = (/*setter*/ "B2+100");
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
eval.EvaluateAll();
Assert.AreEqual(3, (int)s.GetRow(0).GetCell(0).NumericCellValue);
Assert.AreEqual(13, (int)s.GetRow(1).GetCell(1).NumericCellValue);
Assert.AreEqual(113, (int)s.GetRow(2).GetCell(2).NumericCellValue);
wb.Close();
}
public void TestEvaluateRefInsideWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.SetCellValue(1.5);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "A1*2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
wb.Close();
}
public void AutoSizeColumn_untrackColumnForAutoSizing()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackColumnsForAutoSizing(columns);
sheet.UntrackColumnForAutoSizing(columns.First());
Assume.That(sheet.TrackedColumnsForAutoSizing.Contains(columns.Last()));
sheet.AutoSizeColumn(columns.Last(), useMergedCells);
try
{
Assume.That(!sheet.TrackedColumnsForAutoSizing.Contains(columns.First()));
sheet.AutoSizeColumn(columns.First(), useMergedCells);
Assert.Fail("Should not be able to auto-size an untracked column");
}
catch (InvalidOperationException)
{
// expected
}
}
public void Test_WindowSizeEqualsOne_flushedRowHasMergedCell()
{
workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackAllColumnsForAutoSizing();
ICell a1 = CreateRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
assumeRequiredFontsAreInstalled(workbook, a1);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1"));
CreateRowWithCellValues(sheet, 1, SHORT_CELL_VALUE, SHORT_CELL_VALUE);
/**
* A B
* 1 LONGMERGED
* 2 SHORT SHORT
*/
sheet.AutoSizeColumn(0, useMergedCells);
sheet.AutoSizeColumn(1, useMergedCells);
if (useMergedCells)
{
// Excel and LibreOffice behavior: ignore merged cells for auto-sizing.
// POI behavior: evenly distribute the column width among the merged columns.
// each column must be auto-sized in order for the column widths
// to add up to the best fit width.
int colspan = 2;
int expectedWidth = (10000 + 1000) / colspan; //average of 1_000 and 10_000
int minExpectedWidth = expectedWidth / 2;
int maxExpectedWidth = expectedWidth * 3 / 2;
assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), minExpectedWidth, maxExpectedWidth); //short
}
else
{
assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); //long
}
assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(1), 0, COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG); //short
}
public void AutoSizeColumn_trackColumnForAutoSizing()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackColumnForAutoSizing(0);
SortedSet <int> expected = new SortedSet <int>();
expected.Add(0);
Assert.AreEqual(expected, sheet.TrackedColumnsForAutoSizing);
sheet.AutoSizeColumn(0, useMergedCells);
try
{
sheet.AutoSizeColumn(1, useMergedCells);
Assert.Fail("Should not be able to auto-size an untracked column");
}
catch (InvalidOperationException)
{
// expected
}
}
public void AutoSizeColumn_trackColumnsForAutoSizing()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
sheet.TrackColumnsForAutoSizing(columns);
SortedSet <int> sorted = new SortedSet <int>(columns);
Assert.AreEqual(sorted, sheet.TrackedColumnsForAutoSizing);
sheet.AutoSizeColumn(sorted.First(), useMergedCells);
try
{
//assumeFalse(columns.Contains(5));
Assume.That(!columns.Contains(5));
sheet.AutoSizeColumn(5, useMergedCells);
Assert.Fail("Should not be able to auto-size an untracked column");
}
catch (InvalidOperationException)
{
// expected
}
}
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();
}
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 SXSSFRow(SXSSFSheet sheet)
{
_sheet = sheet;
}
public override int GetSheetIndex(IEvaluationSheet evalSheet)
{
SXSSFSheet sheet = ((SXSSFEvaluationSheet)evalSheet).GetSXSSFSheet();
return(_xBook.GetSheetIndex(sheet));
}
public int getSheetIndex(SXSSFEvaluationSheet evalSheet)
{
SXSSFSheet sheet = ((SXSSFEvaluationSheet)evalSheet).getSXSSFSheet();
return(_uBook.GetSheetIndex(sheet));
}
public void SetUpSheetAndWorkbook()
{
workbook = new SXSSFWorkbook();
sheet = workbook.CreateSheet() as SXSSFSheet;
tracker = new AutoSizeColumnTracker(sheet);
}
private XSSFSheet GetXSSFSheet(SXSSFSheet sheet)
{
return(_sxFromXHash[sheet]);
}
private void RegisterSheetMapping(SXSSFSheet sxSheet, XSSFSheet xSheet)
{
_sxFromXHash.Add(sxSheet, xSheet);
_xFromSxHash.Add(xSheet, sxSheet);
}
public SXSSFEvaluationSheet(SXSSFSheet sheet)
{
_xs = sheet;
}
