busy developers' guide to hssf features.htm

Jakarta POI 是apache的子项目

    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=Alignment></A>
      <DIV class=h2>
      <H2>Demonstrates various alignment options</H2></DIV><PRE class=code>    public static void main(String[] args)
            throws IOException
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("new sheet");
        HSSFRow row = sheet.createRow((short) 2);
        createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
        createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
        createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
        createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
        createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
        createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
        createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();

    }

    /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb        the workbook
     * @param row       the row to create the cell in
     * @param column    the column number to create the cell in
     * @param align     the alignment for the cell.
     */
    private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
    {
        HSSFCell cell = row.createCell(column);
        cell.setCellValue("Align It");
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(align);
        cell.setCellStyle(cellStyle);
    }
                    </PRE><A name=Borders></A>
      <DIV class=h2>
      <H2>Working with borders</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);

    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue(4);

    // Style the cell with borders all around.
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBottomBorderColor(HSSFColor.BLACK.index);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setLeftBorderColor(HSSFColor.GREEN.index);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.BLUE.index);
    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(HSSFColor.BLACK.index);
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=Iterator></A>
      <DIV class=h2>
      <H2>Iterate over rows and cells (including Java 5 foreach 
loops)</H2></DIV>
      <P>Sometimes, you'd like to just iterate over all the rows in a sheet, or 
      all the cells in a row. If you are using Java 5 or later, then this is 
      especially handy, as it'll allow the new foreach loop support to work.</P>
      <P>Luckily, this is very easy. HSSFRow defines a <EM>CellIterator</EM> 
      inner class to handle iterating over the cells (get one with a call to 
      <EM>row.cellIterator()</EM>), and HSSFSheet provides a 
      <EM>rowIterator()</EM> method to give an iterator over all the rows.</P><PRE class=code>	HSSFSheet sheet = wb.getSheetAt(0);
	for (HSSFRow row : sheet.rowIterator()) {
		for (HSSFCell cell : row.cellIterator()) {
			// Do something here
		}
	}
				</PRE><A name=TextExtraction></A>
      <DIV class=h2>
      <H2>Text Extraction</H2></DIV>
      <P>For most text extraction requirements, the standard ExcelExtractor 
      class should provide all you need.</P><PRE class=code>	InputStream inp = new FileInputStream("workbook.xls");
	HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
	ExcelExtractor extractor = new ExcelExtractor(wb);

	extractor.setFormulasNotResults(true);
	extractor.setIncludeSheetNames(false);
	String text = extractor.getText();
					</PRE>
      <P>For very fancy text extraction, XLS to CSV etc, take a look at 
      <EM>/src/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java</EM> 
      </P><A name=FillsAndFrills></A>
      <DIV class=h2>
      <H2>Fills and colors</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);

    // Aqua background
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(HSSFColor.AQUA.index);
    style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("X");
    cell.setCellStyle(style);

    // Orange "foreground", foreground being the fill foreground not the font color.
    style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.ORANGE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cell = row.createCell((short) 2);
    cell.setCellValue("X");
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=MergedCells></A>
      <DIV class=h2>
      <H2>Merging cells</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFRow row = sheet.createRow((short) 1);
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of merging");

    sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=WorkingWithFonts></A>
      <DIV class=h2>
      <H2>Working with fonts</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow((short) 1);

    // Create a new font and alter it.
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short)24);
    font.setFontName("Courier New");
    font.setItalic(true);
    font.setStrikeout(true);

    // Fonts are set into a style so create a new one to use.
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    // Create a cell and put a value in it.
    HSSFCell cell = row.createCell((short) 1);
    cell.setCellValue("This is a test of fonts");
    cell.setCellStyle(style);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
  </PRE>
      <P>Note, the maximum number of unique fonts in a workbook is limited to 
      32767 ( the maximum positive short). You should re-use fonts in your 
      apllications instead of creating a font for each cell. Examples: </P>
      <P><STRONG>Wrong:</STRONG> </P><PRE class=code>        for (int i = 0; i &lt; 10000; i++) {
            HSSFRow row = sheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);

            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
            cell.setCellStyle(style);
        }
</PRE>
      <P><STRONG>Correct:</STRONG> </P><PRE class=code>
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        for (int i = 0; i &lt; 10000; i++) {
            HSSFRow row = sheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellStyle(style);
        }
</PRE><A name=CustomColors></A>
      <DIV class=h2>
      <H2>Custom colors</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFRow row = sheet.createRow((short) 0);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("Default Palette");

    //apply some colors from the standard palette,
    // as in the previous examples.
    //we'll use red text on a lime background

    HSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(HSSFColor.LIME.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);

    cell.setCellStyle(style);

    //save with the default palette
    FileOutputStream out = new FileOutputStream("default_palette.xls");
    wb.write(out);
    out.close();

    //now, let's replace RED and LIME in the palette
    // with a more attractive combination
    // (lovingly borrowed from freebsd.org)

    cell.setCellValue("Modified Palette");

    //creating a custom palette for the workbook
    HSSFPalette palette = wb.getCustomPalette();

    //replacing the standard red with freebsd.org red
    palette.setColorAtIndex(HSSFColor.RED.index,
            (byte) 153,  //RGB red (0-255)
            (byte) 0,    //RGB green
            (byte) 0     //RGB blue
    );
    //replacing lime with freebsd.org gold
    palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);

    //save with the modified palette
    // note that wherever we have previously used RED or LIME, the
    // new colors magically appear
    out = new FileOutputStream("modified_palette.xls");
    wb.write(out);
    out.close();
                    </PRE><A name=ReadWriteWorkbook></A>
      <DIV class=h2>
      <H2>Reading and Rewriting Workbooks</H2></DIV><PRE class=code>    POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream("workbook.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row = sheet.getRow(2);
    HSSFCell cell = row.getCell((short)3);
    if (cell == null)
        cell = row.createCell((short)3);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
                    </PRE><A name=NewLinesInCells></A>
      <DIV class=h2>
      <H2>Using newlines in cells</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    cs = wb.createCellStyle();

    cs.setFont( f2 );
    //Word Wrap MUST be turned on
    cs.setWrapText( true );

    r = s.createRow( (short) 2 );
    r.setHeight( (short) 0x349 );
    c = r.createCell( (short) 2 );
    c.setCellType( HSSFCell.CELL_TYPE_STRING );
    c.setCellValue( "Use \n with word wrap on to create a new line" );
    c.setCellStyle( cs );
    s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );

    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
    wb.write( fileOut );
    fileOut.close();</PRE><A name=DataFormats></A>
      <DIV class=h2>
      <H2>Data Formats</H2></DIV><PRE class=code>    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("format sheet");
    HSSFCellStyle style;
    HSSFDataFormat format = wb.createDataFormat();
    HSSFRow row;
    HSSFCell cell;
    short rowNum = 0;
    short colNum = 0;

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0"));
    cell.setCellStyle(style);