Excel导出快速案例

使用easyexcel导出Excel表格

1.添加依赖

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

2.工具类的封装

package com.xsili.context.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.xsili.context.exception.BusinessException;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * 使用EasyExcel导出Excel封装的工具类
 */
public class EasyExcelUtils {

    /**
     * 导出数据的高级封装
     * @param response
     * @param fileName
     * @param list
     */
    public static void doWriter(HttpServletResponse response, String fileName, List list){
        if(list==null||list.size()<=0){
            throw new BusinessException("导出数据不能为空");
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
            EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).sheet(fileName).doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出数据的高级封装
     * @param response
     * @param fileName
     * @param list
     */
    public static void doWriter(HttpServletResponse response, String fileName, String sheetName, List list){
        if(list==null||list.size()<=0){
            throw new BusinessException("导出数据不能为空");
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
            EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).sheet(sheetName).doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }






    /**
     * 导出Excel文件的初级版本
     * @param response
     * @param fileName
     * @param list
     */
    public static void doExportExcelV1(HttpServletResponse response, String fileName, List list){
        if(list==null||list.size()<=0){
            throw new BusinessException("导出数据不能为空");
        }
        //进行导出文件操作
        OutputStream out=null;
        ExcelWriter writer=null;
        try {
            out = response.getOutputStream();
            //获取ExcelWriter对象，自定义表头信息
            writer = EasyExcel.write(out, list.get(0).getClass()).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetName(fileName);
            writer.write(list, writeSheet);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName+ ".xlsx").getBytes(), "ISO8859-1"));
            out.flush();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (writer != null) {
                writer.finish();
            }
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    //################################################上传Excel文件相关的接口###########################################

    /**
     * 上传Excel文件功能
     * @param multipartFile
     * @param t
     * @param listener  进行自定义，主要的数据保存操作在listener当中操作，支持异步和同步操作
     * @param <T>
     */
    public static <T> void doRead(MultipartFile multipartFile, T t, AnalysisEventListener listener){
        try {
            EasyExcel.read(multipartFile.getInputStream(), t.getClass(), listener).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 读或上传Excel文件的高级封装
     * @param multipartFile
     * @param clz
     * @param listener  主要的数据保存操作在listener当中操作，支持异步和同步操作
     */
    public static void doRead(MultipartFile multipartFile, Class clz, AnalysisEventListener listener){
        try {
            EasyExcel.read(multipartFile.getInputStream(), clz, listener).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3.创建传导出工具类的List中的Vo对象

package com.xsili.business.user.model.vo;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * 导出广告商基本信息VO接口
 * @ContentRowHeight(10) //内容高宽
  * @HeadRowHeight(20) //行高
 *  @ColumnWidth(25) //列宽
 */
public class ExportAdvertisersVO {

    /**
     * 公司名称
     * index表示位置（从零开始），value表示列名
     */
    @ExcelProperty(value = {"公司名称"}, index = 0)
    private String company;
    /**
     * 用户昵称
     */
    @ExcelProperty(value = {"用户昵称"}, index = 1)
    private String nickName;
    /**
     * 负责人
     */
    @ExcelProperty(value = {"负责人"}, index = 2)
    private String responsibleName;
    /**
     * 联系人电话
     */
    @ExcelProperty(value = {"联系人电话"}, index = 3)
    private String responsiblePhone;
    /**
     * 广告商详细地址
     */
    @ExcelProperty(value = {"广告商详细地址"}, index = 4)
    private String companyAddressDetails;