easyPoi 多sheet导出excel

springboot使用easypoi 实现多sheet的数据导出

easyPoi开发文档

1 导入依赖

<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-spring-boot-starter</artifactId>
			<version>4.1.1</version>
		</dependency>

2 导出

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;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("personBO")
public class PersonBO implements Serializable {

    @Excel(name = "姓名", width = 20)
    private String name;

    @Excel(name = "年龄", width = 20)
    private String age;


}

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;


@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("orgBO")
public class OrgBO implements Serializable {

   @Excel(name = "部门名称", width = 20)
    private String deptName;
   @Excel(name = "部门地址", width = 20)
    private String addr;

}

 
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class ExportController {

    @ResponseBody
    @RequestMapping("/export")
    public void downloadGridorgMemList(ModelMap map, HttpServletRequest request, HttpServletResponse response) {

        //导出数据
        List<PersonBO> personList = new ArrayList<>();
        List<OrgBO> orgList = new ArrayList<>();
        PersonBO personBO1 = new PersonBO();
        PersonBO personBO2 = new PersonBO();
        personBO1.setName("jim");
        personBO1.setAge("20");
        personBO2.setName("tom");
        personBO2.setAge("30");
        personList.add(personBO1);
        personList.add(personBO2);
        OrgBO orgBO1 = new OrgBO();
        OrgBO orgBO2 = new OrgBO();
        orgBO1.setDeptName("部门1");
        orgBO1.setAddr("地址1");
        orgBO2.setDeptName("部门2");
        orgBO2.setAddr("地址1");
        orgList.add(orgBO1);
        orgList.add(orgBO2);


        // 创建参数对象 这里定义的格式要跟数据文件格式保持一致
        ExportParams exportParamsPerson = new ExportParams("人员信息","人员sheet", ExcelType.XSSF);
        ExportParams exportParamsOrg = new ExportParams("部门信息","部门sheet", ExcelType.XSSF);


        // 创建sheet1使用得map
        Map<String, Object> personMap = new HashMap<>(4);
        // title的参数为ExportParams类型
        personMap.put("title", exportParamsPerson);
        // 模版导出对应得实体类型
        personMap.put("entity", PersonBO.class);
        // sheet中要填充得数据
        personMap.put("data", personList);

        // 创建sheet2使用得map
        Map<String, Object> dutyDataMap = new HashMap<>(4);
        dutyDataMap.put("title", exportParamsOrg);
        dutyDataMap.put("entity", OrgBO.class);
        dutyDataMap.put("data", orgList);
        // 将sheet1和sheet2使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(personMap);
        sheetsList.add(dutyDataMap);
        // 执行方法
        Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.XSSF);
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode("测试统计.xlsx", "UTF-8"));
            //编码
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}