testworkbook.java

来自「EXCEL read and write」· Java 代码 · 共 546 行 · 第 1/2 页

        workbook = HSSFTestDataSamples.writeOutAndReadBack(workbook);
        sheet    = workbook.getSheetAt(0);
        cell     = sheet.getRow(0).getCell(0);
        assertEquals(REPLACED, cell.getRichStringCellValue().getString());
    }

    /**
     * TEST NAME:  Test Modify Sheet Simple With Skipped cells<P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with string values and replace
     *             them with other string values while not replacing other cells.<P>
     * 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.    <P>
     * 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. <P>
     *
     */
    public void testModifySimpleWithSkip() {
        HSSFWorkbook workbook = openSample("SimpleWithSkip.xls");
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFCell 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);
        assertEquals(REPLACED, cell.getRichStringCellValue().getString());
        cell = sheet.getRow(0).getCell(0);
        assertEquals(DO_NOT_REPLACE, cell.getRichStringCellValue().getString());
        cell = sheet.getRow(1).getCell(0);
        assertEquals(REPLACED, cell.getRichStringCellValue().getString());
        cell = sheet.getRow(1).getCell(1);
        assertEquals(DO_NOT_REPLACE, cell.getRichStringCellValue().getString());
    }

    /**
     * TEST NAME:  Test Modify Sheet With Styling<P>
     * 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.<P>
     * 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.    <P>
     * 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. <P>
     *
     */
    public void testModifySimpleWithStyling() {
        HSSFWorkbook workbook = openSample("SimpleWithStyling.xls");
        HSSFSheet       sheet    = workbook.getSheetAt(0);

        for (int k = 0; k < 4; k++)
        {
            HSSFCell 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++)
        {
            HSSFCell cell = sheet.getRow(k).getCell(0);

            assertEquals(REPLACED, cell.getRichStringCellValue().getString());
        }
    }

    /**
     * TEST NAME:  Test Modify Employee Sheet<P>
     * 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.<P>
     * 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.    <P>
     * 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. <P>
     *
     */
    public void testModifyEmployee() {
        HSSFWorkbook workbook = openSample("Employee.xls");
        HSSFSheet       sheet    = workbook.getSheetAt(0);
        HSSFCell        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);
        assertEquals(EMPLOYEE_INFORMATION, sheet.getRow(1).getCell(1).getRichStringCellValue().getString());
        assertEquals(LAST_NAME_VALUE, sheet.getRow(3).getCell(2).getRichStringCellValue().getString());
        assertEquals(FIRST_NAME_VALUE, sheet.getRow(4).getCell(2).getRichStringCellValue().getString());
        assertEquals(SSN_VALUE, sheet.getRow(5).getCell(2).getRichStringCellValue().getString());
    }

    /**
     * TEST NAME:  Test Read Sheet with an RK number<P>
     * 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.  <P>
     * SUCCESS:    HSSF reads a sheet.  HSSF returns that the cell is a numeric type cell.    <P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF incorrectly indentifies the cell<P>
     *
     */
    public void testReadSheetWithRK() {
        HSSFWorkbook h = openSample("rk.xls");
        HSSFSheet       s  = h.getSheetAt(0);
        HSSFCell        c  = s.getRow(0).getCell(0);
        int             a  = c.getCellType();

        assertEquals(a, c.CELL_TYPE_NUMERIC);
    }

    /**
     * TEST NAME:  Test Write/Modify Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can create a simple spreadsheet with numeric and string values,
     *             remove some rows, yet still have a valid file/data.<P>
     * SUCCESS:    HSSF creates a sheet.  Filesize matches a known good.  HSSFSheet objects
     *             Last row, first row is tested against the correct values (74,25).<P>
     * FAILURE:    HSSF does not create a sheet or excepts.  Filesize does not match the known good.
     *             HSSFSheet last row or first row is incorrect.             <P>
     *
     */
    public void testWriteModifySheetMerged() {
        HSSFWorkbook     wb   = new HSSFWorkbook();
        HSSFSheet        s    = wb.createSheet();

        for (int rownum = 0; rownum < 100; rownum++) {
            HSSFRow r = s.createRow(rownum);

            for (int cellnum = 0; cellnum < 50; cellnum += 2) {
                HSSFCell c = r.createCell(cellnum);
                c.setCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.createCell(cellnum + 1);
                c.setCellValue(new HSSFRichTextString("TEST"));
            }
        }
        s.addMergedRegion(new CellRangeAddress(0, 10, 0, 10));
        s.addMergedRegion(new CellRangeAddress(30, 40, 5, 15));
        sanityChecker.checkHSSFWorkbook(wb);
        wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
        
        s  = wb.getSheetAt(0);
        CellRangeAddress r1 = s.getMergedRegion(0);
        CellRangeAddress r2 = s.getMergedRegion(1);

        confirmRegion(new CellRangeAddress(0, 10, 0, 10), r1);
        confirmRegion(new CellRangeAddress(30, 40,5, 15), r2);
    }

    private static void confirmRegion(CellRangeAddress ra, CellRangeAddress rb) {
        assertEquals(ra.getFirstRow(), rb.getFirstRow());
        assertEquals(ra.getLastRow(), rb.getLastRow());
        assertEquals(ra.getFirstColumn(), rb.getFirstColumn());
        assertEquals(ra.getLastColumn(), rb.getLastColumn());
    }

    /**
     * Test the backup field gets set as expected.
     */
    public void testBackupRecord() {
        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());
    }

    private static final class RecordCounter implements RecordVisitor {
        private int _count;

        public RecordCounter() {
            _count=0;
        }
        public int getCount() {
            return _count;
        }
        public void visitRecord(Record r) {
            if (r instanceof LabelSSTRecord) {
                _count++;
            }
        }
    }
    
    /**
     * This tests is for bug [ #506658 ] Repeating output.
     *
     * We need to make sure only one LabelSSTRecord is produced.
     */
    public void testRepeatingBug() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet    sheet    = workbook.createSheet("Design Variants");
        HSSFRow      row      = sheet.createRow(2);
        HSSFCell     cell     = row.createCell(1);

        cell.setCellValue(new HSSFRichTextString("Class"));
        cell = row.createCell(2);

        RecordCounter rc = new RecordCounter();
        sheet.getSheet().visitContainedRecords(rc, 0);
        assertEquals(1, rc.getCount());
    }


    public void testManyRows() {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row;
        HSSFCell cell;
        int i, j;
        for ( i = 0, j = 32771; j > 0; i++, j-- )
        {
            row = sheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(i);
        }
        sanityChecker.checkHSSFWorkbook(workbook);
        assertEquals("LAST ROW == 32770", 32770, sheet.getLastRowNum());
        cell = sheet.getRow(32770).getCell(0);
        double lastVal = cell.getNumericCellValue();

        HSSFWorkbook    wb = HSSFTestDataSamples.writeOutAndReadBack(workbook);
        HSSFSheet       s    = wb.getSheetAt(0);
        row = s.getRow(32770);
        cell = row.getCell(0);
        assertEquals("Value from last row == 32770", lastVal, cell.getNumericCellValue(), 0);
        assertEquals("LAST ROW == 32770", 32770, s.getLastRowNum());
    }

    /**
     * Generate a file to visually/programmatically verify repeating rows and cols made it
     */
    public void testRepeatingColsRows() throws IOException
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Test Print Titles");

        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = row.createCell(1);
        cell.setCellValue(new HSSFRichTextString("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());
    }
}