ExportExcelUtil类
package com.ml.support.excel;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.ml.support.core.BusinessException;
import com.ml.support.core.ErrorCode;
import com.ml.support.utils.Reflections;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import static org.apache.poi.ss.usermodel.HorizontalAlignment.*;
@Slf4j @Getter
public class ExcelMoreSheetUtil {
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
Map<String, Integer> rownumMap = new LinkedHashMap<>();
/**
* 拥有合并单元格的行
*/
private Set<Integer> mergedRows = Sets.newHashSet();
/**
* key 为sheetName
* value 为表头标题
*/
Map<String, List<String>> headerMap = new HashMap<>();
/**
* key 为sheetName
* value 为数据集
*/
Map<String, List<Object[]>> annotationMap = new LinkedHashMap<>();
/**
* @param isAutoCreateHeader 只能创建单行标题
*/
public ExcelMoreSheetUtil(Map<String, Class<?>> clsMap, int type, boolean isAutoCreateHeader, int... groups) {
//遍历多个sheet
for (Map.Entry<String, Class<?>> entry : clsMap.entrySet()) {
List<Object[]> annotationList = Lists.newArrayList();
String sheetName = entry.getKey();
Class<?> cls = entry.getValue();
// Get annotation statisfield
Field[] fs = cls.getDeclaredFields();
for (Field f : fs) {
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == type)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms) {
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type() == 0 || ef.type() == type)) {
if (groups != null && groups.length > 0) {
boolean inGroup = false;
for (int g : groups) {
if (inGroup) {
break;
}
for (int efg : ef.groups()) {
if (g == efg) {
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
} else {
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField) o1[0]).sort()).compareTo(
new Integer(((ExcelField) o2[0]).sort()));
}
});
// Initialize
List<String> headerList = Lists.newArrayList();
for (Object[] os : annotationList) {
String t = ((ExcelField) os[0]).title();
// 如果是导出，则去掉注释
if (type == 1) {
String[] ss = StringUtils.split(t, "**", 2);
if (ss.length == 2) {
t = ss[0];
}
}
headerList.add(t);
}
// 能否在此处理数据
headerMap.put(sheetName, headerList);
annotationMap.put(sheetName, annotationList);
}
if (isAutoCreateHeader) createHeader();
}
/**
* 创建头部
*/
public void createHeader() {
if (this.wb == null) {
this.wb = new SXSSFWorkbook(500);
}
//遍历map创建响应的sheet
for (Map.Entry<String, List<String>> entry : headerMap.entrySet()) {
int rownum = 0;
String key = entry.getKey();
List<String> headerList = entry.getValue();
Sheet sheet = wb.getSheet(key);
if (sheet == null) {
sheet = wb.createSheet(key);
}
this.styles = createStyles(wb);
// add title
if (StringUtils.isNotBlank(key)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(key);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new BusinessException(ErrorCode.ERR_20604);
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment = sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
// sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
rownumMap.put(key, rownum);
}
}
/**
* 添加数据（通过annotation.ExportField添加数据）
*
* @return dataMap 数据列表,用于注解解析
*/
public <E> ExcelMoreSheetUtil setDataSource(Map<String, List<E>> dataMap) {
int rownum;
for (Map.Entry<String, List<E>> entry : dataMap.entrySet()) {
String sheetName = entry.getKey();//sheet名称
rownum = rownumMap.get(sheetName);//获取本sheet的row行数
//根据sheet名称获取sheet对象
Sheet fileSheet = getSheet(sheetName);
List<E> list = entry.getValue();
for (E e : list) {
int colunm = 0;
Row row = fileSheet.createRow(rownum++);
StringBuilder sb = new StringBuilder();
List<Object[]> annotationList = annotationMap.get(sheetName);
for (Object[] os : annotationList) {
ExcelField ef = (ExcelField) os[0];
Object val = null;
// Get entity value
try {
if (StringUtils.isNotBlank(ef.value())) {
val = Reflections.invokeGetter(e, ef.value());
} else {
if (os[1] instanceof Field) {
val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
} else if (os[1] instanceof Method) {
val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[]{}, new Object[]{});
}
}
// If is dict, get dict label
if (StringUtils.isNotBlank(ef.dictType())) {
//val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
}
} catch (Exception ex) {
// Failure to ignore
val = "";
}
this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
sb.append(val + ", ");
}
}
}
return this;
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = Maps.newHashMap();
CellStyle style = wb.createCellStyle();
style.setAlignment(CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBold(true);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
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(LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CENTER);
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());//单元格背景颜色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);//单元格内字体颜色
headerFont.setColor(IndexedColors.BLACK.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/**
* 添加一个单元格
*
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式（1：靠左；2：居中；3：靠右）
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
Cell cell = row.createCell(column);
String cellFormatString = "@";
try {
if (val == null) {
val = "";
} else if (fieldType != Class.class) {
cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
} else {
if (val instanceof String) {
cell.setCellValue((String) val);
} else if (val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString = "0";
} else if (val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString = "0";
} else if (val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString = "0.00";
} else if (val instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) val).doubleValue());
} else if (val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString = "0.00";
} else if (val instanceof Date) {
cell.setCellValue((Date) val);
cellFormatString = "yyyy-MM-dd HH:mm";
} else {
cell.setCellValue((String) Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype." + val.getClass().getSimpleName() + "Type")).getMethod("setValue", Object.class).invoke(null, val));
}
}
if (val != null) {
if (styles == null) this.styles = createStyles(wb);
CellStyle style = styles.get("data_column_" + column);
if (style == null) {
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
if (!Strings.isNullOrEmpty(cellFormatString)) {
style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
}
styles.put("data_column_" + column, style);
}
cell.setCellStyle(style);
}
} catch (Exception ex) {
cell.setCellValue(val.toString());
}
return cell;
}
/**
* 根据sheet名称获取sheet
*/
public Sheet getSheet(String sheetName) {
Sheet sheet = wb.getSheet(sheetName);
return sheet;
}
/**
* 输出数据流
*
* @param os 输出数据流
*/
public ExcelMoreSheetUtil write(OutputStream os) throws IOException {
wb.write(os);
return this;
}
/**
* 输出到文件
*/
public ExcelMoreSheetUtil writeFile(String name) throws IOException {
FileOutputStream os = new FileOutputStream(name);
this.write(os);
return this;
}
}
注解类：
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
*/
String value() default "";
/**
* 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
*/
String title();
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
int type() default 0;
/**
* 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
*/
int align() default 0;
/**
* 导出字段字段排序（升序）
*/
int sort() default 0;
/**
* 如果是字典类型，请设置字典的type值
*/
String dictType() default "";
/**
* 反射类型
*/
Class<?> fieldType() default Class.class;
/**
* 字段归属组（根据分组导出导入）
*/
int[] groups() default {};
}
测试例子：
Map<String, Class<?>> clsMap = new LinkedHashMap<String, Class<?>>();
clsMap.put("sheetName1", BaseAdmissionsStudentsInput.class);
clsMap.put("sheetName2", BaseAdmissionsStudentsInput.class);
Map<String, List<BaseAdmissionsStudentsInput>> dataSource = new LinkedHashMap<String, List<BaseAdmissionsStudentsInput>>();
//sheet1数据
List<BaseAdmissionsStudentsInput> cusAccount = Lists.newArrayList();
BaseAdmissionsStudentsInput input = new BaseAdmissionsStudentsInput();
input.setUnitName("单位1");
cusAccount.add(input);
dataSource.put("sheetName1", cusAccount);
//sheet2数据
List<BaseAdmissionsStudentsInput> cusRatio = Lists.newArrayList();
BaseAdmissionsStudentsInput input2 = new BaseAdmissionsStudentsInput();
input2.setUnitName("单位2");
cusRatio.add(input2);
dataSource.put("sheetName2", cusRatio);
ExcelMoreSheetUtil excelUtil = new ExcelMoreSheetUtil(clsMap, 1, true).setDataSource(dataSource);
try {
excelUtil.writeFile("C:/file/template/测试sheet模板.xlsx");
} catch (IOException e) {
e.printStackTrace();
}