用POI的HSSF来控制EXCEL的研究
1.创建工作簿 (WORKBOOK)
1 HSSFWorkbook wb = new HSSFWorkbook();
2 FileOutputStream fileOut = new FileOutputStream("workbook.xls");
3 wb.write(fileOut);
4 fileOut.close();
2.创建工作表（SHEET）
1 HSSFWorkbook 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)
1 HSSFWorkbook 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.创建指定单元格式的单元格
1 HSSFWorkbook 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. 单元格的不同格式
1 HSSFWorkbook 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.单元格的不通对齐方式
1 public 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.单元格的边框设置
1 Working 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.填充和颜色设置
1 HSSFWorkbook 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.合并单元格操作
1 HSSFWorkbook 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.字体设置
1 HSSFWorkbook 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.自定义颜色
1 HSSFWorkbook 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文件
1 POIFSFileSystem 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单元格中使用自动换行
1 HSSFWorkbook 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.数字格式自定义
1 HSSFWorkbook 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.调整工作单位置
1 HSSFWorkbook 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.设置打印区域
1 HSSFWorkbook 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.标注脚注
1 HSSFWorkbook 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.使用方便的内部提供的函数
1 HSSFWorkbook 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.在工作单中移动行，调整行的上下位置
1 HSSFWorkbook 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.选种指定的工作单
1 HSSFWorkbook 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.工作单的放大缩小
1 HSSFWorkbook 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.头注和脚注
1 HSSFWorkbook 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();
11 //-------------------------------以上实例代码均来自官方网站
12 //-------------------------------POI中使用的颜色是用颜色索引来实现，如下：
13 /*
14 * 颜色对照表 数字代表颜色索引
15 8： BLACK
16 60： BROWN
17 59： OLIVE_GREEN
18 58： DARK_GREEN
19 56： DARK_TEAL
20 18： DARK_BLUE
21 32： DARK_BLUE
22 62： INDIGO
23 63： GREY_80_PERCENT
24 53： ORANGE
25 19： DARK_YELLOW
26 17： GREEN
27 21： TEAL
28 38： TEAL
29 12： BLUE
30 39： BLUE
31 54： BLUE_GREY
32 23： GREY_50_PERCENT
33 10： RED
34 52： LIGHT_ORANGE
35 50： LIME
36 57： SEA_GREEN
37 49： AQUA
38 48： LIGHT_BLUE
39 20： VIOLET
40 36： VIOLET
41 55： GREY_40_PERCENT
42 14： PINK
43 33： PINK
44 51： GOLD
45 13： YELLOW
46 34： YELLOW
47 11： BRIGHT_GREEN
48 35： BRIGHT_GREEN
49 15： TURQUOISE
50 35： TURQUOISE
51 16： DARK_RED
52 37： DARK_RED
53 40： SKY_BLUE
54 61： PLUM
55 25： PLUM
56 22： GREY_25_PERCENT
57 45： ROSE
58 43： LIGHT_YELLOW
59 42： LIGHT_GREEN
60 41： LIGHT_TURQUOISE
61 27：LIGHT_TURQUOISE
62 44： PALE_BLUE
63 46： LAVENDER
64 9： WHITE
65 24： CORNFLOWER_BLUE
66 26： LEMON_CHIFFON
67 25： MAROON
68 28： ORCHID
69 29： CORAL
70 30： ROYAL_BLUE
71 31： LIGHT_CORNFLOWER_BLUE
72 */
73 //----------------------------------------------------你可以按上面的方法来自定义颜色
74 /*
75 * 自定义颜色，去掉注释，贴加，其他则查看颜色对照表
76 HSSFPalette palette = this.getCustomPalette();
77 palette.setColorAtIndex(idx,
78 i, //RGB red (0-255)
79 j, //RGB green
80 k //RGB blue
81 );
82 */
83 //---------------------------------------------------用以上的基础知识我们就可以制作复杂的多表头，控制元/
84 //格样式的操作了，具体的代码考虑到公司资产，所以隐去。
官方网站：http://jakarta.apache.org/poi/
