对一次简单的poi导出整理
public ResponseBean export(List<WjdExportModel> list, HttpServletRequest request, HttpServletResponse response) {
WjdExportModel wjdExportModel = list.get(0);
//获取仓库管理人名字
String name = wmsAllotRecordDao.getOutWareManager(wjdExportModel.getOldWare());
ClassPathResource resource = new ClassPathResource("excelTemplate/wjdExportErsTemplate.xlsx");
try {
// 获取表格对象
XSSFWorkbook wb = new XSSFWorkbook(resource.getInputStream());
XSSFCellStyle style = wb.createCellStyle();
// 字体样式
XSSFFont xssfFont = wb.createFont();
//合并单元格样式
XSSFCellStyle boderStyle = wb.createCellStyle();
// 竖向居中
boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
boderStyle.setAlignment(HorizontalAlignment.CENTER);
; // 创建一个居中格式
//设置一个边框
boderStyle.setBorderBottom(BorderStyle.MEDIUM);
boderStyle.setBorderLeft(BorderStyle.MEDIUM);
boderStyle.setBorderRight(BorderStyle.MEDIUM);
boderStyle.setBorderTop(BorderStyle.MEDIUM);
// 设置字体css
style.setFont(xssfFont);
// 竖向居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
style.setAlignment(HorizontalAlignment.CENTER);
// 边框
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBorderLeft(BorderStyle.MEDIUM);
style.setBorderRight(BorderStyle.MEDIUM);
style.setBorderTop(BorderStyle.MEDIUM);
// 获取工作簿
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row1 = sheet.getRow(1);
XSSFRow row2 = sheet.getRow(2);
//制单日期
row1.getCell(0).setCellValue("制单日期:" + new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
//NO
row1.getCell(5).setCellValue("NO:" + wjdExportModel.getOutCode());
//外借客户名称
row2.getCell(3).setCellValue(wjdExportModel.getBorrowClientName());
if (list.size() < 4) {
for (int i = 0; i < list.size(); i++) {
XSSFRow row4 = sheet.createRow(4 + i);
sheet.addMergedRegion(new CellRangeAddress(4 + i, 4 + i, 0, 2));
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
//产品名称
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
row4.createCell(0).setCellValue(list.get(i).getPName());
row4.getCell(0).setCellStyle(boderStyle);
//固定资产编号
row4.createCell(3).setCellValue(list.get(i).getProductCode());
row4.getCell(3).setCellStyle(boderStyle);
//外借日期
row4.createCell(4).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getOutDate()));
row4.getCell(4).setCellStyle(boderStyle);
//归还日期
if (wjdExportModel.getUntilTime() != null) {
row4.createCell(5).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getUntilTime()));
row4.getCell(5).setCellStyle(boderStyle);
} else {
row4.createCell(5).setCellValue("");
row4.getCell(5).setCellStyle(boderStyle);
}
//外借数量
row4.createCell(6).setCellValue(list.get(i).getCount());
row4.getCell(6).setCellStyle(boderStyle);
//备注
row4.createCell(7).setCellValue(list.get(i).getRemark());
row4.getCell(7).setCellStyle(boderStyle);
}
int a = 3-list.size();
for (int i = 0; i < (4+a); i++) {
XSSFRow row4 = sheet.createRow(4+list.size() + i);
sheet.addMergedRegion(new CellRangeAddress(4+list.size() + i, 4+list.size() + i, 0, 2));
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
//产品名称
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
row4.createCell(0).setCellValue("");
row4.getCell(0).setCellStyle(boderStyle);
//固定资产编号
row4.createCell(3).setCellValue("");
row4.getCell(3).setCellStyle(boderStyle);
//外借日期
row4.createCell(4).setCellValue("");
row4.getCell(4).setCellStyle(boderStyle);
//归还日期
row4.createCell(5).setCellValue("");
row4.getCell(5).setCellStyle(boderStyle);
//外借数量
row4.createCell(6).setCellValue("");
row4.getCell(6).setCellStyle(boderStyle);
//备注
row4.createCell(7).setCellValue("");
row4.getCell(7).setCellStyle(boderStyle);
}
//底部行
XSSFRow addreeRow = sheet.createRow(11);
addreeRow.createCell(1).setCellStyle(boderStyle);
addreeRow.createCell(2).setCellStyle(boderStyle);
addreeRow.createCell(3).setCellStyle(boderStyle);
addreeRow.createCell(4).setCellStyle(boderStyle);
addreeRow.createCell(5).setCellStyle(boderStyle);
addreeRow.createCell(6).setCellStyle(boderStyle);
addreeRow.createCell(7).setCellStyle(boderStyle);
sheet.addMergedRegion(new CellRangeAddress(11, 11, 0, 7));
addreeRow.createCell(0).setCellValue("地址/电话:");
addreeRow.getCell(0).setCellStyle(boderStyle);
XSSFRow row11 = sheet.createRow(12);
//仓库管理
if (StringUtil.isNotEmpty(name)) {
row11.createCell(0).setCellValue("仓库管理:" + name);
} else {
row11.createCell(0).setCellValue("仓库管理:" + wjdExportModel.getOldWareName());
}
row11.createCell(2).setCellValue("经办人:");
row11.createCell(4).setCellValue("客户:");
row11.createCell(6).setCellValue("外借日期:");
ExcelUtil.setResponseHeader(response, "外借.xlsx");
} else {
for (int i = 0; i < list.size(); i++) {
XSSFRow row4 = sheet.createRow(4 + i);
sheet.addMergedRegion(new CellRangeAddress(4 + i, 4 + i, 0, 2));
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
//产品名称
row4.createCell(2).setCellStyle(boderStyle);
row4.createCell(1).setCellStyle(boderStyle);
row4.createCell(0).setCellValue(list.get(i).getPName());
row4.getCell(0).setCellStyle(boderStyle);
//固定资产编号
row4.createCell(3).setCellValue(list.get(i).getProductCode());
row4.getCell(3).setCellStyle(boderStyle);
//外借日期
row4.createCell(4).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getOutDate()));
row4.getCell(4).setCellStyle(boderStyle);
//归还日期
if (wjdExportModel.getUntilTime() != null) {
row4.createCell(5).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(list.get(i).getUntilTime()));
row4.getCell(5).setCellStyle(boderStyle);
} else {
row4.createCell(5).setCellValue("");
row4.getCell(5).setCellStyle(boderStyle);
}
//外借数量
row4.createCell(6).setCellValue(list.get(i).getCount());
row4.getCell(6).setCellStyle(boderStyle);
//备注
row4.createCell(7).setCellValue(list.get(i).getRemark());
row4.getCell(7).setCellStyle(boderStyle);
}
//底部行
XSSFRow addreeRow = sheet.createRow(list.size() + 4);
addreeRow.createCell(1).setCellStyle(boderStyle);
addreeRow.createCell(2).setCellStyle(boderStyle);
addreeRow.createCell(3).setCellStyle(boderStyle);
addreeRow.createCell(4).setCellStyle(boderStyle);
addreeRow.createCell(5).setCellStyle(boderStyle);
addreeRow.createCell(6).setCellStyle(boderStyle);
addreeRow.createCell(7).setCellStyle(boderStyle);
sheet.addMergedRegion(new CellRangeAddress(list.size() + 4, list.size() + 4, 0, 7));
addreeRow.createCell(0).setCellValue("地址/电话:");
addreeRow.getCell(0).setCellStyle(boderStyle);
XSSFRow row11 = sheet.createRow( list.size() + 5);
//仓库管理
if (StringUtil.isNotEmpty(name)) {
row11.createCell(0).setCellValue("仓库管理:" + name);
} else {
row11.createCell(0).setCellValue("仓库管理:" + wjdExportModel.getOldWareName());
}
row11.createCell(2).setCellValue("经办人:");
row11.createCell(4).setCellValue("客户:");
row11.createCell(6).setCellValue("外借日期:");
ExcelUtil.setResponseHeader(response, "外借.xlsx");
}
//底部行
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
return ResponseBean.success("下载成功");
} catch (Exception e) {
e.printStackTrace();
return ResponseBean.error("下载异常");
}
}