Java原生POI实现的Excel导入导出（简单易懂）

1.首先需要引入jar包

<dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.15</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.15</version>        </dependency>

2.导入接口

首先是Controller入口方法

import org.springframework.WEB.multipart.MultipartFile;@RequestMapping(value = "importexcel", method = RequestMethod.POST)@ResponseBodypublic Map importExcel(@ModelAttribute MultipartFile file) throws Exception {return schoolService.importExcel(file);}

这个接口在postman上传参是下面这样的：
注意里面的参数名称要和接口上的一致，不然会拿不到值
还有file那里key的类型要选file类型的，这样就可以在后面value里面选择文件

然后是Service方法

import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import java.io.IOException;@Overridepublic void importExcel(MultipartFile file) throws Exception {Workbook wb = null;try {if (file != null) {// 兼容Excel新版本和老版本，xlsx和xls不兼容wb = getWorkbook(file);// 获取第一页数据Sheet sheet = wb.getSheetAt(0);// 获取第一页总行数int rowCount = sheet.getPhysicalNumberOfRows();// 开始循环取出每一行的数据，-1是因为取值时要去掉抬头for (int i = 0; i < rowCount - 1; i++) {// 从第0行开始，因为第0行一般是抬头所以直接从下面一行开始Row row = sheet.getRow(i + 1);// 行数计数器int tempNum = i + 2;// 取出每一列的值，从第0列开始Cell cellname = row.getCell(0);Cell cellmobile = row.getCell(1);String name = "";// 姓名String mobile = "";// 手机号if (cellname != null) {cellname.setCellType(CellType.STRING);name = cellname.getStrinGCellValue().trim();if (StringUtil.isEmpty(name)) {throw new BizException(500, "第" + tempNum + "行姓名不能为空");}}if (cellmobile != null) {cellmobile.setCellType(CellType.STRING);mobile = cellmobile.getStringCellValue().trim();if (StringUtil.isEmpty(mobile)) {throw new BizException(500, "第" + tempNum + "行手机号不能为空");}}}} else {log.info("++++++++++++传入文件为空+++++++++++++");throw new BizException(500, "传入文件为空");}} catch (BizException e) {throw new BizException(500, e.getMsg());} catch (Exception e) {e.printStackTrace();throw new BizException(500, "文件信息异常");} finally {if (wb != null) {try {wb.close();} catch (IOException e) {e.printStackTrace();}}}}// 兼容Excel新版本和老版本，xlsx和xls不兼容public static Workbook getWorkbook(MultipartFile file) throws IOException {Workbook workbook = null;String fileName = file.getOriginalFilename();if (fileName.endsWith("xls")) {POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());workbook = new HSSFWorkbook(pois);} else if (fileName.endsWith("xlsx")) {workbook = new XSSFWorkbook(file.getInputStream());}return workbook;}

3.导出接口

首先是Controller入口
strJSON是用来接受其它参数的，一般导出的时候会带着一些查询条件

@RequestMapping(value = "exportExcel", method = RequestMethod.POST)@ResponseBodypublic void exportExcel(httpservletRequest request, HttpServletResponse response, @RequestBody String strjson) throws Exception {schoolService.exportExcel(request, response, strJson);}

然后是Service方法

@Overridepublic void exportExcel(HttpServletRequest request, HttpServletResponse response, String strJson) throws Exception {try (Workbook wb = new SXSSFWorkbook(1000)) {// 构建一个导出页Sheet sheet = wb.createSheet("用户信息表");Row row = sheet.createRow((int) 0);row.setHeight((short) 1000);CellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setWrapText(true);// 自动换行Font font = wb.createFont();font.setFontName("宋体");font.setFontHeightInPoints((short) 11);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);style.setFont(font);// 构建excel抬头String[] excelHeader = new String[10];excelHeader[0] = "姓名";excelHeader[1] = "电话";for (int i = 0; i < excelHeader.length; i++) {Cell cell = row.createCell(i);cell.setCellValue(excelHeader[i]);cell.setCellStyle(style);sheet.setColumnWidth(i, 20 * 256);// 列宽}// 调用导出数据获取接口，拿到要导出的数据List<Map<String, Object>> exportDatas = findExportDatas (strJson);for (int i = 0; i < exportDatas .size(); i++) {row = sheet.createRow(i + 1);row.setHeight((short) 700);// 创建列对象Cell cell0 = row.createCell(0);Cell cell1 = row.createCell(1);// 将值放入列中// 姓名if (exportDatas.get(i).get("name") != null) {cell0.setCellValue(exportDatas.get(i).get("name").toString());} else {cell0.setCellValue("");}// 电话if (exportDatas.get(i).get("mobile") != null) {cell1.setCellValue(exportDatas.get(i).get("mobile").toString());} else {cell1.setCellValue("");}cell0.setCellStyle(style);cell1.setCellStyle(style);String fileName = "用户信息表.xlsx";if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器} else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {fileName = URLEncoder.encode(fileName, "UTF-8");// IE浏览器} else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// 谷歌}response.reset();response.setContentType("application/octet-stream; charset=utf-8");response.setHeader("Access-Control-Allow-Origin", "*");response.setHeader("Content-Disposition", "attachment; filename=" + fileName);wb.write(response.getOutputStream());response.getOutputStream().close();}}

最后是用postman导出测试
点击Send旁边的箭头，然后点下面的Send and Download就可以导出了

来源地址：https://blog.csdn.net/cl617287/article/details/131739434