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

短命女 2023-10-13 17:46 23阅读 0赞

### 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)
    	@ResponseBody
    	public Map importExcel(@ModelAttribute MultipartFile file) throws Exception {
        
    		return schoolService.importExcel(file);
    	}

这个接口在postman上传参是下面这样的：  
注意里面的参数名称要和接口上的一致，不然会拿不到值  
还有file那里key的类型要选file类型的，这样就可以在后面value里面选择文件  
![在这里插入图片描述][2f9ebd6ce54541e2a41af78c1f5351fb.png]  
然后是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;
    
    
    
    	@Override
    	public 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)
    	@ResponseBody
    	public void exportExcel(HttpServletRequest request, HttpServletResponse response, @RequestBody String strJson) throws Exception {
        
    		schoolService.exportExcel(request, response, strJson);
    	}

然后是Service方法

@Override
    	public 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就可以导出了  
![在这里插入图片描述][38a7ef04e8cf425daf4d31c07404ce6d.png]

[2f9ebd6ce54541e2a41af78c1f5351fb.png]: https://img-blog.csdnimg.cn/2f9ebd6ce54541e2a41af78c1f5351fb.png
[38a7ef04e8cf425daf4d31c07404ce6d.png]: https://img-blog.csdnimg.cn/38a7ef04e8cf425daf4d31c07404ce6d.png