首先在pom.xml 中引用apache poi
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
Controller 代码
@RequestMapping(value="/download",method=RequestMethod.POST) public Object download(Register re, HttpServletRequest request, HttpServletResponse response) { log.info("export="+re); try { Register reRes = service.getRegisterById(re.getId()+""); XSSFWorkbook workbook = null; String fileName="xxx.xlsx"; //已经存在文件放在webapp目录下 String fileNamePath=request.getRealPath("/")+fileName; if (reRes != null) { workbook = exportRegisterFormToExcel(fileNamePath,reRes); } if (workbook != null) { ExcelImportUtils.downloadExcel(workbook, fileName, request, response); }else{ ModelAndView view = new ModelAndView("error"); return view; } } catch (Exception e) { e.printStackTrace(); } return null; } private XSSFWorkbook exportRegisterFormToExcel(String finalXlsxPath,Register re) { OutputStream out = null; //这需要需要注意,这里使用的是 XSSFWorkbook,不是 SXSSFWorkbook //import org.apache.poi.xssf.streaming.SXSSFWorkbook; //import org.apache.poi.xssf.usermodel.XSSFWorkbook; XSSFWorkbook workBook=null; try { File finalXlsxFile = new File(finalXlsxPath); FileInputStream in = new FileInputStream(finalXlsxFile); workBook = new XSSFWorkbook(in); Sheet sheet = workBook.getSheetAt(0); Row row =sheet.getRow(3); Cell cell = row.getCell(1); cell.setCellValue(re.getUserName()); row.getCell(3).setCellValue(re.getNickName()); row.getCell(5).setCellValue(re.getGender()==0?"男":"女"); row.getCell(7).setCellValue(re.getBirthday()); // ........ //根据自己的需要把数据 写入到excel表格中 //下面这一句必须要 out = new FileOutputStream(finalXlsxPath); workBook.write(out); } catch (Exception e) { e.printStackTrace(); }finally{ try { if(out != null){ out.flush(); out.close(); } } catch (IOException e) { e.printStackTrace(); } } return workBook; }
这需要需要注意,这里使用的是 XSSFWorkbook,不是 SXSSFWorkbook,导入的包也不一样。
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;