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;
}

 

 

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