EasyExcel的简单使用

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.lanzous.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

目录导航
  • 依赖
  • 使用
    • 示例
    • Converter
    • WriteHandler
    • head
    • 筛选列导出