开发项目中经常使用到Excel文件的导入和导出，今天就来总结一下使用autopoi做导出和导入Excel数据
添加依赖
<dependency>
<groupId>org.jeecgframework</groupId>
<artifactId>autopoi-web</artifactId>
<version>1.0.5</version>
<exclusions>
<exclusion>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</exclusion>
</exclusions>
</dependency>
exclusions 是将 commons-codec 从 autopoi 中排除，避免冲突
一、导出实体类List数据
1.创建实体类，添加注解
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
@Setter
@Getter
@ToString
@ApiModel(value = "CandidateParamAdd", description = "投标情况-投标人添加参数")
public class CandidateParamAdd implements Serializable {
@ApiModelProperty(value = "投标单位名称", required = true)
@NotEmpty(message = "投标单位名称不能为空")
@Excel(name="投标单位名称",width=15)
private String bidUnitName;
@ApiModelProperty(value = "机构代码")
@NotNull(message = "社会统一信用代码不能为空")
@Excel(name="机构代码",width=15)
private String mainUidCode;
@ApiModelProperty(value = "保证金缴纳情况", required = true)
@NotNull(message = "保证金缴纳情况不能为空")
@Excel(name="保证金缴纳情况",width=15)
private String bondPayStatus;
//格式化时间
@ApiModelProperty(value = "招标文件获取时间", required = true)
@NotNull(message = "招标文件获取时间不能为空")
@Excel(name="招标文件获取时间",width=15,format = "yyyy-MM-dd")
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date dataSubmitEndTime;
@Excel(name="招标文件费(元)",width=15)
@ApiModelProperty(value = "招标文件费(元)")
private BigDecimal fileFee;
}
2.导出工具类
public class ExcleUtil {
public static void createExecl(Workbook workbook, HttpServletRequest request, HttpServletResponse response,String name){
if (workbook instanceof HSSFWorkbook) {
name = name + ".xls";
} else {
name = name + ".xlsx";
}
ServletOutputStream out = null;
ByteArrayOutputStream baos = null;
try {
out = response.getOutputStream();
response.reset();
String userAgent = request.getHeader("user-agent");
userAgent = userAgent ==null?"":userAgent.toLowerCase();
//针对IE或者以IE为内核的浏览器：
if(userAgent.contains("msie") ||userAgent.contains("trident")){
name = URLEncoder.encode(name, "UTF-8");
}else{
name = new String(name.getBytes(), "iso-8859-1");
}
response.setContentType("application/force-download");
response.addHeader("Content-Disposition", "attachment;fileName=" + name);
baos = new ByteArrayOutputStream();
workbook.write(baos);
response.setContentLengthLong(baos.size());
out.write(baos.toByteArray());
} catch (IOException e) {
throw new BootException(e.getMessage(),e);
} finally {
try {
if (out != null) {
out.close();
}
if (baos != null) {
baos.close();
}
} catch (IOException e) {
throw new BootException(e.getMessage(),e);
}
}
}
protected static void exportXls( HttpServletResponse response, Workbook wb) {
String codedFileName = "临时文件";
if (wb instanceof HSSFWorkbook) {
codedFileName = codedFileName + ".xls";
} else {
codedFileName = codedFileName + ".xlsx";
}
ServletOutputStream out;
try {
out = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + oConvertUtils.toUtf8String(codedFileName));
response.setContentType("application/vnd.ms-excel");
wb.write(out);
out.close();
} catch (IOException e) {
log.error(e.getMessage(), e);
}
}
}
3.导出（填充数据）
@GetMapping(value = "/exportCandidateTempt")
@ApiOperation(value = "导出投标单位模板", notes = "导出投标单位模板")
public void exportCandidateTempt(HttpServletRequest request, HttpServletResponse response) {
CandidateParamAdd candidateParamAdd = new CandidateParamAdd();
candidateParamAdd.setBidUnitName("A公司");
candidateParamAdd.setMainUidCode("20210101");
candidateParamAdd.setBondPayStatus("6573.00");
candidateParamAdd.setDataSubmitEndTime(new Date());
candidateParamAdd.setFileFee(new BigDecimal(123.00));
//此处的list可以是自己从数据库中查询的数据
List<CandidateParamAdd> candidateParamAdds = Lists.newArrayList(candidateParamAdd);
//此实体类用来设置一些属性
//entity.setTitle("设置标题");
//entity.setSecondTitle("设置子标题");
ExportParams entity = new ExportParams();
entity.setSheetName("投标情况模板");
Workbook workbook = ExcelExportUtil.exportExcel(entity, CandidateParamAdd.class,candidateParamAdds);
ExcleUtil.createExecl(workbook,request,response,"导出投标单位模板");
}
模板样例
二、导出表头变化数据
解决这类数据并且专家数量是变化的情况就得用到另外一个方法，查看源码可以看到这样一个方法，那么只需要组装一下数据即可
1.导出方法
@ApiOperation(value = "代理端评审汇总-导出", notes = "代理端评审汇总-导出")
@GetMapping(value = "/unitexport")
public void unitExportXls(BidUnitParamList bidUnit, HttpServletResponse response) {
//此处Pair非必须，只要能得到List<Map<String, Object>和List<ExcelExportEntity>就行
Pair<List<Map<String, Object>>, List<ExcelExportEntity>> pair = this.bidUnitService.agencyExpert(bidUnit);
Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("标题", "二级标题", "左下角sheet名字"), pair.getValue(), pair.getKey());
Sheet sheet = wb.getSheet(bidUnit.getTitle());
sheet.setAutobreaks(true);
ExcleUtil.exportXls(response, wb);
}
ExportParams是用来设置一些属性的，例如标题等，List<ExcelExportEntity 里面的数据主要是表头和设置分组之类的，List<Map<String, Object>为数据
ExcelExportEntity 数据
key为下标，name为表头显示内容
Map<String, Object>数据
其中map中key值为ExcelExportEntity 下标，这样才能是数据一一对应，map中的value就是数据
要实现分组效果
只需要在ExcelExportEntity 中添加groupName和多加一个ExcelExportEntity 实体在subColumnList中添加子数据的下标key即可（此处的key在map数据中无需有对应的key值）
模板样例
三、合并行列单元格
代码
@ApiOperation(value = "所有投标单位表", notes = "所有投标单位表")
@GetMapping(value = "/exportBidOpenSignIn")
public void exportXls(@RequestParam(name = "bidSectionId",required = true) Long bidSectionId,HttpServletRequest request, HttpServletResponse response) {
List<BidOpenSignInExportDTO> bidOpenSignInExportDTOS = bidOpenCloseService.queryDetailBySectionId(bidSectionId);
BidSectionDTO dto = bidSectionService.getDTO(bidSectionId);
if (Objects.nonNull(dto)) {
ExportParams entity = new ExportParams();
entity.setTitle("所有投标单位表");
entity.setSecondTitle("标段编号:" + dto.getBidSectionNo() + " 标段名称:" + dto.getBidSectionName());
entity.setSheetName("所有投标单位");
Workbook workbook = ExcelExportUtil.exportExcel(entity,BidOpenSignInExportDTO.class,bidOpenSignInExportDTOS);
//合并单元格操作开始
Sheet sheet = workbook.getSheet("所有投标单位");
CellRangeAddress region = new CellRangeAddress(3, 4, 0, 0);
sheet.addMergedRegion(region);
CellRangeAddress region1 = new CellRangeAddress(4, 5, 1, 2);
sheet.addMergedRegion(region1);
//合并单元格操作结束，复杂合并可能需要计算
ExcleUtil.createExecl(workbook,request,response,"所有投标单位表");
}
}
四、创建多sheet表格
1.数据实体类(List<Map<String,Object>和List<ExcelExportEntity中数据同上)
@Data
@NoArgsConstructor
@Accessors(chain = true)
@ApiModel(value = "BidSectionVueTableInfoDTO", description = "活动表格返回数据")
public class BidSectionVueTableInfoDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "数据")
private List<Map<String,Object>> dataList;
@ApiModelProperty(value = "表头")
private List<ExcelExportEntity> titleList;
@ApiModelProperty(value = "专家名称")
private String expertName;
}
2.导出数据
@ApiOperation(value = "代理端评审详情（专家评分表）-导出", notes = "代理端评审汇总和评审详情（专家评分表）-导出")
@GetMapping(value = "/expertEvalDetailInfo")
public void expertEvalInfo1(BidUnitParamList bidUnit, HttpServletResponse response) {
List<BidSectionVueTableInfoDTO> pair = bidUnitService.expertEvalDetailInfo(bidUnit);
Workbook wb = new HSSFWorkbook();
pair.forEach(e -> {
new ExcelExportServer().createSheetForMap(wb, new ExportParams("标题", "子标题", "sheet名字"), pair.getTitlList, pair.getDataList());
});
ExcleUtil.exportXls(response, wb);
}
四、从Excel导入数据
1.创建实体类
记住：@Excel(name = “标题”) name中的文字对应表格中表头的文字，一模一样
import java.io.Serializable;
import com.anhuibidding.common.aspect.annotation.Dict;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.springframework.format.annotation.DateTimeFormat;
/**
* @Description: 系统通告表
* @Author: jeecg-boot
* @Date: 2019-01-02
* @Version: V1.0
*/
@Data
@TableName("sys_announcement")
public class SysAnnouncement implements Serializable {
private static final long serialVersionUID = 1L;
/**
* id
*/
@TableId(type = IdType.ID_WORKER_STR)
private java.lang.String id;
/**
* 标题
*/
@Excel(name = "标题", width = 15)
private java.lang.String title;
/**
* 内容
*/
@Excel(name = "内容", width = 30)
private java.lang.String msgContent;
/**
* 开始时间
*/
@Excel(name = "开始时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date startTime;
/**
* 结束时间
*/
@Excel(name = "结束时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date endTime;
/**
* 发布人
*/
@Excel(name = "发布人", width = 15)
private java.lang.String sender;
/**
* 优先级（L低，M中，H高）
*/
@Excel(name = "优先级", width = 15, dicCode = "priority")
@Dict(dicCode = "priority")
private java.lang.String priority;
/**
* 消息类型1:通知公告2:系统消息
*/
@Excel(name = "消息类型", width = 15, dicCode = "msg_category")
@Dict(dicCode = "msg_category")
private java.lang.String msgCategory;
/**
* 通告对象类型（USER:指定用户，ALL:全体用户）
*/
@Excel(name = "通告对象类型", width = 15, dicCode = "msg_type")
@Dict(dicCode = "msg_type")
private java.lang.String msgType;
/**
* 发布状态（0未发布，1已发布，2已撤销）
*/
@Excel(name = "发布状态", width = 15, dicCode = "send_status")
@Dict(dicCode = "send_status")
private java.lang.String sendStatus;
/**
* 发布时间
*/
@Excel(name = "发布时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date sendTime;
/**
* 撤销时间
*/
@Excel(name = "撤销时间", width = 15, format = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date cancelTime;
/**
* 删除状态（0，正常，1已删除）
*/
private java.lang.String delFlag;
/**
* 创建人
*/
private java.lang.String createBy;
/**
* 创建时间
*/
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date createTime;
/**
* 更新人
*/
private java.lang.String updateBy;
/**
* 更新时间
*/
@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private java.util.Date updateTime;
/**
* 指定用户
**/
private java.lang.String userIds;
/**
* 业务类型(email:邮件 bpm:流程)
*/
private java.lang.String busType;
/**
* 业务id
*/
private java.lang.String busId;
/**
* 打开方式 组件：component 路由：url
*/
private java.lang.String openType;
/**
* 组件/路由 地址
*/
private java.lang.String openPage;
}
源码中的导入方法
2.导入数据
@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
MultipartFile file = entity.getValue();// 获取上传文件对象
ImportParams params = new ImportParams();
params.setTitleRows(2);
params.setHeadRows(1);
params.setNeedSave(true);
try {
//使用api获取到的List数据
List<SysAnnouncement> listSysAnnouncements = ExcelImportUtil.importExcel(file.getInputStream(), SysAnnouncement.class, params);
//执行自己的保存方法开始
for (SysAnnouncement sysAnnouncementExcel : listSysAnnouncements) {
if(sysAnnouncementExcel.getDelFlag()==null){
sysAnnouncementExcel.setDelFlag(CommonConstant.DEL_FLAG_0.toString());
}
sysAnnouncementService.save(sysAnnouncementExcel);
}
//执行自己的保存方法结束
return Result.ok("文件导入成功！数据行数：" + listSysAnnouncements.size());
} catch (Exception e) {
log.error(e.getMessage(),e);
return Result.error("文件导入失败！");
} finally {
try {
file.getInputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return Result.error("文件导入失败！");
}
本文地址：https://blog.csdn.net/weixin_49051190/article/details/112213522
黄山市民网：https://www.huangshanshimin.com/
