上个文章已说明iop所需准备的东西直接进入正题:
1.再controller中创建另一个方法exportsummaryDetail:
@RequestMapping(value = "/excel/exportt") public void exportsummaryDetail(HttpServletRequest request, HttpServletResponse response) throws Exception { // 创建一个webbook,对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("测试报表"); HSSFRow row = null; HSSFCell cell = null; // // 设置缺省列高 // sheet.setDefaultRowHeightInPoints(20); // //设置缺省列宽 // sheet.setDefaultColumnWidth(13); //----------------二级标题格样式---------------------------------- HSSFCellStyle style2 = wb.createCellStyle(); //表格样式 style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 Font f2 = wb.createFont(); f2.setFontHeightInPoints((short)11); // 将字体大小设置为18px f2.setFontName("宋体"); // 字体应用到当前单元格上 f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗 style2.setFont(f2); // cellStyle.setWrapText(true);//设置自动换行 String arr[] = {"大厦","大厦","大厦","大厦", "大厦","大厦"}; int a=1; int b=2; int c=3; int d=4; int e=5; int f=6; int g=7; int h=8; int i=9; int k=10; int l=11; int m=12; int n=13; int o=14; for (String string : arr) { // ----------------------创建第一行--------------- // 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个 row = sheet.createRow(a); // 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个 cell = row.createCell(1); // 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列 sheet.addMergedRegion(new CellRangeAddress(a, a, 1, 10)); // 设置单元格内容 cell.setCellValue(string); cell.setCellStyle(style2); //添加边框 for (int j = 2; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第二行--------------------- row = sheet.createRow(b); // 创建第二行 cell = row.createCell(1);//第二行第一列 // 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列 sheet.addMergedRegion(new CellRangeAddress(b, g, 1, 1)); cell.setCellValue("大厦单数"); //设置缺省列宽 // sheet.setColumnWidth(2, 20 * 256);//设置第一列的宽度是20个字符宽度 cell.setCellStyle(style2); cell = row.createCell(2); cell.setCellValue("类别"); cell.setCellStyle(style2); cell = row.createCell(3); cell.setCellValue("2018-11"); cell.setCellStyle(style2); cell = row.createCell(4); cell.setCellValue("2018-12"); cell.setCellStyle(style2); cell = row.createCell(5); cell.setCellValue("2019-01"); cell.setCellStyle(style2); cell = row.createCell(6); cell.setCellValue("2019-02"); cell.setCellStyle(style2); cell = row.createCell(7); cell.setCellValue("2019-03"); cell.setCellStyle(style2); cell = row.createCell(8); cell.setCellValue("2019-04"); cell.setCellStyle(style2); cell = row.createCell(9); cell.setCellValue("2019-05"); cell.setCellStyle(style2); cell = row.createCell(10); cell.setCellValue("2019-06"); cell.setCellStyle(style2); // ------------------创建第三行--------------------- row = sheet.createRow(c); // 创建第三行 row.createCell(1).setCellStyle(style2);//第三行第一列 cell = row.createCell(2);//第三行第一列 cell.setCellValue("大厦服务"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第四行--------------------- row = sheet.createRow(d); // 创建第四行 row.createCell(1).setCellStyle(style2);//第四行第二列 cell = row.createCell(2);//第四行第一列 cell.setCellValue("大厦服务"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第五行--------------------- row = sheet.createRow(e); // 创建第五行 row.createCell(1).setCellStyle(style2);//第五行第二列 cell = row.createCell(2);//第五行第三列 cell.setCellValue("大厦服务"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第六行--------------------- row = sheet.createRow(f); // 创建第六行 row.createCell(1).setCellStyle(style2);//第六行第二列 cell = row.createCell(2);//第六行第三列 cell.setCellValue("大厦服务"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第七行--------------------- row = sheet.createRow(g); // 创建第七行 row.createCell(1).setCellStyle(style2);//第七行第二列 cell = row.createCell(2);//第七行第三列 cell.setCellValue("合计"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第八行--------------------- row = sheet.createRow(h); // 创建第二行 cell = row.createCell(1);//第二行第一列 // 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列 sheet.addMergedRegion(new CellRangeAddress(h, n, 1, 1)); cell.setCellValue("大厦金额"); //设置缺省列宽 // sheet.setColumnWidth(2, 20 * 256);//设置第一列的宽度是20个字符宽度 cell.setCellStyle(style2); cell = row.createCell(2); cell.setCellValue("类别"); cell.setCellStyle(style2); cell = row.createCell(3); cell.setCellValue("2018-11"); cell.setCellStyle(style2); cell = row.createCell(4); cell.setCellValue("2018-12"); cell.setCellStyle(style2); cell = row.createCell(5); cell.setCellValue("2019-01"); cell.setCellStyle(style2); cell = row.createCell(6); cell.setCellValue("2019-02"); cell.setCellStyle(style2); cell = row.createCell(7); cell.setCellValue("2019-03"); cell.setCellStyle(style2); cell = row.createCell(8); cell.setCellValue("2019-04"); cell.setCellStyle(style2); cell = row.createCell(9); cell.setCellValue("2019-05"); cell.setCellStyle(style2); cell = row.createCell(10); cell.setCellValue("2019-06"); cell.setCellStyle(style2); // ------------------创建第九行--------------------- row = sheet.createRow(i); // row.createCell(1).setCellStyle(style2); cell = row.createCell(2); //添加边框 for (int j = 2; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第十行--------------------- row = sheet.createRow(k); // row.createCell(1).setCellStyle(style2); cell = row.createCell(2); //添加边框 for (int j = 2; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第十一行--------------------- row = sheet.createRow(l); // row.createCell(1).setCellStyle(style2); cell = row.createCell(2); //添加边框 for (int j = 2; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第十二行--------------------- row = sheet.createRow(m); // row.createCell(1).setCellStyle(style2); cell = row.createCell(2); //添加边框 for (int j = 2; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } // ------------------创建第十三行--------------------- row = sheet.createRow(n); // 创建第七行 row.createCell(1).setCellStyle(style2);//第七行第二列 cell = row.createCell(2);//第七行第三列 cell.setCellValue("合计"); cell.setCellStyle(style2); //添加边框 for (int j = 3; j <= 10; j++) { cell = row.createCell(j); cell.setCellStyle(style2); //style为带边框的样式 上面有定义 cell.setCellValue(""); } //自动调整列宽 sheet.autoSizeColumn((short)0); //调整第一列宽度 sheet.setColumnWidth(0,3 * 256); sheet.autoSizeColumn((short)1); //调整第二列宽度 sheet.setColumnWidth(1,sheet.getColumnWidth(1) *12 /10); sheet.autoSizeColumn((short)2); //调整第三列宽度 sheet.setColumnWidth(2,sheet.getColumnWidth(2) *12 /10); sheet.autoSizeColumn((short)3); //调整第四列宽度 sheet.setColumnWidth(3,sheet.getColumnWidth(3) *12 /10); sheet.autoSizeColumn((short)4); //调整第五列宽度 sheet.setColumnWidth(4,sheet.getColumnWidth(4) *12 /10); sheet.autoSizeColumn((short)5);//调整第六列宽度 sheet.setColumnWidth(5,sheet.getColumnWidth(5) *12 /10); sheet.autoSizeColumn((short)6); sheet.setColumnWidth(6,sheet.getColumnWidth(6) *12 /10); sheet.autoSizeColumn((short)7); sheet.setColumnWidth(7,sheet.getColumnWidth(7) *12 /10); sheet.autoSizeColumn((short)8); sheet.setColumnWidth(8,sheet.getColumnWidth(8) *12 /10); sheet.autoSizeColumn((short)9); sheet.setColumnWidth(9,sheet.getColumnWidth(9) *12 /10); sheet.autoSizeColumn((short)10); sheet.setColumnWidth(10,sheet.getColumnWidth(10) *12 /10); sheet.autoSizeColumn((short)11); sheet.setColumnWidth(11,3* 256); a=a+14; b=b+14; c=c+14; d=d+14; e=e+14; f=f+14; g=g+14; h=h+14; i=i+14; k=k+14; l=l+14; m=m+14; n=n+14; o=o+14; } //下载流 response.setContentType("application/vnd.ms-excel"); String name="测试表.xls"; response.setHeader("Content-disposition", "attachment;filename="+new String(name.getBytes("utf-8"),"ISO8859-1")); OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); }
2.修改HTML下载路径为:
action="/excel/exportt"
结果: