EasyExcel导出横向填充数据文件

今天公司有个统计需求,要求统计一份工作生产量表格,其中客户需要传入起止时间,我需要查询这段时间内的工作产量

模板是这样

统计日期和工序这俩行需要随着日期的增多而向右合并单元格

导入依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.ehcache</groupId>
                    <artifactId>ehcache</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

Controller的代码

一些逻辑写在service层美观一些,我懒得弄过去了,就写在控制层

/**
	 * 生产量统计
	 * 
	 * @throws ParseException
	 * @throws IOException
	 */
	@GetMapping("/scl")
	public void scl(HttpServletResponse response, Date startDate, Date endDate) throws ParseException, IOException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
		SimpleDateFormat sdf2 = new SimpleDateFormat("MM月dd日");
		
		//这里根据客户传入的起止时间,查询出这俩个时间段内的所有时间
		List<Date> dates = getDatesBetween(startDate, endDate);
		List<Map> list = new ArrayList<>();

		for (Date date : dates) {
			//数据从数据库取出来,可以new一个对象,我偷懒就用map来代替,方便
			Map md = countService.selectSclList(date);
			md.put("date", sdf2.format(date));
			md.put("dates", sdf.format(startDate) + "-" + sdf.format(endDate));
		    list.add(md);
		}
		//关键代码,表示是横向填充数据
		FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
		//模板的路径
		InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("exportTemplate/" + "scl.xlsx");
		//下面几行都是返回一个文件流给前端
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		response.setCharacterEncoding("utf-8");
		String fileName2 = new String("生产量统计统计".getBytes("gb2312"), "ISO8859-1");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName2 + ".xlsx");
		ServletOutputStream outputStream = response.getOutputStream();

		ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();
		ExcelRowMergeHandler rowMergeHandler = new ExcelRowMergeHandler();

		// 如果长度大于1 就需要合并
		if (list.size() > 1) {
			int i = list.size() - 1;
			rowMergeHandler.add(0, 1, i);//这个代表第0行的第1列需要向右合并i和单元格
			rowMergeHandler.add(1, 1, i);//代表第1行的第1列需要向右合并i和单元格
			rowMergeHandler.add(11, 1, i);//代表第11行的第1列需要向右合并i和单元格
			rowMergeHandler.add(12, 1, i);//......
			rowMergeHandler.add(20, 1, i);//......
			rowMergeHandler.add(21, 1, i);//......
		}
		//这里使用了自定义的行合并处理器类
		WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(rowMergeHandler).build();
		excelWriter.fill(list, fillConfig, writeSheet);
		excelWriter.finish();
	}
	
	public static List<Date> getDatesBetween(Date startDate, Date endDate) throws ParseException {
        List<Date> dates = new ArrayList<>();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startDate);
       
        while (calendar.getTime() .before(endDate) || calendar.getTime().equals(endDate)) {
            Date result = calendar.getTime();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String date = sdf.format(result);
            dates.add(sdf.parse(date));
            calendar.add(Calendar.DATE, 1);
        }
        return dates;
    }

合并处理器类

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 合并处理器
 */
public class ExcelRowMergeHandler implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelRowMergeHandler() {
    }
    public ExcelRowMergeHandler(Map<String, Integer> mergeInfo) {
        this.mergeInfo = mergeInfo;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if(null != num){
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
        }
    }

    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }
    public void add (int curRowIndex,  int curColIndex , int num){
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
    }

}

最后导出文件如图: