springboot快速实现导出excel

56 阅读 0 评论 37 点赞

我是靠谱客的博主彩色音响，最近开发中收集的这篇文章主要介绍springboot快速实现导出excel，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

去年自己写了个导出excel的方法，其中表头需要按顺序放入数组，如：String[] headers = {"姓名", "电话", "地址"};，每一行的数据也是放入List, 这样的好处就是每一行的样式自己可以定制化，坏处就是要不一样的表格都要一个个按顺序统计表头或者数据，相对于比较简单的导出就比较麻烦。

感兴趣可点击：https://blog.csdn.net/pqj222/article/details/98848325

在此分享一个工具jar，只需要添加注解，就能轻松搞定表格的表头、表头顺序、数据等的轻易导出。

先

1、jar包依赖

<!-- easypoi
核心依赖包 -->
<poi.version>3.17</poi.version>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
<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>

2、Util类

ExcelUtil.java

package com.util;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private Class<T> tClass;
public int pageSize = 10000;
private int total;
public CellStyle bodyStyle;
public CellStyle headStyle;
public CellStyle SpecialStyle;
public ExcelUtil() {
}
public Workbook getWork(List<T> list) throws NoSuchFieldException {
Workbook wb = new HSSFWorkbook();
this.fillDefaultStyle(wb);
this.tClass = (Class)((ParameterizedType)this.getClass().getGenericSuperclass()).getActualTypeArguments()[0];
ExcelUtil.Describe classDescribe = (ExcelUtil.Describe)this.tClass.getAnnotation(ExcelUtil.Describe.class);
String tableName;
if (classDescribe != null) {
tableName = classDescribe.value();
} else {
tableName = this.tClass.getName();
}
List<ExcelUtil<T>.sortDescribe> sortDescribes = this.getHeadData();
this.total = (list == null ? 0 : list.size() + this.pageSize - 1) / this.pageSize;
if (this.total > 0) {
for(int i = 0; i < this.total; ++i) {
List<T> pageList = (List)list.stream().skip((long)(this.pageSize * i)).limit((long)this.pageSize).collect(Collectors.toList());
Sheet sheet = wb.createSheet(tableName + i);
this.setHead(sheet, sortDescribes);
int bodyIndex = 1;
for(Iterator var11 = pageList.iterator(); var11.hasNext(); ++bodyIndex) {
T item = var11.next();
Row bodyRow = sheet.createRow(bodyIndex);
int bodyCellIndex = 0;
Iterator var15 = sortDescribes.iterator();
while(var15.hasNext()) {
ExcelUtil<T>.sortDescribe entity = (ExcelUtil.sortDescribe)var15.next();
Field field = this.tClass.getDeclaredField(entity.getName());
Cell cell = bodyRow.createCell(bodyCellIndex);
cell.setCellStyle(this.bodyStyle);
String getMethodName = "get" + this.toFirstLetterUpperCase(field.getName());
ExcelUtil.Describe describe = entity.getDescribe();
try {
Object obj = this.tClass.getMethod(getMethodName).invoke(item);
if (obj != null) {
if (describe.isAmount()) {
Long amount = Long.parseLong(obj.toString());
cell.setCellValue(String.format(describe.amountFormat(), amount.doubleValue() / 100.0D));
} else if (describe.isDate()) {
SimpleDateFormat sdf = new SimpleDateFormat(describe.dateFormat());
cell.setCellValue(sdf.format((Date)obj));
} else {
cell.setCellValue(obj.toString().trim());
}
if (describe.isConcat()) {
cell.setCellValue(cell.getStringCellValue().concat(describe.concatString()));
}
} else {
cell.setCellValue(describe.isNullValue());
}
} catch (Exception var26) {
log.error("ExcelUtil->getWork", var26);
} finally {
++bodyCellIndex;
}
}
}
}
} else {
Sheet sheet = wb.createSheet(tableName);
this.setHead(sheet, sortDescribes);
}
return wb;
}
private void setHead(Sheet sheet, List<ExcelUtil<T>.sortDescribe> sortDescribes) {
Row headRow = sheet.createRow(0);
if (sortDescribes != null && sortDescribes.size() > 0) {
int headIndex = 0;
for(Iterator var5 = sortDescribes.iterator(); var5.hasNext(); ++headIndex) {
ExcelUtil<T>.sortDescribe entity = (ExcelUtil.sortDescribe)var5.next();
sheet.setColumnWidth(headIndex, 252 * entity.getDescribe().width() + 323);
sheet.autoSizeColumn((short)headIndex);
Cell cell = headRow.createCell(headIndex);
cell.setCellValue(entity.getDescribe().value());
cell.setCellStyle(this.headStyle);
}
}
}
private void fillDefaultStyle(Workbook workbook) {
Font font;
if (this.bodyStyle == null) {
this.bodyStyle = workbook.createCellStyle();
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("新宋体");
this.bodyStyle.setFont(font);
this.bodyStyle.setAlignment(HorizontalAlignment.CENTER);
}
if (this.headStyle == null) {
this.headStyle = workbook.createCellStyle();
font = workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("新宋体");
font.setBold(true);
this.headStyle.setFont(font);
this.headStyle.setAlignment(HorizontalAlignment.CENTER);
}
if (this.SpecialStyle == null) {
this.SpecialStyle = workbook.createCellStyle();
this.SpecialStyle.setAlignment(HorizontalAlignment.CENTER);
font = workbook.createFont();
font.setFontName("黑体");
font.setFontName("仿宋_GB2312");
font.setBold(true);
font.setFontHeightInPoints((short)12);
this.SpecialStyle.setFont(font);
}
}
public Workbook getWork(List<T> list, HashMap<Integer, Object> info) throws NoSuchFieldException {
Workbook workbook = this.getWork(list);
return this.fileSpecialInfo(workbook, info);
}
private String toFirstLetterUpperCase(String str) {
if (str != null && str.length() >= 2) {
String firstLetter = str.substring(0, 1).toUpperCase();
return firstLetter + str.substring(1);
} else {
return str;
}
}
private List<ExcelUtil<T>.sortDescribe> getHeadData() {
List<ExcelUtil<T>.sortDescribe> result = new ArrayList();
Field[] fields = this.tClass.getDeclaredFields();
if (fields != null && fields.length > 0) {
for(int i = 0; i < fields.length; ++i) {
ExcelUtil.Describe itemDescribe = (ExcelUtil.Describe)fields[i].getAnnotation(ExcelUtil.Describe.class);
if (itemDescribe != null) {
ExcelUtil<T>.sortDescribe describe = new ExcelUtil.sortDescribe();
describe.setIndex(itemDescribe.index());
describe.setDescribe(itemDescribe);
describe.setName(fields[i].getName());
result.add(describe);
}
}
}
return (List)result.stream().sorted(Comparator.comparing(ExcelUtil.sortDescribe::getIndex).reversed()).collect(Collectors.toList());
}
private Workbook fileSpecialInfo(Workbook workbook, HashMap<Integer, Object> info) {
if (info != null && info.size() > 0) {
Set set = info.keySet();
Object[] arr = set.toArray();
Arrays.sort(arr);
for(int i = 0; i < workbook.getNumberOfSheets(); ++i) {
Sheet itemSheet = workbook.getSheetAt(i);
Row bodyRow = itemSheet.createRow(itemSheet.getLastRowNum() + 1);
for(int k = 0; k < arr.length; ++k) {
Cell cell;
int leftSum;
if (k == 0) {
cell = bodyRow.createCell(0);
cell.setCellValue(info.get(arr[k]).toString());
leftSum = Integer.parseInt(arr[k].toString()) - 0;
} else if (k + 1 == arr.length) {
cell = bodyRow.createCell(Integer.parseInt(arr[k].toString()));
cell.setCellValue(info.get(arr[k]).toString());
} else {
cell = bodyRow.createCell(Integer.parseInt(arr[k].toString()));
cell.setCellValue(info.get(arr[k]).toString());
leftSum = Integer.parseInt(arr[k + 1].toString()) - Integer.parseInt(arr[k].toString()) - 1;
if (leftSum > 0) {
}
}
cell.setCellStyle(this.SpecialStyle);
}
}
}
return workbook;
}
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface Describe {
String value() default "";
String dateFormat() default "yyyy-MM-dd HH:mm";
boolean isDate() default false;
boolean isAmount() default false;
String amountFormat() default "%.2f";
int index() default 0;
int width() default 35;
boolean isConcat() default false;
String concatString() default "";
String isNullValue() default "";
}
public class sortDescribe {
private ExcelUtil.Describe describe;
private String name;
private Integer index;
public sortDescribe() {
}
public ExcelUtil.Describe getDescribe() {
return this.describe;
}
public String getName() {
return this.name;
}
public Integer getIndex() {
return this.index;
}
public void setDescribe(ExcelUtil.Describe describe) {
this.describe = describe;
}
public void setName(String name) {
this.name = name;
}
public void setIndex(Integer index) {
this.index = index;
}
public boolean equals(Object o) {
if (o == this) {
return true;
} else if (!(o instanceof ExcelUtil.sortDescribe)) {
return false;
} else {
ExcelUtil<?>.sortDescribe other = (ExcelUtil.sortDescribe)o;
if (!other.canEqual(this)) {
return false;
} else {
label47: {
Object this$describe = this.getDescribe();
Object other$describe = other.getDescribe();
if (this$describe == null) {
if (other$describe == null) {
break label47;
}
} else if (this$describe.equals(other$describe)) {
break label47;
}
return false;
}
Object this$name = this.getName();
Object other$name = other.getName();
if (this$name == null) {
if (other$name != null) {
return false;
}
} else if (!this$name.equals(other$name)) {
return false;
}
Object this$index = this.getIndex();
Object other$index = other.getIndex();
if (this$index == null) {
if (other$index != null) {
return false;
}
} else if (!this$index.equals(other$index)) {
return false;
}
return true;
}
}
}
protected boolean canEqual(Object other) {
return other instanceof ExcelUtil.sortDescribe;
}
public int hashCode() {
int PRIME = true;
int resultx = 1;
Object $describe = this.getDescribe();
int result = resultx * 59 + ($describe == null ? 43 : $describe.hashCode());
Object $name = this.getName();
result = result * 59 + ($name == null ? 43 : $name.hashCode());
Object $index = this.getIndex();
result = result * 59 + ($index == null ? 43 : $index.hashCode());
return result;
}
public String toString() {
return "ExcelUtil.sortDescribe(describe=" + this.getDescribe() + ", name=" + this.getName() + ", index=" + this.getIndex() + ")";
}
}
}

3、controller

@RestController
@Slf4j
@RequestMapping("/export")
@Api("导出excel")
public class DemoExportApi {
@Autowired
private IReconciliationService reconciliationService;
/**
*
* @param req
* @return
*/
@PostMapping("/exportList")
public void exportList(@Validated @RequestBody OrderListReq req, HttpServletResponse response) throws IOException, NoSuchFieldException {
//这里调用自己的service方法，得到结果list
PageInfo<OrderListResp> pageInfo = reconciliationService.getOrderList(req);
//调用ExcelUtil.getWork方法
Workbook workbook = (new ExcelUtil<OrderListResp>(){}).getWork(pageInfo.getList());
OutputStream output = response.getOutputStream();
response.reset();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmm");
String filename = "导出列表".concat(sdf.format(new Date(System.currentTimeMillis())));
response.setHeader("Content-disposition", "attachment; filename="+filename+".xlsx");
// 跨域
response.setHeader("Access-Control-Allow-Origin", "*");
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}
}

4、返回的VO类字段添加注解

@ExcelUtil.Describe(value = "安装日期", index = 29, isDate = true, dateFormat = "yyyy-MM-dd")

value：表头名称

index:索引值，各列按这个值来按倒序排序，越大的越在前面

isDate ：是否为date类型

dateFormat：为date类型时，可以指定格式化格式

OrderListResp.java

@Data
//这里加的注解，会默认为工作表的表名
@ExcelUtil.Describe("订单列表")
public class OrderListResp implements Serializable {
private static final long serialVersionUID = 8789848844535006453L;
/**
* 订单类型 0：线上，1：线下
*/
private Integer type;
/**
* 订单类型名称
*/
@ExcelUtil.Describe(value = "订单类型", index = 31)
private String orderName;
/**
* 订单号
*/
@ExcelUtil.Describe(value = "订单号", index = 30)
private String orderCode;
/**
* 安装日期
*/
@ExcelUtil.Describe(value = "安装日期", index = 29, isDate = true, dateFormat = "yyyy-MM-dd")
private Date installDate;
}

这样就可以啦，是不是很方便呢^_^