【自用】数据导出到Excel

导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;

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

/**
 * @Describe 导出Excel 工具类
 */
@Slf4j
public class ExportExcelUtil<T> {

    /**
     * 导出excel
     *
     * @param response
     * @param listParam  打印对象
     * @param classParam 模板class
     * @param sheetName  模板名字
     * @param <T>
     */
    public static <T> void export(HttpServletResponse response, List<T> listParam, Class<T> classParam, String sheetName) {
        try {
            //设置响应域
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
            String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //构建excelWriter对象
            @Cleanup("finish") ExcelWriter writer = EasyExcel
                    //将数据映射到Student实体类并响应到浏览器
                    .write(new BufferedOutputStream(response.getOutputStream()), classParam)
                    //自动关闭输出流
                    .autoCloseStream(Boolean.TRUE)
                    //excel版本，强烈建议导出07版excel，因为省内存
                    .excelType(ExcelTypeEnum.XLSX)
                    //是否需要表头
                    .needHead(Boolean.TRUE)
                    //构建
                    .build();
            //初始化WriteSheet对象
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            writeSheet.setSheetNo(1);
            writeSheet.setSheetName(sheetName);
            //导出excel，注意导出结束要finish
            writer.write(listParam, writeSheet);
            //finish的时候会自动关闭OutputStream
            writer.finish();
        } catch (Exception e) {
            log.error("ExportExcelUtil 导出excel 异常:" + e);
        }
    }

}

导出实体定义

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@ExcelIgnoreUnannotated  //字段不加@ApiModelProperty注解 不参与读写
public class 导出实体 {


	//index  表示导出后该字段在表中位置  value为对应名称

    @ExcelProperty(index = 0, value = "名称1")
    @ApiModelProperty("xxxx")
    private String xxxxx;

    @ExcelProperty(index = 1, value = "名称2")
    @ApiModelProperty("xxxx")
    private String xxxx;

    @ExcelProperty(index = 2, value = "名称3")
    @ApiModelProperty("xxxx")
    private String xxxx;


}

service 层

	/**
     * 导出数据 
     * @param response
     * @param 查询条件实体  如果需要导出的数据是全量没查询条件可不加
     */
    void 方法名(HttpServletResponse response, 查询条件实体);

serviceImpl 层

	@Override
    public void 方法名(HttpServletResponse response, 查询条件实体) {
    	//查询导出数据的集合
        List<返回实体> 集合名字 = Mapper.xxxxx(查询条件实体);
        ExportExcelUtil.export(response, 集合, 返回实体.class, "表格名称");

    }