testworkbook.java

Office格式转换代码

	assertEquals(format.getFormat(cell.getCellStyle().getDataFormat()), "0.0");
        stream.close();
    }

/**
     * TEST NAME:  Test Read/Write Simple w/ Data Format<P>
     * OBJECTIVE:  Test that HSSF can write a sheet with custom data formats and then read it and get the proper formats.<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions and format is correct<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testWriteDataFormat()
        throws IOException
    {
	File             file = File.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();
    }

    /**
     * TEST NAME:  Test Read Employee Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet (Employee.xls).<P>
     * SUCCESS:    HSSF reads the sheet.  Matches values in their particular positions.<P>
     * FAILURE:    HSSF does not read a sheet or excepts.  HSSF cannot identify values
     *             in the sheet in their known positions.<P>
     *
     */

    public void testReadEmployeeSimple()
        throws IOException
    {
        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();
    }

    /**
     * TEST NAME:  Test Modify Sheet Simple <P>
     * OBJECTIVE:  Test that HSSF can read a simple spreadsheet with a string value and replace
     *             it with another string value.<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.    <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 testModifySimple()
        throws IOException
    {
        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 = File.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();
    }

    /**
     * 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()
        throws IOException
    {
        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 =
            File.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();
    }

    /**
     * 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()
        throws IOException
    {
        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 =
            File.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();
    }

    /**
     * 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()
        throws IOException
    {
        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);