投稿
  1. 极客之音首页
  2. 后端开发

java使用easypoi导入，导出

飞熊 • • 后端开发 • 阅读 146

生活中，最使人疲惫的往往不是道路的遥远，而是心中的郁闷;最使人痛苦的往往不是生活的不幸，而是希望的破灭;最使人颓废的往往不是前途的坎坷，而是自信的丧失;最使人绝望的往往不是挫折的打击，而是心灵的死亡。所以我们要有自己的梦想，让梦想的星光指引着我们走出落漠，走出惆怅，带着我们走进自己的理想。

导读：本篇文章讲解 java使用easypoi导入，导出，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

第一步，先引入easypoi依赖：

 <!-- easypoi 核销包 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
            <exclusions>
                <exclusion>
                    <groupId>com.google.guava</groupId>
                    <artifactId>guava</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- easypoi web包 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!-- easypoi 注解包 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

第二步，创建工具类

package com.sport.sportdigitalmanagement.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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author zdj
 * @date 2022-04-26
 */
public class FileUtil {
    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);

    }

    /**
     * 导出
     * @param list 需要导出的集合
     * @param title excel标题名称
     * @param sheetName sheet表名称
     * @param pojoClass 转化的实体类对象
     * @param fileName 文件名称
     * @param response 导出后的响应
     */
    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);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
            //throw new NotFoundException(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();
            //throw new NotFoundException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NotFoundException(e.getMessage());
        }
        return list;
    }

    /**
     * 导入
     * @param file 需要导入的文件
     * @param titleRows 从第一行开始解析
     * @param headerRows 头部行下标
     * @param pojoClass 转化为对应的实体类
     * @param <T>
     * @return 返回解析后的实体类对象集合
     */
    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){
            //throw new NotFoundException("excel文件不能为空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
           // throw new NotFoundException(e.getMessage());
        }
        return list;
    }
}

第三步，导出
controller：

/**
     * 局系统总预算信息列表--导出
     * @return
     */
    @ApiOperation(value = "局系统总预算信息列表--导出", notes = "局系统总预算信息列表--导出", httpMethod = "POST")
    @RequestMapping(value = "/exportTotalBudgetStatisticsList", method = RequestMethod.POST)
    public void exportTotalBudgetStatisticsList(@RequestHeader String adminToken, @RequestBody TotalBudgetDTO totalBudgetDTO, HttpServletResponse res){
        // 处理参数对象
        try {
            //        Account account = redisService.get(adminToken, Account.class);
            //        if (account == null) {
            //            return ResultData.token();
            //        }

            // 参数过滤
            if(!CommonStringUtils.getFiledDescribe().contains(totalBudgetDTO.getFieldName())){
                throw new Exception("月份对应的字段描述错误！");
            }

            // 调用服务处理业务
            totalBudgetService.exportTotalBudgetStatisticsList(totalBudgetDTO, res);
        } catch (Exception e) {
            logger.error("局系统总预算信息列表--导出出现异常：", e);
        }
    }

servicer:

/**
     * 局系统总预算信息列表--导出
     * @return
     */
    public void exportTotalBudgetStatisticsList(TotalBudgetDTO totalBudgetDTO, HttpServletResponse res) {
        // 获取预算列表
        List<TotalBudget> totalBudgetList = this.getAllCommonTotalBudgetList(totalBudgetDTO);

        //导出的excel的名称
        String fileName = "局系统总预算信息列表";
        FileUtil.exportExcel(totalBudgetList, fileName, fileName, TotalBudget.class, System.currentTimeMillis() + "", res);
    }

导出的映射实体类：

package com.sport.sportdigitalmanagement.po;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * @description total_budget
 * @author zdj
 * @date 2022-04-26
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TotalBudget implements Serializable {
    /**
    * 不需要导出的请用改注解
    */
    @ExcelIgnore
    private Integer id;

    /**
    * 部门
    */
    @Excel(name = "预算单位/类别", orderNum = "1", width = 25)
    @ApiModelProperty("部门")
    @Column(name="department")
    private String department;

    /**
    * 指标数
    */
    @Excel(name = "指标数", orderNum = "2", width = 15)
    @ApiModelProperty("指标数")
    @Column(name="target")
    private BigDecimal target;

    /**
    * 结余指标数
    */
    @Excel(name = "结余指标数", orderNum = "4", width = 15)
    @ApiModelProperty("结余指标数")
    @Column(name="balance_target")
    private BigDecimal balanceTarget;

    /**
    * 年度
    */
    @Excel(name = "年度", orderNum = "1", width = 15)
    @ApiModelProperty("年度")
    @Column(name="year")
    private Integer year;


    /**
    * 执行进度
    */
    @Excel(name = "执行进度", orderNum = "5", width = 15)
    @ApiModelProperty("执行进度")
    @Column(name="progress")
    private BigDecimal progress;

    /**
     * 统一展示的字段
     */
    @Excel(name = "支出数", orderNum = "5", width = 15)
    @ApiModelProperty("统一展示的字段")
    @Transient
    private BigDecimal commonFiled;
}

第四步：导入
controller：

/**
     * 局系统总预算信息列表--导入
     * @param adminToken  登录用户
     * @param file 文件
     * @throws Exception
     */
    @ApiOperation(value = "局系统总预算信息列表--导入", notes = "局系统总预算信息列表--导入", httpMethod = "POST")
    @RequestMapping(value = "/importTotalBudgetStatisticsList", method = RequestMethod.POST)
    public ResultData<Object> importTotalBudgetStatisticsList(@RequestHeader String adminToken,
                                                      @ApiParam(name = "file", value = "excel文件") @RequestParam(value = "file", required = false) @RequestPart MultipartFile file) throws Exception {
        // 处理参数对象
        try {
            //        Account account = redisService.get(adminToken, Account.class);
            //        if (account == null) {
            //            return ResultData.token();
            //        }

            // 参数过滤
            if (file == null || file.getBytes() == null){
                return ResultData.error("文件不能为空！");
            }

            // 调用服务处理业务
            return totalBudgetService.importTotalBudgetStatisticsList(file);
        } catch (Exception e) {
            logger.error("局系统总预算信息列表--导入异常：", e);
        }

        // 返回结果
        return ResultData.exp();
    }

service:

/**
     * 局系统总预算信息列表--导入
     * @param file 文件
     * @throws Exception
     */
    public ResultData<Object> importTotalBudgetStatisticsList(MultipartFile file) {
        // 先将excel数据转换为list集合数据
        List<TotalBudgetVo> convertAfterList = FileUtil.importExcel(file, 0, 1, TotalBudgetVo.class);
        if(CollectionUtils.isEmpty(convertAfterList)){
            return ResultData.error("Excel数据为空，excel转化失败！");
        }

        // 过滤掉不必要的数据集合
        for (int i = 0; i < convertAfterList.size(); i++) {
            TotalBudgetVo convertAfter = convertAfterList.get(i);
            if(StringUtils.isEmpty(convertAfter.getDepartment())){
                convertAfterList = convertAfterList.subList(0, i);
                break;
            }
        }

        // 申请提示返回对象
        StringBuffer lastMsg = new StringBuffer();
        lastMsg.append("共计【"+convertAfterList.size()+"】条！");

        // 当前行索引
        int index = 1;
        int successIndex = 0;
        for (TotalBudgetVo convertAfter : convertAfterList) {
            index ++;
            // 过滤预算单位/类别
            String department = StringUtils.isEmpty(convertAfter.getDepartment()) ? "" : convertAfter.getDepartment().trim().replaceAll(" ","");
            if(StringUtils.isEmpty(department)){
                lastMsg.append("第【"+index+"】行预算单位/类别为空！");
                continue;
            }

            /***   导入中奖用户   ****/
            TotalBudget totalBudget = new TotalBudget();
            totalBudget.setDepartment(convertAfter.getDepartment());
            totalBudget.setTarget(new BigDecimal(convertAfter.getTarget()));
            totalBudget.setYear(Integer.parseInt(convertAfter.getYear()));
            totalBudget.setJanuary(new BigDecimal(StringUtils.isEmpty(convertAfter.getJanuary()) ? "0" : convertAfter.getJanuary()));
            totalBudget.setFebruary(new BigDecimal(StringUtils.isEmpty(convertAfter.getFebruary()) ? "0" : convertAfter.getFebruary()));
            totalBudget.setMarch(new BigDecimal(StringUtils.isEmpty(convertAfter.getMarch()) ? "0" : convertAfter.getMarch()));
            totalBudget.setApril(new BigDecimal(StringUtils.isEmpty(convertAfter.getApril()) ? "0" : convertAfter.getApril()));
            totalBudget.setMay(new BigDecimal(StringUtils.isEmpty(convertAfter.getMay()) ? "0" : convertAfter.getMay()));
            totalBudget.setJune(new BigDecimal(StringUtils.isEmpty(convertAfter.getJune()) ? "0" : convertAfter.getJune()));
            totalBudget.setJuly(new BigDecimal(StringUtils.isEmpty(convertAfter.getJuly()) ? "0" : convertAfter.getJuly()));
            totalBudget.setAugust(new BigDecimal(StringUtils.isEmpty(convertAfter.getAugust()) ? "0" : convertAfter.getAugust()));
            totalBudget.setSeptember(new BigDecimal(StringUtils.isEmpty(convertAfter.getSeptember()) ? "0" : convertAfter.getSeptember()));
            totalBudget.setOctober(new BigDecimal(StringUtils.isEmpty(convertAfter.getOctober()) ? "0" : convertAfter.getOctober()));
            totalBudget.setNovember(new BigDecimal(StringUtils.isEmpty(convertAfter.getNovember()) ? "0" : convertAfter.getNovember()));
            totalBudget.setDecember(new BigDecimal(StringUtils.isEmpty(convertAfter.getDecember()) ? "0" : convertAfter.getDecember()));
            totalBudget.setCreateTime(LocalDateTime.now());
            totalBudget.setIsValid(1);
            int affectRow = totalBudgetMapper.insert(totalBudget);
            if(affectRow > 0){
                successIndex ++;
            }
        }

        // 返回提示
        if(successIndex == convertAfterList.size()){
            lastMsg.append("导入成功【"+successIndex+"】条！");
        } else {
            if(successIndex == convertAfterList.size()){
                lastMsg.append("导入成功【"+successIndex+"】条！");
            } else {
                lastMsg.append("注：导入失败【"+(convertAfterList.size() - successIndex)+"】条！导入成功【"+successIndex+"】条！");
            }
        }


        // 返回结果
        return ResultData.ok(lastMsg.toString(), null);
    }

导入对应的映射实体：

package com.sport.sportdigitalmanagement.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * @description TotalBudgetVo
 * @author zdj
 * @date 2022-04-26
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class TotalBudgetVo implements Serializable {
    @Excel(name = "预算单位/类别", orderNum = "1", width = 25)
    private String department;

    @Excel(name = "年度", orderNum = "2", width = 15)
    private String year;

    @Excel(name = "指标数", orderNum = "3", width = 15)
    private String target;

    @Excel(name = "1月支出数", orderNum = "4", width = 15)
    private String january;

    @Excel(name = "1-2月支出数", orderNum = "5", width = 15)
    private String february;

    @Excel(name = "1-3月支出数", orderNum = "6", width = 15)
    private String march;

    @Excel(name = "1-4月支出数", orderNum = "7", width = 15)
    private String april;

    @Excel(name = "1-5月支出数", orderNum = "8", width = 15)
    private String may;

    @Excel(name = "1-6月支出数", orderNum = "9", width = 15)
    private String june;

    @Excel(name = "1-7月支出数", orderNum = "10", width = 15)
    private String july;

    @Excel(name = "1-8月支出数", orderNum = "11", width = 15)
    private String august;

    @Excel(name = "1-9月支出数", orderNum = "12", width = 15)
    private String september;

    @Excel(name = "1-10月支出数", orderNum = "13", width = 15)
    private String october;

    @Excel(name = "1-11月支出数", orderNum = "14", width = 15)
    private String november;

    @Excel(name = "1-12月支出数", orderNum = "15", width = 15)
    private String december;
}

完成

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/137475.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！