POI 帮助文档

jianghejyjy1314

贡献于2013-02-28

字数:13848 关键词: Office文档处理

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 ar e 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 ce ll = 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);     FileOutpu tStream 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          );            */ //---------------------------------------------------用以上的基础知识我们就可以制作复杂的多表头,控制元/ //格样式的操作了,具体的代码考虑到公司资产,所以隐去。

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享文档获得金币 ]
0 人已下载

下载文档

相关文档