POI
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF(用于操作Excel的组件)提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象,样式和格式,有以下几种常用的对象:
常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFCellStyle cell样式
导入jar包
compile group: ‘org.apache.poi’, name: ‘poi’, version: ‘4.0.1’
controller
@GetMapping("Exceldownload/{eventType}/{eventId}")
public String Exceldownload(@PathVariable("eventType") Integer eventType, @PathVariable("eventId") Long eventId, HttpServletRequest request, HttpServletResponse response) {
String pdfPath = baobiaoConfig.getPdfPath();
String chartPath = baobiaoConfig.getPicPath();
String excelPath =baobiaoConfig.getExcelPath();//设置导出路径
String fileName = "";
String chartENVName = "";
String chartSanqiName = "";
String warehouseAndSegmentName = "";
Date start = new Date();
Date end = new Date();
EventCommonInfo eventVo = new EventCommonInfo();
ShuifenEvent shuifenEvent = new ShuifenEvent();
XunzhenEvent xunzhenEvent = new XunzhenEvent();
RukuwenduEvent rukuwenduEvent = new RukuwenduEvent();
ZaikubaowenEvent zaikubaowenEvent = new ZaikubaowenEvent();
ZaikuwenduEvent zaikuwenduEvent = new ZaikuwenduEvent();
ZaikushiduEvent zaikushiduEvent = new ZaikushiduEvent();
if (eventType.equals(EventType.XUNZHEN.getNumber())) {
eventVo = xunzhenEventVoDao.getXunzhenEventVoById(eventId);
xunzhenEvent = xunzhenEventDao.getById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_XUNZHEN.xlsx";// 文件名
} else if (eventType.equals(EventType.RUKUSHUIFEN.getNumber())) {
eventVo = shuifenEventVoDao.getShuifenEventVoById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
shuifenEvent = shuifenEventDao.getById(eventId);
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_SHUIFEN.xls";
} else if (eventType.equals(EventType.RUKUWENDU.getNumber())) {
eventVo = rukuwenduEventVoDao.getRukuwenduEventVoById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
rukuwenduEvent = rukuwenduEventDao.getById(eventId);
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_RUKUBAOWEN.xls";
} else if (eventType.equals(EventType.ZAIKUBAOWEN.getNumber())) {
eventVo = zaikubaowenEventVoDao.getZaikubaowenEventVoById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
zaikubaowenEvent = zaikubaowenEventDao.getById(eventId);
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_ZAIKUBAOWEN.xlsx";
} else if (eventType.equals(EventType.ZAIKUWENDU.getNumber())) {
eventVo = zaikuwenduEventVoDao.getZaikuwenduEventVoById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
zaikuwenduEvent = zaikuwenduEventDao.getById(eventId);
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_ZAIKUWENDU.xls";
} else if (eventType.equals(EventType.ZAIKUSHIDU.getNumber())) {
eventVo = zaikushiduEventVoDao.getZaikushiduEventVoById(eventId);
warehouseAndSegmentName = eventVo.getWarehouseName() + eventVo.getSegmentName() + "仓";
zaikushiduEvent = zaikushiduEventDao.getById(eventId);
start = eventVo.getStartTime();
end = eventVo.getEndTime();
String startStr = DateUtils.parseDateToString(start, DateUtils.DATE_FORMAT_COMPACTFULL);
String endStr = DateUtils.parseDateToString(end, DateUtils.DATE_FORMAT_COMPACTFULL);
fileName = warehouseAndSegmentName + "_" + startStr + "_" + endStr + "_ZAIKUSHIDU.xls";
}
File file = new File(excelPath + fileName);
if (!file.exists()) {
//文件不存在就生成
if (eventType.equals(EventType.XUNZHEN.getNumber())) {
generateXunzhenExcel(eventId, excelPath, fileName, chartPath, chartENVName, chartSanqiName, eventVo, xunzhenEvent);
} else if (eventType.equals(EventType.RUKUSHUIFEN.getNumber())) {
generateShuifenPdf(eventId, pdfPath, fileName, chartPath, chartENVName, eventVo, shuifenEvent);
} else if (eventType.equals(EventType.RUKUWENDU.getNumber())) {
generateRukubaowenPdf(eventId, pdfPath, fileName, chartPath, chartENVName, eventVo, rukuwenduEvent);
} else if (eventType.equals(EventType.ZAIKUBAOWEN.getNumber())) {
generateZaikubaowenPdf(eventId, pdfPath, fileName, chartPath, chartENVName, eventVo, zaikubaowenEvent);
} else if (eventType.equals(EventType.ZAIKUWENDU.getNumber())) {
generateZaikuwenduPdf(eventId, pdfPath, fileName, chartPath, chartENVName, eventVo, zaikuwenduEvent);
} else if (eventType.equals(EventType.ZAIKUSHIDU.getNumber())) {
generateZaikushiduPdf(eventId, pdfPath, fileName, chartPath, chartENVName, eventVo, zaikushiduEvent);
}
}
response.setContentType("application/force-download");// 设置强制下载不打开
response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);// 设置文件名
byte[] buffer = new byte[1024];//文件不能过小,会导致下载后打不开
FileInputStream fis = null;
BufferedInputStream bis = null;
try {//将生成的文件在浏览器下载
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
return "success";
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return "failed";
}
生成Excel
public class GenerateExcelService {
public static void generateXunzhenBaogao(XunzhenBaobiaoInfo xunzhenBaobiaoInfo) {
HSSFWorkbook xunzhenexcel =new HSSFWorkbook();
try {
FileOutputStream output=new FileOutputStream(xunzhenBaobiaoInfo.getFilePath()+xunzhenBaobiaoInfo.getFileName());//设置流输出的路径
HSSFSheet sheet = xunzhenexcel.createSheet("熏蒸表格");
sheet.setColumnWidth((short) 0, (short) (200*80));//设置列的宽度
sheet.setColumnWidth((short) 1, (short) (50*80));
sheet.setColumnWidth((short) 2, (short) (50*80));
sheet.setColumnWidth((short) 3, (short) (50*80));
sheet.setColumnWidth((short) 4, (short) (50*80));
HSSFRow titleRow = sheet.createRow(0);
titleRow.setHeight((short) 500);
HSSFCell titleCell1 = titleRow.createCell(0); //在行中创建第1个单元格
titleCell1.setCellValue("文件名"); //设置第1个单元格的值
HSSFCellStyle cs = xunzhenexcel.createCellStyle();
HSSFFont f = xunzhenexcel.createFont();
f.setFontName("宋体");
f.setFontHeightInPoints((short) 10);
f.setBold(true);
cs.setFont(f);
cs.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cs.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cs.setLocked(true);
cs.setWrapText(true);//自动换行
cs.setFillBackgroundColor((short) 43);
titleCell1.setCellStyle(cs);
HSSFCell titleCell2= titleRow.createCell(1); //在行中创建第2个单元格
titleCell2.setCellStyle(cs);
titleCell2.setCellValue("仓库和仓间名"); //设置第2个单元格的值
HSSFCell titleCell3 =titleRow .createCell(2); //在行中创建第3个单元格
titleCell3.setCellStyle(cs);
titleCell3.setCellValue("熏蒸时间范围"); //设置第3个单元格的值
HSSFCell titleCell4= titleRow.createCell(3); //在行中创建第4个单元格
titleCell4.setCellStyle(cs);
titleCell4.setCellValue("仓间信息"); //设置第4个单元格的值
HSSFCell titleCell5= titleRow.createCell(4); //在行中创建第5个单元格
titleCell5.setCellStyle(cs);
titleCell5.setCellValue("管理员"); //设置第5个单元格的值
HSSFRow valueRow = sheet.createRow(1); //创建第2行
HSSFCell nameCell = valueRow.createCell(0); //在第2行中创建单元格
nameCell.setCellStyle(cs);
nameCell.setCellValue(xunzhenBaobiaoInfo.getFileName());
HSSFCell pwdCell = valueRow.createCell(1);
pwdCell.setCellStyle(cs);
pwdCell.setCellValue(xunzhenBaobiaoInfo.getWarehouseAndSegmentName());
HSSFCell sexCell = valueRow.createCell(2);
sexCell.setCellValue(xunzhenBaobiaoInfo.getXunzhenTimeRange());
sexCell.setCellStyle(cs);
HSSFCell ageCell = valueRow.createCell(3);
ageCell.setCellValue(xunzhenBaobiaoInfo.getSegmentInfo());
ageCell.setCellStyle(cs);
HSSFCell emailCell = valueRow.createCell(4);
emailCell.setCellValue(xunzhenBaobiaoInfo.getChargeName());
emailCell.setCellStyle(cs);
HSSFRow titleRow1 = sheet.createRow(2);
HSSFCell atitleCell1 = titleRow1.createCell(0); //在行中创建第1个单元格
atitleCell1.setCellStyle(cs);
atitleCell1.setCellValue("重量"); //设置第1个单元格的值
HSSFCell atitleCell2= titleRow1.createCell(1); //在行中创建第2个单元格
atitleCell2.setCellStyle(cs);
atitleCell2.setCellValue("熏蒸类型"); //设置第2个单元格的值
HSSFCell atitleCell3 =titleRow1.createCell(2); //在行中创建第3个单元格
atitleCell3.setCellStyle(cs);
atitleCell3.setCellValue("maxPPM"); //设置第3个单元格的值
HSSFCell atitleCell4= titleRow1.createCell(3); //在行中创建第4个单元格
atitleCell4.setCellStyle(cs);
atitleCell4.setCellValue("averagePPM"); //设置第4个单元格的值
HSSFCell atitleCell5= titleRow1.createCell(4); //在行中创建第5个单元格
atitleCell5.setCellStyle(cs);
atitleCell5.setCellValue("hint"); //设置第5个单元格的值
HSSFRow valueRow1 = sheet.createRow(3); //创建第2行
HSSFCell anameCell = valueRow1.createCell(0);
anameCell.setCellValue(xunzhenBaobiaoInfo.getWeight());
anameCell.setCellStyle(cs);
HSSFCell apwdCell = valueRow1.createCell(1);
anameCell.setCellStyle(cs);
apwdCell.setCellValue(xunzhenBaobiaoInfo.getHzsType());
HSSFCell asexCell = valueRow1.createCell(2);
asexCell.setCellStyle(cs);
asexCell.setCellValue("maxPPM");
HSSFCell aageCell = valueRow1.createCell(3);
aageCell.setCellStyle(cs);
aageCell.setCellValue("averagePPM");
HSSFCell aemailCell = valueRow1.createCell(4);
aemailCell.setCellStyle(cs);
aemailCell.setCellValue("hint");
xunzhenexcel.write(output);//输出excel
xunzhenexcel.close();
output.flush();
output.close();//关闭流
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
目前存在问题
Excel可以正常导出,但大小较小
同时浏览器下载的Excel无法打开
但如果打开java导出的excel文件,进行保存操作,大小会变大
同时再次使用浏览器下载,文件可以正常下载,并打开
分析问题
新建一个xls文件大小也比较小,如果将新建文件名改为对应接口浏览器下载的文件名,同样无法正常下载
完成修改文件名
下载后同样无法正常打开
当同样如果打开文件进行保存操作,文件大小发生变化
浏览器下载也可以正常打开
感觉问题应该是新建文件没有进行保存,处于一种特使状态,无法正常读取,但查阅文档暂时没有发现生成excel文件同时保存的API
暂定思路
这个问题目前感觉和代码的关系不大(不排除水平不足没有发现),想出一个解决方案:用户在选择下载Excel文件是,自主现在保存路径,并将路径传给后端,这样用户就可以直接拿到java导出的Excel表格,也就不存在上述问题。