最近项目上需要开发数据表格统计功能，每个表格都需要实现导出功能，但是每个表格导出的数据都不一致，而且存在多个合并行合并列，并且每个表格数据有些是以对象-集合-集合-集合都方式呈现，如果每个表格都实现一遍导出必定会很麻烦，因此便想到了采用通用导出实现此功能，行业内比较好用的导出插件都不适用或者比较难实现，结合以上问题，我决定自己实现通用导出，适用于大多数导出，特殊的样式和数据除外，以下是导出后的测试，并且已经应用至项目上。
实现通用导出需要实现以下功能：
- 导出的样式需要适应不同的数据格式
- 导出的数据需要能自主合并空白行和空白列
- 导出的数据如果存在常数值则需要自动转换
- 导出的数据能自适应列宽
实现功能：
- 自定义注解（用于实现列排序及常数值转换以及是否初始化）
- 删除空白行
- 合并单元格
- 自适应单元格
一、自定义注解（ExcelColumn）
package cn.com.ebidding.common.dataview.annotation;
import java.lang.annotation.*;
/**
* @version V1.0
* @description: 通用导出注解
* @author: xulin
* @date: 2022/8/29 10:01
*/
@Target({ElementType.TYPE, ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface ExcelColumn {
/**
* 表头行名称
**/
String value() default "";
/** * 排序
**/ int col() default 0;
/** * 是否初始化
**/ boolean init() default true;
/** * 常熟值编码
**/ String constantCode() default "";
}
注意：当是否初始化为 false 时，该列在当前对象初始化时不会去初始化表头行会进入子循环。
二、应用方法
package cn.com.ebidding.dataview.vo;
import cn.com.ebidding.common.dataview.annotation.ExcelColumn;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import java.util.List;
/**
* @project_name: ebs-stdv6
* @description:
* @author: xulin
* @date: 2022/8/29 15:44
*/
@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
public class TestChieldVo {
@ExcelColumn(value = "姓名", col = 1)
private String userName;
@ExcelColumn(value = "年龄", col = 2)
private Integer age;
@ExcelColumn(value = "性别", col = 3, constantCode = "GENDER")
private String sex;
@ExcelColumn(value = "其他", col = 4, init = false)
private List<TestCVo> testCVoList;
}
三、通用方法（写Excel）
/**
* 导出excel返回Excel
* @param <T>
* @return
*/
public static <T> Workbook writeExcel(List<T> dataList, Class<T> cls ,
String sheetName) {
MERGE_LIST = new ArrayList<>();
Workbook workbook = new XSSFWorkbook();
List<Field> fieldList = getFields(cls);
Sheet sheet = workbook.createSheet(sheetName);
// 行计数器
AtomicInteger rowCount = new AtomicInteger(0);
// 列计数器
AtomicInteger colCount = new AtomicInteger(0);
createHead(workbook, fieldList, sheet, rowCount, colCount);
setVal(workbook, dataList, fieldList, sheet, rowCount, 0);
// 删除空白行
getAccuracyContextNum(sheet);
// 合并单元格
mergeCell(sheet, workbook);
// 自适应单元格
adaptiveCell(sheet);
return workbook;
}
四、表头行创建方法
/**
* 创建表头行
* @param workbook
* @param fieldList
* @param sheet
* @param rowCount
* @param colCount
*/
private static void createHead(Workbook workbook,
List<Field> fieldList,
Sheet sheet,
AtomicInteger rowCount,
AtomicInteger colCount) {
AtomicReference<Row> headRow = new AtomicReference<>(sheet.getRow(rowCount.get()));
if (Objects.isNull(headRow.get())) {
headRow.set(sheet.createRow(rowCount.getAndIncrement()));
}
fieldList.forEach(field -> {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
AtomicReference<Object> colName = new AtomicReference<>("");
// 获取字段名称
if (Objects.nonNull(excelColumn)) {
colName.set(excelColumn.value());
}
// 需要初始化
if (excelColumn.init()) {
Cell cell = headRow.get().createCell(colCount.getAndIncrement());
createCellStyle(workbook, colName, cell, true, false);
}
});
}
五、设置表格样式
/**
* 设置表格样式
* @param workbook
* @param colName
* @param cell
*/
private static void createCellStyle(Workbook workbook, AtomicReference<Object> colName,
Cell cell, boolean bold, boolean wrapText) {
Object value = colName.get();
//设置格式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setWrapText(wrapText);
//设置字体
Font font = workbook.createFont();
font.setBold(bold);
//应用格式与字体
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
if (Objects.isNull(value)) {
cell.setCellValue("-");
}
if (Objects.nonNull(value)) {
if (value instanceof Date) {
cell.setCellValue(conventDateFormat(value));
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue());
} else if (value instanceof Integer || value instanceof Long) {
cell.setCellValue(Long.parseLong(value.toString()));
} else {
cell.setCellValue(value.toString());
}
}
}
六、时间转换方法
/**
* 转换时间
* @param date
* @return
*/
private static String conventDateFormat(Object date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String formatDate = sdf.format(date);
return formatDate;
}
七、设置值
/**
* 设置值
* @param workbook
* @param dataList
* @param fieldList
* @param sheet
* @param rowCount
* @param <T>
* @param colNum
*/
private static <T> void setVal(Workbook workbook,
List<T> dataList,
List<Field> fieldList,
Sheet sheet,
AtomicInteger rowCount,
int colNum) {
if (Objects.nonNull(dataList) && CollectionUtils.isNotEmpty(dataList)) {
dataList.forEach(d -> {
int rowNum = rowCount.get();
Row contentRow = Objects.isNull(sheet.getRow(rowNum)) ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
AtomicInteger aj = new AtomicInteger(colNum);
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String code = annotation.constantCode();
boolean empty = StringUtils.isNotEmpty(annotation.constantCode());
Object value = "";
try {
value = empty ? getValSetName(code, String.valueOf(field.get(d))) : field.get(d);
} catch (Exception e) {
log.error("====== 获取值失败 ======", e);
throw new Exception("导出失败");
}
// 执行子列表
if (value instanceof List) {
Class<?> aClass = ((List) value).get(0).getClass();
// 重新获取字段信息
List<Field> fields = getFields(aClass);
// 记录需要合并的列数
int cellNum = aj.get();
// 往后设置头部
if (!MERGE_LIST.contains(aClass)) {
createHead(workbook, fields, sheet, new AtomicInteger(0), aj);
MERGE_LIST.add(aClass);
}
setVal(workbook, (List<T>) value, fields, sheet, rowCount, cellNum);
} else {
setCelVal(workbook, contentRow, aj, value);
}
});
rowCount.incrementAndGet();
});
}
}
八、设置单元格内容
/**
* 设置单元格内容
* @param workbook
* @param contentRow
* @param aj
* @param value
*/
private static void setCelVal(Workbook workbook, Row contentRow, AtomicInteger aj, Object value) {
int cellNum = aj.getAndIncrement();
Cell cell = Objects.isNull(contentRow.getCell(cellNum)) ?
contentRow.createCell(cellNum) : contentRow.getCell(cellNum);
AtomicReference<Object> colName = new AtomicReference<>(value);
createCellStyle(workbook, colName, cell, false, true);
}
九、删除空白行
/**
* 删除空白行
* @param sheet
* @return
*/
public static Sheet getAccuracyContextNum(Sheet sheet) {
// 删除空行
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// 删除空行
if (isRowEmpty(row)) {
int lastRowNum = sheet.getLastRowNum();
if (i >= 0 && i < lastRowNum) {
// 将行号为i+1一直到行号为lastRowNum的单元格全部上移一行，以便删除i行
sheet.shiftRows(i + 1, lastRowNum, -1);
}
if (i == lastRowNum) {
if (row != null) {
sheet.removeRow(row);
}
}
i--;
}
}
return sheet;
}
十、计算需要合并的单元格
/**
* 合并单元格
* @param sheet
*/
public static void mergeCell(Sheet sheet, Workbook workbook) {
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(0);
short cellNum = row.getLastCellNum();
Map<Integer, Map<Integer, Integer>> mergeMap = new HashMap<>();
// 循环列
for (int i = 0; i < cellNum; i++) {
// 当前行
AtomicInteger mergeStartRow = new AtomicInteger();
AtomicInteger mergeLastRow = new AtomicInteger();
Map<Integer, Integer> integerMap = mergeMap.get(i);
if (Objects.isNull(integerMap)) {
integerMap = new HashMap<>();
mergeMap.put(i, integerMap);
}
// 循环行
for (int j = 1; j < lastRowNum; j++) {
// 当前行
Row currentRow = sheet.getRow(j);
int next = j + 1;
// 下一行
Row nextRow = sheet.getRow(next);
// 当前列
Cell currentLineCell = currentRow.getCell(i);
// 下一列
Cell nextLineCell = nextRow.getCell(i);
if (Objects.nonNull(currentLineCell)
&& StringUtils.isNotEmpty(getCellValue(currentLineCell))) {
mergeStartRow.set(j);
}
if (Objects.isNull(nextLineCell)
|| StringUtils.isEmpty(getCellValue(nextLineCell))) {
nextLineCell = nextRow.createCell(i);
nextLineCell.setCellValue("");
AtomicReference<Object> colName = new AtomicReference<>("");
createCellStyle(workbook, colName, nextLineCell, false, true);
mergeLastRow.set(next);
}
integerMap.put(mergeStartRow.get(), mergeLastRow.get() < mergeStartRow.get() ? 0 : mergeLastRow.get());
}
}
log.info("====== 处理完后需要合并单元格的数据为：{} ======", JSONObject.toJSONString(mergeMap));
// 执行合并单元格
invokeMergeCell(sheet, mergeMap);
}
十一、合并单元格
/**
* 执行合并
* @param sheet
* @param mergeMap
*/
private static void invokeMergeCell(Sheet sheet, Map<Integer, Map<Integer, Integer>> mergeMap) {
mergeMap.forEach((k, v) -> v.forEach((k1, v1) -> {
if (v1 > 0) {
sheet.addMergedRegion(new CellRangeAddress(k1, v1, k, k));
}
}));
}
十二、自适应单元格
/**
* 自适应单元格
* @param sheet
*/
private static void adaptiveCell(Sheet sheet) {
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i,sheet.getColumnWidth(i)*10/10);
}
}
十三、文件下载
/**
* 文件下载
* @param fileName
* @param wb
* @param request
* @param response
*/
public static void excelDownload(String fileName, Workbook wb, HttpServletRequest request,
HttpServletResponse response) {
FileOutputStream out = null;
FileInputStream in = null;
OutputStream os = null;
ByteArrayOutputStream bos = null;
ByteArrayInputStream inputStream = null;
String attachment = "attachment";
long contentLength = 0;
try {
fileName = fileName.concat(ExcelUtils.SUFFIX);
File file = new File(PATH.concat(fileName));
if (!file.exists() && !file.isDirectory()) {
File parentFile = file.getParentFile();
parentFile.mkdirs();
}
out = new FileOutputStream(file);
wb.write(out);
contentLength = file.length();
if (request.getHeader(BROWSER_USER_AGENT).toLowerCase().indexOf(BROWSER_FIREFOX) > 0) {
// firefox浏览器
fileName = new String(fileName.getBytes(Charsets.UTF_8.name()), Charsets.ISO_8859_1.name());
} else if (request.getHeader(BROWSER_USER_AGENT).toUpperCase().indexOf(BROWSER_MSIE) > 0) {
// IE浏览器
fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
} else if (request.getHeader(BROWSER_USER_AGENT).toUpperCase().indexOf(BROWSER_EDGE) > 0) {
// edge
fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
} else if (request.getHeader(BROWSER_USER_AGENT).toUpperCase().indexOf(BROWSER_CHROME) > 0) {
// 谷歌
fileName = new String(fileName.getBytes(Charsets.UTF_8), Charsets.ISO_8859_1.name());
} else {
fileName = new String(fileName.getBytes("gbk"), Charsets.ISO_8859_1.name());
}
response.setContentType("application/x-download");
response.addHeader("Content-Disposition", attachment + ";filename=" + fileName);
if (contentLength > 0) {
response.addHeader("Content-Length", contentLength + "");
}
in = new FileInputStream(file);
os = response.getOutputStream();
bos = new ByteArrayOutputStream();
byte[] b = new byte[1024];
int n;
while ((n = in.read(b)) != -1)
{
bos.write(b, 0, n);
}
inputStream = new ByteArrayInputStream(bos.toByteArray());
IOUtils.copy(inputStream, os);
os.flush();
bos.flush();
} catch (IOException e) {
log.error("====== 文件下载失败 ======", e);
throw new Exception("文件下载失败");
}finally {
try {
IOUtils.closeQuietly(out);
IOUtils.closeQuietly(in);
IOUtils.closeQuietly(os);
IOUtils.closeQuietly(bos);
IOUtils.closeQuietly(inputStream);
if (Objects.nonNull(wb)) {
wb.close();
}
} catch (IOException e) {
log.error("====== 流关闭异常 =====", e);
throw new Exception("文件下载失败");
}
}
}
十四、使用方法
Workbook workbook = ExcelUtils.writeExcel(data, TestVo.class, "测试");
ExcelUtils.excelDownload("测试", workbook, request, response);
以上就是本次的通用导出功能实现，属于个人想法与实现，有问题请及时指出，谢谢！！！
