刚做项目的时间不是太久,项目中用到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();
通过一点点的拼写,把表格拼出来。虽然做法可能有点呗,但是好在出来结果了。