基于注解设置excel的字段。
使用方法:
// 导出数据
List data = new ArrayList();
String excelName = "name";
ExcelUtil.export(response, data, excelName, true);
ExcelUtil.java
package com.yuwen.common.util;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author 相柳
* @date 2021/12/16
*/
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 导出excel
*
* @param response response
* @param data data
* @param excelName excelName
* @param noFlag 添加序号的标志:true-添加,false-不添加
*/
public static <T> void export(HttpServletResponse response, List<T> data, String excelName, boolean noFlag) {
OutputStream out;
try {
// 1.创建excel和sheet
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
if (data.size() != 0) {
// 2.填充数据
// 创建一个居中格式
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
// 创建表头
Class<?> dataClass = data.get(0).getClass();
List<String> head = getHead(dataClass, noFlag);
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < head.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(head.get(i));
cell.setCellStyle(style);
}
// 2.设置数据
for (int i = 1; i <= data.size(); i++) {
T tar = data.get(i - 1);
XSSFRow dataRow = sheet.createRow(i);
Field[] fields = dataClass.getDeclaredFields();
int limit = 0;
if (noFlag) {
XSSFCell cell = dataRow.createCell(0);
cell.setCellValue(i);
cell.setCellStyle(style);
limit = 1;
}
// 通过反射插入数据
for (int j = limit; j < fields.length + limit; j++) {
XSSFCell cell = dataRow.createCell(j);
Field field = fields[j - limit];
// 有ExcelCol注解的字段才会被导出
if (field.isAnnotationPresent(ExcelCol.class)) {
field.setAccessible(true);
// 对于时间字段,根据规则格式化字段值
if (field.isAnnotationPresent(ExcelDatePattern.class)) {
String pattern = field.getAnnotation(ExcelDatePattern.class).pattern();
Date obj = (Date) field.get(tar);
SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
String format = obj == null ? "" : dateFormat.format(obj);
cell.setCellValue(format);
} else {
// 把字段进行convert转换
ExcelCol excelCol = field.getAnnotation(ExcelCol.class);
ExcelDictConvert convert = excelCol.convert().newInstance();
Object key = field.get(tar);
cell.setCellValue(key == null ? "" : convert.convert(key.toString()));
}
// 设置cell的格式
cell.setCellStyle(style);
}
}
}
}
// 3.导出excel
out = response.getOutputStream();
// 设置文件名
String fileName = excelName + ".xlsx";
// 导出
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
wb.write(out);
} catch (Exception e) {
LOGGER.error("导出" + excelName + "失败", e);
try {
out = response.getOutputStream();
LOGGER.error("导出" + excelName + "失败", e);
String fileName = "error";
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
out.write(new byte[]{});
} catch (IOException ignored) {
}
}
}
/**
* 获取表头
*
* @param cls cls
* @param noFlag noFlag
* @return java.util.List<java.lang.String>
*/
private static List<String> getHead(Class<?> cls, boolean noFlag) {
List<String> retList = new ArrayList<>();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelCol.class)) {
retList.add(field.getAnnotation(ExcelCol.class).name());
}
}
if (noFlag) {
retList.add(0, "序号");
}
return retList;
}
}
ExcelDictConvert.java
package com.yuwen.common.util;
/**
* 字段字段转换接口
*
* @author yuwen
* @date 2022/6/6
*/
public interface ExcelDictConvert {
/**
* 把key,转为对应的值
*
* @param key key
* @return java.lang.String
*/
String convert(String key);
}
ExcelDatePattern.java
package com.yuwen.common.util;
import java.lang.annotation.*;
/**
* 该注解作用于字段上,配合ExcelUtil使用
*
* @author yuwen
* @date 2022/6/2
*/
@Target(value = ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelDatePattern {
/**
* 格式化时间的规则
*
* @return java.lang.String
*/
String pattern();
}
ExcelCol.java
package com.yuwen.common.util;
import java.lang.annotation.*;
/**
* 该注解作用于字段上,配合ExcelUtil使用
*
* @author yuwen
* @date 2022/6/2
*/
@Target(value = ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCol {
/**
* 列名
*
* @return java.lang.String
*/
String name();
Class<? extends ExcelDictConvert> convert() default DefaultDictConvert.class;
}
DefaultDictConvert.java
package com.yuwen.common.util;
/**
* @author yuwen
* @date 2022/6/6
*/
public class DefaultDictConvert implements ExcelDictConvert {
@Override
public String convert(String key) {
return key;
}
}