//待调用的后端方法
@RequestMapping(value = "/manage/list/export")
@ResponseBody
public void doExportExcel(HttpServletResponse response,HttpServletRequest request,String keyId) {
OutputStream ouputStream = null;
try {
ReturnDO<EXCELVO> rdo = excelService.getEXCELVOById(keyId);
if (rdo.isSuccess() && StringTool.isNotEmpty(rdo.getObj())) {
HSSFWorkbook wb = new HSSFWorkbook();
//第一页sheet表
exportExcel(wb, 0, "表1",rdo.getObj());
//第二页sheet表
exportExcel(wb, 1, "表2",rdo.getObj());
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition",
"attachment;filename=excelReport.xls");
ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
} catch (Exception e) {
logger.debug("导出异常" + e.getMessage());
e.printStackTrace();
try {
ouputStream.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
其中exportExcel(wb, 0, "表1",rdo.getObj()); 就是给每个sheet页赋值的方法
//sheetNum页数,sheetTiTle页标题, ReportVO数据实体类
public void exportExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, ReportVO vo) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置背景色
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// style.setAlignment(HorizontalAlignment.CENTER);// 文字水平居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("仿宋_GB2312");
font.setColor(HSSFColor.OLIVE_GREEN.index);
font.setFontHeightInPoints((short) 24);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
//生成一行
HSSFRow row = sheet.createRow((int) 0);
row.setHeightInPoints(20);
HSSFCell cell = row.createCell(0);
cell.setCellValue(sheetTitle);
cell.setCellStyle(style);
for (int i = 1; i < 3; i++) {
cell = row.createCell(i);
cell.setCellStyle(style);
}
sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 1, 0, 2));
// 设置字体
// 生成一个样式
HSSFCellStyle style1 = workbook.createCellStyle();
// 设置背景色
style1.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置边框
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
HSSFFont font1 = workbook.createFont();
font1.setFontName("仿宋_GB2312");
font1.setColor(HSSFColor.BLACK.index);
font1.setFontHeightInPoints((short) 10);
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style1.setFont(font1);
style1.setWrapText(true);
//生成一行
HSSFRow row2 = sheet.createRow((int) 2);
row.setHeightInPoints(20);
HSSFCell cell1 = row2.createCell(0);
cell1.setCellValue("名称");
cell1.setCellStyle(style1);
sheet.setColumnWidth(0, 5000);
cell1 = row2.createCell(1);
cell1.setCellValue("类型");
cell1.setCellStyle(style1);
sheet.setColumnWidth(1, 5000);
cell1 = row2.createCell(2);
cell1.setCellValue("时间");
cell1.setCellStyle(style1);
sheet.setColumnWidth(2, 5000);
// 生成一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
//设置边框
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
// 设置字体
HSSFFont font2 = workbook.createFont();
font2.setFontName("仿宋_GB2312");
font2.setColor(HSSFColor.BLACK.index);
font2.setFontHeightInPoints((short) 10);
// font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style2.setFont(font2);
HSSFRow row3 = sheet.createRow((int) 3);
if(StringTool.isNotEmpty(vo.getList()) &&
vo.getList().size()>0){
for(int i=0;i< vo.getList().size();i++){
ElementStepSchedule bean = vo.getList().get(i);
row3 = sheet.createRow((int) 3 + i);
cell1 = row3.createCell(0);
cell1.setCellValue(bean.getName());
cell1.setCellStyle(style2);
cell1 = row3.createCell(1);
String type= "";
if (StringTool.isNotEmpty(bean.getType())) {
if(bean.getType().equals("1")){
type= "TRUE";
}else if(bean.getType().equals("2")){
type= "FLASE";
}
}
cell1.setCellValue(type);
cell1.setCellStyle(style2);
cell1 = row3.createCell(2);
cell1.setCellValue(DateUtil.formatDateToString(bean.getTime(), "yyyy-MM-dd HH:mm:ss"));
cell1.setCellStyle(style2);
}
}
}