EasyExcel的简单使用

😂 这篇文章最后更新于1175天前,您需要注意相关的内容是否还可用。
目录导航
  • 依赖
  • 使用
    • 示例
    • Converter
    • WriteHandler
    • head
    • 筛选列导出
  • EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 github地址:https://github.com/alibaba/easyexcel

    依赖

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>3.17</version>
            </dependency>
            <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.7</version>
            </dependency>

    若是使用jar包方式请下载:https://hik.lanzoux.com/i1qEtk5mvzi

    使用

    官方文档:https://www.yuque.com/easyexcel/doc/easyexcel

    一般操作官网都有,下列介绍不创建对象方式创建Excel,并且自定义列宽、单元格对齐方式。示例为导出表格

    其中代码设置样式参考:https://www.yuque.com/easyexcel/doc/write#W4u1e

    示例

            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
            ServletOutputStream out = response.getOutputStream();
    
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    
            EasyExcel.write(out).registerConverter(new TimestampCovert())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new FixedColumnWidthStyleStrategy())
                    .head(Utils.toTead(titles))
                    .sheet("sheet1").doWrite(list);
            out.flush();

    Converter

    由于表格数据list来自数据库,包含Timestamp类型数据,因此这里需要自己写了个转换器:

    import com.alibaba.excel.converters.Converter;
    import com.alibaba.excel.enums.CellDataTypeEnum;
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.GlobalConfiguration;
    import com.alibaba.excel.metadata.property.ExcelContentProperty;
    
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    
    public class TimestampCovert implements Converter<Timestamp> {
        @Override
        public Class supportJavaTypeKey() {
            return Timestamp.class;
        }
    
        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }
    
        @Override
        public Timestamp convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            return Timestamp.valueOf(cellData.getStringValue());
        }
    
        @Override
        public CellData convertToExcelData(Timestamp timestamp, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
            return new CellData<>(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(timestamp));
        }
    }

    详细可参考:https://blog.csdn.net/fsadkjl/article/details/105823830

    WriteHandler

    示例代码中使用了样式的与列宽的Handler,FixedColumnWidthStyleStrategy继承了AbstractColumnWidthStyleStrategy,代码如下:

    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
    import org.apache.poi.ss.usermodel.Cell;
    
    import java.util.List;
    
    public class FixedColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
        private int columnWidth = 23;
    
        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    
        public FixedColumnWidthStyleStrategy() {
        }
    
        public FixedColumnWidthStyleStrategy(int columnWidth) {
            this.columnWidth = columnWidth;
        }
    }

    head

    head设置头信息,由于head是一个List<List<String>>集合,这里封装成方法,传表头集合进去生成合适的表头类型。

        public static List<List<String>> toTead(List<String> titles) {
            List<List<String>> list = new ArrayList<List<String>>();
            List<String> head = new ArrayList<String>();
            for (String title : titles) {
                head.add(title);
                list.add(head);
                head = new ArrayList<String>();
            }
            return list;
        }

    筛选列导出

    可以根据传入的需要显示的字段titles动态改变sql获取数据,若查出所有字段的话筛选如下:

        /**
         * 设置表格信息
         * @param dataList  查询出的数据
         * @param titles  需要显示的字段
         * @return
         */
        private static List<List<Object>> dataList(List<Object> dataList, List<String> titles) {
            List<List<Object>> list = new ArrayList<>();
            for (Object person : dataList) {
                List<Object> data = new ArrayList<>();
                for (String fieldName : titles) {
                    /**通过反射根据需要显示的字段,获取对应的属性值*/
                    data.add(getFieldValue(fieldName, person));
                }
                list.add(data);
            }
            return list;
        }
    
        /**
         * 根据传入的字段获取对应的get方法,如name,对应的getName方法
         * @param fieldName  字段名
         * @param person    对象
         * @return
         */
        private static Object getFieldValue(String fieldName, Object person) {
            try {
    
                String firstLetter = fieldName.substring(0, 1).toUpperCase();
                String getter = "get" + firstLetter + fieldName.substring(1);
                Method method = person.getClass().getMethod(getter);
                return method.invoke(person);
            } catch (Exception e) {
                logger.error("使用反射获取对象属性值失败", e);
                return null;
            }
    
        }

    参考:https://my.oschina.net/zjiamin/blog/4710076