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

用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();

通过一点点的拼写，把表格拼出来。虽然做法可能有点呗，但是好在出来结果了。