目录

1.需求背景:

2.初步实现:

3.列合并

4.行合并

5.excel字体样式内容居中

6.将三个工具类初始化后注册后最终代码:

1.需求背景:

①需要从第三方获取数据,第三方接口有两个参数,开始时间和结束时间

②获取回来的数据并没有入库,所以不能通过数据库将数据归类统计,excel合并大概的流程是判断上一行或者左右相邻列是否相同,然后进行合并,所以不能是零散的数据且客户要求每一个自治区和每一个航站要统计总数(后续会出一个数据整合文章),咱们默认数据已经整理好了.效果如下:

③最终效果:

2.初步实现:

①利用easyExcel模板填充,实现效果如下图

代码:

        //模板位置
        InputStream template = new PathMatchingResourcePatternResolver()
        .getResource("templates/飞机扑救火场统计表.xlsx").getInputStream();

        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode("飞机扑救火场统计表.xlsx", "UTF-8"));
        //ExcelWriter该对象用于通过POI将值写入Excel
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template).build();
        //构建excel的sheet         
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        Map<String, String> fileData = new HashMap<>();
        fileData.put("beginDate", beginDate);
        fileData.put("endDate", endDate);
        excelWriter.fill(list, writeSheet);
        excelWriter.fill(fileData, writeSheet);
        excelWriter.finish();

模板:

3.列合并

 列合并工具类,合并代码在afterCellDispose这个方法中,不管是列合并还是行合并其实是重写这个方法,将你的合并逻辑写在里面就可以.

//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    //所有的合并信息都存在了这个map里面
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevColUtils() {
    }

    @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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, 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);
    }
    //num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
    public void add (int curRowIndex,  int curColIndex , int num){
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
    }

}

可以参考下面的这个excel看一下,广西壮族自治区的航站合计是从第8行,第2列开始+2列的范围合并

列合并效果图:

4.行合并

行合并工具类初级版本: 

报错位置：ExcelFillCellMergeStrategyUtils合并策略类的 mergeWithPrevRow()方法中

这一行代码会报空指针异常 java.lang.NullPointerException

Row preRow = cell.getSheet().getRow(curRowIndex - 1);

原因:

debug发现，cell.getSheet() 行的下标第0到42的数据行，获取的是同一个 sheet 实例

当下标为43时，执行cell.getSheet()获取到的 sheet 实例不一样

而下标0到42的行数据被存储到 存储sheet中。如果上一行为空则去缓存中获取上一行,

writeSheetHolder.getCachedSheet()

   Row preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时，使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell=preRow.getCell(curColIndex);

行合并工具类最终版:

public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {

    /**
     * 合并字段的下标
     */
    private int[] mergeColumnIndex;
    /**
     * 合并几行
     */
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                       CellData cellData, 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();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }

    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
                cell.getNumericCellValue();
        Row preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时，使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell=preRow.getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
                    preCell.getNumericCellValue();
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            if (curData.equals(preData)) {
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
                            curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }





    }
}

5.excel字体样式内容居中

样式工具类: 

public class CellStyleStrategy extends AbstractCellStyleStrategy {

    private WriteCellStyle headWriteCellStyle;
    private List<WriteCellStyle> contentWriteCellStyleList;

    private CellStyle headCellStyle;
    private List<CellStyle> contentCellStyleList;

    public CellStyleStrategy(WriteCellStyle headWriteCellStyle,
                                       List<WriteCellStyle> contentWriteCellStyleList) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyleList = contentWriteCellStyleList;
    }

    public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.headWriteCellStyle = headWriteCellStyle;
        contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
        contentWriteCellStyleList.add(contentWriteCellStyle);
    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        if (headWriteCellStyle != null) {
            headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
        }
        if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
            contentCellStyleList = new ArrayList<CellStyle>();
            for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
                contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
            }
        }
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (headCellStyle == null) {
            return;
        }
        cell.setCellStyle(headCellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
            return;
        }
        cell.setCellStyle(contentCellStyleList.get(0));
    }

}

 public CellStyleStrategy horizontalCellStyleStrategyBuilder() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

6.将三个工具类初始化后注册后最终代码:

 public void exportExcel(HttpServletResponse response, String beginDate, String endDate) throws IOException {

        ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();

        String terminalTotal = "航站合计";
        String provinceTotal = "省区合计";
        Map<String, ProvinceInfo> map = handlePlaneDownFire(beginDate, endDate);
        List<PlaneDownFire> list = new ArrayList<>();
        map.forEach((k, v) -> {
            //添加航站合计
            v.getTerminalInfos().forEach((k1, v1) -> {
                list.addAll(v1.getList());
                int size = list.size();
                excelFillCellMergePrevColUtils.add(size + 3, 2, 2);
                CommissionInfo terminalCommissionInfoTotal = v1.getSum();
                PlaneDownFire planeDownFire = CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(terminalCommissionInfoTotal);
                planeDownFire.setProvincialArea(v.getName())
                        .setMachineNumber(String.valueOf(size))
                        .setFlyingCommission(String.valueOf(size))
                        .setTerminal(v1.getName())
                        .setModel(terminalTotal);
                list.add(planeDownFire);
            });
            int size = list.size();
            excelFillCellMergePrevColUtils.add(size + 3, 1, 3);
            //省区合计
            CommissionInfo provinceCommissionInfoTotal = v.getSum();
            PlaneDownFire planeDownFire = CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(provinceCommissionInfoTotal);
            planeDownFire.setProvincialArea(v.getName())
                    .setTerminal(provinceTotal)
                    .setModel(String.valueOf(size));
            list.add(planeDownFire);

        });


        //设置第几列开始合并
        int[] mergeColumnIndex = {0, 1, 2, 3};
        //设置第几行开始合并
        int mergeRowIndex = 3;

        ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils = new ExcelFillCellMergeStrategyUtils(mergeRowIndex, mergeColumnIndex);

        InputStream template = new PathMatchingResourcePatternResolver().getResource("templates/飞机扑救火场统计表.xlsx").getInputStream();


            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + java.net.URLEncoder.encode("飞机扑救火场统计表.xlsx", "UTF-8"));
            //ExcelWriter该对象用于通过POI将值写入Excel
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template)
                     //样式注册
                    .registerWriteHandler(horizontalCellStyleStrategyBuilder())
                     //行注册
                    .registerWriteHandler(excelFillCellMergeStrategyUtils)
                     //列注册
                    .registerWriteHandler(excelFillCellMergePrevColUtils)
                    .build();
            //构建excel的sheet
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            Map<String, String> fileData = new HashMap<>();
            fileData.put("beginDate", beginDate);
            fileData.put("endDate", endDate);
            excelWriter.fill(fileData, writeSheet);
            excelWriter.fill(list, writeSheet);
            excelWriter.finish();

    }

总结:EasyExcel动态导出几乎能够满足大部分需求,说到底还是实现CellWriteHandler 类里面的

afterCellDispose方法,里面实现你想要的效果.

创建SpringBoot项目

导入EasyExcel.jar

<dependency>
	<groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

创建Excel模板

实体类

public class FillData {
	private String name;
	@NumberFormat("##.0")
	private Double chinese;
	@NumberFormat("##.0")
	private Double math;
	@NumberFormat("##.0")
	private Double english;
	@NumberFormat("##.0")
	private Double number;
	//getter setter
}

Service实现

@Service
public class ExcelExportServiceImpl implements ExcelExportService{
	//在springboot文件(application.properties)中配置模板所在的位置，例如
	//excel.template=E:/SpringToolSuiteForEclipseWorkSpace/PrivateApplication/EasyExcel/src/main/resources/1.xlsx
	@Value("${excel.template}")
	private String templateFileName;
	
	@Override
	public void excelExport(HttpServletResponse response) throws IOException {
		// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
		System.out.println(templateFileName);
		// 方案1 根据对象填充
		String fileName = "simpleFill" + System.currentTimeMillis() + ".xlsx";
		// 这里 会填充到第一个sheet， 然后文件流会自动关闭
		List<FillData> l = new ArrayList<>();
		for(Integer i = 0; i < 50; i++){
			FillData fillData = new FillData();
			fillData.setName("张三" + i+"号");
			fillData.setChinese(120.0+i);
			fillData.setEnglish(141.0+i);
			fillData.setMath(119.0+i);
			fillData.setNumber(fillData.getChinese()+fillData.getEnglish()+fillData.getMath());
			l.add(fillData);
		}
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("UTF-8");
		response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(),"iso-8859-1"));
		EasyExcel.write(response.getOutputStream(),FillData.class).withTemplate(templateFileName).sheet().doFill(l);
	}
}

Service接口

public interface ExcelExportService {

	void excelExport(HttpServletResponse response) throws IOException;

}

控制器Controller

@RestController
public class HelloController {
	@Autowired
	private ExcelExportService excelExportService;
	
	@GetMapping("/h")
	public void h(HttpServletResponse response) throws IOException {
		excelExportService.excelExport(response);
	}
}

然后访问/h接口就可以导出一个

有问题的小伙伴欢迎留言评论，看了第一时间会回复的哈

EasyExcel模板填充 导出Excel

参考官网easyExcel例 ：https://www.yuque.com/easyexcel/doc/fill

1.简单对象填充：

/**
	 * 根据id导出Excel模板
	 * @param id
	 * @param response
	 */
    @Override
    public void exportExcelTemplateById(String id, HttpServletResponse response) throws IOException {
		// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
		String path = this.getClass().getClassLoader().getResource("template/lswttjXtxz.xls").getPath();
		System.out.println("path========================"+path+"======================");
		File fileTemp = new File(path);

		//查询需要导出的数据
		PageData pd = new PageData();
		pd.put("id",StringUtil2.subStrForInQuery(id));
		List<TLswttjXtxz> lswttjXtxz = tLswttjXtxzMapper.getTLswttjXtxzByMonth(pd);

		//导出直接浏览器下载
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf-8");
		//设置要导出的文件的名字、这里URLEncoder.encode可以防止中文乱码
		String fileName = "粮食购销领域腐败问题专项整治自查自纠问题统计表" + System.currentTimeMillis()+".xls";
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.setHeader("Content-Disposition", "attachment;filename=" +fileName);
		OutputStream outputStream = response.getOutputStream();

		EasyExcel.write(outputStream).withTemplate(fileTemp).sheet().doFill(lswttjXtxz.get(0));
    }

excel模板：

2.复杂、多条数据填充

/**
	 * 根据id导出Excel模板
	 * @param id
	 * @param response
	 */
    @Override
    public void exportExcelTemplateById(String id, HttpServletResponse response) throws IOException {
		// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替 {} 代表普通变量 {.} 代表是list的变量
		//1.创建excel模板
		String path = this.getClass().getClassLoader().getResource("template/lswttjXtxz.xls").getPath();
		System.out.println("path========================"+path+"======================");

		//2.导出直接浏览器下载
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		response.setCharacterEncoding("utf-8");
		//设置要导出的文件的名字、这里URLEncoder.encode可以防止中文乱码
		String fileName = "粮食购销领域腐败问题专项整治自查自纠问题统计表" + System.currentTimeMillis()+".xls";
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.setHeader("Content-Disposition", "attachment;filename=" +fileName);
		OutputStream outputStream = response.getOutputStream();

		ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(path).build();

		//3.填充赋值、查询需要导出的数据
		PageData pd = new PageData();
		pd.put("id",StringUtil2.subStrForInQuery(id));
		List<TLswttjXtxz> lswttjXtxzList = tLswttjXtxzMapper.getTLswttjXtxzByMonth(pd);

		// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
		// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
		// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
		// 如果数据量大 list不是最后一行 参照下一个
		FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
		WriteSheet writeSheet = EasyExcel.writerSheet().build();
		excelWriter.fill(lswttjXtxzList, fillConfig, writeSheet);

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("tbdw", "2019年10月9日13:28:28");
		map.put("tbsj", 1000);
		map.put("tbr", 1000);
		map.put("lxfs", 1000);
		excelWriter.fill(map, writeSheet);
		excelWriter.finish();
     }

如果想要保存到指定目录，更改outputStream；
以上测试没问题，可以进一步自己封装一下，封装到工具类。

遇到的问题：
1.pom.xml文件里面加以下内容，否则编译后classes里面模板乱码

<build>
       
		<resources>
			<resource>
				<directory>src/main/resources</directory>
				<filtering>true</filtering>
				<excludes>
					<exclude>**/*.docx</exclude>
					<exclude>**/*.xls</exclude>
				</excludes>
			</resource>
			<resource>
				<directory>src/main/resources</directory>
				<filtering>false</filtering>
				<includes>
					<include>**/*.docx</include>
					<include>**/*.xls</include>
				</includes>
			</resource>
		</resources>
    </build>

2.我用自己本地创建excel当做模板报错，可能和版本有关，粘贴到官方的excel模板例子里就可以了；