EasyExcel动态生成表头

最新推荐文章于 2024-07-11 11:21:50 发布

小丁丐

最新推荐文章于 2024-07-11 11:21:50 发布

阅读量3.2k

点赞数 5

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/DJYDFT2831djydft/article/details/119786441

版权

java 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

一、需求

需要实现根据数据库里面的数据，动态的生成表头，如图所示：

解析：身份证信息、驾驶证信息、行驶证信息是需要随着数据库里面的证件类型动态变化的，也就是说如果证件类型只选择了身份证信息，那么这个excel导出里面只能有身份证信息

二、实现

1、表结构

--创建表tOcrUploadFile--上传文件批次表

CREATE TABLE [dbo].[tOcrUploadFile](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[licenseType] [varchar](50) NOT NULL,
	[zipName] [nvarchar](128) NOT NULL,
	[zipNameSuffix] [varchar](50) NOT NULL,
	[fileDirectoryPath] [varchar](512) NOT NULL,
	[status] [tinyint] NULL,
	[certNum] [int] NULL,
	[estimatedTime] [datetime] NULL,
	[actualTime] [datetime] NULL,
	[creatorName] [nvarchar](128) NOT NULL,
	[createTime] [datetime] NOT NULL,
CONSTRAINT [PK__tOcrUploadFile__3213E83F487583F6] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'证件类型#1身份证,#2行驶证,#3驾驶证' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'licenseType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'压缩包名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'zipName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'压缩包名称后缀' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'zipNameSuffix'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解压缩文件一级目录:/data/zipName/xxx' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'fileDirectoryPath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态#0待处理,#1正在解压缩,#2解压缩完成,#3正在OCR验证,#4ORC验证完毕' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'认证人数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'certNum'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预计完成时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'estimatedTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际完成时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'actualTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'creatorName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile', @level2type=N'COLUMN',@level2name=N'createTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OCR-上传文件批次表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrUploadFile'
GO

create index idx_zipName on tOcrUploadFile(zipName);

--创建表tOcrDealData--上传文件处理表
CREATE TABLE [dbo].[tOcrDealData](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[uploadFileId] [bigint] NOT NULL,
	[directoryPath] [varchar](512) NOT NULL,
	[fileName] [nvarchar](64) NOT NULL,
	[cardType] [int] NOT NULL,
	[filePath] [varchar](512) NOT NULL,
	[status] [tinyint] NOT NULL,
	[licenseType] [tinyint] NOT NULL,
	[respContent] [varchar](max) NULL,
	[failReason] [varchar](512) NULL,
	[creatorName] [nvarchar](128) NULL,
	[createTime] [datetime] NULL,
CONSTRAINT [PK__tOcrDealData__3213E83F487583F6] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'tOcrUploadFile主键ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'uploadFileId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件路径地址:/data/xxx/xxx/xxx' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'directoryPath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'fileName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1#正面 | 2#反面' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'cardType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件上传返回key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'filePath'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态#0待处理,#1处理中,#2处理成功,#3处理失败' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'证件类型#1身份证,#2行驶证,#3驾驶证' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'licenseType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'返回报文' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'respContent'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'失败原因' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'failReason'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'creatorName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData', @level2type=N'COLUMN',@level2name=N'createTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OCR-上传文件处理表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOcrDealData'
GO

create index idx_uploadFileId on tOcrDealData(uploadFileId);

create index idx_directoryPath on tOcrDealData(directoryPath);

2、数据结构：

身份证正面：{"address":"湖北省云梦县城关镇蔡陈村4组","birthday":"1990年10月08日","code":"0","direction":0,"idCard":"420923199010080634","imageStatus":"normal","imageURL":"http://preuploadv2.viphrm.com/filemanager/preview/?path=288572/202108/26a05c8bab4740cda5e492c4c2f95c72.jpg","message":"成功","name":"金刚","nation":"汉","retryTimes":0,"seqNo":"21081813320043044","sex":"男"}
身份证反面：{"authority":"云梦县公安局","code":"0","direction":0,"imageStatus":"normal","imageURL":"http://preuploadv2.viphrm.com/filemanager/preview/?path=288572/202108/52b0249e74034c3d86d67d8979747328.jpg","message":"成功","retryTimes":0,"seqNo":"21081813320043045","timeLimit":"2012.11.28-2022.11.28"}
行驶证正面：{"msg": "成功", "engineNo": "53****Y", "code": "0", "address": "江西省*****************附1号", "tradeNo": "18062915505091149", "plateNo": "赣****10", "usingProperties": "非营运", "brandModel": "东风日产***********L2", "possessor": "段**", "registrationDate": "20170123", "vin": "LGBH*********5652", "openingDate": "20170123", "vehicleType": "小型轿车" }
驾驶证正面：{"msg": "成功", "code": "0", "address": "江西省*******************附1号", "tradeNo": "18062915552391159", "sex": "男", "birth": "19900416", "firstGetCard": "20150506", "number": "3603***********2536", "carType": "C1", "nationality": "中国", "expiryTime": "20150506 - 20210506", "name": "段**" }

这些数据最重要填充到[tOcrDealData]的[respContent]里面

3、实体类：

package com.ms.tools.model;

import com.alibaba.excel.annotation.ExcelProperty;

import io.swagger.annotations.ApiModelProperty;

/**
 * 
 * @ClassName: OcrSupportCardEntity
 * @Description: 证件号码实体
 * @author dingjy
 * @date 2021年8月17日 下午9:15:00
 */
public class OcrSupportCardEntity {

	@ApiModelProperty(value = "调用唯一标识（如有接口问 题，请提供此值)")
	private String respSeqNo;

	@ExcelProperty(value = { "身份证信息", "姓名" }, index = 0)
	@ApiModelProperty(value = "姓名")
	private String name;

	@ExcelProperty(value = { "身份证信息", "身份证号码" }, index = 1)
	@ApiModelProperty(value = "身份证号")
	private String idcard;

	@ExcelProperty(value = { "身份证信息", "性别" }, index = 2)
	@ApiModelProperty(value = "性别")
	private String sex;

	@ExcelProperty(value = { "身份证信息", "民族" }, index = 3)
	@ApiModelProperty(value = "民族")
	private String nation;

	@ExcelProperty(value = { "身份证信息", "出生" }, index = 4)
	@ApiModelProperty(value = "生日")
	private String birthday;

	@ExcelProperty(value = { "身份证信息", "住址" }, index = 5)
	@ApiModelProperty(value = "地址")
	private String address;

	@ExcelProperty(value = { "身份证信息", "签发机关" }, index = 6)
	@ApiModelProperty(value = "签发机关")
	private String authority;

	@ExcelProperty(value = { "身份证信息", "签发日期" }, index = 7)
	@ApiModelProperty(value = "签发日期")
	private String startDate;

	@ExcelProperty(value = { "身份证信息", "失效日期" }, index = 8)
	@ApiModelProperty(value = "失效日期")
	private String endDate;

	@ApiModelProperty(value = "有效期限")
	private String timelimit;

	public String getRespSeqNo() {
		return respSeqNo;
	}

	public void setRespSeqNo(String respSeqNo) {
		this.respSeqNo = respSeqNo;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getIdcard() {
		return idcard;
	}

	public void setIdcard(String idcard) {
		this.idcard = idcard;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getNation() {
		return nation;
	}

	public void setNation(String nation) {
		this.nation = nation;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getAuthority() {
		return authority;
	}

	public void setAuthority(String authority) {
		this.authority = authority;
	}

	public String getTimelimit() {
		return timelimit;
	}

	public void setTimelimit(String timelimit) {
		this.timelimit = timelimit;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

}

package com.ms.tools.model;

import com.alibaba.excel.annotation.ExcelProperty;

import io.swagger.annotations.ApiModelProperty;

/**
 * 
 * @ClassName: OcrSupportEntity
 * @Description: OCR导出实体
 * @author dingjy
 * @date 2021年8月17日 下午9:58:45
 */
public class OcrSupportCommonEntity {

	@ExcelProperty(value = { "错误原因", "错误原因" }, index = 0)
	@ApiModelProperty(value = "错误原因")
	private String failReason;

	@ExcelProperty(value = { "第二目录名", "第二目录名" }, index = 1)
	@ApiModelProperty(value = "二级目录")
	private String secondDirectory;

	@ExcelProperty(value = { "第三目录名", "第三目录名" }, index = 2)
	@ApiModelProperty(value = "三级目录")
	private String thirdDirectory;

	public String getFailReason() {
		return failReason;
	}

	public void setFailReason(String failReason) {
		this.failReason = failReason;
	}

	public String getSecondDirectory() {
		return secondDirectory;
	}

	public void setSecondDirectory(String secondDirectory) {
		this.secondDirectory = secondDirectory;
	}

	public String getThirdDirectory() {
		return thirdDirectory;
	}

	public void setThirdDirectory(String thirdDirectory) {
		this.thirdDirectory = thirdDirectory;
	}

}

package com.ms.tools.model;

import com.alibaba.excel.annotation.ExcelProperty;

import io.swagger.annotations.ApiModelProperty;

/**
 * 
 * @ClassName: OcrSupportDrivingLicenseEntity
 * @Description: 驾驶证实体
 * @author dingjy
 * @date 2021年8月17日 下午9:20:08
 */
public class OcrSupportDrivingLicenseEntity {

	@ApiModelProperty(value = "调用唯一标识（如有接口问 题，请提供此值)")
	private String respSeqNo;

	@ExcelProperty(value = { "驾驶证信息", "证件号" }, index = 3)
	@ApiModelProperty(value = "证件号")
	private String number;

	@ApiModelProperty(value = "有效时间")
	private String expiryTime;

	@ExcelProperty(value = { "驾驶证信息", "准驾车型" }, index = 0)
	@ApiModelProperty(value = "驾驶证类型")
	private String carType;

	@ExcelProperty(value = { "驾驶证信息", "住址" }, index = 6)
	@ApiModelProperty(value = "住址")
	private String address;

	@ExcelProperty(value = { "驾驶证信息", "姓名" }, index = 5)
	@ApiModelProperty(value = "姓名")
	private String name;

	@ExcelProperty(value = { "驾驶证信息", "国籍" }, index = 7)
	@ApiModelProperty(value = "国籍")
	private String nationality;

	@ExcelProperty(value = { "驾驶证信息", "出生日期" }, index = 8)
	@ApiModelProperty(value = "生日")
	private String birth;

	@ExcelProperty(value = { "驾驶证信息", "性别" }, index = 9)
	@ApiModelProperty(value = "性别")
	private String sex;

	@ExcelProperty(value = { "驾驶证信息", "初次领证时间" }, index = 4)
	@ApiModelProperty(value = "初次领证时间")
	private String firstGetCard;

	@ExcelProperty(value = { "驾驶证信息", "有效开始日期" }, index = 1)
	@ApiModelProperty(value = "有效开始日期")
	private String startDate;

	@ExcelProperty(value = { "驾驶证信息", "有效终止日期" }, index = 2)
	@ApiModelProperty(value = "有效终止日期")
	private String endDate;

	public String getRespSeqNo() {
		return respSeqNo;
	}

	public void setRespSeqNo(String respSeqNo) {
		this.respSeqNo = respSeqNo;
	}

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number;
	}

	public String getExpiryTime() {
		return expiryTime;
	}

	public void setExpiryTime(String expiryTime) {
		this.expiryTime = expiryTime;
	}

	public String getCarType() {
		return carType;
	}

	public void setCarType(String carType) {
		this.carType = carType;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getNationality() {
		return nationality;
	}

	public void setNationality(String nationality) {
		this.nationality = nationality;
	}

	public String getBirth() {
		return birth;
	}

	public void setBirth(String birth) {
		this.birth = birth;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getFirstGetCard() {
		return firstGetCard;
	}

	public void setFirstGetCard(String firstGetCard) {
		this.firstGetCard = firstGetCard;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public String getEndDate() {
		return endDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

}

package com.ms.tools.model;

import com.alibaba.excel.annotation.ExcelProperty;

import io.swagger.annotations.ApiModelProperty;

/**
 * 
 * @ClassName: OcrSupportTravelLicenseEntity
 * @Description: 行驶证实体
 * @author dingjy
 * @date 2021年8月17日 下午9:20:31
 */
public class OcrSupportTravelLicenseEntity {

	@ApiModelProperty(value = "调用唯一标识（如有接口问 题，请提供此值)")
	private String respSeqNo;

	@ExcelProperty(value = { "行驶证信息", "品牌型号" }, index = 3)
	@ApiModelProperty(value = "品牌型号")
	private String brandModel;

	@ExcelProperty(value = { "行驶证信息", "发证日期" }, index = 7)
	@ApiModelProperty(value = "发证日期")
	private String openingDate;

	@ExcelProperty(value = { "行驶证信息", "使用性质" }, index = 9)
	@ApiModelProperty(value = "使用性质")
	private String usingProperties;

	@ExcelProperty(value = { "行驶证信息", "发动机号码" }, index = 5)
	@ApiModelProperty(value = "发动机号码")
	private String engineNo;

	@ExcelProperty(value = { "行驶证信息", "所有人" }, index = 2)
	@ApiModelProperty(value = "所有人")
	private String possessor;

	@ExcelProperty(value = { "行驶证信息", "住址" }, index = 8)
	@ApiModelProperty(value = "住址")
	private String address;

	@ExcelProperty(value = { "行驶证信息", "注册日期" }, index = 6)
	@ApiModelProperty(value = "注册日期")
	private String registrationDate;

	@ExcelProperty(value = { "行驶证信息", "车辆识别代号" }, index = 4)
	@ApiModelProperty(value = "车辆识别代号")
	private String vin;

	@ExcelProperty(value = { "行驶证信息", "车辆类型" }, index = 1)
	@ApiModelProperty(value = "车辆类型")
	private String vehicleType;

	@ExcelProperty(value = { "行驶证信息", "号牌号码" }, index = 0)
	@ApiModelProperty(value = "车牌号")
	private String plateNo;

	public String getRespSeqNo() {
		return respSeqNo;
	}

	public void setRespSeqNo(String respSeqNo) {
		this.respSeqNo = respSeqNo;
	}

	public String getBrandModel() {
		return brandModel;
	}

	public void setBrandModel(String brandModel) {
		this.brandModel = brandModel;
	}

	public String getOpeningDate() {
		return openingDate;
	}

	public void setOpeningDate(String openingDate) {
		this.openingDate = openingDate;
	}

	public String getUsingProperties() {
		return usingProperties;
	}

	public void setUsingProperties(String usingProperties) {
		this.usingProperties = usingProperties;
	}

	public String getEngineNo() {
		return engineNo;
	}

	public void setEngineNo(String engineNo) {
		this.engineNo = engineNo;
	}

	public String getPossessor() {
		return possessor;
	}

	public void setPossessor(String possessor) {
		this.possessor = possessor;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getRegistrationDate() {
		return registrationDate;
	}

	public void setRegistrationDate(String registrationDate) {
		this.registrationDate = registrationDate;
	}

	public String getVin() {
		return vin;
	}

	public void setVin(String vin) {
		this.vin = vin;
	}

	public String getVehicleType() {
		return vehicleType;
	}

	public void setVehicleType(String vehicleType) {
		this.vehicleType = vehicleType;
	}

	public String getPlateNo() {
		return plateNo;
	}

	public void setPlateNo(String plateNo) {
		this.plateNo = plateNo;
	}

}

package com.ms.tools.vo;

import com.ms.tools.model.OcrSupportCardEntity;
import com.ms.tools.model.OcrSupportCommonEntity;
import com.ms.tools.model.OcrSupportDrivingLicenseEntity;
import com.ms.tools.model.OcrSupportTravelLicenseEntity;

/**
 * 
* @ClassName: OcrSupportVo
* @Description: OCR实体类
* @author dingjy
* @date 2021年8月18日 上午11:22:52
 */
public class OcrSupportVo {

	private OcrSupportCommonEntity ocrSupportCommonEntity;

	private OcrSupportCardEntity ocrSupportCardEntity;

	private OcrSupportDrivingLicenseEntity ocrSupportDrivingLicenseEntity;

	private OcrSupportTravelLicenseEntity ocrSupportTravelLicenseEntity;

	public OcrSupportCommonEntity getOcrSupportCommonEntity() {
		return ocrSupportCommonEntity;
	}

	public void setOcrSupportCommonEntity(OcrSupportCommonEntity ocrSupportCommonEntity) {
		this.ocrSupportCommonEntity = ocrSupportCommonEntity;
	}

	public OcrSupportCardEntity getOcrSupportCardEntity() {
		return ocrSupportCardEntity;
	}

	public void setOcrSupportCardEntity(OcrSupportCardEntity ocrSupportCardEntity) {
		this.ocrSupportCardEntity = ocrSupportCardEntity;
	}

	public OcrSupportDrivingLicenseEntity getOcrSupportDrivingLicenseEntity() {
		return ocrSupportDrivingLicenseEntity;
	}

	public void setOcrSupportDrivingLicenseEntity(OcrSupportDrivingLicenseEntity ocrSupportDrivingLicenseEntity) {
		this.ocrSupportDrivingLicenseEntity = ocrSupportDrivingLicenseEntity;
	}

	public OcrSupportTravelLicenseEntity getOcrSupportTravelLicenseEntity() {
		return ocrSupportTravelLicenseEntity;
	}

	public void setOcrSupportTravelLicenseEntity(OcrSupportTravelLicenseEntity ocrSupportTravelLicenseEntity) {
		this.ocrSupportTravelLicenseEntity = ocrSupportTravelLicenseEntity;
	}

}

package com.ms.tools.vo;

import io.swagger.annotations.ApiModelProperty;

/**
 * 
 * @ClassName: OcrSupportExcelVo
 * @Description: OCR认证数据组装
 * @author dingjy
 * @date 2021年8月18日 上午11:23:34
 */
public class OcrSupportExcelVo {

	@ApiModelProperty("一级表头名称")
	private String firstHeader;

	@ApiModelProperty("二级表头名称")
	private String secondHeader;

	@ApiModelProperty("属性值")
	private Object data;

	@ApiModelProperty("下标")
	private Integer index;

	public void setIndex(Integer index) {
		this.index = index;
	}

	public String getFirstHeader() {
		return firstHeader;
	}

	public void setFirstHeader(String firstHeader) {
		this.firstHeader = firstHeader;
	}

	public String getSecondHeader() {
		return secondHeader;
	}

	public void setSecondHeader(String secondHeader) {
		this.secondHeader = secondHeader;
	}

	public Object getData() {
		return data;
	}

	public void setData(Object data) {
		this.data = data;
	}

	public Integer getIndex() {
		return index;
	}

}

4、代码：

###########controller###########
@ApiOperation("下载excel")
	@PostMapping(value = "/downloadExcel")
	@Authority(isSkip = true)
	public void downloadExcel(@ApiParam("主键ID") @RequestParam("id") Long id,HttpServletResponse response) {
		iOcrUploadFileService.downloadExcel(id,response);
	}

###################serviceImpl#########################
/**
	 * 
	* Title: downloadExcel
	* Description: 下载excel
	* @param id
	* @param response
	* @see com.ms.tools.service.IOcrUploadFileService#downloadExcel(java.lang.Long, javax.servlet.http.HttpServletResponse)
	 */
	@Override
	public void downloadExcel(Long id, HttpServletResponse response) {
		OcrUploadFileEntity ocrUploadFileEntity = ocrUploadFileDao.selectById(id);
		if (ocrUploadFileEntity == null) {
			throw new BusinessException(10004, "文件不存在");
		}
		if (ocrUploadFileEntity.getStatus() != OcrUploadFileStatusEnums.VALIDATE_COMPLETE.getId()) {
			throw new BusinessException(10004, "文件状态不合法");
		}
		List<OcrDealDataEntity> dealDataList = ocrDealDataDao.selectByUploadFileId(id);
		if (!CollectionUtils.isEmpty(dealDataList)) {
			List<OcrSupportVo> ocrSupportEntityList=new ArrayList<>();
			getOcrSupportEntityList(ocrSupportEntityList,dealDataList);
			
			List<List<OcrSupportExcelVo>> excelVoLists =new ArrayList<>();
			getOcrSupportExcelList(excelVoLists,ocrSupportEntityList);
			
			List<List<String>> header =new ArrayList<>();
			List<List<Object>> data =new ArrayList<>();
			getHeaderAndData(header,data,excelVoLists);
			
			String fileName=ocrUploadFileEntity.getZipName();
			downLoadExcel(fileName,response,header,data);
			
		}
	}

	/**
	 * 
	* @Title: downLoadExcel
	* @Description: 下载excel
	* @param fileName
	* @param response
	* @param header
	* @param data
	* @throws
	 */
	private void downLoadExcel(String fileName, HttpServletResponse response, List<List<String>> header,
			List<List<Object>> data) {
		try {
			String excelName = new String(fileName.getBytes("utf-8"), "ISO-8859-1");
			response.setHeader("Content-Disposition", "attachment; filename=" + excelName+".xlsx");
			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
			WriteSheet writeSheet = EasyExcel.writerSheet(fileName).head(header).build();
			excelWriter.write(data, writeSheet);
			excelWriter.finish();
		} catch (Exception e) {
		}
	}

	/**
	 * 
	* @Title: getHeaderAndData
	* @Description: 组装header和data
	* @param header
	* @param data
	* @param excelVoLists
	* @throws
	 */
	private void getHeaderAndData(List<List<String>> header, List<List<Object>> data,
			List<List<OcrSupportExcelVo>> excelVoLists) {
		for (int i = 0; i < excelVoLists.size(); i++) {
			List<OcrSupportExcelVo> voList = excelVoLists.get(i);
			List<Object> dataList = new ArrayList<>();
			for (int j = 0; j < voList.size(); j++) {
				OcrSupportExcelVo vo = voList.get(j);
				if (i == 0) {
					List<String> headerList = new ArrayList<>();
					if (vo.getFirstHeader().equals(vo.getSecondHeader())) {
						headerList.add(vo.getFirstHeader());
					} else {
						headerList.add(vo.getFirstHeader());
						headerList.add(vo.getSecondHeader());
					}
					header.add(headerList);
				}
				dataList.add(vo.getData());
			}
			data.add(dataList);
		}
	}

	/**
	 * 
	* @Title: getOcrSupportExcelList
	* @Description: 组装OcrSupportExcelList实体
	* @param excelVoLists
	* @param ocrSupportEntityList
	* @throws
	 */
	private void getOcrSupportExcelList(List<List<OcrSupportExcelVo>> excelVoLists,
			List<OcrSupportVo> ocrSupportEntityList) {
		for (int i = 0; i < ocrSupportEntityList.size(); i++) {
			OcrSupportVo entity = ocrSupportEntityList.get(i);
			List<OcrSupportExcelVo> excelVoList = new ArrayList<>();
			if (entity.getOcrSupportCommonEntity() != null) {
				createData(entity.getOcrSupportCommonEntity(), excelVoList);
			}
			if (entity.getOcrSupportCardEntity() != null) {
				createData(entity.getOcrSupportCardEntity(), excelVoList);
			}
			if (entity.getOcrSupportDrivingLicenseEntity() != null) {
				createData(entity.getOcrSupportDrivingLicenseEntity(), excelVoList);
			}
			if (entity.getOcrSupportTravelLicenseEntity() != null) {
				createData(entity.getOcrSupportTravelLicenseEntity(), excelVoList);
			}
			excelVoLists.add(excelVoList);
		}
	}

	/**
	 * 
	* @Title: getOcrSupportEntityList
	* @Description: 组装OcrSupportEntityList实体
	* @param ocrSupportEntityList
	* @param dealDataList
	* @throws
	 */
	private void getOcrSupportEntityList(List<OcrSupportVo> ocrSupportEntityList,
			List<OcrDealDataEntity> dealDataList) {
		//根据目录分组
		Map<String, List<OcrDealDataEntity>> mapList = dealDataList.stream()
				.collect(Collectors.groupingBy(entity -> entity.getDirectoryPath()));
		Iterator<Entry<String, List<OcrDealDataEntity>>> it = mapList.entrySet().iterator();
		while (it.hasNext()) {
			Entry<String, List<OcrDealDataEntity>> entry = it.next();
			OcrSupportVo ocrSupportEntity=new OcrSupportVo();
			OcrSupportCommonEntity commonEntity=new OcrSupportCommonEntity();
			OcrSupportCardEntity cardEntity=new OcrSupportCardEntity();
			OcrSupportTravelLicenseEntity travelLicenseEntity=new OcrSupportTravelLicenseEntity();
			OcrSupportDrivingLicenseEntity drivingLicenseEntity=new OcrSupportDrivingLicenseEntity();
			String[] pathArr=entry.getKey().split("\\/");
			commonEntity.setSecondDirectory(pathArr[3]);
			commonEntity.setThirdDirectory(pathArr[4]);
			for(OcrDealDataEntity entity:entry.getValue()) {
				if(StringUtils.isNotBlank(entity.getFailReason()) && entity.getStatus()==OcrDealDataStatusEnum.FAIL.getId()) {
					commonEntity.setFailReason(commonEntity.getFailReason() == null ? entity.getFailReason()
							: commonEntity.getFailReason() + ";" + entity.getFailReason());
				}
				switch (entity.getLicenseType()) {
				case 1://身份证
					cardEntity = ocrSupportEntity.getOcrSupportCardEntity() != null
							? ocrSupportEntity.getOcrSupportCardEntity()
							: cardEntity;
					OcrSupportCardEntity tempCardEntity=JSONObject.parseObject(entity.getRespContent(), OcrSupportCardEntity.class);
					BeanCopyUtils.copyProperties(tempCardEntity, cardEntity,false);
					if (entity.getCardType() == OcrDealDataCardTypeEnum.REVERSE.getId()
							&& entity.getStatus() == OcrDealDataStatusEnum.SUCCESS.getId()) {
						String[] timeLimitArr=tempCardEntity.getTimelimit().split("-");
						cardEntity.setStartDate(timeLimitArr[0]);
						cardEntity.setEndDate(timeLimitArr[1]);
					}
					ocrSupportEntity.setOcrSupportCardEntity(cardEntity);
					break;
				case 2://行驶证
					if(entity.getCardType()==OcrDealDataCardTypeEnum.OBVERSE.getId()) {
						OcrSupportTravelLicenseEntity tempTravelEntity=JSONObject.parseObject(entity.getRespContent(), OcrSupportTravelLicenseEntity.class);
						BeanUtils.copyProperties(tempTravelEntity,travelLicenseEntity);
					}
					ocrSupportEntity.setOcrSupportTravelLicenseEntity(travelLicenseEntity);
					break;
				case 3://驾驶证
					if(entity.getCardType()==OcrDealDataCardTypeEnum.OBVERSE.getId()) {
						OcrSupportDrivingLicenseEntity tempDrivingEntity=JSONObject.parseObject(entity.getRespContent(), OcrSupportDrivingLicenseEntity.class);
						BeanUtils.copyProperties(tempDrivingEntity,drivingLicenseEntity);
						if(entity.getStatus() == OcrDealDataStatusEnum.SUCCESS.getId()) {
							String[] expiryTimeArr=drivingLicenseEntity.getExpiryTime().split("-");
							drivingLicenseEntity.setStartDate(expiryTimeArr[0]);
							drivingLicenseEntity.setEndDate(expiryTimeArr[1]);
						}
					}
					ocrSupportEntity.setOcrSupportDrivingLicenseEntity(drivingLicenseEntity);
					break;
				default:
					break;
				}
				ocrSupportEntity.setOcrSupportCommonEntity(commonEntity);
			}
			ocrSupportEntityList.add(ocrSupportEntity);
		}
	}

	/**
	 * 
	* @Title: createData
	* @Description: 组装数据
	* @param obj
	* @param excelVoList
	* @throws
	 */
	private void createData(Object obj, List<OcrSupportExcelVo> excelVoList) {
		try {
			Class<?> cls = obj.getClass();
			// 得到所有属性
			Field[] fields = cls.getDeclaredFields();
			List<OcrSupportExcelVo> voList = new ArrayList<>();
			for (int i = 0; i < fields.length; i++) {// 遍历
				// 得到属性
				Field field = fields[i];
				if (field.getAnnotation(ExcelProperty.class) != null) {
					OcrSupportExcelVo excelVo = new OcrSupportExcelVo();
					// 打开私有访问
					field.setAccessible(true);
					// 获取属性String name = field.getName();
					// 获取属性值
					Object value = field.get(obj);
					excelVo.setData(value);
					// 获取注解
					ExcelProperty api = field.getAnnotation(ExcelProperty.class);
					if (null != api && null != api.value()) {
						String firstHeader = api.value()[0];
						String secondHeader = api.value()[1];
						excelVo.setFirstHeader(firstHeader);
						excelVo.setSecondHeader(secondHeader);
						excelVo.setIndex(api.index());
					}
					voList.add(excelVo);
				}
			}
			// 升序
			Collections.sort(voList, (a, b) -> a.getIndex().compareTo(b.getIndex()));
			excelVoList.addAll(voList);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
	}

三、解释：

不管怎样，最终我的header要变成这样的数据结构：

[[错误原因], [第二目录名], [第三目录名], [身份证信息, 姓名], [身份证信息, 身份证号码], [身份证信息, 性别], [身份证信息, 民族], [身份证信息, 出生], [身份证信息, 住址], [身份证信息, 签发机关], [身份证信息, 签发日期], [身份证信息, 失效日期], [驾驶证信息, 准驾车型], [驾驶证信息, 有效开始日期], [驾驶证信息, 有效终止日期], [驾驶证信息, 证件号], [驾驶证信息, 初次领证时间], [驾驶证信息, 姓名], [驾驶证信息, 住址], [驾驶证信息, 国籍], [驾驶证信息, 出生日期], [驾驶证信息, 性别], [行驶证信息, 号牌号码], [行驶证信息, 车辆类型], [行驶证信息, 所有人], [行驶证信息, 品牌型号], [行驶证信息, 车辆识别代号], [行驶证信息, 发动机号码], [行驶证信息, 注册日期], [行驶证信息, 发证日期], [行驶证信息, 住址], [行驶证信息, 使用性质]]

然后data，只需要对应每一个header的值即可，如下图示所示：

小丁丐

关注

5
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
EasyExcel动态生成表头

一、需求需要实现根据数据库里面的数据，动态的生成表头，如图所示：解析：身份证信息、驾驶证信息、行驶证信息是需要随着数据库里面的证件类型动态变化的，也就是说如果证件类型只选择了身份证信息，那么这个excel导出里面只能有身份证信息二、实现1、表结构--创建表tOcrUploadFile--上传文件批次表CREATE TABLE [dbo].[tOcrUploadFile]( [id] [bigint] IDENTITY(1,1) NOT NULL, [licenseType.
复制链接

扫一扫

专栏目录