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里面选择文件
然后是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就可以导出了
