1.创建工作簿 (WORKBOOK)
1HSSFWorkbook wb = new HSSFWorkbook(); 2 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 3 wb.write(fileOut); 4 fileOut.close();
2.创建工作表（SHEET）
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet1 = wb.createSheet("new sheet"); 3 HSSFSheet sheet2 = wb.createSheet("second sheet"); 4 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 5 wb.write(fileOut); 6 fileOut.close();
3.创建单元格(CELL)
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 // Create a row and put some cells in it. Rows are 0 based. 4 HSSFRow row = sheet.createRow((short)0); 5 // Create a cell and put a value in it. 6 HSSFCell cell = row.createCell((short)0); 7 cell.setCellValue(1); 8 // Or do it on one line. 9 row.createCell((short)1).setCellValue(1.2); 10 row.createCell((short)2).setCellValue("This is a string"); 11 row.createCell((short)3).setCellValue(true); 12 // Write the output to a file 13 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 14 wb.write(fileOut); 15 fileOut.close();
4.创建指定单元格式的单元格
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 // Create a row and put some cells in it. Rows are 0 based. 4 HSSFRow row = sheet.createRow((short)0); 5 // Create a cell and put a date value in it. The first cell is not styled 6 // as a date. 7 HSSFCell cell = row.createCell((short)0); 8 cell.setCellValue(new Date()); 9 // we style the second cell as a date (and time). It is important to 10 // create a new cell style from the workbook otherwise you can end up 11 // modifying the built in style and effecting not only this cell but other cells. 12 HSSFCellStyle cellStyle = wb.createCellStyle(); 13 cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 14 cell = row.createCell((short)1); 15 cell.setCellValue(new Date()); 16 cell.setCellStyle(cellStyle); 17 // Write the output to a file 18 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 19 wb.write(fileOut); 20 fileOut.close();
5. 单元格的不同格式
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 HSSFRow row = sheet.createRow((short)2); 4 row.createCell((short) 0).setCellValue(1.1); 5 row.createCell((short) 1).setCellValue(new Date()); 6 row.createCell((short) 2).setCellValue("a string"); 7 row.createCell((short) 3).setCellValue(true); 8 row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR); 9 // Write the output to a file 10 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 11 wb.write(fileOut); 12 fileOut.close();
6.单元格的不通对齐方式
1public static void main(String[] args) 2 throws IOException 3 { 4 HSSFWorkbook wb = new HSSFWorkbook(); 5 HSSFSheet sheet = wb.createSheet("new sheet"); 6 HSSFRow row = sheet.createRow((short) 2); 7 createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER); 8 createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION); 9 createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL); 10 createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL); 11 createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY); 12 createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT); 13 createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT); 14 // Write the output to a file 15 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 16 wb.write(fileOut); 17 fileOut.close(); 18 } 19 /** 20 * Creates a cell and aligns it a certain way. 21 * 22 * @param wb the workbook 23 * @param row the row to create the cell in 24 * @param column the column number to create the cell in 25 * @param align the alignment for the cell. 26 */ 27 private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) 28 { 29 HSSFCell cell = row.createCell(column); 30 cell.setCellValue("Align It"); 31 HSSFCellStyle cellStyle = wb.createCellStyle(); 32 cellStyle.setAlignment(align); 33 cell.setCellStyle(cellStyle); 34 }
**7.单元格的边框设置
**
1Working with borders 2 HSSFWorkbook wb = new HSSFWorkbook(); 3 HSSFSheet sheet = wb.createSheet("new sheet"); 4 // Create a row and put some cells in it. Rows are 0 based. 5 HSSFRow row = sheet.createRow((short) 1); 6 // Create a cell and put a value in it. 7 HSSFCell cell = row.createCell((short) 1); 8 cell.setCellValue(4); 9 // Style the cell with borders all around. 10 HSSFCellStyle style = wb.createCellStyle(); 11 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 12 style.setBottomBorderColor(HSSFColor.BLACK.index); 13 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 14 style.setLeftBorderColor(HSSFColor.GREEN.index); 15 style.setBorderRight(HSSFCellStyle.BORDER_THIN); 16 style.setRightBorderColor(HSSFColor.BLUE.index); 17 style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED); 18 style.setTopBorderColor(HSSFColor.BLACK.index); 19 cell.setCellStyle(style); 20 // Write the output to a file 21 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 22 wb.write(fileOut); 23 fileOut.close();
8.填充和颜色设置
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 // Create a row and put some cells in it. Rows are 0 based. 4 HSSFRow row = sheet.createRow((short) 1); 5 // Aqua background 6 HSSFCellStyle style = wb.createCellStyle(); 7 style.setFillBackgroundColor(HSSFColor.AQUA.index); 8 style.setFillPattern(HSSFCellStyle.BIG_SPOTS); 9 HSSFCell cell = row.createCell((short) 1); 10 cell.setCellValue("X"); 11 cell.setCellStyle(style); 12 // Orange "foreground", foreground being the fill foreground not the font color. 13 style = wb.createCellStyle(); 14 style.setFillForegroundColor(HSSFColor.ORANGE.index); 15 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 16 cell = row.createCell((short) 2); 17 cell.setCellValue("X"); 18 cell.setCellStyle(style); 19 // Write the output to a file 20 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 21 wb.write(fileOut); 22 fileOut.close();
9.合并单元格操作
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 HSSFRow row = sheet.createRow((short) 1); 4 HSSFCell cell = row.createCell((short) 1); 5 cell.setCellValue("This is a test of merging"); 6 sheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); 7 // Write the output to a file 8 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 9 wb.write(fileOut); 10 fileOut.close();
10.字体设置
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 // Create a row and put some cells in it. Rows are 0 based. 4 HSSFRow row = sheet.createRow((short) 1); 5 // Create a new font and alter it. 6 HSSFFont font = wb.createFont(); 7 font.setFontHeightInPoints((short)24); 8 font.setFontName("Courier New"); 9 font.setItalic(true); 10 font.setStrikeout(true); 11 // Fonts are set into a style so create a new one to use. 12 HSSFCellStyle style = wb.createCellStyle(); 13 style.setFont(font); 14 // Create a cell and put a value in it. 15 HSSFCell cell = row.createCell((short) 1); 16 cell.setCellValue("This is a test of fonts"); 17 cell.setCellStyle(style); 18 // Write the output to a file 19 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 20 wb.write(fileOut); 21 fileOut.close();
11.自定义颜色
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet(); 3 HSSFRow row = sheet.createRow((short) 0); 4 HSSFCell cell = row.createCell((short) 0); 5 cell.setCellValue("Default Palette"); 6 //apply some colors from the standard palette, 7 // as in the previous examples. 8 //we'll use red text on a lime background 9 HSSFCellStyle style = wb.createCellStyle(); 10 style.setFillForegroundColor(HSSFColor.LIME.index); 11 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 12 HSSFFont font = wb.createFont(); 13 font.setColor(HSSFColor.RED.index); 14 style.setFont(font); 15 cell.setCellStyle(style); 16 //save with the default palette 17 FileOutputStream out = new FileOutputStream("default_palette.xls"); 18 wb.write(out); 19 out.close(); 20 //now, let's replace RED and LIME in the palette 21 // with a more attractive combination 22 // (lovingly borrowed from freebsd.org) 23 cell.setCellValue("Modified Palette"); 24 //creating a custom palette for the workbook 25 HSSFPalette palette = wb.getCustomPalette(); 26 //replacing the standard red with freebsd.org red 27 palette.setColorAtIndex(HSSFColor.RED.index, 28 (byte) 153, //RGB red (0-255) 29 (byte) 0, //RGB green 30 (byte) 0 //RGB blue 31 ); 32 //replacing lime with freebsd.org gold 33 palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); 34 //save with the modified palette 35 // note that wherever we have previously used RED or LIME, the 36 // new colors magically appear 37 out = new FileOutputStream("modified_palette.xls"); 38 wb.write(out); 39 out.close();
12.读和重写EXCEL文件
1POIFSFileSystem fs = 2 new POIFSFileSystem(new FileInputStream("workbook.xls")); 3 HSSFWorkbook wb = new HSSFWorkbook(fs); 4 HSSFSheet sheet = wb.getSheetAt(0); 5 HSSFRow row = sheet.getRow(2); 6 HSSFCell cell = row.getCell((short)3); 7 if (cell == null) 8 cell = row.createCell((short)3); 9 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 10 cell.setCellValue("a test"); 11 // Write the output to a file 12 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 13 wb.write(fileOut); 14 fileOut.close();
13.在EXCEL单元格中使用自动换行
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet s = wb.createSheet(); 3 HSSFRow r = null; 4 HSSFCell c = null; 5 HSSFCellStyle cs = wb.createCellStyle(); 6 HSSFFont f = wb.createFont(); 7 HSSFFont f2 = wb.createFont(); 8 cs = wb.createCellStyle(); 9 cs.setFont( f2 ); 10 //Word Wrap MUST be turned on 11 cs.setWrapText( true ); 12 r = s.createRow( (short) 2 ); 13 r.setHeight( (short) 0x349 ); 14 c = r.createCell( (short) 2 ); 15 c.setCellType( HSSFCell.CELL_TYPE_STRING ); 16 c.setCellValue( "Use /n with word wrap on to create a new line" ); 17 c.setCellStyle( cs ); 18 s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) ); 19 FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); 20 wb.write( fileOut ); 21 fileOut.close();
14.数字格式自定义
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("format sheet"); 3 HSSFCellStyle style; 4 HSSFDataFormat format = wb.createDataFormat(); 5 HSSFRow row; 6 HSSFCell cell; 7 short rowNum = 0; 8 short colNum = 0; 9 row = sheet.createRow(rowNum++); 10 cell = row.createCell(colNum); 11 cell.setCellValue(11111.25); 12 style = wb.createCellStyle(); 13 style.setDataFormat(format.getFormat("0.0")); 14 cell.setCellStyle(style); 15 row = sheet.createRow(rowNum++); 16 cell = row.createCell(colNum); 17 cell.setCellValue(11111.25); 18 style = wb.createCellStyle(); 19 style.setDataFormat(format.getFormat("#,##0.0000")); 20 cell.setCellStyle(style); 21 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 22 wb.write(fileOut); 23 fileOut.close();
15.调整工作单位置
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("format sheet"); 3 HSSFPrintSetup ps = sheet.getPrintSetup(); 4 sheet.setAutobreaks(true); 5 ps.setFitHeight((short)1); 6 ps.setFitWidth((short)1); 7 // Create various cells and rows for spreadsheet. 8 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 9 wb.write(fileOut); 10 fileOut.close();
16.设置打印区域
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("Sheet1"); 3 wb.setPrintArea(0, "$A$1:$C$2"); 4 //sets the print area for the first sheet 5 //Alternatively: 6 //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details) 7 // Create various cells and rows for spreadsheet. 8 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 9 wb.write(fileOut); 10 fileOut.close();
17.标注脚注
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("format sheet"); 3 HSSFFooter footer = sheet.getFooter() 4 footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() ); 5 6 // Create various cells and rows for spreadsheet. 7 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 8 wb.write(fileOut); 9 fileOut.close();
18.使用方便的内部提供的函数
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet1 = wb.createSheet( "new sheet" ); 3 // Create a merged region 4 HSSFRow row = sheet1.createRow( (short) 1 ); 5 HSSFRow row2 = sheet1.createRow( (short) 2 ); 6 HSSFCell cell = row.createCell( (short) 1 ); 7 cell.setCellValue( "This is a test of merging" ); 8 Region region = new Region( 1, (short) 1, 4, (short) 4 ); 9 sheet1.addMergedRegion( region ); 10 // Set the border and border colors. 11 final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED; 12 HSSFRegionUtil.setBorderBottom( borderMediumDashed, 13 region, sheet1, wb ); 14 HSSFRegionUtil.setBorderTop( borderMediumDashed, 15 region, sheet1, wb ); 16 HSSFRegionUtil.setBorderLeft( borderMediumDashed, 17 region, sheet1, wb ); 18 HSSFRegionUtil.setBorderRight( borderMediumDashed, 19 region, sheet1, wb ); 20 HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 21 HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 22 HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 23 HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 24 // Shows some usages of HSSFCellUtil 25 HSSFCellStyle style = wb.createCellStyle(); 26 style.setIndention((short)4); 27 HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style); 28 HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell"); 29 HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER); 30 // Write out the workbook 31 FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); 32 wb.write( fileOut ); 33 fileOut.close();
19.在工作单中移动行，调整行的上下位置
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("row sheet"); 3 // Create various cells and rows for spreadsheet. 4 // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) 5 sheet.shiftRows(5, 10, -5); 6 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 7 wb.write(fileOut); 8 fileOut.close();
20.选种指定的工作单
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("row sheet"); 3 sheet.setSelected(true); 4 // Create various cells and rows for spreadsheet. 5 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 6 wb.write(fileOut); 7 fileOut.close();
21.工作单的放大缩小
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet1 = wb.createSheet("new sheet"); 3 sheet1.setZoom(3,4); // 75 percent magnification 4 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 5 wb.write(fileOut); 6 fileOut.close();
22.头注和脚注
1HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("new sheet"); 3 HSSFHeader header = sheet.getHeader(); 4 header.setCenter("Center Header"); 5 header.setLeft("Left Header"); 6 header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + 7 HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); 8 FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 9 wb.write(fileOut); 10 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
);
*/