阿里 easyexcel快速导出excel文件简单操作

一、首先引入pom文件，如下所示：

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.2.6</version>
</dependency>

二、控制层实现，如图所示：

/**
	 * 导出 应收余额表
	 * @param receivable
	 * @return
	 */
	@GetMapping(value = "exportReceivable")
	@ApiOperation(value = "导出  应收余额表", notes = "导出  应收余额表", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
	public void exportReceivable(HttpServletResponse response,@Valid QueryReceivable receivable) {
		accountsBiz.exportReceivable(response,receivable);
	}

三、业务层，如图所示：

/**
     *  导出 应收余额表
     * @param response
     * @param receivable
     */
    public void exportReceivable(HttpServletResponse response, QueryReceivable receivable) {
        if (StringUtils.isBlank(receivable.getStartTime())){
            receivable.setStartTime(DateUtils.getDateStr());
        }
        if (StringUtils.isBlank(receivable.getEndTime())){
            receivable.setEndTime(DateUtils.getDateStr());
        }
        IPage<ReceivableVO> page =paymentService.getReceivableList(new Page(receivable.getPageNum(),receivable.getPageSize()),receivable);
        ExcelUtils.export(response, page.getRecords(), "应收余额表", ReceivableVO.class);
    }

四、导出响应实体，如图所示：

package cn.jiezhongtech.logistics.service.vo.payable;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.math.BigDecimal;

/**
 * @author ZHY
 * @since 2020/8/24 15:33
 */
@Data
@ContentRowHeight(25)
@HeadRowHeight(30)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value = "应收余额表")
public class ReceivableVO {

    @ApiModelProperty(value = "结算单位")
    @ExcelProperty(value = "结算单位", index = 0)
    private String settlementUnitName;

    @ApiModelProperty(value = "结算单位id")
    @ExcelIgnore
    private Integer settlementUnitId;

    @ApiModelProperty(value = "期初余额")
    @ExcelProperty(value = "期初余额", index = 1)
    private BigDecimal beginningAmount;

    @ApiModelProperty(value = "本期应收")
    @ExcelProperty(value = "本期应收", index = 2)
    private BigDecimal  receivableAmount;

    @ApiModelProperty(value = "本期实收")
    @ExcelProperty(value = "本期实收", index = 3)
    private BigDecimal  actualAmount;

    @ApiModelProperty(value = "期末余额")
    @ExcelProperty(value = "期末余额", index = 4)
    private BigDecimal endAmount;

    @ApiModelProperty(value = "财务期末余额")
    @ExcelProperty(value = "财务期末余额", index = 5)
    private BigDecimal financeEndAmount;
}

 

五、导出工具封装，如图代码：

package cn.jiezhongtech.logistics.common.utils;

import cn.jiezhongtech.logistics.common.exception.HandException;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import io.undertow.util.Headers;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * Excel工具类
 *
 * @author: ZHY
 * @date: 2020-08-24 17:31
 * @version:
 **/
public class ExcelUtils {

    private final static HorizontalCellStyleStrategy HORIZONTAL_CELL_STYLE_STRATEGY;

    static {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容字体
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HORIZONTAL_CELL_STYLE_STRATEGY = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出
     *
     * @param response
     * @param data     数据
     * @param fileName 文件名
     * @param t        导出对象
     **/
    public static <T> void export(HttpServletResponse response, List<T> data, String fileName, Class<T> t) {
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            response.setHeader(Headers.CONTENT_DISPOSITION_STRING, "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            EasyExcel.write(response.getOutputStream(), t)
                    .registerWriteHandler(HORIZONTAL_CELL_STYLE_STRATEGY)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(fileName).doWrite(data);
        } catch (Exception e) {
            // 重置response
            response.reset();
            throw new HandException("下载失败");
        }
    }


}

 

六、导出的效果图，如图所示：

 

看不懂得话，可以上官网看地址：easyexcel官网  我只能帮你到这了，嘿嘿