最近在开发Excel导出，采用了自定义Excel注解的方式，代码如下
第一：自定义注解类ExportExcelAnnota
定义注解类考虑事项
- 注解是标注在实体对象的属性上(暂时不考虑列表合并)
- 对象属性可能是字典值，需要涉及到字典值转换
- 对象属性可能是日期，需要涉及到日期转换显示
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* excel报表导出
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME) //注解在哪个阶段执行：注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExportExcelAnnota {
/**
* 中文值
* @return
*/
String value() default "";
/**
* 名称
* @return
*/
String name() default "";
/**
* 是否是字典
* @return
*/
boolean dic() default false;
/**
* 字典主表名称
* @return
*/
String dicName() default "";
/**
* 字典主表编码
* @return
*/
String dicCode() default "";
/**
* 是否是时间
* @return
*/
boolean date() default false;
/**
* 时间格式化
* @return
*/
String pattern() default "yyyy-MM-dd";
}
第二.POI版本3.17
<poi.version>3.17</poi.version>
<poi-ooxml.version>3.17</poi-ooxml.version>
..
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
第三：实体类
import com.quantaeye.app.rest.common.annotation.ExportExcelAnnota;
import com.quantaeye.service.dto.ImsProjectDeviceDto;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.List;
/**
* 项目管理：导出对象
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectExportExcelVo {
private Long id;
@ExportExcelAnnota(value = "项目编号", name = "projectNo")
private String projectNo;
@ExportExcelAnnota(value = "项目名称", name = "projectName")
private String projectName;
@ExportExcelAnnota(value = "项目简称", name = "projetAbt")
private String projetAbt;
@ExportExcelAnnota(value = "项目类型", name = "projectType",dic = true,dicName = "projectType")
private Integer projectType;
@ExportExcelAnnota(value = "项目经理", name = "projectChargePerson")
private String projectChargePerson;
@ExportExcelAnnota(value = "市场经理", name = "managerMarket")
private String managerMarket;
@ExportExcelAnnota(value = "订单编号", name = "orderNo")
private String orderNo;
@ExportExcelAnnota(value = "订单日期", name = "orderDate",date = true,pattern = "yyyy-MM-dd")
private Date orderDate;
@ExportExcelAnnota(value = "客户名称", name = "customerName")
private String customerName;
@ExportExcelAnnota(value = "客户联系人", name = "customerContacts")
private String customerContacts;
@ExportExcelAnnota(value = "合同主体", name = "contractSubject",dic = true,dicName = "合同主体")
private Integer contractSubject;
@ExportExcelAnnota(value = "项目开始时间", name = "projectStartTime",date = true,pattern = "yyyy-MM-dd")
private Date projectStartTime;
@ExportExcelAnnota(value = "项目结束时间", name = "projectEndTime",date = true,pattern = "yyyy-MM-dd")
private Date projectEndTime;
@ExportExcelAnnota(value = "区域", name = "areaName")
private String areaName;
@ApiModelProperty(value = "设备信息-设备数量", name = "deviceDtoList")
private List<ImsProjectDeviceDto> deviceDtoList;
}
第四：工具类：使用的时候，需要预先将用到的字典集合查询出来，exportExcelDemo方法可实现简单的列表导出，exportExcelList方法是实现了对象中有List的excel导出。
import com.quantaeye.app.common.util.DateUtil;
import com.quantaeye.app.common.util.LocalDateUtil;
import com.quantaeye.app.rest.common.annotation.ExportExcelAnnota;
import com.quantaeye.app.rest.common.model.ApiResult;
import com.quantaeye.service.dto.ImsProjectDeviceDto;
import com.quantaeye.service.vo.SysDictResVo;
import com.quantaeye.service.vo.response.ProjectExportExcelVo;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* 适用于项目管理-excel列表导出
*/
@Slf4j
public class ExcelExportListUtil {
/**
* 项目管理-列表excel导出
* @param sheetName
* @param response
* @param dictMap
* @param list
*/
public static void exportExcelList(String sheetName, HttpServletResponse response,ApiResult<Map<String, List<SysDictResVo>>> dictMap,List<ProjectExportExcelVo> list){
XSSFWorkbook wb = null;
ServletOutputStream outputStream = null;
try {
wb = ExcelExportListUtil.createWorkBook(sheetName,list,dictMap,ProjectExportExcelVo.class, ImsProjectDeviceDto.class);
list.clear();
outputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=project.xlsx");
wb.write(outputStream);
} catch (IOException e) {
log.error("导出项目数据失败. msg={}", e.getMessage());
} finally {
if (outputStream != null) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error(e.getMessage());
}
}
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
log.error(e.getMessage());
}
}
}
}
/**
* 项目管理-项目Excel导出
* 单sheet报表
* @param sheetName sheet名称
* @param list
* @param targetClass
* @param targetClassTwo
* @return
*/
public static XSSFWorkbook createWorkBook(String sheetName,List<ProjectExportExcelVo> list,ApiResult<Map<String, List<SysDictResVo>>> dictMap,Class<?> targetClass,Class<?> targetClassTwo){
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
//获取列数据
List<ExportExcelAnnota> columnNamesOne = getColumnNames(null,targetClass);
List<ExportExcelAnnota> columnNamesTwo = getColumnNames(null,targetClassTwo);
List<ExportExcelAnnota> columnNames = new ArrayList<>();
columnNames.addAll(columnNamesOne);
columnNames.addAll(columnNamesTwo);
if(CollectionUtils.isNotEmpty(columnNames)){
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < columnNames.size(); i++) {
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
}
// 设置表头
int rowNum = setCellTitle(0,columnNames, wb, sheet);
//创建内容行
CellStyle cs2 = getCellStyle(wb);
if (list != null && list.size() > 0) {
//设置每行每列的值
for (int i = 0; i < list.size(); i++) {
int sindex = rowNum;
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(rowNum);
rowNum++;
ProjectExportExcelVo vo = list.get(i);
List<ImsProjectDeviceDto> flist= vo.getDeviceDtoList();
// 在row行上创建一个方格
for (short j = 0; j < columnNamesOne.size(); j++) {
Cell cell = row1.createCell(j);
ExportExcelAnnota an = columnNamesOne.get(j);
//获取值
Object value = getFieldValueByName(an.name(),list.get(i));
String cellvalue = "";
//转换：日期转换；字典转换
if(an.date()){
//日期
cellvalue = DateUtil.formatDate((Date)value,an.pattern());
cell.setCellValue(cellvalue);
}else if(an.dic()){
//从字典中获取值
cellvalue = getDictMapName(dictMap, an.dicName(), String.valueOf(value));
cell.setCellValue(cellvalue);
}else{
setCellValue(cell,value);
}
cell.setCellStyle(cs2);
}
if(CollectionUtils.isNotEmpty(flist)){
for(int k=0 ; k < flist.size();k++){
if(k > 0){
row1 = sheet.createRow(rowNum);
rowNum++;
for (short j = 0; j < columnNamesOne.size(); j++) {
Cell cell = row1.createCell(j);
cell.setCellValue("");
cell.setCellStyle(cs2);
}
}
Cell cell = null;
for (short j = 0; j < columnNamesTwo.size(); j++) {
cell = row1.createCell(columnNamesOne.size() + j);
//获取值
Object value = getFieldValueByName(columnNamesTwo.get(j).name(),flist.get(k));
String cellvalue = "";
//转换：日期转换；字典转换
if(columnNamesTwo.get(j).date()){
//日期
cellvalue = DateUtil.formatDate((Date)value,columnNamesTwo.get(j).pattern());
cell.setCellValue(cellvalue);
}else if(columnNamesTwo.get(j).dic()){
//从字典中获取值
cellvalue = getDictMapName(dictMap, columnNamesTwo.get(j).dicName(), String.valueOf(value));
cell.setCellValue(cellvalue);
}else{
setCellValue(cell,value);
}
cell.setCellStyle(cs2);
}
}
//合并单元格
if(flist.size() > 1){
for (short j = 0; j < columnNamesOne.size(); j++) {
sheet.addMergedRegion(new CellRangeAddress(sindex,rowNum-1,j,j));
}
}
}else{
Cell cell = null;
for (short j = 0; j < columnNamesTwo.size(); j++) {
cell = row1.createCell(columnNamesOne.size() + j);
cell.setCellValue("");
cell.setCellStyle(cs2);
}
}
}
}
return wb;
}
/**
* 项目管理-列表excel导出
* @param sheetName
* @param response
* @param dictMap
* @param list
*/
public static void exportExcelDemo(String sheetName, HttpServletResponse response,ApiResult<Map<String, List<SysDictResVo>>> dictMap,List<?> list){
XSSFWorkbook wb = null;
ServletOutputStream outputStream = null;
try {
wb = ExcelExportListUtil.createWorkBook(sheetName,list,dictMap,ProjectExportExcelVo.class);
list.clear();
outputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=project.xlsx");
wb.write(outputStream);
} catch (IOException e) {
log.error("导出项目数据失败. msg={}", e.getMessage());
} finally {
if (outputStream != null) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
log.error(e.getMessage());
}
}
if (wb != null) {
try {
wb.close();
} catch (IOException e) {
log.error(e.getMessage());
}
}
}
}
/**
* 单对象导出（通用）
* 单sheet报表
* @param sheetName sheet名称
* @param list
* @param targetClass
* @return
*/
public static XSSFWorkbook createWorkBook(String sheetName,List<?> list,ApiResult<Map<String, List<SysDictResVo>>> dictMap,Class<?> targetClass){
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
//获取列数据
List<ExportExcelAnnota> columnNames = getColumnNames(null,targetClass);
if(CollectionUtils.isNotEmpty(columnNames)){
// 手动设置列宽。第一个参数表示要为第几列设；第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < columnNames.size(); i++) {
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
}
// 设置表头
int rownum = setCellTitle(0,columnNames, wb, sheet);
//创建内容行
if (list != null && list.size() > 0) {
CellStyle cs2 = getCellStyle(wb);
//设置每行每列的值
for (int i = 0; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(rownum);
rownum++;
// 在row行上创建一个方格
for (short j = 0; j < columnNames.size(); j++) {
Cell cell = row1.createCell(j);
//获取值
Object value = getFieldValueByName(columnNames.get(j).name(),list.get(i));
String cellvalue = "";
//转换：日期转换；字典转换
if(columnNames.get(j).date()){
//日期
cellvalue = DateUtil.formatDate((Date)value,columnNames.get(j).pattern());
cell.setCellValue(cellvalue);
}else if(columnNames.get(j).dic()){
//从字典中获取值
cellvalue = getDictMapName(dictMap, columnNames.get(j).dicName(), String.valueOf(value));
cell.setCellValue(cellvalue);
}else{
setCellValue(cell,value);
}
cell.setCellStyle(cs2);
}
}
}
return wb;
}
/**
* 给单元格赋值
* @param cell
* @param obj
*/
private static void setCellValue(Cell cell, Object obj) {
if(obj == null){
cell.setCellValue("");
}else if(obj instanceof BigDecimal || obj instanceof Double) {
cell.setCellValue(Double.valueOf(obj.toString()));
} else if (obj instanceof String) {
cell.setCellValue(String.valueOf(obj));
} else if (obj instanceof Integer || isNumber(obj.toString())) {
cell.setCellValue(Integer.valueOf(obj.toString()));
} else if (obj instanceof Date) {
cell.setCellValue(LocalDateUtil.format((Date) obj));
} else {
cell.setCellValue(String.valueOf(obj));
}
}
/**
* 判断是否是数字
* @param str
* @return
*/
public static boolean isNumber(String str) {
java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("[1-9]{1}\d{0,9}");
java.util.regex.Matcher match = pattern.matcher(str);
if (match.matches()) {
return true;
} else {
return false;
}
}
/**
*
* @param rownum
* @param columnNames
* @param wb
* @param sheet
* @return 行数
*/
private static int setCellTitle(int rownum, List<ExportExcelAnnota> columnNames, XSSFWorkbook wb, Sheet sheet) {
if (CollectionUtils.isEmpty(columnNames)) {
return rownum;
}
// 创建第一行
Row row = sheet.createRow(rownum);
rownum++;
CellStyle cs = wb.createCellStyle();
// 创建字体
Font f = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBold(true);
setStyle(cs, f);
//设置列名
for (int i = 0; i < columnNames.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(columnNames.get(i).value());
cell.setCellStyle(cs);
}
return rownum;
}
/**
* 设置单元格样式
* @param cs
* @param f
*/
private static void setStyle(CellStyle cs, Font f) {
cs.setFont(f);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderTop(BorderStyle.THIN);
cs.setBorderBottom(BorderStyle.THIN);
cs.setAlignment(HorizontalAlignment.LEFT);
cs.setWrapText(true);//设置自动换行
}
private static CellStyle getCellStyle(XSSFWorkbook wb) {
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
// 创建第二种字体样式（用于值）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
// 设置第二种单元格的样式（用于值）
setStyle(cs, f);
return cs;
}
/**
* @param columnNames
* @param targetClass
* @return
*/
public static List<ExportExcelAnnota> getColumnNames(List<ExportExcelAnnota> columnNames, Class<?> targetClass){
if(CollectionUtils.isEmpty(columnNames)){
columnNames = new ArrayList<>();
}
//获取类的属性对象
Field[] fields = targetClass.getDeclaredFields();
for(int i=0;i<fields.length;i++){
ExportExcelAnnota api = fields[i].getAnnotation(ExportExcelAnnota.class);
if(api != null){
columnNames.add(api);
}
}
return columnNames;
}
/**
* 获取属性名数组
* */
private static String[] getFiledName(Class<?> targetClass){
Field[] fields = targetClass.getDeclaredFields();
String[] fieldNames=new String[fields.length];
for(int i=0;i<fields.length;i++){
fieldNames[i]=fields[i].getName();
ApiModelProperty api = fields[i].getAnnotation(ApiModelProperty.class);
System.out.println(api.value());
}
return fieldNames;
}
/**
* 根据属性名获取属性值
* @param fieldName
* @param o
* @return
*/
private static Object getFieldValueByName(String fieldName, Object o) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter, new Class[] {});
Object value = method.invoke(o, new Object[] {});
return value;
} catch (Exception e) {
return null;
}
}
/**
* 获取字典值
* @param dictMap
* @param keyName
* @param value
* @return
*/
public static String getDictMapName(ApiResult<Map<String, List<SysDictResVo>>> dictMap, String keyName, String value){
if (dictMap!=null && dictMap.getData()!=null){
Map<String, List<SysDictResVo>> data = dictMap.getData();
if (data.get(keyName)!=null){
List<SysDictResVo> resVoList = data.get(keyName);
Map<String, SysDictResVo> valueMap = resVoList.stream().collect(Collectors.toMap(SysDictResVo::getNum, dict -> dict));
if (valueMap.get(value)!=null){
return valueMap.get(value).getName();
}
}
}
return "";
}
}
