Java原生POI实现的Excel导入导出（简单易懂）-Toy模板网

这篇具有很好参考价值的文章主要介绍了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)
	@ResponseBody
	public Map importExcel(@ModelAttribute MultipartFile file) throws Exception {
		return schoolService.importExcel(file);
	}

这个接口在postman上传参是下面这样的：
注意里面的参数名称要和接口上的一致，不然会拿不到值
还有file那里key的类型要选file类型的，这样就可以在后面value里面选择文件
poi导入excel数据,java,excel,开发语言
然后是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();
		}
	}