- 一、完成相关配置
- 1.导入依赖
- 2.定义Excel注解以及Excels注解
- 3、给实体类添加Excel注解
- 二、Excel方法类
- 1.大致路线
- 2.定义ExcelUtil中的变量
- 3.ExcelUtil中的Excel导出方法
- 3.1 exportExcel(List list, String sheetName)
- init(List list, String sheetName, Type type)
- 3.2 exportExcel()
- 3.2.1.createSheet()创建工作表
- 3.2.2createCell()创建单元格
- 3.2.3 fillExcelData()填充excel数据
- 3.2.4.addStatisticsRow()创建统计行
- 4.ExcelUtil中的返回给前端文件名部分
- 三、controller
2.定义Excel注解以及Excels注解org.apache.poi poi-ooxml
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
public int sort() default Integer.MAX_VALUE;
public String name() default "";
public String dateFormat() default "";
public String dictType() default "";
public String readConverterExp() default "";
public String separator() default ",";
public int scale() default -1;
public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
public ColumnType cellType() default ColumnType.STRING;
public double height() default 14;
public double width() default 16;
public String suffix() default "";
public String defaultValue() default "";
public String prompt() default "";
public String[] combo() default {};
public boolean isExport() default true;
public String targetAttr() default "";
public boolean isStatistics() default false;
Align align() default Align.AUTO;
public enum Align
{
AUTO(0), LEFt(1), CENTER(2), RIGHt(3);
private final int value;
Align(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
Type type() default Type.ALL;
public enum Type
{
ALL(0), EXPORT(1), IMPORT(2);
private final int value;
Type(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
public enum ColumnType
{
NUMERIC(0), STRING(1);
private final int value;
ColumnType(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excels {
Excel[] value();
}
3、给实体类添加Excel注解
@Data
@TableName("t_enterprise")
public class Enterprise {
@TableField(exist = false)
private List category;
@TableField(exist = false)
private Region region;
@TableField(exist = false)
private User user;
@TableField(exist = false)
private ListcategoryId;//企业分类id
@TableField(exist = false)
private String operateStatus;
@TableId(type = IdType.AUTO)
@Excel(name = "序号", cellType = Excel.ColumnType.NUMERIC)
private Integer id;
@Excel(name = "企业名称")
private String name;
@Excel(name = "所属区域")
private Integer regionId;
@Excel(name = "详细地址")
private String address;
@Excel(name = "注册资本")
private Long registerCapital;
@Excel(name = "成立日期",width = 30, dateFormat = "yyyy-MM-dd")
private LocalDateTime establishDate;
@Excel(name = "经营范围")
private String businessScope;
@Excel(name = "联系方式")
private String phone;
@Excel(name = "企业LOGO")
private String logoUrl;
@Excel(name = "企业用户ID")
private Integer enterpriseUserId;
@Excel(name = "视频推送状态#0:正常#1:优推#2:下架")
private Integer pushStatus;
@Excel(name = "优势产品")
private String featureProduct;
@Excel(name = "优势分析")
private String advantageAnalysis;
@Excel(name = "隐私设置#0:显示#1:隐藏")
private String privacySetting;
@Excel(name = "运营状态ID")
private Integer operateStatusId;
@Excel(name = "统一信用社会代码")
private String socialCode;
@Excel(name = "营业执照" )
private String licenseUrl;
@Excel(name = "法人身份证")
private String legalPersonCardUrl;
@Excel(name = "企业认证公函")
private String letterUrl;
@Excel(name = "管理人")
private String manager;
@Excel(name = "法人")
private String legalPerson;
@Excel(name = "法人联系方式")
private String legalPersonPhone;
@Excel(name = "审核状态#0:未审核#1:通过#2:驳回")
private Integer auditStatus;
@Excel(name = "驳回原因")
private String rejectionReason;
@Excel(name = "审核时间",width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime auditTime;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATe)
@Excel(name = "更新时间", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updateTime;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
@Excel(name = "创建时间", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
@TableField(exist=false)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTimeBegin;
@TableField(exist=false)
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTimeEnd;
@Excel(name = "删除标识")
private Boolean deleteFlag;
@Excel(name = "视频")
private String videoUrl;
@Excel(name="隐私设置")
@TableField(exist = false)
private String privacy;
二、Excel方法类
1.大致路线
2.定义ExcelUtil中的变量
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
public static final int sheetSize = 65536;
private String sheetName;
private Type type;
private Workbook wb;
private Sheet sheet;
private Map styles;
private List list;
private List fields;
private Map statistics = new HashMap();
private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
public Class clazz;
public ExcelUtil(Class clazz) {
this.clazz = clazz;
}
public ExcelUtil(Class3.ExcelUtil中的Excel导出方法 3.1 exportExcel(List list, String sheetName)clazz) { this.clazz = clazz;//构造方法的参数是要导出的实体类class，赋值给clazz变量 }
public ResultJson exportExcel(List list, String sheetName) {
this.init(list, sheetName, Type.EXPORT);
return exportExcel();
}
init(List list, String sheetName, Type type)
将需要的导出的数据和文件名，及区分导出还是导入的Type赋值给变量。
这里type为EXPORT（导出）。
public void init(Listlist, String sheetName, Type type) { if (list == null) { list = new ArrayList (); } this.list = list; this.sheetName = sheetName; this.type = type; createExcelField(); createWorkbook(); }
init中的createExcelField
private void createExcelField() {
this.fields = new ArrayList();
List tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
for (Field field : tempFields) {
// 单注解
if (field.isAnnotationPresent(Excel.class)) {
//isAnnotationPresent() 判断该字段是否标注该注解
putToField(field, field.getAnnotation(Excel.class));
}
// 多注解
if (field.isAnnotationPresent(Excels.class)) {
Excels attrs = field.getAnnotation(Excels.class);
Excel[] excels = attrs.value();
for (Excel excel : excels) {
putToField(field, excel);
}
}
}
this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
}
fields用来存储所有Excel注解的字段，为Object[]
clazz.getSuperclass()为得到实体类的父类方法。
getDeclaredFields()为反射中的方法，获得某个类的所有声明的字段，即包括public，private 和proteced,但是不包括父类的申明字段，所以先获取了父类的声明字段，再获取当前类的声明字段。
Array.asList()是将数组转化成List集合的方法，用此方法得到的List的长度是不可改变的
createExcelField中的putToField:
将实体类中带有Excel注解的字段（field）以及注解的参数（attr）放在了fileds
private void putToField(Field field, Excel attr) {
if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {
this.fields.add(new Object[]{field, attr});
}
}
init中的createWorkbook
public void createWorkbook() {
this.wb = new SXSSFWorkbook(500);
}
3.2 exportExcel()
对list数据源将其里面的数据导入到excel表单
执行流程为：1.createSheet() 2.createStyles() 3.createCell() 4.fillExcelData() 5.addCell()
exportExcel方法中将数据源填入excel部分：
OutputStream out = null;
try {
// 取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() / sheetSize);
for (int index = 0; index <= sheetNo; index++) {
createSheet(sheetNo, index);
// 产生一行
Row row = sheet.createRow(0);
int column = 0;
// 写入各个字段的列头名称
for (Object[] os : fields) {
Excel excel = (Excel) os[1];
this.createCell(excel, row, column++);
}
if (Type.EXPORT.equals(type)) {
fillExcelData(index, row);
addStatisticsRow();
}
}
3.2.1.createSheet()创建工作表
public void createSheet(double sheetNo, int index) {
this.sheet = wb.createSheet();
this.styles = createStyles(wb);
// 设置工作表的名称.
if (sheetNo == 0) {
wb.setSheetName(index, sheetName);
} else {
wb.setSheetName(index, sheetName + index);
}
}
createStyle():
创建表格样式
private Map createStyles(Workbook wb) {
// 写入各条记录,每条记录对应excel表中的一行
Map styles = new HashMap();
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font totalFont = wb.createFont();
totalFont.setFontName("Arial");
totalFont.setFontHeightInPoints((short) 10);
style.setFont(totalFont);
styles.put("total", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.RIGHT);
styles.put("data3", style);
return styles;
}
3.2.2createCell()创建单元格
创建完工作表，需要添加表头信息
public Cell createCell(Excel attr, Row row, int column) {
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
cell.setCellValue(attr.name());
setDataValidation(attr, row, column);
cell.setCellStyle(styles.get("header"));
return cell;
}
效果：
setDataValidation: 设置表头的单元格样式
public void setDataValidation(Excel attr, Row row, int column) {
if (attr.name().indexOf("注：") >= 0) {
sheet.setColumnWidth(column, 6000);
} else {
// 设置列宽
sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
row.setHeight((short) (attr.height() * 20));
}
// 如果设置了提示信息则鼠标放上去提示.
if (StringUtils.isNotEmpty(attr.prompt())) {
// 这里默认设了2-101列提示.
setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
}
// 如果设置了combo属性则本列只能选择不能输入
if (attr.combo().length > 0) {
// 这里默认设了2-101列只能选择不能输入.
setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
}
}
setDataValidation中的setXSSFPrompt和setXSSFValidation:
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DataValidation dataValidation = helper.createValidation(constraint, regions);
dataValidation.createPromptBox(promptTitle, promptContent);
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
}
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
DataValidation dataValidation = helper.createValidation(constraint, regions);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
3.2.3 fillExcelData()填充excel数据
public void fillExcelData(int index, Row row) {
int startNo = index * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
for (int i = startNo; i < endNo; i++) {
row = sheet.createRow(i + 1 - startNo);
// 得到导出对象.
T vo = (T) list.get(i);
int column = 0;
for (Object[] os : fields) {
Field field = (Field) os[0];
Excel excel = (Excel) os[1];
// 设置实体类私有属性可访问
field.setAccessible(true);
this.addCell(excel, row, vo, field, column++);
}
}
}
addCell(): 添加单元格方法
public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
Cell cell = null;
try {
// 设置行高
row.setHeight((short) (attr.height() * 20));
// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if (attr.isExport()) {
// 创建cell
cell = row.createCell(column);
int align = attr.align().value();
cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
// 用于读取对象中的属性
Object value = getTargetValue(vo, field, attr);
String dateFormat = attr.dateFormat();
String readConverterExp = attr.readConverterExp();
String separator = attr.separator();
String dictType = attr.dictType();
if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
cell.setCellValue(DateUtils.parseDateToStr(dateFormat, DateUtils.asDate((LocalDateTime) value)));
} else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
} else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value)) {
// cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
} else if (value instanceof BigDecimal && -1 != attr.scale()) {
cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
} else {
// 设置列类型
setCellVo(value, attr, cell);
}
addStatisticsData(column, Convert.toStr(value), attr);
}
} catch (Exception e) {
log.error("导出Excel失败{}", e);
}
return cell;
}
addCell中的getTargetValue：
private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
Object o = field.get(vo);
if (StringUtils.isNotEmpty(excel.targetAttr())) {
String target = excel.targetAttr();
if (target.indexOf(".") > -1) {
String[] targets = target.split("[.]");
for (String name : targets) {
o = getValue(o, name);
}
} else {
o = getValue(o, target);
}
}
return o;
}
getTargetValue中的getValue
private Object getValue(Object o, String name) throws Exception {
if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
Class clazz = o.getClass();
Field field = clazz.getDeclaredField(name);
field.setAccessible(true);
o = field.get(o);
}
return o;
}
java.lang.reflect.Field.get(Object obj)方法返回指定对象上由此Field表示的字段的值。如果该对象具有原始类型，则该值将自动包装在对象中。
java.lang.Class.getDeclaredField()方法返回一个Field对象，它反映此Class对象所表示的类或接口的指定已声明字段。name参数是一个字符串，指定所需的字段的简单名称。
addCell中的setCellVo方法：
public void setCellVo(Object value, Excel attr, Cell cell) {
if (Excel.ColumnType.STRING == attr.cellType()) {
cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
} else if (Excel.ColumnType.NUMERIC == attr.cellType()) {
cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
}
}
Convert是自己声明的一个工具类。toStr方法如下：
public static String toStr(Object value, String defaultValue)
{
if (null == value)
{
return defaultValue;
}
if (value instanceof String)
{
return (String) value;
}
return value.toString();
}
public static String toStr(Object value)
{
return toStr(value, null);
}
addCell中的addStatisticsData：
private void addStatisticsData(Integer index, String text, Excel entity) {
if (entity != null && entity.isStatistics()) {
Double temp = 0D;
if (!statistics.containsKey(index)) {
statistics.put(index, temp);
}
try {
temp = Double.valueOf(text);
} catch (NumberFormatException e) {
}
statistics.put(index, statistics.get(index) + temp);
}
}
addCell中的convertByExp：
public static String convertByExp(String propertyValue, String converterExp, String separator) {
StringBuilder propertyString = new StringBuilder();
String[] convertSource = converterExp.split(",");
for (String item : convertSource) {
String[] itemArray = item.split("=");
if (StringUtils.containsAny(separator, propertyValue)) {
for (String value : propertyValue.split(separator)) {
if (itemArray[0].equals(value)) {
propertyString.append(itemArray[1] + separator);
break;
}
}
} else {
if (itemArray[0].equals(propertyValue)) {
return itemArray[1];
}
}
}
return StringUtils.stripEnd(propertyString.toString(), separator);
}
3.2.4.addStatisticsRow()创建统计行
public void addStatisticsRow() {
if (statistics.size() > 0) {
Cell cell = null;
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
Set keys = statistics.keySet();
cell = row.createCell(0);
cell.setCellStyle(styles.get("total"));
cell.setCellValue("合计");
for (Integer key : keys) {
cell = row.createCell(key);
cell.setCellStyle(styles.get("total"));
cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
}
statistics.clear();
}
}
4.ExcelUtil中的返回给前端文件名部分
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
return ResultJson.ok(filename);
}catch (Exception e) {
log.error("导出Excel异常{}", e.getMessage());
} finally {
if (wb != null) {
try {
wb.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
return ResultJson.failure(ResultCode.BAD_REQUEST, "Excel导出失败");
}
encodingFilename:
public String encodingFilename(String filename) {
filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
return filename;
}
getAbsoluteFile:
public String getAbsoluteFile(String filename) {
String downloadPath = NiuaConfig.getDownloadPath() + filename;
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
return downloadPath;
}
获取下载路径中的getProfile()参考 图片上传的文件路径的设置
@GetMapping("/export")
public ResultJson export(Enterprise enterprise) {
List list = service.list();
ExcelUtil util = new ExcelUtil<>(Enterprise.class);
return util.exportExcel(list, "自动生成企业表数据");
}
首先，需要在controller层通过Mybatis-plus查询出数据，然后，初始化ExcelUtil，并调用exportExcel(List list, String sheetName)方法。
