用poi做excel自定义格式的导出。刚做项目的时间不是太久,项目中用到excel表格的导出,之前遇到过的都是固定格式的,一般都可以采用模板的方式进行导入、导出。但是知己遇到的情况比较麻烦,需要自定义导出格式,合并单元格,并且是多个sheet页的类型。
记录一下,以免以后在遇到类似的情况
//查询自定义导出 public void CustomreadExcel(HttpSession session, HttpServletResponse response, List<>> dataList) throws IOException { // TODO Auto-generated method stub HSSFWorkbook workbook = new HSSFWorkbook(); for(int i=0; i data=dataList.get(i); // for (Map data : dataList) { String sheetname= data.get("code").toString(); //给sheet表头加上一个()_i+1 String sheetname1 = "("+sheetname+")"+"_"+(1+i); HSSFSheet sheet = workbook.createSheet(sheetname1);// 创建一个表 //设置列宽: sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 6000); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 6000); sheet.setDefaultRowHeightInPoints(27); HSSFRow row =null; HSSFFont font=null; // 设置第一行 row = sheet.createRow(0); HSSFCellStyle setBorder = workbook.createCellStyle(); setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 setBorder.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // ## 设置底部边框颜色为黑色 ##// setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 setBorder.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // ## 设置左边边框颜色为黑色 ##// setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 setBorder.setTopBorderColor(IndexedColors.BLACK.getIndex()); // ## 设置顶部边框颜色为黑色 ##// setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 setBorder.setRightBorderColor(IndexedColors.BLACK.getIndex()); // ## 设置右边边框颜色为黑色 ##// font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12);//设置字体大小 // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 setBorder.setFont(font); setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 row.createCell(0).setCellValue("姓名"); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(data.get("name").toString()); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue("性别"); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(data.get("gender").toString()); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue("出生日期"); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(data.get("birthDate").toString()); row.getCell(5).setCellStyle(setBorder); //设置第二行 row = sheet.createRow(1); row.createCell(0).setCellValue("儿童编号"); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(data.get("code").toString()); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue("户口类别"); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(data.get("category").toString()); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue("建档时间"); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(data.get("createDate").toString()); row.getCell(5).setCellStyle(setBorder); //设置第三行 row = sheet.createRow(2); row.createCell(0).setCellValue("家长姓名"); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(data.get("parentName").toString()); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue("联系电话"); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(data.get("motherPhone").toString()); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue(""); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(""); row.getCell(5).setCellStyle(setBorder); // Region region = new Region(2, (short) 3, 2, (short) 5); //参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号 // sheet.addMergedRegion(region); CellRangeAddress region = new CellRangeAddress(2, 2, (short) 3, (short) 5); //参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列 sheet.addMergedRegion(region); // row.getCell(1).setCellStyle(setBorder); //设置第四行 row = sheet.createRow(3); row.createCell(0).setCellValue("家庭住址"); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(data.get("addr").toString()); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue(""); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(""); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue(""); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(""); row.getCell(5).setCellStyle(setBorder); CellRangeAddress region1 = new CellRangeAddress(3, 3, (short) 1, (short) 5); sheet.addMergedRegion(region1); //设置第五行 row = sheet.createRow(4); row.createCell(0).setCellValue("疫苗名称"); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(""); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue("剂次"); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(""); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue("接种时间"); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(""); row.getCell(5).setCellStyle(setBorder); CellRangeAddress region2 = new CellRangeAddress(4, 4, (short) 0, (short) 1); sheet.addMergedRegion(region2); CellRangeAddress region3 = new CellRangeAddress(4, 4, (short) 2, (short) 3); sheet.addMergedRegion(region3); CellRangeAddress region4= new CellRangeAddress(4, 4, (short) 4, (short) 5); sheet.addMergedRegion(region4); //设置第六行行 row = sheet.createRow(5); row.createCell(0).setCellValue(data.get("abbrev").toString()); row.getCell(0).setCellStyle(setBorder); row.createCell(1).setCellValue(""); row.getCell(1).setCellStyle(setBorder); row.createCell(2).setCellValue(data.get("inocassess").toString()); row.getCell(2).setCellStyle(setBorder); row.createCell(3).setCellValue(""); row.getCell(3).setCellStyle(setBorder); row.createCell(4).setCellValue(data.get("inocdate").toString()); row.getCell(4).setCellStyle(setBorder); row.createCell(5).setCellValue(""); row.getCell(5).setCellStyle(setBorder); CellRangeAddress region5 = new CellRangeAddress(5, 5, (short) 0, (short) 1); sheet.addMergedRegion(region5); CellRangeAddress region6 = new CellRangeAddress(5, 5, (short) 2, (short) 3); sheet.addMergedRegion(region6); CellRangeAddress region7 = new CellRangeAddress(5, 5, (short) 4, (short) 5); sheet.addMergedRegion(region7); } String fileName = "excel_" + System.currentTimeMillis() + ".xls"; ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); response.setContentType("application/x-download;charset=utf-8"); response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); OutputStream os = response.getOutputStream(); ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray()); byte[] b = new byte[1024]; while ((bais.read(b)) > 0) { os.write(b); } bais.close(); os.flush(); os.close();
通过一点点的拼写,把表格拼出来。虽然做法可能有点呗,但是好在出来结果了。