用POI实现双层标题excel的打印

写了一个方法，注解都在代码里

[java]  view plain  copy

1. /** 带分类标题导出Excel的方法 
2.       *  
3.       * @param title 
4.       *            excel中的sheet名称 
5.       * @param header_2 
6.       *            两列的头的标题 
7.       * @param header_cate 
8.       *            分类列 
9.       * @param cate_num 
10.       *            分类列行数 
11.       * @param header_1 
12.       *            分类列行数 
13.       * @param columns 
14.       *                列名 
15.       * @param result 
16.       *            结果集 
17.       * @param out 
18.       *            输出流 
19.       * @param pattern 
20.       *            时间格式 
21.       */   
22.     @SuppressWarnings("deprecation")   
23.     public void exportoExcel1(String title, String[] header_2, String[] header_cate, int[] cate_num, String[] header_1, String[] columns, Collection<T> result, OutputStream out,  
24.             String pattern) throws Exception {  
25.   
26.         // 声明一个工作薄  
27.         HSSFWorkbook workbook = new HSSFWorkbook();  
28.         // 生成一个表格  
29.         HSSFSheet sheet = workbook.createSheet(title);  
30.         // 设置表格默认列宽度为20个字节  
31.         sheet.setDefaultColumnWidth((short) 20);  
32.   
33.         // 生成一个样式  
34.         HSSFCellStyle style = workbook.createCellStyle();  
35.         // 设置单元格背景色，设置单元格背景色以下两句必须同时设置  
36.         style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置填充色  
37.         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式     
38.   
39.          // 设置单元格上、下、左、右的边框线  
40.         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
41.         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
42.         style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
43.         style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
44.         // 设置居中  
45.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
46.         // 生成一个字体  
47.         HSSFFont font = workbook.createFont();  
48.         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
49.         // 把字体应用到当前的样式  
50.         style.setFont(font);  
51.   
52.         // 指定当单元格内容显示不下时自动换行  
53.         style.setWrapText(true);  
54.   
55.         // 声明一个画图的顶级管理器  
56.         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
57.   
58.         // 以下可以用于设置导出的数据的样式  
59.         // 产生表格标题行  
60.         // 表头的样式  
61.         HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式对象  
62.         titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中  
63.         titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中  
64.         // 设置字体  
65.         HSSFFont titleFont = workbook.createFont(); // 创建字体对象  
66.         titleFont.setFontHeightInPoints((short) 15); // 设置字体大小  
67.         titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体  
68.         // titleFont.setFontName("黑体"); // 设置为黑体字  
69.         titleStyle.setFont(titleFont);  
70.         //第一行  
71.         CellRangeAddress cra=new CellRangeAddress(0, (short) 0, 0, (short) (columns.length - 1));//参数:起始行号，终止行号， 起始列号，终止列号  
72.         sheet.addMergedRegion(cra);  
73.           
74.         Row row = sheet.createRow(0);  
75.         Cell row0=row.createCell(0);  
76.         row0.setCellValue(title);  
77.         row0.setCellStyle(titleStyle);  
78.           
79.         for(int i=0; i<header_2.length; i++){  
80.             cra=new CellRangeAddress(1, 2, i, i);  
81.             sheet.addMergedRegion(cra);  
82.         }  
83.         int sum1 = 0;  
84.         int sum2 = 0;  
85.         for(int i=0; i<header_cate.length; i++){  
86.             sum1 += cate_num[i];  
87.             cra=new CellRangeAddress(1, 1, 1+sum2, sum1);   //  
88.             sheet.addMergedRegion(cra);  
89.             sum2 += cate_num[i];  
90.         }  
91.           
92.         row = sheet.createRow(1);  
93.           
94.         for(int i=0; i<header_2.length; i++){  
95.             final Cell cell = row.createCell(i);  
96.             cell.setCellStyle(style);     
97.             cell.setCellValue(header_2[i]);  
98.         }  
99.           
100.         int sum = 0;  
101.         for(int i=0; i<header_cate.length; i++){  
102.             final Cell cell = row.createCell(1+sum);    //  
103.             cell.setCellStyle(style);  
104.             cell.setCellValue(header_cate[i]);  
105.             sum += cate_num[i];  
106.         }  
107.         row = sheet.createRow(2);  
108.         for(int i=0; i<header_1.length; i++){  
109.             final Cell cell = row.createCell(i+1);  //  
110.             cell.setCellStyle(style);  
111.             cell.setCellValue(header_1[i]);  
112.         }  
113.           
114.         // 遍历集合数据，产生数据行  
115.         if (result != null) {  
116.             int index = 3;  
117.             for (T t : result) {  
118.                 row = sheet.createRow(index);  
119.                 index++;  
120.                 for (short i = 0; i < columns.length; i++) {  
121.                     Cell cell = row.createCell(i);  
122.                     String fieldName = columns[i];  
123.                     String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
124.                     Class tCls = t.getClass();  
125.                     Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
126.                     Object value = getMethod.invoke(t, new Class[] {});  
127.                     String textValue = null;  
128.                     if (value == null) {  
129.                         textValue = "";  
130.                     } else if (value instanceof Date) {  
131.                         Date date = (Date) value;  
132.                         SimpleDateFormat sdf = new SimpleDateFormat(pattern);  
133.                         textValue = sdf.format(date);  
134.                     } else if (value instanceof byte[]) {  
135.                         // 有图片时，设置行高为60px;  
136.                         row.setHeightInPoints(60);  
137.                         // 设置图片所在列宽度为80px,注意这里单位的一个换算  
138.                         sheet.setColumnWidth(i, (short) (35.7 * 80));  
139.                         // sheet.autoSizeColumn(i);  
140.                         byte[] bsValue = (byte[]) value;  
141.                         HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,  
142.                                 index);  
143.                         anchor.setAnchorType(2);  
144.                         patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));  
145.                     } else {  
146.                         // 其它数据类型都当作字符串简单处理  
147.                         textValue = value.toString();  
148.                     }  
149.                     if (textValue != null) {  
150.                         Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
151.                         Matcher matcher = p.matcher(textValue);  
152.                         if (matcher.matches()) {  
153.                             // 是数字当作double处理  
154.                             cell.setCellValue(Double.parseDouble(textValue));  
155.                         } else {  
156.                             HSSFRichTextString richString = new HSSFRichTextString(textValue);  
157.                             cell.setCellValue(richString);  
158.                         }  
159.                     }  
160.                 }  
161.             }  
162.         }  
163.         workbook.write(out);  
164.         out.flush();  
165.         out.close();  
166.     }  

实现方法

[java]  view plain  copy

1. @RequestMapping(InterfaceConsts.EXPORT_MONTHTESTRSTLIST)  
2.     @ResponseBody  
3.     public void export_MonthTestrstList(HttpServletRequest request, WqTestrstDForm form,  
4.             String jsonpCallback, HttpServletResponse response) throws Exception{  
5.         Map<String, Object> params = new HashMap<String, Object>();  
6.         params = MapUtil.toMap(form);  
7.           
8.         response.setContentType("application/vnd.ms-excel;charset=utf-8");  
9.         OutputStream out = response.getOutputStream();  
10.         SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");  
11.         String title = sdf.format(new Date(form.getTimeStart().replaceAll("-", "/")))+wqPrjBService.getWqPrjBBySmid(Integer.valueOf(form.getSmid())).getPrjnm()+"水质监测月报表";  
12.           
13.         String[] header_2={"检测项目"};  
14.         int[] cate_num={5,5,4};  
15.         String[] header_cate={"源水","出厂水","末梢水"};  
16.         String[] headers = { "原水检验次数", "原水最大值", "原水最小值", "原水平均值", "出厂水检验次数", "出厂水最大值", "出厂水最小值", "出厂水平均值", "出厂水标准值", "出厂水检验次数", "管网末梢水最大值", "管网末梢水最小值", "管网末梢水平均值", "管网末梢水标准值"};  
17.         String[] columns = { "itmnm", "count01", "tstv01Max", "tstv01Min", "tstv01Avg", "count02", "tstv02Max", "tstv02Min", "tstv02Avg", "ostnv", "count03", "tstv03Max", "tstv03Min", "tstv03Avg", "estnv"};  
18.         List<WqTestrstDMonth> houseList = wqTestrstDService.getAllMonthTestrstList(params);//我的数据源  
19.         ExcelUitl<WqTestrstDMonth> excelUtil = new ExcelUitl<WqTestrstDMonth>();  
20.         String pattern = "yyyy-MM-dd HH:mm:dd";  
21.         response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xls").getBytes(), "iso-8859-1"));  
22.         excelUtil.exportoExcel1(title, header_2, header_cate, cate_num, headers, columns, houseList, out, pattern);  
23.     }  

达成效果