使用POI导出Excel文件后打开文件提示因为文件格式或文件扩展名无效而无法打开
问题原因(在导出时均需使用HSSF相关方法(后缀名仅可为.xls, 如果设置为".xlsx",会提示文件已损坏无法打开))(在导出时使用XSSF相关方法(后缀名为.xls和.xlsx均可)) 就这样我们的问题成功解决了!!!!问题
当我们通过POI导出Excel文件后,发现文件出错打不开,如下所示
原因
这是因为我们代码中 这三个的应用应该和我们的office表格的版本有关系。HSSFWorkBook:适用于Excel03版本的,文件扩展名只能为.xls;XSSFWorkBook:适用于Excel07版本及以后,文件扩展名为.xlsx或者.xls这两个都行;SXSSFWorkBook:XSSFWorkBook升级;所以 ,我们可以通过查看我们电脑的版本来选择,当我们office版本为03时,我们就用HSSFWorkBook创建,当我们的Office版本为07以后的话我们就用XSSFWorkBook来创建对象。
(在导出时均需使用HSSF相关方法(后缀名仅可为.xls, 如果设置为".xlsx",会提示文件已损坏无法打开))
@Overridepublic Workbook exportExcel() {// 创建新的Excel 工作簿Workbook workbook = new HSSFWorkBook();// 在Excel工作簿中建一工作表,其名为缺省值 Sheet0//Sheet sheet = workbook.createSheet();// 创建工作表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;}
//导出excel@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.setHeader("Content-Disposition", "attachment; filename=" + fileName );// response.setContentType("application/octet-stream;charset=GB2312");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();}}}
(在导出时使用XSSF相关方法(后缀名为.xls和.xlsx均可))
@Overridepublic Workbook exportExcel() {// 创建新的Excel 工作簿Workbook workbook = new XSSFWorkBook();// 在Excel工作簿中建一工作表,其名为缺省值 Sheet0//Sheet sheet = workbook.createSheet();// 创建工作表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;}
//导出excel@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";//这里我们的.xls或者xlsx都可以// response.setHeader("Content-Disposition", "attachment;filename=" + fileName);response.setHeader("Content-Disposition", "attachment; filename=" + fileName );// response.setContentType("application/octet-stream;charset=GB2312");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();}}}
就这样我们的问题成功解决了!!!!
POI导出失败(使用POI导出Excel文件后打开文件提示因为文件格式或文件扩展名无效而无法打开)
如果觉得《POI导出失败(使用POI导出Excel文件后打开文件提示因为文件格式或文件扩展名无效而无》对你有帮助,请点赞、收藏,并留下你的观点哦!