使用POI导出Excel文件后打开文件提示因为文件格式或文件扩展名无效而无法打开
问题
当我们通过POI导出Excel文件后,发现文件出错打不开,如下所示
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/5d359c6ba4146ac8e794fb366c073222.png)
原因
这是因为我们代码中 这三个的应用应该和我们的office表格的版本有关系。
HSSFWorkBook:适用于Excel03版本的,文件扩展名只能为.xls;
XSSFWorkBook:适用于Excel07版本及以后,文件扩展名为.xlsx或者.xls这两个都行;
SXSSFWorkBook:XSSFWorkBook升级;
所以 ,我们可以通过查看我们电脑的版本来选择,当我们office版本为03时,我们就用HSSFWorkBook创建,
当我们的Office版本为07以后的话我们就用XSSFWorkBook来创建对象。
-
2003(在导出时均需使用HSSF相关方法(后缀名仅可为.xls, 如果设置为".xlsx",会提示文件已损坏无法打开))
@Override
public Workbook exportExcel() {
Workbook workbook = new HSSFWorkBook();
Sheet sheet = workbook.createSheet("题库");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("题目标题");
row.createCell(1).setCellValue("题目解答");
row.createCell(2).setCellValue("题目难度等级");
row.createCell(3).setCellValue("排序");
row.createCell(4).setCellValue("副标题");
row.createCell(5).setCellValue("题目类型");
row.createCell(6).setCellValue("是否显示");
List<QuestionEntity> list = this.list();
for (int i = 0; i < list.size(); i++) {
Row row2 = sheet.createRow(i + 1);
row2.createCell(0).setCellValue(list.get(i).getTitle());
row2.createCell(1).setCellValue(list.get(i).getAnswer());
row2.createCell(2).setCellValue(list.get(i).getLevel());
row2.createCell(3).setCellValue(list.get(i).getDisplayOrder());
row2.createCell(4).setCellValue(list.get(i).getSubTitle());
row2.createCell(5).setCellValue(list.get(i).getType());
row2.createCell(6).setCellValue(list.get(i).getEnable());
}
return workbook;
}
@GetMapping("exportExcel")
public void export(String tableName, HttpServletResponse response){
System.out.println("导出excele");
Workbook workbook = questionService.exportExcel();
if (workbook != null) {
String fileName = "uxue_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
response.setHeader("Content-Disposition", "attachment; filename=" + fileName );
response.setContentType("application/binary;charset=ISO8859_1");
response.setCharacterEncoding("GB2312");
OutputStream outputStream;
try {
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
response.getWriter().print("error");
} catch (Exception e) {
e.printStackTrace();
}
}
}
-
2007(在导出时使用XSSF相关方法(后缀名为.xls和.xlsx均可))
@Override
public Workbook exportExcel() {
Workbook workbook = new XSSFWorkBook();
Sheet sheet = workbook.createSheet("题库");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("题目标题");
row.createCell(1).setCellValue("题目解答");
row.createCell(2).setCellValue("题目难度等级");
row.createCell(3).setCellValue("排序");
row.createCell(4).setCellValue("副标题");
row.createCell(5).setCellValue("题目类型");
row.createCell(6).setCellValue("是否显示");
List<QuestionEntity> list = this.list();
for (int i = 0; i < list.size(); i++) {
Row row2 = sheet.createRow(i + 1);
row2.createCell(0).setCellValue(list.get(i).getTitle());
row2.createCell(1).setCellValue(list.get(i).getAnswer());
row2.createCell(2).setCellValue(list.get(i).getLevel());
row2.createCell(3).setCellValue(list.get(i).getDisplayOrder());
row2.createCell(4).setCellValue(list.get(i).getSubTitle());
row2.createCell(5).setCellValue(list.get(i).getType());
row2.createCell(6).setCellValue(list.get(i).getEnable());
}
return workbook;
}
@GetMapping("exportExcel")
public void export(String tableName, HttpServletResponse response){
System.out.println("导出excele");
Workbook workbook = questionService.exportExcel();
if (workbook != null) {
String fileName = "uxue_" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xlsx";
response.setHeader("Content-Disposition", "attachment; filename=" + fileName );
response.setContentType("application/binary;charset=ISO8859_1");
response.setCharacterEncoding("GB2312");
OutputStream outputStream;
try {
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
response.getWriter().print("error");
} catch (Exception e) {
e.printStackTrace();
}
}
}
就这样我们的问题成功解决了!!!!