TestWorkbookpublic class TestWorkbook extends TestCase | Class to test Workbook functionality |
| Fields Summary |
|---|
| private static final String | LAST_NAME_KEY | | private static final String | FIRST_NAME_KEY | | private static final String | SSN_KEY | | private static final String | REPLACE_ME | | private static final String | REPLACED | | private static final String | DO_NOT_REPLACE | | private static final String | EMPLOYEE_INFORMATION | | private static final String | LAST_NAME_VALUE | | private static final String | FIRST_NAME_VALUE | | private static final String | SSN_VALUE | | private SanityChecker | sanityChecker |
| Constructors Summary |
|---|
public TestWorkbook(String name)Constructor TestWorkbook
super(name);
|
| Methods Summary |
|---|
| public static void | main(java.lang.String[] ignored_args)
String filename = System.getProperty("HSSF.testdata.path");
// assume this is relative to basedir
if (filename == null)
{
System.setProperty(
"HSSF.testdata.path",
"src/testcases/org/apache/poi/hssf/data");
}
System.out
.println("Testing org.apache.poi.hssf.usermodel.HSSFWorkbook");
junit.textui.TestRunner.run(TestWorkbook.class);
| | public void | testBackupRecord()Test the backup field gets set as expected.
HSSFWorkbook wb = new HSSFWorkbook();
wb.createSheet();
Workbook workbook = wb.getWorkbook();
BackupRecord record = workbook.getBackupRecord();
assertEquals(0, record.getBackup());
wb.setBackupFlag(true);
assertEquals(1, record.getBackup());
| | public void | testManyRows()
String testName = "TestManyRows";
File file = TempFile.createTempFile(testName, ".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = null;
HSSFCell cell = null;
int i, j;
for ( i = 0, j = 32771; j > 0; i++, j-- )
{
row = sheet.createRow(i);
cell = row.createCell((short) 0);
cell.setCellValue(i);
}
workbook.write(out);
out.close();
sanityChecker.checkHSSFWorkbook(workbook);
assertEquals("LAST ROW == 32770", 32770, sheet.getLastRowNum());
double lastVal = cell.getNumericCellValue();
FileInputStream in = new FileInputStream(file);
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet s = wb.getSheetAt(0);
row = s.getRow(32770);
cell = row.getCell(( short ) 0);
assertEquals("Value from last row == 32770", lastVal, cell.getNumericCellValue(), 0);
assertEquals("LAST ROW == 32770", 32770, s.getLastRowNum());
in.close();
file.deleteOnExit();
| | public void | testModifyEmployee()TEST NAME: Test Modify Employee Sheet
OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
them with other string values despite any styling. In this release of HSSF styling will
probably be lost and is NOT tested.
SUCCESS: HSSF reads a sheet. HSSF replaces the cell values with other cell values. HSSF
writes the sheet out to another file. HSSF reads the result and ensures the value
has been properly replaced.
FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
is incorrect or has not been replaced.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/Employee.xls";
FileInputStream instream = new FileInputStream(filename);
POIFSFileSystem fsin = new POIFSFileSystem(instream);
HSSFWorkbook workbook = new HSSFWorkbook(fsin);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCell cell =
sheet.getRow(( short ) 3).getCell(( short ) 2);
cell.setCellValue(LAST_NAME_VALUE);
cell = sheet.getRow(( short ) 4).getCell(( short ) 2);
cell.setCellValue(FIRST_NAME_VALUE);
cell = sheet.getRow(( short ) 5).getCell(( short ) 2);
cell.setCellValue(SSN_VALUE);
File destination = TempFile.createTempFile("EmployeeResult",
".xls");
FileOutputStream outstream = new FileOutputStream(destination);
workbook.write(outstream);
instream.close();
outstream.close();
instream = new FileInputStream(destination);
workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
sheet = workbook.getSheetAt(0);
assertEquals(EMPLOYEE_INFORMATION,
sheet.getRow(1).getCell(( short ) 1)
.getStringCellValue());
assertEquals(LAST_NAME_VALUE,
sheet.getRow(3).getCell(( short ) 2)
.getStringCellValue());
assertEquals(FIRST_NAME_VALUE,
sheet.getRow(4).getCell(( short ) 2)
.getStringCellValue());
assertEquals(SSN_VALUE,
sheet.getRow(5).getCell(( short ) 2)
.getStringCellValue());
instream.close();
| | public void | testModifySimple()TEST NAME: Test Modify Sheet Simple
OBJECTIVE: Test that HSSF can read a simple spreadsheet with a string value and replace
it with another string value.
SUCCESS: HSSF reads a sheet. HSSF replaces the cell value with another cell value. HSSF
writes the sheet out to another file. HSSF reads the result and ensures the value
has been properly replaced.
FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
is incorrect or has not been replaced.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/Simple.xls";
FileInputStream instream = new FileInputStream(filename);
POIFSFileSystem fsin = new POIFSFileSystem(instream);
HSSFWorkbook workbook = new HSSFWorkbook(fsin);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCell cell =
sheet.getRow(( short ) 0).getCell(( short ) 0);
cell.setCellValue(REPLACED);
File destination = TempFile.createTempFile("SimpleResult",
".xls");
FileOutputStream outstream = new FileOutputStream(destination);
workbook.write(outstream);
instream.close();
outstream.close();
instream = new FileInputStream(destination);
workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
sheet = workbook.getSheetAt(0);
cell = sheet.getRow(( short ) 0).getCell(( short ) 0);
assertEquals(REPLACED, cell.getStringCellValue());
instream.close();
| | public void | testModifySimpleWithSkip()TEST NAME: Test Modify Sheet Simple With Skipped cells
OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
them with other string values while not replacing other cells.
SUCCESS: HSSF reads a sheet. HSSF replaces the cell value with another cell value. HSSF
writes the sheet out to another file. HSSF reads the result and ensures the value
has been properly replaced and unreplaced values are still unreplaced.
FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
is incorrect or has not been replaced or the incorrect cell has its value replaced
or is incorrect.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/SimpleWithSkip.xls";
FileInputStream instream = new FileInputStream(filename);
POIFSFileSystem fsin = new POIFSFileSystem(instream);
HSSFWorkbook workbook = new HSSFWorkbook(fsin);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCell cell =
sheet.getRow(( short ) 0).getCell(( short ) 1);
cell.setCellValue(REPLACED);
cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
cell.setCellValue(REPLACED);
File destination =
TempFile.createTempFile("SimpleWithSkipResult", ".xls");
FileOutputStream outstream = new FileOutputStream(destination);
workbook.write(outstream);
instream.close();
outstream.close();
instream = new FileInputStream(destination);
workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
sheet = workbook.getSheetAt(0);
cell = sheet.getRow(( short ) 0).getCell(( short ) 1);
assertEquals(REPLACED, cell.getStringCellValue());
cell = sheet.getRow(( short ) 0).getCell(( short ) 0);
assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
cell = sheet.getRow(( short ) 1).getCell(( short ) 0);
assertEquals(REPLACED, cell.getStringCellValue());
cell = sheet.getRow(( short ) 1).getCell(( short ) 1);
assertEquals(DO_NOT_REPLACE, cell.getStringCellValue());
instream.close();
| | public void | testModifySimpleWithStyling()TEST NAME: Test Modify Sheet With Styling
OBJECTIVE: Test that HSSF can read a simple spreadsheet with string values and replace
them with other string values despite any styling. In this release of HSSF styling will
probably be lost and is NOT tested.
SUCCESS: HSSF reads a sheet. HSSF replaces the cell values with other cell values. HSSF
writes the sheet out to another file. HSSF reads the result and ensures the value
has been properly replaced.
FAILURE: HSSF does not read a sheet or excepts. HSSF does not write the sheet or excepts.
HSSF does not re-read the sheet or excepts. Upon re-reading the sheet the value
is incorrect or has not been replaced.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/SimpleWithStyling.xls";
FileInputStream instream = new FileInputStream(filename);
POIFSFileSystem fsin = new POIFSFileSystem(instream);
HSSFWorkbook workbook = new HSSFWorkbook(fsin);
HSSFSheet sheet = workbook.getSheetAt(0);
for (int k = 0; k < 4; k++)
{
HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);
cell.setCellValue(REPLACED);
}
File destination =
TempFile.createTempFile("SimpleWithStylingResult", ".xls");
FileOutputStream outstream = new FileOutputStream(destination);
workbook.write(outstream);
instream.close();
outstream.close();
instream = new FileInputStream(destination);
workbook = new HSSFWorkbook(new POIFSFileSystem(instream));
sheet = workbook.getSheetAt(0);
for (int k = 0; k < 4; k++)
{
HSSFCell cell = sheet.getRow(( short ) k).getCell(( short ) 0);
assertEquals(REPLACED, cell.getStringCellValue());
}
instream.close();
| | public void | testReadEmployeeSimple()TEST NAME: Test Read Employee Simple
OBJECTIVE: Test that HSSF can read a simple spreadsheet (Employee.xls).
SUCCESS: HSSF reads the sheet. Matches values in their particular positions.
FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
in the sheet in their known positions.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/Employee.xls";
FileInputStream stream = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(0);
assertEquals(EMPLOYEE_INFORMATION,
sheet.getRow(1).getCell(( short ) 1)
.getStringCellValue());
assertEquals(LAST_NAME_KEY,
sheet.getRow(3).getCell(( short ) 2)
.getStringCellValue());
assertEquals(FIRST_NAME_KEY,
sheet.getRow(4).getCell(( short ) 2)
.getStringCellValue());
assertEquals(SSN_KEY,
sheet.getRow(5).getCell(( short ) 2)
.getStringCellValue());
stream.close();
| | public void | testReadSheetWithRK()TEST NAME: Test Read Sheet with an RK number
OBJECTIVE: Test that HSSF can read a simple spreadsheet with and RKRecord and correctly
identify the cell as numeric and convert it to a NumberRecord.
SUCCESS: HSSF reads a sheet. HSSF returns that the cell is a numeric type cell.
FAILURE: HSSF does not read a sheet or excepts. HSSF incorrectly indentifies the cell
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/rk.xls";
// a.xls has a value on position (0,0)
FileInputStream in = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook h = new HSSFWorkbook(fs);
HSSFSheet s = h.getSheetAt(0);
HSSFRow r = s.getRow(0);
HSSFCell c = r.getCell(( short ) 0);
int a = c.getCellType();
assertEquals(a, c.CELL_TYPE_NUMERIC);
| | public void | testReadSimple()TEST NAME: Test Read Simple
OBJECTIVE: Test that HSSF can read a simple spreadsheet (Simple.xls).
SUCCESS: HSSF reads the sheet. Matches values in their particular positions.
FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
in the sheet in their known positions.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/Simple.xls";
FileInputStream stream = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(0);
assertEquals(REPLACE_ME,
sheet.getRow(( short ) 0).getCell(( short ) 0)
.getStringCellValue());
stream.close();
| | public void | testReadSimpleWithDataFormat()TEST NAME: Test Read Simple w/ Data Format
OBJECTIVE: Test that HSSF can read a simple spreadsheet (SimpleWithDataFormat.xls).
SUCCESS: HSSF reads the sheet. Matches values in their particular positions and format is correct
FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
in the sheet in their known positions.
String filename = System.getProperty("HSSF.testdata.path");
filename = filename + "/SimpleWithDataFormat.xls";
FileInputStream stream = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFDataFormat format = workbook.createDataFormat();
HSSFCell cell =
sheet.getRow(( short ) 0).getCell(( short ) 0);
assertEquals(1.25,cell.getNumericCellValue(), 1e-10);
assertEquals(format.getFormat(cell.getCellStyle().getDataFormat()), "0.0");
stream.close();
| | public void | testRepeatingBug()This tests is for bug [ #506658 ] Repeating output.
We need to make sure only one LabelSSTRecord is produced.
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Design Variants");
HSSFRow row = sheet.createRow(( short ) 2);
HSSFCell cell = row.createCell(( short ) 1);
cell.setCellValue("Class");
cell = row.createCell(( short ) 2);
// workbook.write(new FileOutputStream("/a2.xls"));
ValueRecordsAggregate valueAggregate =
( ValueRecordsAggregate ) sheet.getSheet()
.findFirstRecordBySid(ValueRecordsAggregate.sid);
int sstRecords = 0;
Iterator iterator = valueAggregate.getIterator();
while (iterator.hasNext())
{
if ((( Record ) iterator.next()).getSid() == LabelSSTRecord.sid)
{
sstRecords++;
}
}
assertEquals(1, sstRecords);
| | public void | testRepeatingColsRows()Generate a file to visually/programmatically verify repeating rows and cols made it
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Test Print Titles");
String sheetName = workbook.getSheetName(0);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)1);
cell.setCellValue("hi");
workbook.setRepeatingRowsAndColumns(0, 0, 1, 0, 0);
File file = TempFile.createTempFile("testPrintTitles",".xls");
FileOutputStream fileOut = new FileOutputStream(file);
workbook.write(fileOut);
fileOut.close();
assertTrue("file exists",file.exists());
| | public void | testWriteDataFormat()TEST NAME: Test Read/Write Simple w/ Data Format
OBJECTIVE: Test that HSSF can write a sheet with custom data formats and then read it and get the proper formats.
SUCCESS: HSSF reads the sheet. Matches values in their particular positions and format is correct
FAILURE: HSSF does not read a sheet or excepts. HSSF cannot identify values
in the sheet in their known positions.
File file = TempFile.createTempFile("testWriteDataFormat",
".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cs = wb.createCellStyle();
short df = format.getFormat("0.0");
cs.setDataFormat(df);
r = s.createRow((short)0);
c = r.createCell((short)0);
c.setCellStyle(cs);
c.setCellValue(1.25);
wb.write(out);
out.close();
FileInputStream stream = new FileInputStream(file);
POIFSFileSystem fs = new POIFSFileSystem(stream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCell cell =
sheet.getRow(( short ) 0).getCell(( short ) 0);
format = workbook.createDataFormat();
assertEquals(1.25,cell.getNumericCellValue(), 1e-10);
assertEquals(format.getFormat(df), "0.0");
assertEquals(format, workbook.createDataFormat());
stream.close();
| | public void | testWriteModifySheetMerged()TEST NAME: Test Write/Modify Sheet Simple
OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values,
remove some rows, yet still have a valid file/data.
SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
Last row, first row is tested against the correct values (74,25).
FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
HSSFSheet last row or first row is incorrect.
File file = TempFile.createTempFile("testWriteSheetMerged",
".xls");
FileOutputStream out = new FileOutputStream(file);
FileInputStream in = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.createRow(rownum);
// r.setRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.createCell(cellnum);
c.setCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.createCell(( short ) (cellnum + 1));
c.setCellValue("TEST");
}
}
s.addMergedRegion(new Region(( short ) 0, ( short ) 0, ( short ) 10,
( short ) 10));
s.addMergedRegion(new Region(( short ) 30, ( short ) 5, ( short ) 40,
( short ) 15));
wb.write(out);
out.close();
sanityChecker.checkHSSFWorkbook(wb);
in = new FileInputStream(file);
wb = new HSSFWorkbook(new POIFSFileSystem(in));
s = wb.getSheetAt(0);
Region r1 = s.getMergedRegionAt(0);
Region r2 = s.getMergedRegionAt(1);
in.close();
// System.out.println(file.length());
// assertEquals("FILE LENGTH == 87552",file.length(), 87552);
// System.out.println(s.getLastRowNum());
assertEquals("REGION1 = 0,0,10,10", 0,
new Region(( short ) 0, ( short ) 0, ( short ) 10,
( short ) 10).compareTo(r1));
assertEquals("REGION2 == 30,5,40,15", 0,
new Region(( short ) 30, ( short ) 5, ( short ) 40,
( short ) 15).compareTo(r2));
| | public void | testWriteModifySheetSimple()TEST NAME: Test Write/Modify Sheet Simple
OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values,
remove some rows, yet still have a valid file/data.
SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
Last row, first row is tested against the correct values (74,25).
FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
HSSFSheet last row or first row is incorrect.
File file = TempFile.createTempFile("testWriteSheetSimple",
".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.createRow(rownum);
// r.setRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.createCell(cellnum);
c.setCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.createCell(( short ) (cellnum + 1));
c.setCellValue("TEST");
}
}
for (short rownum = ( short ) 0; rownum < 25; rownum++)
{
r = s.getRow(rownum);
s.removeRow(r);
}
for (short rownum = ( short ) 75; rownum < 100; rownum++)
{
r = s.getRow(rownum);
s.removeRow(r);
}
wb.write(out);
out.close();
sanityChecker.checkHSSFWorkbook(wb);
assertEquals("LAST ROW == 74", 74, s.getLastRowNum());
assertEquals("FIRST ROW == 25", 25, s.getFirstRowNum());
| | public void | testWriteSheetSimple()TEST NAME: Test Write Sheet Simple
OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values.
SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects
Last row, first row is tested against the correct values (99,0).
FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good.
HSSFSheet last row or first row is incorrect.
File file = TempFile.createTempFile("testWriteSheetSimple",
".xls");
FileOutputStream out = new FileOutputStream(file);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.createRow(rownum);
// r.setRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.createCell(cellnum);
c.setCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.createCell(( short ) (cellnum + 1));
c.setCellValue("TEST");
}
}
wb.write(out);
out.close();
sanityChecker.checkHSSFWorkbook(wb);
assertEquals("LAST ROW == 99", 99, s.getLastRowNum());
assertEquals("FIRST ROW == 0", 0, s.getFirstRowNum());
// assert((s.getLastRowNum() == 99));
|
|
