【笑小枫的SpringBoot系列】【十三】JAVA使用EasyExcel导出excel

功能背景

简单的说下这个功能的背景需求吧，有类似需求的可以复用，果然导入还没写完，导出的功能接踵而来，一块写了吧

实现excel导出（依旧废话...）
多个sheet页一起导出
第一个sheet页数据表头信息有两行
样式稍微美观，列宽可以自定义等
数据量稍微有些大（多个sheet页总量50w左右）

项目引入依赖

如果是从上一篇看过来的，就不用看项目引入了🙈

gradle:

compile "com.alibaba:easyexcel:3.1.0"

maven:

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

注意： 3+版本的的easyexcel，使用poi 5+版本时，需要手动排除：poi-ooxml-schemas，例如：

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

项目编码

如多个表格样式相同，可编写一个父类，将样式定义在父类上，子类继承父类即可。

在config.bean.excel包下定义经销商信息对象ExportCompany.java，代码如下👇

package com.maple.demo.config.bean.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author 笑小枫
 * @date 2022/7/22
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
@ColumnWidth(20)
public class ExportCompany {

    // -------------------- 基本信息 start -------------

    @ExcelProperty({"基本信息", "公司名称"})
    private String companyName;

    @ExcelProperty({"基本信息", "省份"})
    private String province;

    @ExcelProperty({"基本信息", "成立时间"})
    private Date startDate;

    @ExcelProperty({"基本信息", "企业状态"})
    private String entStatus;

    @ColumnWidth(30)
    @ExcelProperty({"基本信息", "博客地址"})
    private String csdnAddress;

    // ---------------- 基本信息 end ---------------------

    // ---------------- 经营信息 start ---------------------

    @ExcelProperty({"经营信息", "员工数"})
    private String employeeMaxCount;

    @ExcelProperty({"经营信息", "网站地址"})
    private String netAddress;

    @ExcelProperty({"经营信息", "所属区域省"})
    private String businessProvinceName;

    @ExcelProperty({"经营信息", "所属区域市"})
    private String businessCityName;

    @ExcelProperty({"经营信息", "所属区域区县"})
    private String businessAreaName;

    // ---------------- 经营信息 end ---------------------
}

在config.bean.excel包下定义联系人信息对象ExportContact.java，代码如下👇

package com.maple.demo.config.bean.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author 笑小枫
 * @date 2022/7/22
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
@HeadFontStyle(fontHeightInPoints = 12)
@ContentFontStyle(fontHeightInPoints = 11)
@ColumnWidth(20)
public class ExportContact {

    @ExcelProperty("公司名称")
    private String companyName;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("身份证号码")
    private String idCard;

    @ExcelProperty("电话号码")
    private String mobile;

    @ExcelProperty("职位")
    private String contactPostName;
}

在controller包下编写TestExportExcelController.java进行测试，代码如下：

package com.maple.demo.controller;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.maple.demo.config.bean.excel.ExportCompany;
import com.maple.demo.config.bean.excel.ExportContact;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 笑小枫
 * @date 2022/7/22
 */
@Slf4j
@RestController
@RequestMapping("/example")
@Api(tags = "实例演示-导出Excel")
public class TestExportExcelController {


    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) {
        try (OutputStream out = response.getOutputStream()) {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("笑小枫测试导出", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            handleExcel(out);
            out.flush();
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

    private void handleExcel(OutputStream out) {
        try (ExcelWriter excelWriter = EasyExcelFactory.write(out).build()) {
            WriteSheet dealerSheet = EasyExcelFactory.writerSheet(0, "经销商信息").head(ExportCompany.class).build();
            WriteSheet contactSheet = EasyExcelFactory.writerSheet(1, "联系人").head(ExportContact.class).build();
            excelWriter.write(getCompany(), dealerSheet);
            excelWriter.write(getContact(), contactSheet);
        }
    }

    private List<ExportCompany> getCompany() {
        List<ExportCompany> companyList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            companyList.add(ExportCompany.builder()
                    .companyName("笑小枫公司" + i)
                    .province("上海市")
                    .businessProvinceName("山东省")
                    .businessCityName("临沂市")
                    .businessAreaName("河东区")
                    .entStatus("营业")
                    .netAddress("www.xiaoxiaofeng.site")
                    .csdnAddress("https://zhangfz.blog.csdn.net")
                    .employeeMaxCount("100")
                    .startDate(new Date())
                    .build());
        }
        return companyList;
    }

    private List<ExportContact> getContact() {
        List<ExportContact> contactList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            contactList.add(ExportContact.builder()
                    .companyName("笑小枫公司" + i)
                    .name("笑小枫" + i)
                    .mobile("183000000000")
                    .idCard("371324199011111111")
                    .contactPostName("后端")
                    .build());
        }
        return contactList;
    }
}