反正都要被打死,所以把这个例子记下来吧,一个学习的辛苦历程!
/**
* 导出Excel
* @param request 请求对象
* @param response 相应对象
* @return
* @throws Exception 导出异常
*/
@GetMapping("/download")
public RestResult downloadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
//初始整个Excel
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//创建一个表格
HSSFSheet createSheet = hssfWorkbook.createSheet("药品信息数据表");
//设置单元格风格
PoiUtil poiUtil = new PoiUtil();
//设置标题风格
HSSFCellStyle cs = hssfWorkbook.createCellStyle();
//设置单元格
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//设置字体
HSSFFont createFont = hssfWorkbook.createFont();
createFont.setFontHeight((short)300);
createFont.setFontName("黑体");
createFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
createFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(createFont);
//设置一级单元格风格
HSSFCellStyle cs1 = hssfWorkbook.createCellStyle();
cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cs1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cs1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont createFont1 = hssfWorkbook.createFont();
createFont1.setFontHeight((short)260);
createFont1.setFontName("宋体");
cs1.setFont(createFont1);
//设置二级单元格风格
HSSFCellStyle cs2 = hssfWorkbook.createCellStyle();
cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont createFont2 = hssfWorkbook.createFont();
createFont2.setFontHeight((short)220);
createFont2.setFontName("宋体");
cs2.setFont(createFont2);
//获取数据库的数据确定需要多少行
List<DrugInfo> findAll = drugInfoService.findAll();
HSSFRow createRowTitle = createSheet.createRow(1);
//设置高宽40px
createRowTitle.setHeight((short)(15.625*40));
createRowTitle.setHeightInPoints(30f);
for (int i = 2; i <7; i++) {
HSSFCell createCell1 = createRowTitle.createCell(i);
createCell1.setCellStyle(cs);
createCell1.setCellValue("药品信息数据表");
createSheet.autoSizeColumn(i);
createSheet.addMergedRegion(new Region(1, (short)2, 1, (short)6));
//createSheet.addMergedRegion(new CellRangeAddress(0,0,2,7));
}
HSSFRow createRowTop = createSheet.createRow(2);
createRowTop.setHeight((short)(15.625*25));
createRowTop.setHeightInPoints(20f);
HSSFCell createCell2 = createRowTop.createCell(2);
createCell2.setCellValue("药品状态");
createCell2.setCellStyle(cs1);
createSheet.autoSizeColumn(2);
HSSFCell createCell3 = createRowTop.createCell(3);
createCell3.setCellValue("批准文号");
createCell3.setCellStyle(cs1);
createSheet.autoSizeColumn(3);
HSSFCell createCell4 = createRowTop.createCell(4);
createCell4.setCellValue("商品名称(中)");
createCell4.setCellStyle(cs1);
createSheet.autoSizeColumn(4);
HSSFCell createCell5 = createRowTop.createCell(5);
createCell5.setCellValue("商品名称(中)");
createCell5.setCellStyle(cs1);
createSheet.autoSizeColumn(5);
HSSFCell createCell6 = createRowTop.createCell(6);
createCell6.setCellValue("规格");
createCell6.setCellStyle(cs1);
createSheet.autoSizeColumn(6);
//创建行
for (int rowIndex = 0; rowIndex < findAll.size(); rowIndex++) {
HSSFRow createRow = createSheet.createRow(rowIndex+3);
DrugInfo drugInfo = findAll.get(rowIndex);
createRow.setHeight((short)(15.625*30));
createRow.setHeightInPoints(20f);
//创建列
for (int columnIndex = 0; columnIndex < 7; columnIndex++) {
HSSFCell createCell = createRow.createCell(columnIndex);
//设置风格
switch (columnIndex) {
case 2:
createCell.setCellValue(drugInfo.getProductionStatus());
createCell.setCellStyle(cs2);
createSheet.autoSizeColumn(2);
break;
case 3:
createCell.setCellValue(drugInfo.getLicence());
createCell.setCellStyle(cs2);
createSheet.autoSizeColumn(3);
break;
case 4:
createCell.setCellValue(drugInfo.getDrugName());
createCell.setCellStyle(cs2);
createSheet.autoSizeColumn(4);
break;
case 5:
createCell.setCellValue(drugInfo.getDrugProductName());
createCell.setCellStyle(cs2);
createSheet.autoSizeColumn(5);
break;
case 6:
createCell.setCellValue(drugInfo.getDrugSpec());
createCell.setCellStyle(cs2);
createSheet.autoSizeColumn(6);
break;
}
}
}
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("药品信息数据表","UTF-8") + ".xls");
//编码
response.setCharacterEncoding("UTF-8");
hssfWorkbook.write(response.getOutputStream());
/*ExportParams params = new ExportParams() ;
params.setSheetName("第一页"); ;
params.setTitle("药品信息数据");
params.setSecondTitle("导出日期:"+new Date());
// 告诉浏览器用什么软件可以打开此文件
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("药品信息数据表","UTF-8") + ".xls");
//编码
response.setCharacterEncoding("UTF-8");
List<DrugInfo> findAll = drugInfoService.findAll();
Workbook workbook = ExcelExportUtil.exportExcel(params, DrugInfo.class, findAll);
CellStyle createCellStyle = workbook.createCellStyle();
createCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
workbook.write(response.getOutputStream());*/
return ResultGenerator.genSuccessResult().setMessage("导出成功");
}
代码还未做优化,下次要做poi导出就简单多了!