给自己写个记录。复合表头创建,以及excel里没内容的可能问题:①单元格少建,或者写漏错误②多次引用HSSFWorkbook 对象。
用HSSFWorkbook,需要导入对应jar包
@RequestMapping(params = "moveExportXls")
public void moveExportXls(String areaId, String startDate ,String endDate,HttpServletRequest request, HttpServletResponse response) {
//查询
if(StringUtils.isBlank(areaId)){
//获取用户区划
TSUser user = ResourceUtil.getSessionUser();
areaId = user.getDepartid();
}
//通过查数据库获取到数据
List<Map<String, Object>> moveInfoList =
taskPersonMoveService.getStatistics(request,areaId,startDate,endDate);
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
String newDay=format.format(new Date());
String time="";
if(StringUtils.isNotBlank(startDate)){
time=startDate;
if(StringUtils.isNotBlank(endDate)){
time=time+" ~ "+endDate;
}else{
time=time+" ~ "+newDay;
}
}else{
if(StringUtils.isNotBlank(endDate)){
time="截止到"+endDate;
}else{
time="截止到"+newDay;
}
}
/*
给自己的提示:创建HSSFWorkbook wb = new HSSFWorkbook();只能创建一次,不要引用自己
项目里的其他带有HSSFWorkbook 的方法,不然excel文件输出后,excel里可能会没有表头,没有数
据,就是空的excel。
情景:ExcelExpHelper exh=new ExcelExpHelper();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row = null;//行
HSSFCell cell = null;//列
说明:ExcelExpHelper 已有的简单封装,里面有HSSFWorkbook 的创建,我又重新创建了一遍,导
致excel没有结果输出
*/
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row = null;//行
HSSFCell cell = null;//列
HSSFSheet sheet = wb.createSheet("人员迁移");
sheet.setDefaultRowHeightInPoints(30);
sheet.setDefaultColumnWidth(20);
//标题样式
HSSFCellStyle titleStyle = wb.createCellStyle(); //标题样式
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font ztFont = wb.createFont();
ztFont.setItalic(false); // 设置字体为斜体字
ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short)16); // 将字体大小设置为18px
ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
titleStyle.setFont(ztFont);
//表格样式
HSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
Font cellFont = wb.createFont();
cellFont.setItalic(false); // 设置字体为斜体字
cellFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
cellFont.setFontHeightInPoints((short)10); // 将字体大小设置为18px
cellFont.setFontName("宋体"); // 字体应用到当前单元格上
//cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(cellFont);
cellStyle.setWrapText(true);//设置自动换行
// 创建第一行
// 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
row = sheet.createRow(0);
// 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
cell = row.createCell(0);
// 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
// 设置单元格内容
cell.setCellValue("迁移统计时间:"+time);
cell.setCellStyle(titleStyle);
//第二行
row=sheet.createRow(1);
sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));
cell=row.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));
cell=row.createCell(1);
cell.setCellValue("名称");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,2,5));
cell=row.createCell(2);
cell.setCellValue("已办数");
cell.setCellStyle(cellStyle);
cell=row.createCell(3);
cell.setCellStyle(cellStyle);
cell=row.createCell(4);
cell.setCellStyle(cellStyle);
cell=row.createCell(5);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,6,9));
cell=row.createCell(6);
cell.setCellValue("待办数");
cell.setCellStyle(cellStyle);
cell=row.createCell(7);
cell.setCellStyle(cellStyle);
cell=row.createCell(8);
cell.setCellStyle(cellStyle);
cell=row.createCell(9);
cell.setCellStyle(cellStyle);
//第三行
row=sheet.createRow(2);
sheet.addMergedRegion(new CellRangeAddress(2,2,2,2));
cell=row.createCell(2);
cell.setCellValue("迁入数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,3,3));
cell=row.createCell(3);
cell.setCellValue("迁出数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,4,4));
cell=row.createCell(4);
cell.setCellValue("退回数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,5,5));
cell=row.createCell(5);
cell.setCellValue("已办总数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,6,6));
cell=row.createCell(6);
cell.setCellValue("上报未处理");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,7,7));
cell=row.createCell(7);
cell.setCellValue("签收未处理");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,8,8));
cell=row.createCell(8);
cell.setCellValue("派发未签收");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,9,9));
cell=row.createCell(9);
cell.setCellValue("待处理总数");
cell.setCellStyle(cellStyle);
//把获取到的数据塞到对应单元格
List<String> sublist=null;
for(int i=0;i<moveInfoList.size();i++){
String qhmc=moveInfoList.get(i).get("ST_TEAM_NAME").toString();//名称
String qrs=moveInfoList.get(i).get("QRS").toString();//迁入数
String qcs=moveInfoList.get(i).get("QCS").toString();//迁出数
String ths=moveInfoList.get(i).get("THS").toString();//退回数
String ybzs=moveInfoList.get(i).get("YBZS").toString();//已办总数
String wgwcl=moveInfoList.get(i).get("WGWCL").toString();//上报未处理
String sqwcl=moveInfoList.get(i).get("SQWCL").toString();//签收未处理
String ypfwqs=moveInfoList.get(i).get("YPFWQS").toString();//派发未签收
String dclzs=moveInfoList.get(i).get("DCLZS").toString();//待处理总数
sublist=new ArrayList<String>();
sublist.add(i+1+"");
sublist.add(qhmc);
sublist.add(qrs);
sublist.add(qcs);
sublist.add(ths);
sublist.add(ybzs);
sublist.add(wgwcl);
sublist.add(sqwcl);
sublist.add(ypfwqs);
sublist.add(dclzs);
int rowNum=3+i; //从第三行开始
row=sheet.createRow(rowNum);
for(int j=0;j<sublist.size();j++){
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,j,j));
cell=row.createCell(j);
cell.setCellValue(""+sublist.get(j)+"");
cell.setCellStyle(cellStyle);
}
}
//输出
String sheetName= "迁移统计导出";
try {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition","attachment;filename="+new
String(sheetName.getBytes(), "iso-8859-1")+".xls");
ServletOutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
最终结果展示: