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 
         );         
   */ 
//---------------------------------------------------用以上的基础知识我们就可以制作复杂的多表头，控制元/ 
//格样式的操作了，具体的代码考虑到公司资产，所以隐去。 
官方网站：http://jakarta.apache.org/poi/