1.EasyExcel 导出

1.1. Response流导出单sheet页

1.1.1 response流处理（防止中文乱码）：

		response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(getTitle(), "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

1.1.2 根据excel映射对象导出（适合结构化数据：列表台账）

excel导出实体：

@Data
@NoArgsConstructor
//@ContentRowHeight(50)//内容单元格高度
//@HeadRowHeight(50)//表头单元格高度
//@ColumnWidth(50)//单元格宽度
public class DemoExcelBO {
    @ExcelProperty(value = {"导出台账","序号"})
    private Integer no;
    @ExcelProperty(value = {"导出台账","编号"})
    private  String  code;
}

执行实际的导出动作：

        //要导出的excel 数据
        List<DemoExcelBO> dataList = new ArrayList<>();
        //指定outputStream和 excel映射实体
         EasyExcel.write(response.getOutputStream(), DemoExcelBO.class)
         			//注册 WriteHandler ，getDefaultWriteHandler() 见下
                    .registerWriteHandler(getDefaultWriteHandler())
                    //注入原生 或 自定义转换器
                    .registerConverter(new DateStringConverter())
                    .registerConverter(new BigDecimalStringConverter())
                    .sheet("这是指定要写入的sheet页")
                    .doWrite(dataList );

1.1.3. 根据单元格集合导出（适合非结构化数据：详情页）

		//执行excel写入对象
        ExcelWriter excelWriter = EasyExcel.write(url).build();
        //当前sheet页 写入对象创建者
        ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet("测试sheet");
        //todo sheetBuilder  可以注入 writeHandler
        WriteSheet writeSheet = sheetBuilder.build();
        //实际要写入的数据 ，List<Object> 为每一行的合并前的数据，放入List中为整个要写入的数据
        List<List<Object>> dataList = new ArrayList<>();
        //可以多次调用  excelWriter.write() 方法 。 写入同一sheet 的话 ，WriteSheet 对象不变
        excelWriter.write(list, writeSheet);
        //关流
        excelWriter.finish();

1.2. 单元格样式（背景色、加粗、字体、边框）

HorizontalCellStyleStrategy 分别设置 表头 和 表体 的样式：

	//默认设置的样式 （表头样式 和 表体样式 不同）
    default WriteHandler getDefaultWriteHandler() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
//        headWriteFont.setBold(true);// 加粗
        headWriteFont.setFontHeightInPoints((short) 11);//字体大小
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(false);//自动换行
//        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);//CENTER
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);//THIN
        contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
        contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
        contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
        contentWriteCellStyle.setWrapped(false);//自动换行
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;

    }

1.3. 单元格合并策略

easyExcel 抽象父类类 ：AbstractMergeStrategy

public abstract class AbstractMergeStrategy implements CellWriteHandler {
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
		//在创建单元格之前调用
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
        Head head, Integer relativeRowIndex, Boolean isHead) {
		//创建单元格后调用
	}

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //在单元格上的所有操作完成后调用
        if (isHead) {
            return;
        }
        merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
    }

    //合并策略
    protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex);
}

1.3.1 只合并一次

easyExcel 预制子类：OnceAbsoluteMergeStrategy

//创建单元格时只合并一次
public class OnceAbsoluteMergeStrategy extends AbstractMergeStrategy {
    private int firstRowIndex;
    private int lastRowIndex;
    private int firstColumnIndex;
    private int lastColumnIndex;
}

1.3.2. 循环合并

easyExcel 预制子类：LoopMergeStrategy

//循环区域合并
public class LoopMergeStrategy extends AbstractMergeStrategy {
    private int eachRow;
    private int columnCount;
    private int columnIndex;
}

继承 AbstractMergeStrategy 重写 merge（）方法 ，可自定义合并策略

1.4. 列宽样式策略

easyExcel 抽象父类：

public abstract class AbstractColumnWidthStyleStrategy implements CellWriteHandler, NotRepeatExecutor {

    @Override
    public String uniqueValue() {
        return "ColumnWidthStyleStrategy";
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
		//在创建单元格之前调用
}

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
        Head head, Integer relativeRowIndex, Boolean isHead) {
		//在创建单元格之后调用
}

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    	 //在单元格上的所有操作完成后调用
        setColumnWidth(writeSheetHolder, cellDataList, cell, head, relativeRowIndex, isHead);
    }

 	//列宽样式策略
    protected abstract void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
        Integer relativeRowIndex, Boolean isHead);

}

1.4.1. 根据每个列标题返回列宽

easyExcel 预制子类：AbstractHeadColumnWidthStyleStrategy

//根据每个列标题返回列宽
public abstract class AbstractHeadColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
}

1.4.2. 以最长列的宽度作为所有列的宽度

easyExcel 预制子类：LongestMatchColumnWidthStyleStrategy

//以最长列的宽度作为宽度
public class LongestMatchColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
}

1.4.3. 定长列宽

easyExcel 预制子类：SimpleColumnWidthStyleStrategy

//所有列的宽度相同
public class SimpleColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy {
}

继承 AbstractColumnWidthStyleStrategy 重写 setColumnWidth（）方法 ，可自定义列宽策略

1.4.4. 自动调整列宽（推荐）

在这里插入代码片

1.5. 转换器

easyExcel 转换器 父接口：

public interface Converter<T> {

	// 写入excel时 ，指定一个class ，该class 会在写入时 被拦截执行转换
    Class supportJavaTypeKey();
    // 读取excel时 ，指定一个excel字段类型，该类型 会在读取时 被拦截执行转换
    CellDataTypeEnum supportExcelTypeKey();

	//写入时 ， java 类型 转换 excel数据类型 动作
    T convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) throws Exception;

   //读取时 ， excel数据类型 转换  java 类型动作
    CellData convertToExcelData(T value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
        throws Exception;
}

有预制转换器，均实现改接口，如需自定义转换器，可实现 Converter 接口。

1.5.1. 转换器注入方式

方式一： 在excel实体上指定 某些特定字段生效

public class ContractAccountExcelBO {
    @ExcelProperty(value = {"合同导出台账","","序号"} ,converter = IntegerStringConverter.class)
    private Integer no;
}

方式二： .registerConverter() 方法注入 ，本次读取或写入时，均生效

 //要导出的excel 数据
List<DemoExcelBO> dataList = new ArrayList<>();
EasyExcel.write(response.getOutputStream(),DemoExcelBO.class )
                    .registerConverter(new BigDecimalStringConverter())
                    .doWrite(dataList);//

1.5.2. 转换器 注解

指定日期格式注解：@DateTimeFormat(“yyyy-MM-dd”)
指定数字格式注解：@NumberFormat("#.00")
NumberFormat 中 format格式 遵循 Bigdecimal.format() 策略（#不补位，没有数字则空。0自动补位，没有数字自动补零）。

1.6. RowWriteHandler 行处理器

// 行创建 动作 拦截器
public interface RowWriteHandler extends WriteHandler {
	//行 创建前执行
    void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex,
        Integer relativeRowIndex, Boolean isHead);
	//行 创建后执行
    void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Integer relativeRowIndex, Boolean isHead);
	//在该行的所有操作完成后调用。填充数据时不调用此方法。
    void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Integer relativeRowIndex, Boolean isHead);
}

1.7. CellWriteHandler 单元格处理器

// 单元格创建 动作 拦截器
public interface CellWriteHandler extends WriteHandler {
	//单元格创建 前动作
    void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
        Integer columnIndex, Integer relativeRowIndex, Boolean isHead);
	//单元格创建 后动作
    void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
        Integer relativeRowIndex, Boolean isHead);
	//在单元格上的所有操作完成后调用
    void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead);
}

1.8. 图片导出

EasyExcel支持5种图片类型导出，根据1.1.3.把图片形式放入单元格中，写会即可

@Data
@ContentRowHeight(200)
@ColumnWidth(200 / 8)
public class ImageData {
    // 图片导出方式有5种
    private File file;
    private InputStream inputStream;
    /**
     * 如果string类型 必须指定转换器，string默认转换成string，该转换器是官方支持的
     */
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;
    private byte[] byteArray;
    /**
     * 根据url导出 版本2.1.1才支持该种模式
     */
    private URL url;
}

Excel解析工具easyexcel全面探索
easyexcel复杂格式导出、自定义合并
图片导出

使用EasyExcel导出

下载模板

导入excel功能，都需要对应的模板导入，也有动态的表头数据导入，可以看excel行数据不规则解析匹配。这里一般都是固定excel文件，存放在项目路径下面，下载模板的时候都是找对应的文件。

@GetMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) {
        String fileName = "导入模板.xlsx";
        ClassPathResource classPathResource = new ClassPathResource("template/file.xlsx");
        try (InputStream inputStream = classPathResource.getInputStream();
             OutputStream out = response.getOutputStream();) {
            setResponseAttribute(fileName, response);
            Map<String, String> map = new HashMap<>(4);
            int year = DateUtil.thisYear();
            map.put("lastYear", String.valueOf(year - 1));
            map.put("currentYear", String.valueOf(year));
            ExcelWriterBuilder writerBuilder = EasyExcel.write(out).withTemplate(inputStream);
            //这边第二个sheet页面里面有个参数需要动态生成，指定了往第二个sheet页里面填充值
            writerBuilder.sheet(1).doFill(map);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

数据下载

表头数据修改

一般导出数据固定的话都可以使用对象导入，建立对应的对象，使用EasyExcel中相应的注解，对Excel中单元格样式，字体样式，背景颜色等等都可以实现。

/**
 * 
 * @author 洛必达法则不会
 * @since 2021-07-22
 */
@Data
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
@HeadFontStyle(fontHeightInPoints = 10, fontName = "微软雅黑")
@ContentFontStyle(fontHeightInPoints = 10)
@HeadRowHeight(18)
@ColumnWidth(14)
public class TalentSituationHeadData {
    /**
     * 年份
     */
    @ColumnWidth(8)
    @ExcelProperty(value = "年份", index = 0)
    private String year;

    /**
     * 事业部
     */
    @ExcelProperty(value = "事业分部", index = 1)
    private String deptName;
    
    /**
     * 待招岗位
     */
    @ExcelProperty(value = "待招岗位", index = 2)
    private String recruitedPost;

    /**
     * 姓名
     */
    @HeadStyle(fillForegroundColor = 44)
    @ExcelProperty(value = "姓名", index = 3)
    private String name;
}

本次功能导入需要导出一个Excel包含多个sheet页，其中一个sheet页面的表头需要根据时间动态生成，直接使用@ExcelProperty注解，value值都是固定的。

// 首先尝试的通过 WriteSheet中head属性获取表头的字段，再修改里面对应的值，实际运行中属性值是null，此方法不可以
WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index,sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build();
List<List<String>> headList = potentialCustomerSheet.getHead();
//另一种就是重新构造List<List<String>> headList，通过EasyExcel.write(fileName).head(headList).sheet("模板");写入表头,这种方式在对象的字体颜色注解就没有，需要重新构造对应的格式，也比较繁琐
List<List<String>> headList = buildHeadDataList();
EasyExcel.write(fileName).head(headList).sheet("模板");
//之前的逻辑写好，不想用上面的方法，后面就采用反射的方式，拿到对应属性上面的注解，修改里面的值再通过EasyExcel.writerSheet写入
String year = StrUtil.isNotBlank(param.getStartDate())
                ? param.getStartDate().substring(0, 4)
                : Integer.toString(DateUtil.thisYear());
Field yearEstimateField = PotentialCustomerHeadData.class.getDeclaredField("yearEstimate");
yearEstimateField.setAccessible(true);
ExcelProperty excel = yearEstimateField.getAnnotation(ExcelProperty.class);
InvocationHandler excelH = Proxy.getInvocationHandler(excel);
// 获取 AnnotationInvocationHandler 的 memberValues 字段
Field excelF = excelH.getClass().getDeclaredField("memberValues");
excelF.setAccessible(true);
Map excelValues = (Map) excelF.get(excelH);
excelValues.put("value", new String[]{"项目利润", String.format("%s预估\n（万元/年）", year)});
WriteSheet potentialCustomerSheet = EasyExcel.writerSheet(index, sheetNameEnum.getName()).head(PotentialCustomerHeadData.class).build();

合并单元格

刚好一个表单导出的时候，需要实现单元格合并，实现AbstractMergeStrategy类的方法，具体合并策略根据业务需求实现。

//策略类
public class CellMergeStrategy extends AbstractMergeStrategy {
    private Map<String, List<RowRange>> strategyMap;
    private Sheet sheet;

    public CellMergeStrategy() {
    }

    public CellMergeStrategy(Map<String, List<RowRange>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<RowRange>> entry : strategyMap.entrySet()) {
                int columnIndex = Integer.parseInt(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }
}

/**
 * Excel工具类
 */
public class ExcelUtil {
    //获取需要合并的单元格
    public static Map<String, List<RowRange>> getProfitReachedMergeStrategy(List<ProfitReachedHeadData> profitReachedHeadDataList) {
        Map<String, List<RowRange>> strategyMap = new HashMap<>();
        ProfitReachedHeadData previousData = null;
        int size = profitReachedHeadDataList.size();
        for (int i = 0; i < size; i++) {
            ProfitReachedHeadData currentData = profitReachedHeadDataList.get(i);
            if (previousData != null) {
                if (currentData.getDeptName().equals(previousData.getDeptName())) {
                    fillStrategyMap(strategyMap, "0", i);
                }
                if (currentData.getOrgName().equals(previousData.getOrgName())) {
                    fillStrategyMap(strategyMap, "1", i);
                }
            }
            previousData = currentData;
        }
        return strategyMap;
    }
    
    /**
     * 增加合并策略
     * @param strategyMap   Map<String, List<RowRange>>
     * @param key           String
     * @param index         int
     */
    private static void fillStrategyMap(Map<String, List<RowRange>> strategyMap, String key, int index) {
        List<RowRange> rowRangeList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (RowRange rowRange : rowRangeList) {
            //分段list中是否有end索引是上一行索引的，如果有，则索引+1
            if (rowRange.getEnd() == index) {
                rowRange.setEnd(index + 1);
                flag = true;
            }
        }
        //如果没有，则新增分段
        if (!flag) {
            rowRangeList.add(new RowRange(index, index + 1));
        }
        strategyMap.put(key, rowRangeList);
    }
    
    public class RowRange {
        private int start;
        private int end;
	}
}

//业务实现
 List<ProfitReachedHeadData> profitSummaryHeadDataList = profitSummaryService.buildProfitReachedHeadDataList(entry.getValue());
Map<String, List<RowRange>> summaryStrategyMap = ExcelUtil.getProfitReachedMergeStrategy(profitSummaryHeadDataList);
WriteSheet profitSummarySheet = EasyExcel.writerSheet(index, String.format("%s-%s合计-%s月", sheetNameEnum.getName(),permissionOperate.getDeptName(),entry.getKey().substring(5))).registerWriteHandler(new CellMergeStrategy(summaryStrategyMap)).head(ProfitReachedHeadData.class).build();
excelWriter.write(profitSummaryHeadDataList, profitSummarySheet);

如题，使用easyExcel简单的导出一下数据，代码如下：
首先在pom.xml里面引入如下依赖:

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>

controller层代码：

@RestController
@RequestMapping("/export")
public class ExportTestController {

    private static Logger log= LoggerFactory.getLogger(ExportTestController.class);
    @RequestMapping("/hello")
    public String hello(){
        return "hello world!";
    }

    @Autowired
    private ExcelTestService excelTestService;

    /**
     * 史上最简单的数据数据导出demo
     * 开箱即会
     */
    @RequestMapping("/exporttest")
    public void exporttest(HttpServletResponse response){
        excelTestService.export(response);
    }
}

service层代码：

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hdd.exportTest.model.Users;
import com.hdd.exportTest.service.ExcelTestService;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName ExcelTestServiceImpl
 * @DesCription TODO
 * @Author 584654
 * @Date 2021/10/31 9:08
 * @Version 1.0
 * @PackageName com.hdd.exportTest.service.impl
 **/
@Service
public class ExcelTestServiceImpl implements ExcelTestService {

    public void export(HttpServletResponse response){
        //需要导出的数据
        List<Users> list=getUsersList();
        try {
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            String fileName = new String("用户信息表格"
                    .getBytes("utf-8"), "iso8859-1");
            Sheet sheet1 = new Sheet(1, 0, Users.class);
            sheet1.setSheetName("sheet1");

            response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.write(list, sheet1);
            writer.finish();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<Users> getUsersList() {
        List<Users> usersList=new ArrayList<Users>();
        usersList.add(new Users("高俊也",28,"男"));
        usersList.add(new Users("李雪琴",25,"女"));
        return usersList;
    }
}

model层代码:

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.io.Serializable;

/**
 * @ClassName Users
 * @DesCription TODO
 * @Author 584654
 * @Date 2021/10/31 9:13
 * @Version 1.0
 * @PackageName com.hdd.exportTest.model
 **/
@Data
public class Users extends BaseRowModel implements Serializable {

    private static final long serialVersionUID = 15353L;

    @ExcelProperty(value = "姓名",index = 0)
    private String name;

    @ExcelProperty(value = "年龄",index = 1)
    private Integer age;

    @ExcelProperty(value = "性别",index = 2)
    private String sex;

    public Users(String name, Integer age, String sex) {
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
}

启动项目后导出数据结果如下: