1 import com.google.common.collect.Maps;
2 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
3 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
4 import org.apache.poi.ss.usermodel.*;
5
6 import java.io.File;
7 import java.io.FileOutputStream;
8 import java.io.OutputStream;
9 import java.util.ArrayList;
10 import java.util.List;
11 import java.util.Map;
12
13 public class programTestmmm {
14 static final float PIXEL = 30.2f;
15 static Map<Byte, Short> headerWidth = Maps.newHashMap();
16 static {
17 headerWidth.put((byte)0, (short)(PIXEL * 50));
18 headerWidth.put((byte)1, (short)(PIXEL * 50));
19 headerWidth.put((byte)2, (short)(PIXEL * 50));
20 headerWidth.put((byte)3, (short)(PIXEL * 50));
21 headerWidth.put((byte)4, (short)(PIXEL * 150));
22 headerWidth.put((byte)5, (short)(PIXEL * 150));
23 headerWidth.put((byte)6, (short)(PIXEL * 130));
24 headerWidth.put((byte)7, (short)(PIXEL * 130));
25 headerWidth.put((byte)8, (short)(PIXEL * 130));
26 }
27
28 public static void createHeader4Excel(Workbook wb, Sheet sheet, String number) {
29 Row row = sheet.createRow((short)0);
30 row.setHeight((short)512);
31 String numStr = "newline\r\n（II：" + number + "）";
32 String[] headers = { "序号", "AA", "BB", "CC", "DD", numStr, "EE", "FF", "GG" };
33 createHeader(wb, sheet, row, headers);
34
35 for( int i = 0; i < headers.length; i++ )
36 sheet.setColumnWidth((short)i, (short) headerWidth.get(Byte.valueOf((byte) i)));
37 }
38
39 private static void createHeader(Workbook wb, Sheet sheet, Row row, String[] headers) {
40 for( int i = 0; i < headers.length; i++ ){
41 Cell cell = row.createCell(i);
42 CellStyle cellStyle = wb.createCellStyle();
43 cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//左右居中
44 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
45 cellStyle.setWrapText(true);//先设置为自动换行
46
47 //设置边框
48 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
49 cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
50 cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
51 cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框
52
53 //背景色
54 cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
55 cellStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.index);
56 cellStyle.setFillPattern(CellStyle.FINE_DOTS);
57
58 cell.setCellStyle(cellStyle);
59 cell.setCellValue(headers[i]);
60
61 if(i == 5)
62 cell.setCellValue(new HSSFRichTextString(headers[i]));
63 }
64 }
65
66 private static void fillRecords(Workbook wb, Sheet sheet, List<String> records) {
67 for( int i = 0; i < records.size(); i++ ){
68 Row row = sheet.createRow((short)i+1);
69
70 setCellValue( wb, row.createCell(0), records.get(i));
71 setCellValue( wb, row.createCell(1), records.get(i) );
72 setCellValue( wb, row.createCell(2), records.get(i) );
73 setCellValue( wb, row.createCell(3), records.get(i));
74 setCellValue( wb, row.createCell(4), records.get(i) );
75 setCellValue( wb, row.createCell(5), records.get(i) );
76 }
77 }
78
79 // Excel最多支持4000个style
80 private static void setCellValue( Workbook wb, Cell cell, String value ){
81 //CellStyle cellStyle = wb.createCellStyle();
82 //cell.setCellStyle(cellStyle);
83 cell.setCellValue(value);
84 cell.setCellType(Cell.CELL_TYPE_STRING);
85 }
86
87 public static void main(String[] args) {
88 File file = new File("C://Documents//createExcel.xls");
89 OutputStream os = null;
90 try {
91 os = new FileOutputStream(file, true);
92 Workbook wb = new HSSFWorkbook();
93 Sheet sheet = wb.createSheet();
94
95 //合并单元格，参数说明：1：开始行 2：结束行 3：开始列 4：结束列
96 // sheet.addMergedRegion(new CellRangeAddress(0,0,2,3));
97
98 createHeader4Excel( wb, sheet, "150" );
99
100 List<String> records = new ArrayList<String>();
101 records.add("11");
102 records.add("22");
103 records.add("33");
104 records.add("44");
105 records.add("55");
106 records.add("66");
107 fillRecords( wb, sheet, records );
108 wb.setSheetName(0, "first sheet");//设置sheet名字
109 wb.write(os);
110 } catch (Exception e) {
111 e.printStackTrace();
112 }
113 }
114 }
