EasyPOI插件导出Excel

最新推荐文章于 2023-12-04 11:50:37 发布

Amumu丶

最新推荐文章于 2023-12-04 11:50:37 发布

阅读量492

点赞数 1

分类专栏：导出文章标签： java

本文链接：https://blog.csdn.net/ssadasdas/article/details/107233104

版权

导出专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、前言

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法

二、在`pom.xml`中加入依赖

<!-- start -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.18</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>20.0</version>
</dependency>
<!-- end -->
<dependency>
    <groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.16.10</version>
</dependency>

三、格式化导出字段实体类

package com.mumu.service.provider.param;

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

@Data
@ExcelTarget(value = "BillDownloadVO")
public class BillDownloadVO {

    @Excel(name = "资源Id", width = 20, isImportField = "true_st", needMerge = true, mergeVertical = true, orderNum = "0")
    private String resourceId;

    @Excel(name = "产品名称", width = 20, isImportField = "true_st", orderNum = "1")
    private String productName;

    @Excel(name = "开始时间", databaseFormat = "yyyy-MM-dd HH:mm:ss", width = 20, isImportField = "true_st", orderNum = "2")
    private String startTime;

    @Excel(name = "计费类型", replace = {"一次性_1", "包年_2", "按量_3"},width = 20, isImportField = "true_st", orderNum = "3")
    private String billingType;
    
     @Excel(name = "金额", numFormat = "0.00",width = 20, isImportField = "true_st", orderNum = "4")
    private BigDecimal amount;

    @Excel(name = "费用占比", numFormat = "#.##%",width = 20, isImportField = "true_st", orderNum = "5")
    private BigDecimal proportion;
}

四、后面代码

package com.mumu.service.provider.param;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.fileupload.RequestContext;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

public class Service {
    public void getDownload(){

        HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
        //这边代表从数据库查询出来的list集合数据
        List<Object> list = new ArrayList<>();

        List<BillDownloadVO> downloadVOS = new ArrayList<>();
        for (Object o : list){
            BillDownloadVO vo = new BillDownloadVO();
            BeanUtils.copyProperties(vo,o);
            downloadVOS.add(vo);
        }
        String sheetName = "";
        String fileName = "";
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        exportParams.setCreateHeadRows(true);
        exportParams.setHeaderHeight(500D);
        exportParams.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,BillDownloadVO.class,downloadVOS);
        try {
            ExportUtil.exportHeader(response,workbook,fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

工具类

package com.mumu.service.provider.param;

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

public class ExportUtil {
    public static void exportHeader(HttpServletResponse response, Workbook workbook,String fileName) throws Exception{
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vdn.ms-excel");
        fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");
        response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xlsx");
        response.setHeader("Cache-Control","no-cache");
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        }finally {
            outputStream.close();
        }
    }
}