EasyPoi 注解方式实现excel导出(多sheet)

导出结果展示：

首先导入jar包：

<!--EasyPoi导入导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
            <exclusions>
                <exclusion>
                    <artifactId>guava</artifactId>
                    <groupId>com.google.guava</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version> </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
            <version>2.0.1.Final</version>
            <scope>compile</scope>
        </dependency>

封装实体类信息：
TbBudgetExcel：

package com.xk.weekreport.admin.vo;


import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.math.BigDecimal;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("budgetExcel")
public class TbBudgetExcel implements Serializable {
    /**
     * 累计
     */
    @Excel(name = "累计",orderNum = "1")
    private BigDecimal monthAdd;
    /**
     * 本月预算
     */
    @Excel(name = "本月预算",orderNum = "2")
    private BigDecimal monthBudget;

    /**
     * 与预算比
     */
    @Excel(name = "与预算比",orderNum = "3")
    private BigDecimal monthBudgetRatio;

}

TbReportExcel：

package com.xk.weekreport.admin.vo;


import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("reportExcel")
public class TbReportExcel implements Serializable {
    /**
     * 项目
     */
    @Excel(name = "项目",orderNum = "1", width = 20, needMerge = true)
    private String itemName;
    /**
     * 本周
     */
    @Excel(name = "本周",orderNum = "2", width = 20, needMerge = true)
    private BigDecimal week;

    /**
     * XX月
     */
    @ExcelCollection(name = "XX月",orderNum = "3")
    private List<TbBudgetExcel> budgets;
    /**
     * 情况说明
     */
    @Excel(name = "情况说明",orderNum = "4", width = 20, needMerge = true)
    private String remark;

}

excel导入导出工具类：

package com.xk.weekreport.admin.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author gourd
 */
@Slf4j
public class ExcelUtil {


    public static boolean isExcel2003(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 得到Workbook对象
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException{
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * 得到错误信息
     * @param sb
     * @param list
     * @param i
     * @param obj
     * @param name  用哪个属性名去表明不和规定的数据
     * @param msg
     * @throws Exception
     */
    public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{
        Class clazz=obj.getClass();
        Object str=null;
        //得到属性名数组
        Field[] fields = clazz.getDeclaredFields();
        for(Field f : fields){
            if(f.getName().equals(name)){
                //用来得到属性的get和set方法
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                //得到get方法
                Method getMethod=pd.getReadMethod();
                str = getMethod.invoke(obj);
            }
        }
        if(i==0) {
            sb.append(msg + str + ";");
        }
        else if(i==(list.size()-1)) {
            sb.append(str + "</br>");
        }
        else {
            sb.append(str + ";");
        }
    }


    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            //编码
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

代码如下：

/**
     * 多sheet导出
     * @param response
     */
    @PostMapping("/export")
    public void exportSheets(@RequestBody Map<String, Object> params, HttpServletResponse response) {
        //获取所有医院信息(每个医院为一个sheet)
        List<TbMainWeekReportDto> records = mainWeekReportService.getWeekReportList(params);

        // 将所有sheet使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        for (TbMainWeekReportDto reportDto : records){
            params.put("hospitalId",reportDto.getHospitalId());
            //根据医院id获取医院具体数据
            List<ExportDataDto> exportDataDtos = mainWeekReportService.selectExportData(params);
            List<TbReportExcel> reportExcels = new ArrayList<>();
            // 赋值
            for (ExportDataDto dto : exportDataDtos){
                List<TbBudgetExcel> budgetExcels = new ArrayList<>();
                TbBudgetExcel tbBudgetExcel = new TbBudgetExcel();
                tbBudgetExcel.setMonthAdd(dto.getMonthAdd());
                tbBudgetExcel.setMonthBudget(dto.getMonthBudget());
                tbBudgetExcel.setMonthBudgetRatio(dto.getMonthBudgetRatio());
                budgetExcels.add(tbBudgetExcel);

                TbReportExcel tbReportExcel = new TbReportExcel();
                tbReportExcel.setItemName(dto.getItemName());
                tbReportExcel.setWeek(dto.getReportWeekValue());
                tbReportExcel.setBudgets(budgetExcels);
                tbReportExcel.setRemark(dto.getRemark());
                reportExcels.add(tbReportExcel);
            }

            // 创建参数对象
            ExportParams exportParams1 = new ExportParams();
            // 设置sheet的名称
            exportParams1.setSheetName(reportDto.getHospitalName());
            StringBuilder sb = new StringBuilder();
            sb.append("经营周汇报");
            StringBuilder sb2 = new StringBuilder();
            sb2.append("医院/公司："+ reportDto.getHospitalName());
            sb2.append("    ");
            sb2.append("报告期："+ reportDto.getReportWeekTime());
            //设置标题名称
            exportParams1.setTitle(sb.toString());
            //设置第二行名称
            exportParams1.setSecondTitle(sb2.toString());

            Map<String, Object> map1 = new HashMap<>();
            // title的参数为ExportParams类型
            map1.put("title", exportParams1);
            // 模版导出对应得实体类型
            map1.put("entity", TbReportExcel.class);
            // sheet中要填充得数据
            map1.put("data", reportExcels);
            sheetsList.add(map1);
        }
        // 执行方法
        Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        ExcelUtil.downLoadExcel("周报信息导出.xls",response,workbook);
    }

复杂样式的多sheet导出可以看我的另一篇文章：EasyPoi模板形式导出excel（多sheet）