POI创建Excel

1.结果EXCEL

2.

@Overridepublic void downloadDetail(HttpServletRequest request,HttpServletResponse response) {String projectId = request.getParameter("projectId");String projectName = request.getParameter("projectName");PrintUtils pu = new PrintUtils();JSONObject json = new JSONObject();try {projectName=new String(projectName.getBytes("ISO8859-1"),"UTF-8");projectName = URLDecoder.decode(projectName, "utf-8"); String title = projectName + "进度查看";String path = request.getSession().getServletContext().getRealPath("")+ "\\exportfile\\";OutputStream output = new FileOutputStream(path + title + ".xls");ExcelExp2(title, request, output);String newPath=request.getSession().getServletContext().getRealPath("exportfile");newPath = newPath + "\\"+title+".xls";// 得到要下载的文件File file = new File(newPath);// 得到要下载的文件名String fileName = file.getName(); // 2323928392489-美人鱼.avi// fileName = new String(fileName.getBytes("iso8859-1"), "UTF-8");// 如果文件不存在if (!file.exists()) {request.setAttribute("message", "您要下载的资源已被删除！！");return;}// 设置响应头，控制浏览器下载该文件response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));// 读取要下载的文件，保存到文件输入流FileInputStream in = new FileInputStream(file);// 创建输出流OutputStream out = response.getOutputStream();// 创建缓冲区byte buffer[] = new byte[1024];int len = 0;// 循环将输入流中的内容读取到缓冲区当中while ((len = in.read(buffer)) > 0) {// 输出缓冲区的内容到浏览器，实现文件下载out.write(buffer, 0, len);}// 关闭文件输入流in.close();// 关闭输出流out.close();} catch (Exception e) {}}

3.

@SuppressWarnings("deprecation")private void ExcelExp2(String title, HttpServletRequest request,OutputStream output) {String projectId = request.getParameter("projectId");String projectName = request.getParameter("projectName");try {projectName=new String(projectName.getBytes("ISO8859-1"),"UTF-8");projectName = URLDecoder.decode(projectName, "utf-8");} catch (UnsupportedEncodingException e1) {// TODO Auto-generated catch blocke1.printStackTrace();} Map projectMain = this.getProjectInfo(projectId);List<Map<String, Object>> projectDetailList = this.getProjectDetailList(projectId);// 创建HSSFWorkbook对象(excel的文档对象)HSSFWorkbook wkb = new HSSFWorkbook();// 生成一个样式HSSFCellStyle style = wkb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 生成一个字体HSSFFont font = wkb.createFont();font.setFontHeightInPoints((short) 20);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 把字体应用到当前的样式style.setFont(font);// 再定义一个style,设置单元格为自动换行HSSFCellStyle style2 = wkb.createCellStyle();style2.setWrapText(true);style2.setBorderTop(HSSFCellStyle.BORDER_THIN);style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);style2.setBorderRight(HSSFCellStyle.BORDER_THIN);style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);HSSFFont font2 = wkb.createFont();font2.setFontHeightInPoints((short) 10);font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);style2.setFont(font2);// 再定义一个style,HSSFCellStyle style3 = wkb.createCellStyle();style3.setWrapText(true);style3.setBorderTop(HSSFCellStyle.BORDER_THIN);style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);style3.setBorderRight(HSSFCellStyle.BORDER_THIN);style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);HSSFFont font3 = wkb.createFont();font3.setFontHeightInPoints((short) 10);style3.setFont(font3);// 建立新的sheet对象（excel的表单）HSSFSheet sheet = wkb.createSheet(title);//设置列宽sheet.setColumnWidth(0, 256*20);sheet.setColumnWidth(1, 256*20);sheet.setColumnWidth(2, 256*20);sheet.setColumnWidth(3, 256*20);sheet.setColumnWidth(4, 256*20);sheet.setColumnWidth(5, 256*20);sheet.setColumnWidth(6, 256*20);// 设置横向打印sheet.getPrintSetup().setLandscape(true);// 设置纸张类型sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);// 设置上下左右页边距sheet.setMargin(HSSFSheet.TopMargin, 0.1);sheet.setMargin(HSSFSheet.BottomMargin, 0.01);sheet.setMargin(HSSFSheet.LeftMargin, 0.03);sheet.setMargin(HSSFSheet.RightMargin, 0.01);// 表名// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个HSSFRow row0 = sheet.createRow(0);row0.setHeightInPoints((short) 50);HSSFCell cell = row0.createCell(0);cell.setCellStyle(style);cell.setCellValue(title);// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));/** * 固定内容开始 */// 第一行HSSFRow row1 = sheet.createRow(1);HSSFCell cell1_1 = row1.createCell(0);cell1_1.setCellStyle(style2);cell1_1.setCellValue("项目概况");HSSFCell cell1_2 = row1.createCell(1);cell1_2.setCellStyle(style2);cell1_2.setCellValue("项目名称");HSSFCell cell1_3 = row1.createCell(2);cell1_3.setCellStyle(style3);cell1_3.setCellValue(projectName);HSSFCell cell1_4 = row1.createCell(3);cell1_4.setCellStyle(style2);cell1_4.setCellValue("建设单位");HSSFCell cell1_5 = row1.createCell(4);cell1_5.setCellStyle(style3);cell1_5.setCellValue(projectMain.get("DEPARTNAME") == null ? "": projectMain.get("DEPARTNAME").toString());HSSFCell cell1_6 = row1.createCell(5);cell1_6.setCellStyle(style2);cell1_6.setCellValue("责任人");HSSFCell cell1_7 = row1.createCell(6);cell1_7.setCellStyle(style3);cell1_7.setCellValue(projectMain.get("USER_NAME") == null ? "": projectMain.get("USER_NAME").toString());// 第二行HSSFRow row2 = sheet.createRow(2);HSSFCell cell2_1 = row2.createCell(1);cell2_1.setCellStyle(style2);cell2_1.setCellValue("资金预算");HSSFCell cell2_2 = row2.createCell(2);cell2_2.setCellStyle(style3);cell2_2.setCellValue(projectMain.get("FUND_SPEND") == null ? "": projectMain.get("FUND_SPEND").toString());HSSFCell cell2_3 = row2.createCell(3);cell2_3.setCellStyle(style2);cell2_3.setCellValue("合同签订时间");HSSFCell cell2_4 = row2.createCell(4);cell2_4.setCellStyle(style3);cell2_4.setCellValue(projectMain.get("PROJECT_START_DATE") == null ? "": projectMain.get("PROJECT_START_DATE").toString());HSSFCell cell2_5 = row2.createCell(5);cell2_5.setCellStyle(style2);cell2_5.setCellValue("合同完成时间");HSSFCell cell2_6 = row2.createCell(6);cell2_6.setCellStyle(style3);cell2_6.setCellValue(projectMain.get("PROJECT_END_DATE") == null ? "": projectMain.get("PROJECT_END_DATE").toString());/** * 固定内容结束,循环部分开始 */int indexrow = 3;int indexrowmini = 3;if (projectDetailList.size() > 0) {// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));// sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));HSSFRow row3 = null;HSSFRow row4 = null;HSSFRow row5 = null;Map maptemp = null;Map maptempmini = null;List DETAILLIST = null;/** * 填充右边最小的格子 */int start=3;for (int i = 0; i < projectDetailList.size(); i++) {maptemp = projectDetailList.get(i);DETAILLIST = (List) maptemp.get("DETAILLIST");for (int j = 0; j < DETAILLIST.size(); j++) {maptempmini = (Map) DETAILLIST.get(j);sheet.addMergedRegion(new CellRangeAddress(start, start, 4, 6));row3 = sheet.createRow(indexrowmini);HSSFCell celln_3 = row3.createCell(3);celln_3.setCellStyle(style2);celln_3.setCellValue("建设目标");HSSFCell celln_3v = row3.createCell(4);celln_3v.setCellStyle(style3);String NEXT_GOAL=maptempmini.get("NEXT_GOAL") == null ? "": maptempmini.get("NEXT_GOAL").toString();celln_3v.setCellValue(NEXT_GOAL);//合并单元格要把后面的格子补上，不然没有边框row3.createCell(0).setCellStyle(style3);row3.createCell(1).setCellStyle(style3);row3.createCell(5).setCellStyle(style3);row3.createCell(6).setCellStyle(style3);start=start+1;sheet.addMergedRegion(new CellRangeAddress(start, start, 4, 6));row4 = sheet.createRow(indexrowmini+1);HSSFCell celln_4 = row4.createCell(3);celln_4.setCellStyle(style2);celln_4.setCellValue("进展情况");HSSFCell celln_4v = row4.createCell(4);celln_4v.setCellStyle(style3);String PROGRESS_DESCRIBE=maptempmini.get("PROGRESS_DESCRIBE") == null ? "": maptempmini.get("PROGRESS_DESCRIBE").toString();celln_4v.setCellValue(PROGRESS_DESCRIBE);//合并单元格要把后面的格子补上，不然没有边框row4.createCell(0).setCellStyle(style3);row4.createCell(1).setCellStyle(style3);row4.createCell(5).setCellStyle(style3);row4.createCell(6).setCellStyle(style3);indexrowmini = indexrowmini + 2;start=start+1;}}indexrowmini=3;/** * 填充中间的格子 */for (int i = 0; i < projectDetailList.size(); i++) {maptemp = projectDetailList.get(i);DETAILLIST = (List) maptemp.get("DETAILLIST");for (int j = 0; j < DETAILLIST.size(); j++) {maptempmini = (Map) DETAILLIST.get(j);sheet.addMergedRegion(new CellRangeAddress(indexrowmini,indexrowmini + 2 - 1, 2, 2));row3 = sheet.getRow(indexrowmini);//合并单元格要把后面的格子补上，不然没有边框sheet.getRow(indexrowmini+1).createCell(2).setCellStyle(style2);HSSFCell celln_3 = row3.createCell(2);celln_3.setCellStyle(style2);String PROJECT_NODE_NAME = maptempmini.get("PROJECT_NODE_NAME").toString();celln_3.setCellValue(PROJECT_NODE_NAME);indexrowmini = indexrowmini + 2;}}/** * 填充最左边的大格子 */for (int i = 0; i < projectDetailList.size(); i++) {maptemp = projectDetailList.get(i);int nodecount = (Integer) maptemp.get("NODECOUNT");sheet.addMergedRegion(new CellRangeAddress(i + indexrow, i+ indexrow + nodecount - 1, 0, 0));row3 = sheet.getRow(i + indexrow);HSSFCell celln_1 = row3.createCell(0);celln_1.setCellStyle(style2);celln_1.setCellValue("");sheet.addMergedRegion(new CellRangeAddress(i + indexrow, i+ indexrow + nodecount - 1, 1, 1));HSSFCell celln_2 = row3.createCell(1);celln_2.setCellStyle(style2);String PROJECT_PHASES_NAME = maptemp.get("PROJECT_PHASES_NAME").toString();celln_2.setCellValue(PROJECT_PHASES_NAME);DETAILLIST = (List) maptemp.get("DETAILLIST");indexrow = indexrow + nodecount - 1;}}/** * 循环部分结束 */try {wkb.write(output);output.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}