springboot快速实现导出excel

ゞ浴缸里的玫瑰 2023-02-13 14:40 13阅读 0赞

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

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

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

先

1、jar包依赖

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

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

[https_blog.csdn.net_pqj222_article_details_98848325]: https://blog.csdn.net/pqj222/article/details/98848325