POI操作EXCEL技巧(转)

1.创建工作簿 (WORKBOOK)  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
2.创建工作表（SHEET）  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet1 = wb.createSheet("new sheet");  
 
    HSSFSheet sheet2 = wb.createSheet("second sheet");  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
3.创建单元格(CELL)  
 
    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)0);  
 
    // Create a cell and put a value in it.  
 
    HSSFCell cell = row.createCell((short)0);  
 
    cell.setCellValue(1);  
 
    // Or do it on one line.  
 
    row.createCell((short)1).setCellValue(1.2);  
 
    row.createCell((short)2).setCellValue("This is a string");  
 
    row.createCell((short)3).setCellValue(true);  
 
    // Write the output to a file  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
4.创建指定单元格式的单元格  
 
    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)0);  
 
    // Create a cell and put a date value in it.  The first cell is not styled  
 
    // as a date.  
 
    HSSFCell cell = row.createCell((short)0);  
 
    cell.setCellValue(new Date());  
 
    // we style the second cell as a date (and time).  It is important to  
 
    // create a new cell style from the workbook otherwise you can end up  
 
    // modifying the built in style and effecting not only this cell but other cells.  
 
    HSSFCellStyle cellStyle = wb.createCellStyle();  
 
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));  
 
    cell = row.createCell((short)1);  
 
    cell.setCellValue(new Date());  
 
    cell.setCellStyle(cellStyle);  
 
    // Write the output to a file  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
5. 单元格的不同格式  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("new sheet");  
 
    HSSFRow row = sheet.createRow((short)2);  
 
    row.createCell((short) 0).setCellValue(1.1);  
 
    row.createCell((short) 1).setCellValue(new Date());  
 
    row.createCell((short) 2).setCellValue("a string");  
 
    row.createCell((short) 3).setCellValue(true);  
 
    row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);  
 
    // Write the output to a file  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
6.单元格的不通对齐方式  
 
    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);  
 
    }  
 
7.单元格的边框设置  
 
Working with borders  
 
    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();  
 
8.填充和颜色设置  
 
    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();  
 
9.合并单元格操作  
 
    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();  
 
10.字体设置  
 
    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();  
 
11.自定义颜色  
 
    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();  
 
12.读和重写EXCEL文件  
 
    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();  
 
13.在EXCEL单元格中使用自动换行  
 
    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();  
 
14.数字格式自定义  
 
    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);  
 
    row = sheet.createRow(rowNum++);  
 
    cell = row.createCell(colNum);  
 
    cell.setCellValue(11111.25);  
 
    style = wb.createCellStyle();  
 
    style.setDataFormat(format.getFormat("#,##0.0000"));  
 
    cell.setCellStyle(style);  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
15.调整工作单位置  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("format sheet");  
 
    HSSFPrintSetup ps = sheet.getPrintSetup();  
 
    sheet.setAutobreaks(true);  
 
    ps.setFitHeight((short)1);  
 
    ps.setFitWidth((short)1);   
 
 
 
    // Create various cells and rows for spreadsheet.  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
16.设置打印区域  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("Sheet1");  
 
    wb.setPrintArea(0, "$A$1:$C$2");  
 
    //sets the print area for the first sheet  
 
    //Alternatively:  
 
    //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)  
 
    // Create various cells and rows for spreadsheet.  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
17.标注脚注  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("format sheet");  
 
    HSSFFooter footer = sheet.getFooter()  
 
    footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );  
 
   
 
    // Create various cells and rows for spreadsheet.  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
18.使用方便的内部提供的函数  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet1 = wb.createSheet( "new sheet" );  
 
    // Create a merged region  
 
    HSSFRow row = sheet1.createRow( (short) 1 );  
 
    HSSFRow row2 = sheet1.createRow( (short) 2 );  
 
    HSSFCell cell = row.createCell( (short) 1 );  
 
    cell.setCellValue( "This is a test of merging" );  
 
    Region region = new Region( 1, (short) 1, 4, (short) 4 );  
 
    sheet1.addMergedRegion( region );  
 
    // Set the border and border colors.  
 
    final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;  
 
    HSSFRegionUtil.setBorderBottom( borderMediumDashed,  
 
        region, sheet1, wb );  
 
    HSSFRegionUtil.setBorderTop( borderMediumDashed,  
 
        region, sheet1, wb );  
 
    HSSFRegionUtil.setBorderLeft( borderMediumDashed,  
 
        region, sheet1, wb );  
 
    HSSFRegionUtil.setBorderRight( borderMediumDashed,  
 
        region, sheet1, wb );  
 
    HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);  
 
    HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);  
 
    HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);  
 
    HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);  
 
    // Shows some usages of HSSFCellUtil  
 
    HSSFCellStyle style = wb.createCellStyle();  
 
    style.setIndention((short)4);  
 
    HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);  
 
    HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");  
 
    HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);  
 
    // Write out the workbook  
 
    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );  
 
    wb.write( fileOut );  
 
    fileOut.close();  
 
19.在工作单中移动行，调整行的上下位置  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("row sheet");  
 
    // Create various cells and rows for spreadsheet.  
 
    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)  
 
    sheet.shiftRows(5, 10, -5);  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
20.选种指定的工作单  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("row sheet");  
 
    sheet.setSelected(true);  
 
    // Create various cells and rows for spreadsheet.  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
21.工作单的放大缩小  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet1 = wb.createSheet("new sheet");  
 
    sheet1.setZoom(3,4);   // 75 percent magnification  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
22.头注和脚注  
 
    HSSFWorkbook wb = new HSSFWorkbook();  
 
    HSSFSheet sheet = wb.createSheet("new sheet");  
 
    HSSFHeader header = sheet.getHeader();  
 
    header.setCenter("Center Header");  
 
    header.setLeft("Left Header");  
 
    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +  
 
                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");  
 
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");  
 
    wb.write(fileOut);  
 
    fileOut.close();  
 
//-------------------------------以上实例代码均来自官方网站  
 
//-------------------------------POI中使用的颜色是用颜色索引来实现，如下：  
 
   /* 

    * 颜色对照表 数字代表颜色索引 

        8： BLACK 

        60： BROWN 

        59： OLIVE_GREEN 

        58： DARK_GREEN 

        56： DARK_TEAL 

        18： DARK_BLUE 

        32： DARK_BLUE 

        62： INDIGO 

        63： GREY_80_PERCENT 

        53： ORANGE 

        19： DARK_YELLOW 

        17： GREEN 

        21： TEAL 

        38： TEAL 

        12： BLUE 

        39： BLUE 

        54： BLUE_GREY 

        23： GREY_50_PERCENT 

        10： RED 

        52： LIGHT_ORANGE 

        50： LIME 

        57： SEA_GREEN 

        49： AQUA 

        48： LIGHT_BLUE 

        20： VIOLET 

        36： VIOLET 

        55： GREY_40_PERCENT 

        14： PINK 

        33： PINK 

        51： GOLD 

        13： YELLOW 

        34： YELLOW 

        11： BRIGHT_GREEN 

        35： BRIGHT_GREEN 

        15： TURQUOISE 

        35： TURQUOISE 

        16： DARK_RED 

        37： DARK_RED 

        40： SKY_BLUE 

        61： PLUM 

        25： PLUM 

        22： GREY_25_PERCENT 

        45： ROSE 

        43： LIGHT_YELLOW 

        42： LIGHT_GREEN 

        41： LIGHT_TURQUOISE 

        27：LIGHT_TURQUOISE 

        44： PALE_BLUE 

        46： LAVENDER 

        9： WHITE 

        24： CORNFLOWER_BLUE 

        26： LEMON_CHIFFON 

        25： MAROON 

        28： ORCHID 

        29： CORAL 

        30： ROYAL_BLUE 

        31： LIGHT_CORNFLOWER_BLUE 

    */ 
 
//----------------------------------------------------你可以按上面的方法来自定义颜色  
 
   /* 

    * 自定义颜色，去掉注释，贴加，其他则查看颜色对照表 

   HSSFPalette palette = this.getCustomPalette(); 

   palette.setColorAtIndex(idx, 

            i,  //RGB red (0-255) 

            j,    //RGB green 

            k     //RGB blue 

         );          

   */ 

1.创建工作簿 (WORKBOOK)

    HSSFWorkbook wb = new HSSFWorkbook();

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

2.创建工作表（SHEET）

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet("new sheet");

    HSSFSheet sheet2 = wb.createSheet("second sheet");

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

3.创建单元格(CELL)

    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)0);

    // Create a cell and put a value in it.

    HSSFCell cell = row.createCell((short)0);

    cell.setCellValue(1);

    // Or do it on one line.

    row.createCell((short)1).setCellValue(1.2);

    row.createCell((short)2).setCellValue("This is a string");

    row.createCell((short)3).setCellValue(true);

    // Write the output to a file

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

4.创建指定单元格式的单元格

    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)0);

    // Create a cell and put a date value in it.  The first cell is not styled

    // as a date.

    HSSFCell cell = row.createCell((short)0);

    cell.setCellValue(new Date());

    // we style the second cell as a date (and time).  It is important to

    // create a new cell style from the workbook otherwise you can end up

    // modifying the built in style and effecting not only this cell but other cells.

    HSSFCellStyle cellStyle = wb.createCellStyle();

    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    cell = row.createCell((short)1);

    cell.setCellValue(new Date());

    cell.setCellStyle(cellStyle);

    // Write the output to a file

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

5. 单元格的不同格式

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFRow row = sheet.createRow((short)2);

    row.createCell((short) 0).setCellValue(1.1);

    row.createCell((short) 1).setCellValue(new Date());

    row.createCell((short) 2).setCellValue("a string");

    row.createCell((short) 3).setCellValue(true);

    row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);

    // Write the output to a file

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

6.单元格的不通对齐方式

    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);

    }

7.单元格的边框设置

Working with borders

    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();

8.填充和颜色设置

    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();

9.合并单元格操作

    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();

10.字体设置

    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();

11.自定义颜色

    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();

12.读和重写EXCEL文件

    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();

13.在EXCEL单元格中使用自动换行

    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();

14.数字格式自定义

    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);

    row = sheet.createRow(rowNum++);

    cell = row.createCell(colNum);

    cell.setCellValue(11111.25);

    style = wb.createCellStyle();

    style.setDataFormat(format.getFormat("#,##0.0000"));

    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

15.调整工作单位置

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("format sheet");

    HSSFPrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    ps.setFitHeight((short)1);

    ps.setFitWidth((short)1);



    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

16.设置打印区域

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("Sheet1");

    wb.setPrintArea(0, "$A$1:$C$2");

    //sets the print area for the first sheet

    //Alternatively:

    //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

17.标注脚注

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("format sheet");

    HSSFFooter footer = sheet.getFooter()

    footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );



    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

18.使用方便的内部提供的函数

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet( "new sheet" );

    // Create a merged region

    HSSFRow row = sheet1.createRow( (short) 1 );

    HSSFRow row2 = sheet1.createRow( (short) 2 );

    HSSFCell cell = row.createCell( (short) 1 );

    cell.setCellValue( "This is a test of merging" );

    Region region = new Region( 1, (short) 1, 4, (short) 4 );

    sheet1.addMergedRegion( region );

    // Set the border and border colors.

    final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;

    HSSFRegionUtil.setBorderBottom( borderMediumDashed,

        region, sheet1, wb );

    HSSFRegionUtil.setBorderTop( borderMediumDashed,

        region, sheet1, wb );

    HSSFRegionUtil.setBorderLeft( borderMediumDashed,

        region, sheet1, wb );

    HSSFRegionUtil.setBorderRight( borderMediumDashed,

        region, sheet1, wb );

    HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

    HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

    HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

    HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);

    // Shows some usages of HSSFCellUtil

    HSSFCellStyle style = wb.createCellStyle();

    style.setIndention((short)4);

    HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);

    HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");

    HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);

    // Write out the workbook

    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );

    wb.write( fileOut );

    fileOut.close();

19.在工作单中移动行，调整行的上下位置

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("row sheet");

    // Create various cells and rows for spreadsheet.

    // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)

    sheet.shiftRows(5, 10, -5);

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

20.选种指定的工作单

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("row sheet");

    sheet.setSelected(true);

    // Create various cells and rows for spreadsheet.

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

21.工作单的放大缩小

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.setZoom(3,4);   // 75 percent magnification

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

22.头注和脚注

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet = wb.createSheet("new sheet");

    HSSFHeader header = sheet.getHeader();

    header.setCenter("Center Header");

    header.setLeft("Left Header");

    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +

                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");

    FileOutputStream fileOut = new FileOutputStream("workbook.xls");

    wb.write(fileOut);

    fileOut.close();

//-------------------------------以上实例代码均来自官方网站

//-------------------------------POI中使用的颜色是用颜色索引来实现，如下：

   /*

    * 颜色对照表 数字代表颜色索引

        8： BLACK

        60： BROWN

        59： OLIVE_GREEN

        58： DARK_GREEN

        56： DARK_TEAL

        18： DARK_BLUE

        32： DARK_BLUE

        62： INDIGO

        63： GREY_80_PERCENT

        53： ORANGE

        19： DARK_YELLOW

        17： GREEN

        21： TEAL

        38： TEAL

        12： BLUE

        39： BLUE

        54： BLUE_GREY

        23： GREY_50_PERCENT

        10： RED

        52： LIGHT_ORANGE

        50： LIME

        57： SEA_GREEN

        49： AQUA

        48： LIGHT_BLUE

        20： VIOLET

        36： VIOLET

        55： GREY_40_PERCENT

        14： PINK

        33： PINK

        51： GOLD

        13： YELLOW

        34： YELLOW

        11： BRIGHT_GREEN

        35： BRIGHT_GREEN

        15： TURQUOISE

        35： TURQUOISE

        16： DARK_RED

        37： DARK_RED

        40： SKY_BLUE

        61： PLUM

        25： PLUM

        22： GREY_25_PERCENT

        45： ROSE

        43： LIGHT_YELLOW

        42： LIGHT_GREEN

        41： LIGHT_TURQUOISE

        27：LIGHT_TURQUOISE

        44： PALE_BLUE

        46： LAVENDER

        9： WHITE

        24： CORNFLOWER_BLUE

        26： LEMON_CHIFFON

        25： MAROON

        28： ORCHID

        29： CORAL

        30： ROYAL_BLUE

        31： LIGHT_CORNFLOWER_BLUE

    */

//----------------------------------------------------你可以按上面的方法来自定义颜色

   /*

    * 自定义颜色，去掉注释，贴加，其他则查看颜色对照表

   HSSFPalette palette = this.getCustomPalette();

   palette.setColorAtIndex(idx,

            i,  //RGB red (0-255)

            j,    //RGB green

            k     //RGB blue

         );        

   */