用poi做excel自定义格式的导出

来源：互联网发布：mysql union 用法编辑：程序博客网时间：2024/06/14 10:34
刚做项目的时间不是太久，项目中用到excel表格的导出，之前遇到过的都是固定格式的，一般都可以采用模板的方式进行导入、导出。但是知己遇到的情况比较麻烦，需要自定义导出格式，合并单元格，并且是多个sheet页的类型。效果大概是这个样子
记录一下，以免以后在遇到类似的情况
//查询自定义导出public void CustomreadExcel(HttpSession session, HttpServletResponse response, List<Map<String, Object>> dataList) throws IOException {        // TODO Auto-generated method stub          HSSFWorkbook workbook = new HSSFWorkbook();           for(int i=0; i<dataList.size();i++){            Map<String, Object> data=dataList.get(i);      //         for (Map<String, Object> 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();
通过一点点的拼写，把表格拼出来。虽然做法可能有点呗，但是好在出来结果了。
阅读全文
0 0