Springboot——使用EasyPoi实现excel导出(样式)

原创 已于 2022-08-03 09:32:44 修改 · 2.8k 阅读 · 8 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#spring boot #easypoi #excel

于 2022-08-02 18:20:36 首次发布

文章目录

依赖引入

主要依赖pom如下所示：

<dependencies>
   <dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi-spring-boot-starter</artifactId>
       <version>4.1.3</version>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.10</version>
       <scope>provided</scope>
   </dependency>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
   </dependency>
</dependencies>

工具类编写

编写工具类，用于将数据转换为excel信息输出，其逻辑如下所示：

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 org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

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

public class ExcelOperateTools {

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
     *
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param fileName
     * @param response
     * @return
     */
    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));
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param pojoClass pojo实体
     * @param exportParams ExportParams封装实体
     * @param response
     * @return
     */
    private static void defaultExport(	List<?> list, Class<?> pojoClass, String fileName,
                                          HttpServletResponse response, ExportParams exportParams) {
        exportParams.setStyle(ExcelExportStatisticStyler.class);
        //exportParams.setStyle(ExcelExportStyler.class);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    /**
     * 功能描述：Excel导出(下载)
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook Excel对象
     * @return
     */
    private static void downLoadExcel(	String fileName, HttpServletResponse response,
                                          Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "multipart/form-data");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @param file 上传的文件
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @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 RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());

        }
        return list;
    }
}

样式配置类

针对有些其他个别特性化样式变更，可以编写ExcelExportStylerDefaultImpl子实现类，在ExcelOperateTools.defaultExport中，针对ExportParams，采取exportParams.setStyle(xxxx.class)方式设置。

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerDefaultImpl;
import org.apache.poi.ss.usermodel.*;

public class ExcelExportStatisticStyler extends ExcelExportStylerDefaultImpl {


    private CellStyle numberCellStyle;
    private CellStyle numberCellStyle1;
    
    public ExcelExportStatisticStyler(Workbook workbook) {
        super(workbook);
        createNumberCellStyler();
        createNumberCellStyler1();
    }
    private void createNumberCellStyler(){
        numberCellStyle = workbook.createCellStyle();
        numberCellStyle.setAlignment(HorizontalAlignment.CENTER);
        numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0.00"));
        numberCellStyle.setWrapText(true);
//        numberCellStyle.setFont(getFont(workbook, (short)11, false));
//        // 背景色
//        numberCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        numberCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }
    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 字体样式
        font.setBold(isBold);    // 是否加粗
        font.setFontHeightInPoints(size);   // 字体大小
        return font;
    }
    private void createNumberCellStyler1(){
        numberCellStyle1 = workbook.createCellStyle();
        numberCellStyle1.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        numberCellStyle1.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中
        numberCellStyle1.setDataFormat((short)BuiltinFormats.getBuiltinFormat("#,##0")); // 设置数据转换
        numberCellStyle1.setWrapText(true); // 设置自动换行
		
		numberCellStyle1.setBorderBottom(BorderStyle.THIN);   // 下边框
        numberCellStyle1.setBorderLeft(BorderStyle.THIN);     // 左边框
        numberCellStyle1.setBorderTop(BorderStyle.THIN);      // 上边框
        numberCellStyle1.setBorderRight(BorderStyle.THIN);    // 右边框

//        numberCellStyle1.setFont(getFont(workbook, (short)11, false));
//        // 背景色
//        numberCellStyle1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        numberCellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }
    /**
     * 数据行样式
     *
     * @param noneStyler 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity){
        numberCellStyle.setFont(getFont(workbook, (short)11, false));
        // 背景色
        numberCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        numberCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        numberCellStyle1.setFont(getFont(workbook, (short)11, false));
        // 背景色
        numberCellStyle1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        numberCellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        if (entity != null
                && 10==entity.getType()) {
            if("type_1".equals(entity.getDict())){
                return numberCellStyle1;
            }
            return numberCellStyle;
        }
        return super.getStyles(noneStyler, entity);
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return super.getStyles(cell, dataRow, entity, obj, data);
    }
}

样式二：

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

public class ExcelExportStyler implements IExcelExportStyler {
    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;
    private static final short FONT_SIZE_TWELVE = 12;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public ExcelExportStyler(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
    }

    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        // 背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);   // 下边框
        style.setBorderLeft(BorderStyle.THIN);     // 左边框
        style.setBorderTop(BorderStyle.THIN);      // 上边框
        style.setBorderRight(BorderStyle.THIN);    // 右边框
        style.setAlignment(HorizontalAlignment.CENTER);         // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中
        style.setWrapText(true);    // 设置自动换行
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        font.setFontName("宋体"); // 字体样式
        font.setBold(isBold);    // 是否加粗
        font.setFontHeightInPoints(size);   // 字体大小
        return font;
    }
}

测试

数据接受类

导出Excel的数据中，存在一对多的情况，所以需要编写对应的数据接收处理类，进行数据处理。

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.Data;

import java.math.BigDecimal;
@Data
public class BalanceVo {

    @Excel(name = "余额比重（%）",width = 20,height = 8)
    private BigDecimal balanceProp;

    // dict 和 type 在 ExcelExportStatisticStyler 中识别
    // isStatistics 是否需要被合计
    @Excel(name = "本期（万元）",isStatistics=true,width = 20,height = 8,type = 10)
    private BigDecimal currentPeriod;

    @Excel(name = "上期（万元）",isStatistics=true,width = 20,height = 8,type = 10)
    private BigDecimal lastPeriod;

    @Excel(name = "环比增减（%）",width = 20,height = 8)
    private BigDecimal momZJ;

    @Excel(name = "跨期校验（万元）",isStatistics=true,width = 20,height = 8)
    private BigDecimal kqProof;
}

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

import java.math.BigDecimal;

@Data
public class RateVo {

    @Excel(name = "本期（%）",width = 20,height = 8,type = 10)
    private BigDecimal currentRate;

    @Excel(name = "上期（%）",width = 20,height = 8,type = 10)
    private BigDecimal lastRate;
}

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

import java.util.List;

@Data
public class UnitDepositBalance {

    // 正常字段   @Excel  needMerge合并单元格
    @Excel(name = "字段",width = 20,height = 8,needMerge = true)
    private String fields;

    @Excel(name = "名称",width = 20,height = 8)
    private String name;

    // 合并单元格字段
    @ExcelCollection(name="余额")
    private List<BalanceVo> balanceLists;

    @ExcelCollection(name="利率")
    private List<RateVo> rateVoList;
}

测试接口

import com.nstc.util.ExcelOperateTools;
import com.nstc.vo.BalanceVo;
import com.nstc.vo.RateVo;
import com.nstc.vo.UnitDepositBalance;
import com.sun.deploy.net.HttpResponse;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
public class TestController {

    @GetMapping("/export")
    public void export(HttpServletResponse response){
        // 导出数据集合
        List<UnitDepositBalance> unitDepositBalances = new ArrayList<>();

        UnitDepositBalance unitDepositBalance = new UnitDepositBalance();
        unitDepositBalance.setFields("客户证件类型（3）");
        unitDepositBalance.setName("单位");

        List<BalanceVo> balanceVos = new ArrayList<>();

        BalanceVo balanceVo = new BalanceVo();
        balanceVo.setBalanceProp(new BigDecimal(100));
        balanceVo.setCurrentPeriod(new BigDecimal(127488.21));
        balanceVo.setLastPeriod(new BigDecimal(120720.62));
        balanceVo.setMomZJ(BigDecimal.ONE);
        balanceVos.add(balanceVo);

        BalanceVo balanceVo2 = new BalanceVo();
        balanceVo2.setBalanceProp(new BigDecimal(100));
        balanceVo2.setCurrentPeriod(new BigDecimal(127488.21));
        balanceVo2.setLastPeriod(new BigDecimal(120720.62));
        balanceVo2.setMomZJ(BigDecimal.ONE);
        balanceVos.add(balanceVo2);

        unitDepositBalance.setBalanceLists(balanceVos);

        List<RateVo> rateVos = new ArrayList<>();

        RateVo rateVo = new RateVo();
        rateVo.setCurrentRate(new BigDecimal(1.88));
        rateVo.setLastRate(new BigDecimal(1.98));
        rateVos.add(rateVo);
        RateVo rateVo2 = new RateVo();
        rateVo2.setCurrentRate(new BigDecimal(1.88));
        rateVo2.setLastRate(new BigDecimal(1.98));
        rateVos.add(rateVo2);

        unitDepositBalance.setRateVoList(rateVos);

        unitDepositBalances.add(unitDepositBalance);

        // 导出
        ExcelOperateTools.exportExcel(unitDepositBalances,"单位存款余额","单位存款余额",UnitDepositBalance.class,"导出文件测试.xls",response);
    }
}

导出效果

在这里插入图片描述

结语

样式比较粗糙，主要是为了测试功能，博客中未作过多文字说明。

参考资料

EasyPOI 调整单元样式

Springboot 导入导出Excel ，一对多关系，复合表格、合并单元格数据

用easyPoi导出excel，带多sheet,合并单元格，合计，单元格金额类型

EasyPOI 导出excel设置边框，背景颜色，字体样式

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值