目录
前言：
代码复制粘贴即可用，主要包含的功能有Excel模板下载、基于Excel数据导入、Excel数据导出。
根据实际情况修改一些细节即可，最后有结果展示，可以先看下结果，是否是您想要的。
台上一分钟，台下60秒，不喜勿喷。
觉得有用，麻烦点个赞和关注。
新建SpringBoot项目，引入下面的依赖
注：这个依赖已经整合了 poi 和poi-ooxm，无需单独引入。
如有对于POI和Excel有任何疑问，可移步1小时学会 POI 和 EasyExcel （超级详细）_涉世未深果子狸的博客-CSDN博客_easyexcel poi
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>数据导入导出执行原理和思路：
用户端逻辑：
1. 数据导入
用户先下载模板，根据模板填入数据，然后点击上传；
2. 数据导出
用户在界面选择需要导出的数据（导出条件），点击导出。
后台开发逻辑：
1. 模板下载
利用easyExcel生成文件，然后将文件放进响应流中，同时设置响应头为文件下载，浏览器收到响应之后，回去解析流中的内容，然后进行下载。
2. 文件上传
用在填写好Excel内容之后，会以文件上传的形式，将文件上传到服务端，此时，我们只需要利用EasyExcel将文件流中的数据读出来即可。
3. 数据导出
后台在接收到用户的数据导出请求之后，会根据请求中的筛选条件，查询对应数据，再将对应的数据填充进对应的导出模板中，以流的形式响应给浏览器。其实和模板下载的差不错，只是模板下载没有数据，数据导出有数据而已。
代码实现
说明：以下代码，是我根据具体业务编写之后整理出来的，会有不足的地方，欢迎指教！
下拉框策略
如果没有此需求，可以不要此类。
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Map;
import java.util.TreeMap;
/**
* 这个类的作用主要是给列增加下拉框
* 主要是为了方便用户填写数据
*/
public class CustomSheetWriteHandler implements SheetWriteHandler {
/**
* 存放下拉内容的集合
* key为列的下标， value为下拉内容数组
*/
private final Map<Integer, String[]> map = new TreeMap<>();
/**
* 工作簿下标，从0开始
*/
private int index = 0;
/**
* 给多少行添加下拉框，这里默认给2000行
*/
private final int batchSize = 2000;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 宝藏在此：如果下拉框内容总的长度超过255，会导致Cell有下拉框，但是下拉内容显示不了，
* 这时我们可以新建一个sheet，将其隐藏，然后将里面的内容引用到我们的下拉框列就可以。
* 值得细品
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// excel下标从0开始，这里第二列的下拉选择内容
map.put(1, new String[]{"下拉内容一", "下拉内容二"});
// excel下标从0开始，这里第三列的下拉选择内容
map.put(3, new String[]{"北京市", "上海市", "重庆市", "天津市"});
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
map.forEach((k, v) -> {
// 创建sheet，突破下拉框255的限制
// 获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 定义sheet的名称
String sheetName = "sheet" + k;
// 1.创建一个隐藏的sheet 名称为 proviceSheet
Sheet proviceSheet = workbook.createSheet(sheetName);
// 从第二个工作簿开始隐藏
this.index++;
// 设置隐藏
workbook.setSheetHidden(this.index, true);
// 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
for (int i = 0, length = v.length; i < length; i++) {
// i:表示你开始的行数 0表示你开始的列数
proviceSheet.createRow(i).createCell(0).setCellValue(v[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
// 4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (v.length));
// 5 将刚才设置的sheet引用到你的下拉列表中,1表示从行的序号1开始（开始行，通常行的序号为0的行是表头），50表示行的序号50（结束行），表示从行的序号1到50，k表示开始列序号和结束列序号
CellRangeAddressList addressList = new CellRangeAddressList(1, batchSize, k, k);
DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
// 阻止输入非下拉选项的值
dataValidation3.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation3.setShowErrorBox(true);
dataValidation3.setSuppressDropDownArrow(true);
dataValidation3.createErrorBox("提示", "此值与单元格定义格式不一致");
// validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
writeSheetHolder.getSheet().addValidationData(dataValidation3);
});
}
}
批注策略
给表头添加批注，没有此需求可以不要
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
/**
* 自定义拦截器.新增注释,第一行头加批注
* 这个类的作用主要是给表头添加批注
*/
@Slf4j
public class CommentWriteHandler implements RowWriteHandler {
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
// 在第一行 第二列创建一个批注
Comment comment1 =
drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)0, 0, (short)1, 1));
// 输入批注信息
comment1.setString(new XSSFRichTextString("批注1"));
// 将批注添加到单元格对象中
sheet.getRow(0).getCell(0).setCellComment(comment1);
Comment comment2 =
drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));
// 输入批注信息
comment2.setString(new XSSFRichTextString("批注2"));
// 将批注添加到单元格对象中
sheet.getRow(0).getCell(1).setCellComment(comment2);
Comment comment3 =
drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)2, 0, (short)3, 1));
// 输入批注信息
comment3.setString(new XSSFRichTextString("批注3"));
// 将批注添加到单元格对象中
sheet.getRow(0).getCell(2).setCellComment(comment3);
}
}
数据读取监听
导入数据时，程序解析和读取数据用，必须要！！！
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Excel数据解析监听器， 数据解析方法异步执行
* @param <T> Excel中数据的类型
*/
@Getter
@Setter
@NoArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {
// 加入一个判断标签，判断数据是否已经读取完
private volatile boolean retryLock = false;
// 解析完成后的数据集合, 监听对象初始化之后，立即初始化集合对象
private final List<T> dataList = new ArrayList<>();
// 每次最多导入条数
private final int batchSize = 2000;
/**
* 获取解析后的数据集合， 如果数据还没有被解析完成，会对读取该集合的线程进行阻塞，直到数据读取完成之后，进行解锁。
* 如果一次导入数据超过batchSize条，则以抛异常的形式阻止导入数据
* @return 解析后的数据集合
*/
public List<T> getDataList() {
while (true){
if (retryLock){
if (dataList.size() > batchSize){
// 手动清空数据内存数据，减少内存消耗
dataList.clear();
throw new RuntimeException("一次最多导入"+ batchSize +"条数据");
} else {
return dataList;
}
}
}
}
/**
* Excel每解析一行数据，就会调用一次该方法
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
* analysis context
*/
@Override
public void invoke(T data, AnalysisContext context) {
dataList.add(data);
}
/**
* 读取表头内容
* @param headMap 表头部数据
* @param context 数据解析上下文
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//System.out.println("表头：" + headMap);
}
/**
* 流中的数据解析完成之后，就会调用此方法
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 数据解析完成，解锁
retryLock = true;
}
/**
* 解析过程如果发生异常，会调用此方法
* @param exception
* @param context
*/
@Override
public void onException(Exception exception, AnalysisContext context){
throw new RuntimeException("Excel数据异常，请检查或联系管理员！");
}
}
Excel工具类
封装统一的Excel操作入口
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
@Slf4j
public class ExcelUtil {
/**
* 导出数据为excel文件
*
* @param filename 文件名称
* @param dataResult 集合内的bean对象类型要与clazz参数一致
* @param clazz 集合内的bean对象类型要与clazz参数一致
* @param response HttpServlet响应对象
*/
public static void export(String filename, List<?> dataResult, Class<?> clazz, HttpServletResponse response) {
response.setStatus(200);
OutputStream outputStream = null;
ExcelWriter excelWriter = null;
try {
if (StringUtils.isBlank(filename)) {
throw new RuntimeException("'filename' 不能为空");
}
String fileName = filename.concat(".xlsx");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
outputStream = response.getOutputStream();
// 根据不同的策略生成不同的ExcelWriter对象
if (dataResult == null){
excelWriter = getTemplateExcelWriter(outputStream);
} else {
excelWriter = getExportExcelWriter(outputStream);
}
WriteTable writeTable = EasyExcel.writerTable(0).head(clazz).needHead(true).build();
WriteSheet writeSheet = EasyExcel.writerSheet(fileName).build();
// 写出数据
excelWriter.write(dataResult, writeSheet, writeTable);
} catch (Exception e) {
log.error("导出excel数据异常：", e);
throw new RuntimeException(e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
if (outputStream != null) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error("导出数据关闭流异常", e);
}
}
}
}
/**
* 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
* @param outputStream 数据输出流
* @return 模板下载ExcelWriter对象
*/
private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){
return EasyExcel.write(outputStream)
.registerWriteHandler(new CommentWriteHandler()) //增加批注策略
.registerWriteHandler(new CustomSheetWriteHandler()) //增加下拉框策略
.registerWriteHandler(getStyleStrategy()) //字体居中策略
.build();
}
/**
* 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
* @param outputStream 数据输出流
* @return 数据导出ExcelWriter对象
*/
private static ExcelWriter getExportExcelWriter(OutputStream outputStream){
return EasyExcel.write(outputStream)
.registerWriteHandler(getStyleStrategy()) //字体居中策略
.build();
}
/**
* 设置表格内容居中显示策略
* @return
*/
private static HorizontalCellStyleStrategy getStyleStrategy(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容策略
WriteCellStyle writeCellStyle = new WriteCellStyle();
// 设置内容水平居中
writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle);
}
/**
* 根据Excel模板，批量导入数据
* @param file 导入的Excel
* @param clazz 解析的类型
* @return 解析完成的数据
*/
public static List<?> importExcel(MultipartFile file, Class<?> clazz){
if (file == null || file.isEmpty()){
throw new RuntimeException("没有文件或者文件内容为空！");
}
List<Object> dataList = null;
BufferedInputStream ipt = null;
try {
InputStream is = file.getInputStream();
// 用缓冲流对数据流进行包装
ipt = new BufferedInputStream(is);
// 数据解析监听器
ExcelListener<Object> listener = new ExcelListener<>();
// 读取数据
EasyExcel.read(ipt, clazz,listener).sheet().doRead();
// 获取去读完成之后的数据
dataList = listener.getDataList();
} catch (Exception e){
log.error(String.valueOf(e));
throw new RuntimeException("数据导入失败！" + e);
}
return dataList;
}
}
创建导入数据模板类
可以理解为这个类是我们和客户之间的约定，程序根据这个模板类生成对应的Excel文件，客户根据Excel文件将数据填充进来。然后用户将填充好的Excel文件上传到我们的程序中，我们还得根据这个模板类来解析读取用户填充的数据。
根据实际业务调整，上面的工具类会根据提供的模板生成对应的Excel文件。
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;
import javax.validation.constraints.NotEmpty;
import java.io.Serializable;
/**
* 数据导入的Excel模板实体
*/
@Data
public class ImportExcelVo implements Serializable {
private static final long serialVersionUID = 1L;
@ColumnWidth(20)
@ExcelProperty(value = "公司名称", index = 0)
private String name;
@ColumnWidth(20)
@ExcelProperty(value = "公司联系电话", index = 1)
private String phone;
@ColumnWidth(28)
@ExcelProperty(value = "公司统一社会信用代码", index = 2)
private String creditCode;
@ColumnWidth(15)
@ExcelProperty(value = "区域", index = 3)
private String province;
@ColumnWidth(15)
@ExcelProperty(value = "公司法人", index = 4)
private String legalPerson;
@ExcelProperty(value = "备注", index = 5)
private String remark;
}
创建数据导出模板
根据此模板，向用户展示用户可以看到的字段。
根据实际业务调整，上面的工具类会根据提供的模板生成对应的Excel文件。
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;
/**
* 资质信息导出实体
*/
@Data // Lombok注解，用于生成getter setter
@Accessors(chain = true) //Lombok注解，链式赋值使用
public class ExportExcelVo implements Serializable {
private static final long serialVersionUID = 1L;
@ColumnWidth(25)
@ExcelProperty(value = "企业名称", index = 0)
private String name;
@ColumnWidth(25)
@ExcelProperty(value = "社会统一信用代码", index = 1)
private String creditCode;
@ColumnWidth(15)
@ExcelProperty(value = "曾用名", index = 2)
private String formerName;
@ColumnWidth(15)
@ExcelProperty(value = "公司法人", index = 3)
private String legalPerson;
@ExcelProperty(value = "区域", index = 4)
private String province;
@ExcelProperty(value = "录入时间", index = 5)
private String createTime;
@ColumnWidth(15)
@ExcelProperty(value = "公司股东", index = 6)
private String stockholder;
@ExcelProperty(value = "企业联系方式", index = 7)
private String contact;
}
Web接口
import lombok.extern.slf4j.Slf4j;
import com.xxx.xxx.domain.vo.ExportExcelVo;
import com.xxx.xxx.domain.vo.ImportExcelVo;
import com.xxx.xxx.util.CommonResponse;
import com.xxx.xxx.util.ExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 事件控制器
*/
@Slf4j
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
/**
* excel模板下载
*/
@RequestMapping(value = "/template", method = RequestMethod.GET)
public CommonResponse<String> template(HttpServletResponse response){
String fileName = "导入模板下载" + System.currentTimeMillis();
try {
ExcelUtil.export(fileName, null, ImportExcelVo.class, response);
} catch (Exception e) {
return CommonResponse.error("模板下载失败" + e.getMessage());
}
return CommonResponse.success("模板下载成功！");
}
/**
* Excel批量导入数据
* @param file 导入文件
*/
@RequestMapping(value = "/import", method = RequestMethod.POST)
public CommonResponse<String> importEvents(MultipartFile file){
try {
List<?> list = ExcelUtil.importExcel(file, ImportExcelVo.class);
System.out.println(list);
return CommonResponse.success("数据导入完成");
} catch (Exception e) {
return CommonResponse.error("数据导入失败！" + e.getMessage());
}
}
/**
* excel数据导出
* @param size 导出条数， 也可以是用户需要导出数据的条件
* @return
*/
@RequestMapping(value = "/export", method = RequestMethod.GET)
public CommonResponse<String> export(Long size, HttpServletResponse response){
// 模拟根据条件在数据库查询数据
ArrayList<ExportExcelVo> excelVos = new ArrayList<>();
for (int i = 1; i <= size; i++) {
ExportExcelVo excelVo = new ExportExcelVo();
excelVo.setContact(String.valueOf(10000000000L + i));
excelVo.setName("公司名称" + i);
excelVo.setCreditCode("社会性用代码" + i);
excelVo.setProvince("地区" + i);
excelVo.setLegalPerson("法人" + i);
excelVo.setFormerName("曾用名" + i);
excelVo.setStockholder("投资人" + i);
excelVo.setCreateTime(new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒").format(new Date()));
excelVos.add(excelVo);
}
String fileName = "数据导出" + System.currentTimeMillis();
try {
ExcelUtil.export(fileName, excelVos, ExportExcelVo.class, response);
} catch (Exception e) {
return CommonResponse.error("数据导出成功" + e.getMessage());
}
return CommonResponse.success("数据导出失败！");
}
}
至此整个Excel导入导出代码完毕！
结果展示
模板下载
- 浏览器访问模板下载接口，然后会根据接口返回的内容进行解析
- Excel文件查看
数据导入
- 填充数据，在下载下来的模板中填入数据
- 利用PostMan进行文件上传
- 控制台输出
数据导出
- 浏览器访问数据导出接口
- 内容展示
1378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
