Excel的导入导出功能
POI组件的详细介绍文档:
https://www.cnblogs.com/huajiezh/p/5467821.html
.xls 对应 HSSFWorkbook book=new HSSFWorkbook(io);
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>
.xlsx 对应 XSSFWorkbook book=new XSSFWorkbook(io))
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
controller
package com.test.domi.controller; import com.test.domi.annotation.IsFileWanner; import org.apache.poi.xssf.usermodel.*; import org.springframework.validation.annotation.Validated; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import javax.validation.ConstraintViolationException; import java.io.OutputStream; @Validated @RestController @RequestMapping("/excel") public class ExcelController { @GetMapping("/download") public void download(HttpServletResponse response) throws Exception{ XSSFWorkbook workbook = new XSSFWorkbook(); //获取文档信息,并配置 // DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation(); //创建一个Excel表单,参数为sheet的名字 XSSFSheet sheet = workbook.createSheet("课调答卷表"); //创建表头 setTitle(workbook, sheet); //新增数据行,并且设置单元格数据 int rowNum = 1; for (int i=0;i<2;i++) { XSSFRow row = sheet.createRow(rowNum); row.createCell(0).setCellValue(1); row.createCell(1).setCellValue(2); row.createCell(2).setCellValue(3); row.createCell(3).setCellValue(4); rowNum++; } String fileName = "survey-answer"; response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName + ".xlsx"); response.setContentType("application/vnd.ms-excel;charset=utf-8"); //清空response //response.reset(); //设置response的Header //OutputStream os = new BufferedOutputStream(response.getOutputStream()); OutputStream os = response.getOutputStream(); //将excel写入到输出流中 workbook.write(os); //finally关闭流 os.flush(); os.close(); } /*** * 设置表头 * @param workbook * @param sheet */ private void setTitle(XSSFWorkbook workbook, XSSFSheet sheet){ XSSFRow row = sheet.createRow(0); //设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度 sheet.setColumnWidth(0, 10*256); sheet.setColumnWidth(1, 20*256); sheet.setColumnWidth(2, 20*256); sheet.setColumnWidth(3, 100*256); //设置为居中加粗 XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setBold(true); style.setFont(font); XSSFCell cell; cell = row.createCell(0); cell.setCellValue("序号"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("单选"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("多选"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("简答"); cell.setCellStyle(style); } /*** * 上传Excel * @param file */ @ExceptionHandler(ConstraintViolationException.class) @PostMapping("/importEmp") public Boolean importEmp(@RequestParam("file") @IsFileWanner(fileTypes = {"xls","xlsx"},fileSize = 113L) MultipartFile file) throws Exception{ //附件类型的注解校验封装 //判断文件的后缀,用响应的解析类解析 if (file==null) { return false; } try { XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); int sheets = workbook.getNumberOfSheets(); for (int i = 0; i < sheets; i++) { XSSFSheet sheetAt = workbook.getSheetAt(i); int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows(); System.out.println("nihao"); } } catch (Exception e) { return false; } return true; } }