在我们的工作中, 也经常会用到Poi模板技术操作Excel文件数据, 而且不同的应用场景对Excel表格的单元格格式和合并操作都需要写大量的代码调试, 比较麻烦. 通过模板技术将自定义格式的Excel文件模板存放在服务器指定位置, 然后读取数据或者填充数据都使用该模板的样式, 不用自己去编写代码设置样式, 使用非常方便. 同样的, 本篇不会写入门案例, 只是记录自己工作或学习中封装的工具方法.
说明: 以下代码基于poi-3.17版本实现, poi-3.17及以上版本相比3.17以下版本样式设置的api改动比较大, 可能存在数据类型获取api过时或报错等, 请参考 Poi版本升级优化
借鉴EasyPoi和EasyExcel的使用方式, 都通过注解开发方法来导出指定的字段或读取指定的属性数据. 下面我也自定义了一个简单的注解类.
com.poi.anno.ExcelAttr
package com.poi.anno;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义注解, 用于标识需要导出到Excel文件的属性字段
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttr {
int sort() default 0;
}
将导入或导出的数据与pojo实体类关联, 因为要写getter, setter方法, 我使用lombok自动生成.
lombok依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<optional>true</optional>
</dependency>
com.poi.entity.Employee
package com.poi.entity;
import com.poi.anno.ExcelAttr;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 类描述：数据模型, 员工信息
* @Author wang_qz
* @Date 2021/8/14 10:11
* @Version 1.0
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Employee {
@ExcelAttr(sort = 0)
private int id;
@ExcelAttr(sort = 1)
private String name;
@ExcelAttr(sort = 2)
private String empno;
@ExcelAttr(sort = 3)
private int age;
@ExcelAttr(sort = 4)
private String sex;
@ExcelAttr(sort = 5)
private String job;
@ExcelAttr(sort = 6)
private int departid;
}
com.poi.util.PoiTemplateUtil#importExcel
public List<T> importExcel(Map<String, Object> params, InputStream inputStream, Class<T> clazz) throws Exception {
String fileType = (String) params.get("fileType");
fileType = Objects.equals("xls", fileType) ? fileType : "xlsx";
Workbook workbook = Objects.equals("xls", fileType) ? new HSSFWorkbook(inputStream)
: new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
List<T> dataList = new ArrayList<>();
for (int i = 1; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
// 获取单元格的值并存入objs中
Object[] objs = new Object[lastCellNum];
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
Object value = getCellValue(cell);
objs[j] = value;
}
T t = clazz.newInstance();
// 反射设置属性值
Field[] declaredFields = clazz.getDeclaredFields();
for (int k = 0; k < declaredFields.length; k++) {
Field field = declaredFields[k];
field.setAccessible(true);
// 获取被自定义注解ExcelAttr标识的字段
ExcelAttr anno = field.getAnnotation(ExcelAttr.class);
if (anno != null) {
// 获取设置的字段排序
int sort = anno.sort();
if (sort == k) {
String fieldType = field.getType().getName();
final Object value = objs[k];
if (Objects.equals("int", fieldType) ||
Objects.equals("Integer", fieldType)) {
field.setInt(t, Integer.parseInt(value.toString()));
} else if (Objects.equals("double", fieldType) ||
Objects.equals("Double", fieldType)) {
field.setDouble(t, Double.parseDouble(value.toString()));
} else {
field.set(t, value);
}
}
}
}
dataList.add(t);
}
return dataList;
}
com.poi.util.PoiTemplateUtil#getCellValue
/**
* 获取单元格的不同数据类型的值
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
Object cellValue = ""; // 每个单元格的值
if (cell != null) {
// 数据类型判断, 获取不同类型的数据
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字类型
// 日期格式的数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 日期
cellValue = cell.getDateCellValue();
} else {
// 不是日期格式的数字, 返回字符串格式
cellValue = cell.getRichStringCellValue().toString();
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // 布尔
cellValue = cell.getBooleanCellValue();
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空
cellValue = "";
break;
case ERROR: // 错误
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
com.poi.util.PoiTemplateUtil#exportExcel
/**
* 根据模板填充数据生成excel文件
* @param params
* @param outputStream
* @throws Exception
*/
public void exportExcel(@NonNull Map<String, Object> params, OutputStream outputStream) throws Exception {
List<T> datas = (List<T>) params.get("datas");
// "template/excel/template.xls"
String template = (String) params.get("template");
int templateDataRowIndex = (int) params.get("templateDataRowIndex");
String fileType = template.substring(template.lastIndexOf(".") + 1);
fileType = Objects.equals("xls", fileType) ? fileType : "xlsx";
InputStream templateStream = PoiTemplateUtil.class.getClassLoader()
.getResourceAsStream(template);
Workbook workbook = Objects.equals("xls", fileType) ? new
HSSFWorkbook(templateStream) : new XSSFWorkbook(templateStream);
// 获取模板sheet
Sheet sheet = workbook.getSheetAt(0);
// 获取模板数据行
Row templateDataRow = sheet.getRow(templateDataRowIndex);
// 将模板数据行样式用CellStyle数组存起来,后面填充数据用到该模板数据样式
CellStyle[] cellStyles = new CellStyle[templateDataRow.getLastCellNum()];
for (int i = 0; i < cellStyles.length; i++) {
cellStyles[i] = templateDataRow.getCell(i).getCellStyle();
}
// 填充数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行-跟随模板
Row row = sheet.createRow(i + templateDataRowIndex);
T t = datas.get(i);
Field[] declaredFields = t.getClass().getDeclaredFields();
for (int j = 0; j < cellStyles.length; j++) {
// 创建单元格
Cell cell = row.createCell(j);
// 设置单元格样式
cell.setCellStyle(cellStyles[j]);
for (Field field : declaredFields) {
// 获取被@ExcelAttr注解标注且注解的sort=j的的属性
field.setAccessible(true);
ExcelAttr anno = field.getAnnotation(ExcelAttr.class);
if (anno != null) {
int sort = anno.sort();
if (sort == j) {
// 设置单元格的值
cell.setCellValue(field.get(t).toString());
}
}
}
}
}
// 生成excel文件到指定位置
workbook.write(outputStream);
}
com.poi.util.PoiTemplateUtil
package com.poi.util;
import com.poi.anno.ExcelAttr;
import com.poi.entity.Employee;
import lombok.NonNull;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* 类描述：模板导出excel工具类
* @Author wang_qz
* @Date 2021/8/14 10:30
* @Version 1.0
*/
public class PoiTemplateUtil<T> {
/**
* 根据模板填充数据生成excel文件
* @param params
* @param outputStream
* @throws Exception
*/
public void exportExcel(@NonNull Map<String, Object> params,
OutputStream outputStream) throws Exception {
List<T> datas = (List<T>) params.get("datas");
// "template/excel/template.xls"
String template = (String) params.get("template");
int templateDataRowIndex = (int) params.get("templateDataRowIndex");
String fileType = template.substring(template.lastIndexOf(".") + 1);
fileType = Objects.equals("xls", fileType) ? fileType : "xlsx";
InputStream templateStream = PoiTemplateUtil.class.getClassLoader()
.getResourceAsStream(template);
Workbook workbook = Objects.equals("xls", fileType) ?
new HSSFWorkbook(templateStream) : new XSSFWorkbook(templateStream);
// 获取模板sheet
Sheet sheet = workbook.getSheetAt(0);
// 获取模板数据行
Row templateDataRow = sheet.getRow(templateDataRowIndex);
// 将模板数据行样式用CellStyle数组存起来,后面填充数据用到该模板数据样式
CellStyle[] cellStyles = new CellStyle[templateDataRow.getLastCellNum()];
for (int i = 0; i < cellStyles.length; i++) {
cellStyles[i] = templateDataRow.getCell(i).getCellStyle();
}
// 填充数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行-跟随模板
Row row = sheet.createRow(i + templateDataRowIndex);
T t = datas.get(i);
Field[] declaredFields = t.getClass().getDeclaredFields();
for (int j = 0; j < cellStyles.length; j++) {
// 创建单元格
Cell cell = row.createCell(j);
// 设置单元格样式
cell.setCellStyle(cellStyles[j]);
for (Field field : declaredFields) {
// 获取被@ExcelAttr注解标注且注解的sort=j的的属性
field.setAccessible(true);
ExcelAttr anno = field.getAnnotation(ExcelAttr.class);
if (anno != null) {
int sort = anno.sort();
if (sort == j) {
// 设置单元格的值
cell.setCellValue(field.get(t).toString());
}
}
}
}
}
// 生成excel文件到指定位置
workbook.write(outputStream);
}
/**
* 大数据模板导入
* @param params
* @param inputStream
* @param clazz
* @return
* @throws Exception
*/
public List<T> importExcel(Map<String, Object> params, InputStream inputStream,
Class<T> clazz) throws Exception {
String fileType = (String) params.get("fileType");
fileType = Objects.equals("xls", fileType) ? fileType : "xlsx";
Workbook workbook = Objects.equals("xls", fileType) ?
new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
List<T> dataList = new ArrayList<>();
for (int i = 1; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
// 获取单元格的值并存入objs中
Object[] objs = new Object[lastCellNum];
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
Object value = getCellValue(cell);
objs[j] = value;
}
T t = clazz.newInstance();
// 反射设置属性值
Field[] declaredFields = clazz.getDeclaredFields();
for (int k = 0; k < declaredFields.length; k++) {
Field field = declaredFields[k];
field.setAccessible(true);
// 获取被自定义注解ExcelAttr标识的字段
ExcelAttr anno = field.getAnnotation(ExcelAttr.class);
if (anno != null) {
// 获取设置的字段排序
int sort = anno.sort();
if (sort == k) {
String fieldType = field.getType().getName();
final Object value = objs[k];
if (Objects.equals("int", fieldType) ||
Objects.equals("Integer", fieldType)) {
field.setInt(t, Integer.parseInt(value.toString()));
} else if (Objects.equals("double", fieldType) ||
Objects.equals("Double", fieldType)) {
field.setDouble(t,
Double.parseDouble(value.toString()));
} else {
field.set(t, value);
}
}
}
}
dataList.add(t);
}
return dataList;
}
/**
* 获取单元格的不同数据类型的值
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
Object cellValue = ""; // 每个单元格的值
if (cell != null) {
// 数据类型判断, 获取不同类型的数据
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字类型
// 日期格式的数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 日期
cellValue = cell.getDateCellValue();
} else {
// 不是日期格式的数字, 返回字符串格式
cellValue = cell.getRichStringCellValue().toString();
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // 布尔
cellValue = cell.getBooleanCellValue();
break;
case FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: // 空
cellValue = "";
break;
case ERROR: // 错误
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
/**
* 造测试数据
* @return
*/
public static List<Employee> getDatas() {
List<Employee> datas = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
Employee employee = Employee.builder()
.id(Integer.valueOf(i))
.departid(i * 100)
.empno("10000" + i)
.age(20 + i)
.sex(i % 2 == 0 ? "男" : "女")
.job(i % 2 == 0 ? "快递员" : "工程师")
.name("admin" + i)
.build();
datas.add(employee);
}
return datas;
}
}
数据模板就是自己设置好样式的excel文件, 放在项目类路径下, 如下所示:
template/excel/template.xls
com.test.poi.PoiTemplateTest#testImportExcel
@Test
public void testImportExcel() throws Exception {
String filename = "D:\\study\\excel\\employees.xls";
String fileType = filename.substring(filename.lastIndexOf(".") + 1);
Map<String, Object> params = new HashMap<>();
params.put("fileType", fileType);
PoiTemplateUtil util = new PoiTemplateUtil();
List<Employee> dataList = util.importExcel(params, new FileInputStream(filename),
Employee.class);
dataList.forEach(System.out::println);
}
通过控制台日志查看导入数据效果
com.test.poi.PoiTemplateTest#testExportExcel
@Test
public void testExportExcel() throws Exception {
Map<String, Object> params = new HashMap<>();
params.put("template", "template/excel/template.xls");
params.put("templateDataRowIndex", 1); // 模板中第二行是带格式的数据行
params.put("datas", getDatas());
PoiTemplateUtil util = new PoiTemplateUtil();
util.exportExcel(params, new FileOutputStream("D:\\study\\excel\\employees.xls"));
}
com.test.poi.PoiTemplateTest#getDatas
// 测试数据
public static List<Employee> getDatas() {
List<Employee> datas = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
Employee employee = Employee.builder()
.id(Integer.valueOf(i))
.departid(i * 100)
.empno("10000" + i)
.age(20 + i)
.sex(i % 2 == 0 ? "男" : "女")
.job(i % 2 == 0 ? "快递员" : "工程师")
.name("admin" + i)
.build();
datas.add(employee);
}
return datas;
}
导出数据效果
com.poi.controller.ExcelController
package com.poi.controller;
import com.constant.CSISCONSTANT;
import com.exception.MyException;
import com.poi.entity.Employee;
import com.poi.service.ExcelReaderImpl;
import com.poi.util.PoiTemplateUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
/**
* 类描述：文件上传下载
* @Author wang_qz
* @Date 2021/8/14 21:07
* @Version 1.0
*/
@Controller
@RequestMapping("/excel")
public class ExcelController {
@RequestMapping("/toExcelPage")
public String todownloadPage() {
return "excelPage";
}
@RequestMapping("/downloadExcel")
public void downloadExcel(HttpServletResponse response) throws Exception {
// 模板文件
String template = "template/excel/template.xls";
// 文件类型 xls xlsx
String fileType = template.substring(template.lastIndexOf("."));
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置防止中文名乱码
String filename = URLEncoder.encode("employees信息表", "utf-8");
// 文件下载方式(附件下载还是在当前浏览器打开)
response.setHeader("Content-disposition", "attachment;filename=" + filename +
fileType);
// 构建写入到excel文件的数据
PoiTemplateUtil util = new PoiTemplateUtil();
Map<String, Object> params = new HashMap<>();
params.put("template", template);
params.put("templateDataRowIndex", 1); // 模板中第二行是带格式的数据行
params.put("datas", util.getDatas());
util.exportExcel(params, response.getOutputStream());
}
@PostMapping("/uploadExcel")
@ResponseBody
public String uploadExcel(@RequestParam("file") Part part) throws Exception {
// 获取上传的文件流
InputStream inputStream = part.getInputStream();
// 上传文件名称
String filename = part.getSubmittedFileName();
// 读取Excel
String fileType = filename.substring(filename.lastIndexOf(".") + 1);
Map<String, Object> params = new HashMap<>();
params.put("fileType", fileType);
PoiTemplateUtil util = new PoiTemplateUtil();
List<Employee> dataList = util.importExcel(params, inputStream,
Employee.class);
dataList.forEach(System.out::println);
return "upload successful!";
}
@RequestMapping("/toExcelPage2")
public String todownloadPage2() {
return "excelPage2";
}
}
webapp/WEB-INF/jsp/excelPage.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>测试excel文件下载</title>
</head>
<body>
<h3>点击下面链接, 进行excel文件下载</h3>
<a href="<c:url value='/excel/downloadExcel'/>">Excel文件下载</a>
<hr/>
<hr/>
<h3>点击下面按钮, 进行excel文件上传</h3>
<form action="<c:url value='/excel/uploadExcel'/>" method="post" enctype="multipart/form-data">
<input type="file" name="file"/><br/>
<input type="submit" value="上传Excel"/>
</form>
</body>
</html>
启动tomcat, 访问http://localhost:8080/excel/toExcelPage, 进入测试页面:
下载结果
上传效果
欢迎各位访问我的个人博客: https://www.crystalblog.xyz/