EasyPoi 多sheet导入导出功能

最新推荐文章于 2024-06-26 09:38:24 发布

爱穿衬衫的张某某

最新推荐文章于 2024-06-26 09:38:24 发布

阅读量975

点赞数

文章标签： java

原文链接：https://blog.csdn.net/baidu_36821021/article/details/85216855,https://blog.csdn.net/superlover_/article/details/117786731

版权

EasyPoi多sheet导出功能实现

EXCEL
在这里插入图片描述

依赖:

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

controller

package io.btm.modules.importexcel;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import io.btm.common.utils.R;
import io.swagger.annotations.ApiOperation;
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.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("importExcel")
public class ExcelController {

    /**
     * 导入
     */
    @PostMapping("import")
    @ApiOperation(value = "导入")
    public R ordergeneratexml(MultipartFile file) throws Exception {
        //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        Workbook hssfWorkbook = getWorkBook(file);
        //获取sheet数量
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        ImportParams params = new ImportParams();
        //表头在第几行
        params.setTitleRows(3);
        //是否需要通过key-value导入方法，获取特定字段
        params.setReadSingleCell(true);
        //判断一个cell是key的规则,可以自定义,默认就是 “:”
        params.setKeyMark("：");
        List<ExcelVo> listAll = new ArrayList<>();
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            //第几个sheet页
            params.setStartSheetIndex(sheetIndex);
            //获取表头下的数据
            ExcelImportResult<ExcelVo> result = ExcelImportUtil.importExcelMore(file.getInputStream(), ExcelVo.class, params);
            List<ExcelVo> list = result.getList();
            //获取特定字段的map
            Map<String, Object> map = result.getMap();
            list.forEach(entity -> {
                entity.setRukuriqi(map.get("日期：").toString());
                entity.setDianpumingchen(map.get("店铺名：").toString());
            });
            listAll.addAll(list);
        }
        return R.ok().put("listAll", listAll);
    }

    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;
    }

}

实体类

package io.btm.modules.importexcel;

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

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelVo {
    @Excel(name = "序号")
    private String indexNum;
    @Excel(name = "品名")
    private String name;
    @Excel(name = "订货数（斤）")
    private String dinghuoNum;
    @Excel(name = "去皮斤数")
    private String qupiNum;
    @Excel(name = "去皮斤数")
    private String sunhaoNum;
    @Excel(name = "供货单价")
    private String gonghuoNum;
    @Excel(name = "订单金额")
    private String dingnanNum;
    @Excel(name = "订单金额")
    private String qupijine;
    @Excel(name = "订单金额")
    private String sunhaojine;
    @Excel(name = "产品差价")
    private String chanpindanjia;
    @Excel(name = "实际结账")
    private String shijijiezhang;
    @Excel(name = "是否结款")
    private String shifoujiekuan;
	@Excel(name = "是否结款")
    private String beizhu;
    private String rukuriqi;
    private String dianpumingchen;
}

源码地址

原文链接：https://blog.csdn.net/superlover_/article/details/117786731

EasyPoi 多sheet导出功能实现

本需求:

多sheet页导出
合并单元格（跨行、跨列）
多表头合并

一、引入maven jar包

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.2.0</version>
</dependency>
<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.2.0</version>
</dependency>
<dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-annotation</artifactId>
       <version>3.2.0</version>
</dependency>

二、编写导出的实体类（使用注解形式）
1、DeptUtil 类

@ExcelTarget("deptUtil")
public class DeptUtil {

    @Excel(name = "部门编号", width = 30 , needMerge = true)
    private Integer id;

    @Excel(name = "部门名称", width = 30 , needMerge = true)
    private String deptName;

    @ExcelCollection(name = "员工信息")
    private List<EmpUtil> emps;


	....省略getter、setter方法

2、EmpUtil类

@ExcelTarget("empUtil")
public class EmpUtil{

    @Excel(name = "序号", width = 30, isColumnHidden = true)
    private Integer id;

    @Excel(name = "员工姓名", width = 30, groupName = "基本信息")
    private String empName;

    @Excel(name = "年龄", width = 30, type = 10, groupName = "基本信息")
    private Integer age;

    @Excel(name = "入职时间", width = 30, groupName = "工作信息", format = "yyyy/MM/dd HH:mm")
    private Date hiredate;

    @Excel(name = "薪酬", width = 30, type = 10, groupName = "工作信息")
    private BigDecimal salary;


	....省略getter、setter方法

3、核心代码

public String export(){

        Workbook workBook = null;
        try {
            List<DeptUtil> exportList = exportService.exportList();
            System.err.println(JSONArray.toJSONString(exportList));

            // 创建参数对象（用来设定excel得sheet得内容等信息）
            ExportParams deptExportParams = new ExportParams();
            // 设置sheet得名称
            deptExportParams.setSheetName("员工报表1");
            // 创建sheet1使用得map
            Map<String, Object> deptExportMap = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            deptExportMap.put("title", deptExportParams);
            // 模版导出对应得实体类型
            deptExportMap.put("entity", DeptUtil.class);
            // sheet中要填充得数据
            deptExportMap.put("data", exportList);

            ExportParams empExportParams = new ExportParams();
            empExportParams.setSheetName("员工报表2");
            // 创建sheet2使用得map
            Map<String, Object> empExportMap = new HashMap<>();
            empExportMap.put("title", empExportParams);
            empExportMap.put("entity", DeptUtil.class);
            empExportMap.put("data", exportList);

            // 将sheet1、sheet2、sheet3使用得map进行包装
            List<Map<String, Object>> sheetsList = new ArrayList<>();
            sheetsList.add(deptExportMap);
            sheetsList.add(empExportMap);
            // 执行方法
            workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
            fileName = URLEncoder.encode("员工报表导出", "UTF-8");
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workBook.write(outputStream);
            outputStream.flush();
            byte[] byteArray = outputStream.toByteArray();
            excelStream = new ByteArrayInputStream(byteArray,0,byteArray.length);
            outputStream.close();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return "success";
    }

三、效果展示
在这里插入图片描述
有一个问题就是如果sheet填充的数据源是一样的，那么第二个sheet的内容就会为空

在这里插入图片描述
不过一般上要实现多sheet页展示，内容应该是不一样的，这里只是作为实现效果展示，就采用了同一个数据获取源。

以上就是所有内容了，如果想要学习更多可以查阅easyPoi的api（http://easypoi.mydoc.io/）
另外附上项目源码：https://github.com/wzqonly/easyPoi_export

原文链接：https://blog.csdn.net/baidu_36821021/article/details/85216855

多sheet导入导出工具类

package com.hisign.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 com.alibaba.fastjson.JSON;
import com.hisign.base.repository.BaseMapper;
import com.hisign.rest.exception.HisignException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

@Transactional
public class ExcelUtil {

    //日志
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * excel导入工具类  单个excel
     *
     * @param mapper    需要进行数据插入的mapper
     * @param file      导入的excel文件
     * @param classType 新增数据的class类型
     * @return          返回导入参数条数
     */
    public static Integer importExcelUtil(BaseMapper mapper, Class classType, MultipartFile file) {
        ImportParams importParams = new ImportParams();
        importParams.setHeadRows(1);
        //importParams.setTitleRows(1);
        List<T> importList = null;
        List<T> dateList = null;
        //List<T> list = null;
        try {
            importList = ExcelImportUtil.importExcel(file.getInputStream(), classType, importParams);
            logger.info("导入的数据 : " + JSON.toJSONString(importList));
            String replaceJsonList = "";
            if (importList.size() > 0) {
                //反射 去空  未启用
                // list = removeEmptyObject(dateList);
                String jsonList = JSON.toJSONString(importList);
                //去除空对象数据
                replaceJsonList = jsonList.replaceAll(",\\{\\}", "");
                logger.info("去除空对象之后数据 : " + replaceJsonList);

            }
            dateList = JSON.parseArray(replaceJsonList, classType);
            //logger.info("移除空数据后 : " + JSON.toJSONString(dateList));

            for (int i = 0; i < dateList.size(); i++) {
                mapper.insert(dateList.get(i));
            }
        } catch (Exception e) {
            throw new HisignException("文件导入失败!");
        }
        int dateNum = dateList.size();
        logger.info("导入数据条数 : " + dateNum);
        return dateNum;
    }

    /**
     * excel导入工具类  多个excel
     * <p>
     * 要保证  sheet列表的顺序,mapperList的mapper顺序,classList的class顺序保持一致
     *
     * @param mapperList    需要操作新增的数据的mapper集合
     * @param classTypeList 上传数据的数据类型
     * @param file          导入文件
     * @return              sheetNum  导入sheet个数   dateNum导入数据条数
     */
    public static Map<String, Object> importMoreExcelUtil(List<BaseMapper> mapperList, List<Class> classTypeList, MultipartFile file) {
        logger.info("导入多excel");
        HashMap<String, Object> resultMap = new HashMap<>();

        Integer sheetNum = null;
        Integer importUnm = 0;
        try {
            InputStream is = file.getInputStream();
            HSSFWorkbook workbook = new HSSFWorkbook(is);
            //得到导入的sheet数量
            sheetNum = workbook.getNumberOfSheets();
            logger.info("导入sheet数量为 : " + sheetNum);
            ImportParams importParams = new ImportParams();
            importParams.setHeadRows(1);

            for (int i = 0; i < sheetNum; i++) {
                logger.info("开始导入第 : " + i + 1 + " 个sheet");
                importParams.setStartSheetIndex(i);

                List<T> importList = ExcelImportUtil.importExcel(file.getInputStream(), classTypeList.get(i), importParams);
                String jsonData = JSON.toJSONString(importList);
                logger.info("导入第  " + i + 1 + " 个sheet , 数据为 : " + jsonData);
                String replaceData = jsonData.replaceAll(",\\{\\}", "");
                logger.info("去除空对象之后的数据 : " + replaceData);

                List<T> dateList = JSON.parseArray(replaceData, classTypeList.get(i));

                if (dateList.size() > 0) {
                    for (int j = 0; j < dateList.size(); j++) {
                        mapperList.get(i).insert(dateList.get(j));
                    }
                }
                importUnm += dateList.size();
            }

        } catch (Exception e) {
            throw new HisignException(e.getMessage());
        }
        resultMap.put("sheetNum", sheetNum);
        resultMap.put("dateNum", importUnm);
        return resultMap;
    }

    /**
     * 导出excel工具类   单个excel
     *
     * @param dateList  导出的数据集合
     * @param classType 导出的数据类型
     * @param fileName  导出的excel文件名称
     * @param sheetName 导出的sheet名字 (要求为  中文名 + 数据库表名)
     * @param response  响应
     * @return          导出数据数量
     */
    public static Integer exportExcelUtil(List<?> dateList, Class classType, String fileName, String sheetName, HttpServletResponse response) {
        logger.info("导出文件名称为: " + fileName);
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, classType, dateList);
        OutputStream out = null;
        try {
            response.setCharacterEncoding("UTF-8");
            //response.setContentType("application/msexcel");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.addHeader("Access-Control-Allow-Origin", "*");  //跨域

            //写出
            out = response.getOutputStream();
            workbook.write(out);
        } catch (Exception e) {
            throw new HisignException("导入文件失败!");
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                logger.error("关闭流失败!");
            }
        }
        return dateList.size();
    }

    /**
     * 导出excel工具类   多个excel
     * <p>
     * * 要保证  sheetName列表的顺序,dateList的date顺序,sheetName的name顺序保持一致
     *
     * @param classTypeList 导出数据实体集合
     * @param dateList      导出数据集合列表
     * @param sheetNameList 导出多个sheet的name集合,按顺序
     * @param fileName      导出文件名称
     * @param response      响应
     * @return              sheetNum 导出sheet个数    dateNum 导出数据条数
     */
    public static Map<String, Object> exportMoreExcelUtil(List<Class> classTypeList, List<List<?>> dateList, List<String> sheetNameList, String fileName, HttpServletResponse response) {
        logger.info("多sheet导出");
        HashMap<String, Object> resultMap = new HashMap<>();
        ArrayList<Map<String, Object>> sheetList = new ArrayList<>();
        if (sheetNameList.size() > 0) {
            for (int i = 0; i < sheetNameList.size(); i++) {
                ExportParams exportParams = new ExportParams();
                exportParams.setSheetName(sheetNameList.get(i));
                HashMap<String, Object> exportMap = new HashMap<>();
                exportMap.put("title", exportParams);
                exportMap.put("entity", classTypeList.get(i));
                exportMap.put("data", dateList.get(i));
                sheetList.add(exportMap);
            }
        }

        Workbook workbook = ExcelExportUtil.exportExcel(sheetList, ExcelType.HSSF);
        OutputStream out = null;
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.addHeader("Access-Control-Allow-Origin", "*");  //跨域
            out = response.getOutputStream();
            workbook.write(out);

        } catch (Exception e) {
            throw new HisignException(e.getMessage());
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                logger.error("关闭流失败!");
            }
        }
        Integer num = 0;
        for (List<?> list : dateList) {
            num += list.size();
        }
        resultMap.put("sheetNum", sheetNameList.size());
        resultMap.put("dateNum", num);
        return resultMap;
    }

    /**
     * 移除集合中空对象  现在是泛型,无法获取   未启用
     *
     * @param list
     * @return
     */
    private static List<T> removeEmptyObject(List<T> list) {
        if (list.size() > 0) {
            Iterator<T> iterator = list.iterator();
            while (iterator.hasNext()) {
                //单个对象所有字段
                Field[] fields = iterator.next().getClass().getFields();
                //Field[] fields = iterator.next().getClass().getDeclaredFields();

                Boolean flag = true;
                for (Field field : fields) {
                    //访问私有属性
                    //field.setAccessible(true);
                    try {
                        //字典值
                        Object val = field.get(iterator.next());
                        if (val != null) {
                            //只要有一个字段不为空,就表示此对象不为空,结束校验
                            flag = false;
                            break;
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                if (flag) {
                    iterator.remove();
                }
            }
        }
        return list;
    }
}

爱穿衬衫的张某某

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
EasyPoi 多sheet导入导出功能

EasyPoi 多sheet导出功能实现本需求:多sheet页导出合并单元格（跨行、跨列）多表头合并一、引入maven jar包<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version></depen
复制链接

扫一扫