java使用easypoi导入，导出
第一步，先引入easypoi依赖：
<!-- easypoi 核销包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
<exclusions>
<exclusion>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- easypoi web包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<!-- easypoi 注解包 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
第二步，创建工具类
package com.sport.sportdigitalmanagement.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author zdj
* @date 2022-04-26
*/
public class FileUtil {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* 导出
* @param list 需要导出的集合
* @param title excel标题名称
* @param sheetName sheet表名称
* @param pojoClass 转化的实体类对象
* @param fileName 文件名称
* @param response 导出后的响应
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
//throw new NotFoundException(e.printStackTrace());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
e.printStackTrace();
//throw new NotFoundException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
//throw new NotFoundException(e.getMessage());
}
return list;
}
/**
* 导入
* @param file 需要导入的文件
* @param titleRows 从第一行开始解析
* @param headerRows 头部行下标
* @param pojoClass 转化为对应的实体类
* @param <T>
* @return 返回解析后的实体类对象集合
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
//throw new NotFoundException("excel文件不能为空");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
// throw new NotFoundException(e.getMessage());
}
return list;
}
}
第三步，导出
controller：
/**
* 局系统总预算信息列表--导出
* @return
*/
@ApiOperation(value = "局系统总预算信息列表--导出", notes = "局系统总预算信息列表--导出", httpMethod = "POST")
@RequestMapping(value = "/exportTotalBudgetStatisticsList", method = RequestMethod.POST)
public void exportTotalBudgetStatisticsList(@RequestHeader String adminToken, @RequestBody TotalBudgetDTO totalBudgetDTO, HttpServletResponse res){
// 处理参数对象
try {
// Account account = redisService.get(adminToken, Account.class);
// if (account == null) {
// return ResultData.token();
// }
// 参数过滤
if(!CommonStringUtils.getFiledDescribe().contains(totalBudgetDTO.getFieldName())){
throw new Exception("月份对应的字段描述错误！");
}
// 调用服务处理业务
totalBudgetService.exportTotalBudgetStatisticsList(totalBudgetDTO, res);
} catch (Exception e) {
logger.error("局系统总预算信息列表--导出出现异常：", e);
}
}
/**
* 局系统总预算信息列表--导出
* @return
*/
public void exportTotalBudgetStatisticsList(TotalBudgetDTO totalBudgetDTO, HttpServletResponse res) {
// 获取预算列表
List<TotalBudget> totalBudgetList = this.getAllCommonTotalBudgetList(totalBudgetDTO);
//导出的excel的名称
String fileName = "局系统总预算信息列表";
FileUtil.exportExcel(totalBudgetList, fileName, fileName, TotalBudget.class, System.currentTimeMillis() + "", res);
}
导出的映射实体类：
package com.sport.sportdigitalmanagement.po;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
/**
* @description total_budget
* @author zdj
* @date 2022-04-26
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TotalBudget implements Serializable {
/**
* 不需要导出的请用改注解
*/
@ExcelIgnore
private Integer id;
/**
* 部门
*/
@Excel(name = "预算单位/类别", orderNum = "1", width = 25)
@ApiModelProperty("部门")
@Column(name="department")
private String department;
/**
* 指标数
*/
@Excel(name = "指标数", orderNum = "2", width = 15)
@ApiModelProperty("指标数")
@Column(name="target")
private BigDecimal target;
/**
* 结余指标数
*/
@Excel(name = "结余指标数", orderNum = "4", width = 15)
@ApiModelProperty("结余指标数")
@Column(name="balance_target")
private BigDecimal balanceTarget;
/**
* 年度
*/
@Excel(name = "年度", orderNum = "1", width = 15)
@ApiModelProperty("年度")
@Column(name="year")
private Integer year;
/**
* 执行进度
*/
@Excel(name = "执行进度", orderNum = "5", width = 15)
@ApiModelProperty("执行进度")
@Column(name="progress")
private BigDecimal progress;
/**
* 统一展示的字段
*/
@Excel(name = "支出数", orderNum = "5", width = 15)
@ApiModelProperty("统一展示的字段")
@Transient
private BigDecimal commonFiled;
}
第四步：导入
controller：
/**
* 局系统总预算信息列表--导入
* @param adminToken 登录用户
* @param file 文件
* @throws Exception
*/
@ApiOperation(value = "局系统总预算信息列表--导入", notes = "局系统总预算信息列表--导入", httpMethod = "POST")
@RequestMapping(value = "/importTotalBudgetStatisticsList", method = RequestMethod.POST)
public ResultData<Object> importTotalBudgetStatisticsList(@RequestHeader String adminToken,
@ApiParam(name = "file", value = "excel文件") @RequestParam(value = "file", required = false) @RequestPart MultipartFile file) throws Exception {
// 处理参数对象
try {
// Account account = redisService.get(adminToken, Account.class);
// if (account == null) {
// return ResultData.token();
// }
// 参数过滤
if (file == null || file.getBytes() == null){
return ResultData.error("文件不能为空！");
}
// 调用服务处理业务
return totalBudgetService.importTotalBudgetStatisticsList(file);
} catch (Exception e) {
logger.error("局系统总预算信息列表--导入异常：", e);
}
// 返回结果
return ResultData.exp();
}
service:
/**
* 局系统总预算信息列表--导入
* @param file 文件
* @throws Exception
*/
public ResultData<Object> importTotalBudgetStatisticsList(MultipartFile file) {
// 先将excel数据转换为list集合数据
List<TotalBudgetVo> convertAfterList = FileUtil.importExcel(file, 0, 1, TotalBudgetVo.class);
if(CollectionUtils.isEmpty(convertAfterList)){
return ResultData.error("Excel数据为空，excel转化失败！");
}
// 过滤掉不必要的数据集合
for (int i = 0; i < convertAfterList.size(); i++) {
TotalBudgetVo convertAfter = convertAfterList.get(i);
if(StringUtils.isEmpty(convertAfter.getDepartment())){
convertAfterList = convertAfterList.subList(0, i);
break;
}
}
// 申请提示返回对象
StringBuffer lastMsg = new StringBuffer();
lastMsg.append("共计【"+convertAfterList.size()+"】条！");
// 当前行索引
int index = 1;
int successIndex = 0;
for (TotalBudgetVo convertAfter : convertAfterList) {
index ++;
// 过滤预算单位/类别
String department = StringUtils.isEmpty(convertAfter.getDepartment()) ? "" : convertAfter.getDepartment().trim().replaceAll(" ","");
if(StringUtils.isEmpty(department)){
lastMsg.append("第【"+index+"】行预算单位/类别为空！");
continue;
}
/*** 导入中奖用户 ****/
TotalBudget totalBudget = new TotalBudget();
totalBudget.setDepartment(convertAfter.getDepartment());
totalBudget.setTarget(new BigDecimal(convertAfter.getTarget()));
totalBudget.setYear(Integer.parseInt(convertAfter.getYear()));
totalBudget.setJanuary(new BigDecimal(StringUtils.isEmpty(convertAfter.getJanuary()) ? "0" : convertAfter.getJanuary()));
totalBudget.setFebruary(new BigDecimal(StringUtils.isEmpty(convertAfter.getFebruary()) ? "0" : convertAfter.getFebruary()));
totalBudget.setMarch(new BigDecimal(StringUtils.isEmpty(convertAfter.getMarch()) ? "0" : convertAfter.getMarch()));
totalBudget.setApril(new BigDecimal(StringUtils.isEmpty(convertAfter.getApril()) ? "0" : convertAfter.getApril()));
totalBudget.setMay(new BigDecimal(StringUtils.isEmpty(convertAfter.getMay()) ? "0" : convertAfter.getMay()));
totalBudget.setJune(new BigDecimal(StringUtils.isEmpty(convertAfter.getJune()) ? "0" : convertAfter.getJune()));
totalBudget.setJuly(new BigDecimal(StringUtils.isEmpty(convertAfter.getJuly()) ? "0" : convertAfter.getJuly()));
totalBudget.setAugust(new BigDecimal(StringUtils.isEmpty(convertAfter.getAugust()) ? "0" : convertAfter.getAugust()));
totalBudget.setSeptember(new BigDecimal(StringUtils.isEmpty(convertAfter.getSeptember()) ? "0" : convertAfter.getSeptember()));
totalBudget.setOctober(new BigDecimal(StringUtils.isEmpty(convertAfter.getOctober()) ? "0" : convertAfter.getOctober()));
totalBudget.setNovember(new BigDecimal(StringUtils.isEmpty(convertAfter.getNovember()) ? "0" : convertAfter.getNovember()));
totalBudget.setDecember(new BigDecimal(StringUtils.isEmpty(convertAfter.getDecember()) ? "0" : convertAfter.getDecember()));
totalBudget.setCreateTime(LocalDateTime.now());
totalBudget.setIsValid(1);
int affectRow = totalBudgetMapper.insert(totalBudget);
if(affectRow > 0){
successIndex ++;
}
}
// 返回提示
if(successIndex == convertAfterList.size()){
lastMsg.append("导入成功【"+successIndex+"】条！");
} else {
if(successIndex == convertAfterList.size()){
lastMsg.append("导入成功【"+successIndex+"】条！");
} else {
lastMsg.append("注：导入失败【"+(convertAfterList.size() - successIndex)+"】条！导入成功【"+successIndex+"】条！");
}
}
// 返回结果
return ResultData.ok(lastMsg.toString(), null);
}
导入对应的映射实体：
package com.sport.sportdigitalmanagement.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
/**
* @description TotalBudgetVo
* @author zdj
* @date 2022-04-26
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TotalBudgetVo implements Serializable {
@Excel(name = "预算单位/类别", orderNum = "1", width = 25)
private String department;
@Excel(name = "年度", orderNum = "2", width = 15)
private String year;
@Excel(name = "指标数", orderNum = "3", width = 15)
private String target;
@Excel(name = "1月支出数", orderNum = "4", width = 15)
private String january;
@Excel(name = "1-2月支出数", orderNum = "5", width = 15)
private String february;
@Excel(name = "1-3月支出数", orderNum = "6", width = 15)
private String march;
@Excel(name = "1-4月支出数", orderNum = "7", width = 15)
private String april;
@Excel(name = "1-5月支出数", orderNum = "8", width = 15)
private String may;
@Excel(name = "1-6月支出数", orderNum = "9", width = 15)
private String june;
@Excel(name = "1-7月支出数", orderNum = "10", width = 15)
private String july;
@Excel(name = "1-8月支出数", orderNum = "11", width = 15)
private String august;
@Excel(name = "1-9月支出数", orderNum = "12", width = 15)
private String september;
@Excel(name = "1-10月支出数", orderNum = "13", width = 15)
private String october;
@Excel(name = "1-11月支出数", orderNum = "14", width = 15)
private String november;
@Excel(name = "1-12月支出数", orderNum = "15", width = 15)
private String december;
}
完成
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
2021-10-13 C# Linq 常用 整理 Join
2021-10-13 C#代码中实现两个表（DataTable）的关联查询（JOIN） 问题
2021-10-13 C# linq left join 操作表和datatable