public void IfSheetNameByIndexShouldGetSheetNameFromXssfWorkbook()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("test1");
_objectToTest.CreateSheet("test2");
_objectToTest.SetSelectedTab(0);
Assert.IsTrue(_objectToTest.GetSheetAt(0).IsSelected);
}
public void IfSettingSelectedTabShouldSetSelectedTabOfXssfWorkbook()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("test1");
_objectToTest.CreateSheet("test2");
_objectToTest.SetSelectedTab(0);
Assert.IsTrue(_objectToTest.GetSheetAt(0).IsSelected);
}
public void IfCreatingASheetShouldCreateASheetInTheXssfWorkbookWithDefaultName()
{
_objectToTest = new SXSSFWorkbook();
var sheet = _objectToTest.CreateSheet();
var sheet2 = _objectToTest.CreateSheet();
Assert.NotNull(sheet);
Assert.AreEqual("Sheet0", sheet.SheetName);
Assert.AreEqual("Sheet1", sheet2.SheetName);
}
public void IfSettingSheetOrderShouldSetSheetOrderOfXssfWorkbook()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("test1");
_objectToTest.CreateSheet("test2");
_objectToTest.SetSheetOrder("test2", 0);
Assert.AreEqual("test2", _objectToTest.GetSheetName(0));
Assert.AreEqual("test1", _objectToTest.GetSheetName(1));
}
public void IfGivenTheIndexOfAnExistingSheetShouldReturnTheSheet()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("1");
_objectToTest.CreateSheet("2");
var sheet1 = _objectToTest.GetSheetAt(0);
var sheet2 = _objectToTest.GetSheetAt(1);
Assert.AreEqual("1", sheet1.SheetName);
Assert.AreEqual("2", sheet2.SheetName);
}
public void IfGivenThePositionOfAnExistingSheetShouldRemoveThatSheet()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("1");
_objectToTest.CreateSheet("2");
_objectToTest.RemoveSheetAt(0);
var sheet = _objectToTest.GetSheetAt(0);
Assert.IsTrue(_objectToTest.NumberOfSheets == 1);
Assert.AreEqual("2", sheet.SheetName);
}
public void IfRequestingTheSheetIndexBySheetNameShouldReturnTheIndexOfTheXssfSheet()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("test0");
_objectToTest.CreateSheet("test1");
_objectToTest.CreateSheet("test2");
var first = _objectToTest.GetSheetIndex("test0");
var second = _objectToTest.GetSheetIndex("test1");
var third = _objectToTest.GetSheetIndex("test2");
Assert.AreEqual(0, first);
Assert.AreEqual(1, second);
Assert.AreEqual(2, third);
}
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 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.LastFlushedRowNumber);
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.LastFlushedRowNumber);
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();
}
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 IfSettingSheetNameShouldChangeTheSheetNameAtTheSpecifiedIndex()
{
_objectToTest = new SXSSFWorkbook();
_objectToTest.CreateSheet("test1");
_objectToTest.SetSheetName(0, "renamed");
Assert.AreEqual("renamed", _objectToTest.GetSheetAt(0).SheetName);
}
public void IfCreatingASheetShouldCreateASheetInTheXssfWorkbookWithTheGivenName()
{
_objectToTest = new SXSSFWorkbook();
var sheet = _objectToTest.CreateSheet("test");
Assert.NotNull(sheet);
Assert.AreEqual("test", sheet.SheetName);
}
public void IfGivenASheetOfAWorkbookShouldGetTheIndexIfTheSheetExists()
{
_objectToTest = new SXSSFWorkbook();
var sheet = _objectToTest.CreateSheet("test");
var index = _objectToTest.GetSheetIndex(sheet);
Assert.AreEqual(0, index);
}
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));
}
}
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();
}
/// <summary>
/// datatable导出
/// </summary>
/// <param name="dataTable"></param>
/// <param name="col"></param>
public static void ToExcelByNpoi(DataTable dataTable, string col)
{
XSSFWorkbook work = new XSSFWorkbook();
IWorkbook workbook = new SXSSFWorkbook(work, 1000);
//创建一个Sheet1
SXSSFSheet sheet1 = (SXSSFSheet)workbook.CreateSheet("Sheet1");
DataTable dt = dataTable;
IRow rowhead = sheet1.CreateRow(0);
string str = col;
string[] strings = str.Split(',');
if (!strings.Any(x => string.IsNullOrEmpty(x)))
{
for (int i = 0; i < strings.Length; i++)
{
rowhead.CreateCell(i, CellType.String).SetCellValue(strings[i]);
}
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
rowhead.CreateCell(i, CellType.String).SetCellValue(dt.Columns[i].ColumnName);
}
}
int index = 1; for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet1.CreateRow(index++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
string title = "报表";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xlsx", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
work = null;
ms.Close();
ms.Dispose();
}
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);
}
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 ChangeRowNum()
{
SXSSFWorkbook wb = new SXSSFWorkbook(3);
SXSSFSheet sheet = wb.CreateSheet() as SXSSFSheet;
SXSSFRow row0 = sheet.CreateRow(0) as SXSSFRow;
SXSSFRow row1 = sheet.CreateRow(1) as SXSSFRow;
sheet.ChangeRowNum(row0, 2);
Assert.AreEqual(1, row1.RowNum, "Row 1 knows its row number");
Assert.AreEqual(2, row0.RowNum, "Row 2 knows its row number");
Assert.AreEqual(1, sheet.GetRowNum(row1), "Sheet knows Row 1's row number");
Assert.AreEqual(2, sheet.GetRowNum(row0), "Sheet knows Row 2's row number");
Assert.AreEqual(row1, sheet.GetEnumerator().Current, "Sheet row iteratation order should be ascending");
wb.Close();
}
public void testBug58175a()
{
IWorkbook wb = new SXSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(3);
cell.SetCellValue("F4");
IDrawing drawing = sheet.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
IClientAnchor anchor = factory.CreateClientAnchor();
anchor.Col1 = (cell.ColumnIndex);
anchor.Col2 = (cell.ColumnIndex + 1);
anchor.Row1 = (row.RowNum);
anchor.Row2 = (row.RowNum + 3);
// Create the comment and set the text+author
IComment comment = drawing.CreateCellComment(anchor);
IRichTextString str = factory.CreateRichTextString("Hello, World!");
comment.String = (str);
comment.Author = ("Apache POI");
/* fixed the problem as well
* comment.setColumn(cell.ColumnIndex);
* comment.setRow(cell.RowIndex);
*/
// Assign the comment to the cell
cell.CellComment = (comment);
FileStream out1 = new FileStream("C:\\temp\\58175.xlsx", FileMode.CreateNew, FileAccess.ReadWrite);
try
{
wb.Write(out1);
}
finally
{
out1.Close();
}
}
finally
{
wb.Close();
}
}
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 OverrideFlushedRows()
{
IWorkbook wb = new SXSSFWorkbook(3);
try {
ISheet sheet = wb.CreateSheet();
sheet.CreateRow(1);
sheet.CreateRow(2);
sheet.CreateRow(3);
sheet.CreateRow(4);
//thrown.Expect(typeof(Throwable));
//thrown.ExpectMessage("Attempting to write a row[1] in the range [0,1] that is already written to disk.");
sheet.CreateRow(1);
} finally {
wb.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 OverrideFlushedRows()
{
IWorkbook wb = new SXSSFWorkbook(3);
try {
ISheet sheet = wb.CreateSheet();
sheet.CreateRow(1);
sheet.CreateRow(2);
sheet.CreateRow(3);
sheet.CreateRow(4);
Assert.Throws <ArgumentException>(() => {
sheet.CreateRow(1);
}, "Attempting to write a row[1] in the range [0,1] that is already written to disk.");
} finally {
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
}