导出execl工具类

JAVA导出execl工具类

package com.cgnpc.hp.utils;



import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.support.ExcelTypeEnum;

import com.alibaba.excel.write.builder.ExcelWriterBuilder;

import com.alibaba.excel.write.handler.AbstractCellWriteHandler;

import com.alibaba.excel.write.metadata.WriteSheet;

import com.alibaba.excel.write.metadata.fill.FillConfig;

import lombok.extern.slf4j.Slf4j;

import org.apache.http.annotation.Contract;

import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;



/************************************************************

 * 用途说明:  EasyExcel 工具类

 ************************************************************/

@Slf4j

public class EasyExcelUtil {

    //设置最大导出条数

    public static final int MAX_ROW = 5000;



    /*********************************

     * @Author

     * @Date 2021/5/14 10:57

     * 用途说明: 导出excel类型为xlsx

     * 参数说明 params

     * @param dataList : 导出数据

     * @param fileName  : 导出EXCEL名称

     * @param response : response

     * @param sheetName : sheet页名称

     * 返回值说明:

     ***********************************/

    public static <T> void exportSingleSheetExcel(HttpServletResponse response,

                                                  List<T> dataList,

                                                  Class clazz,

                                                  String fileName,

                                                  String sheetName,String template) throws Exception {

        List exportList = new ArrayList();

        if(dataList.size() < 0){

            throw new Exception("无数据");

        }else if(dataList.size() > MAX_ROW){

            //截取最大长度

            dataList = dataList.subList(0, MAX_ROW);

        }

        if(dataList.size() > 0 ){

        	//如果类型不对应  进行转换

            if(!(dataList.get(0).getClass() == clazz)){

                dataList.forEach(n -> {

                    exportList.add(ObjUtil.toBean((Map<String, ?>) n, clazz));

                });

            }

        }



        if(StringUtils.isEmpty(fileName)){

            fileName = "数据导出";

        }

        if(StringUtils.isEmpty(sheetName)){

            sheetName = "sheet1";

        }



        //转码

        fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");

        OutputStream os = response.getOutputStream();

        response.setCharacterEncoding("UTF-8");

        response.setContentType("application/vnd.ms-excel");

        //防止中文乱码

        response.setHeader("Content-Disposition", "attachment;filename="+ fileName + ".xlsx");



//        ExcelWriterBuilder builder = EasyExcel.write(os, clazz).withTemplate(template);

//        builder.excelType(ExcelTypeEnum.XLSX);

//        builder.autoCloseStream(true);

//        builder.sheet(sheetName);

//        FillConfig b= FillConfig.builder().forceNewRow(true).build();

//        ExcelWriter workbook=builder.build();

//        WriteSheet sheet=EasyExcel.writerSheet().build();

//        workbook.fill(dataList,b,sheet);

//        workbook.finish();

        EasyExcel.write(os, clazz).withTemplate(template)

                .excelType(ExcelTypeEnum.XLSX)//导出excel类型为xlsx

                .autoCloseStream(true)

                .sheet(sheetName)

                .doFill(dataList);



        os.flush();

        os.close();



    }



    public static <T> void exportSingleSheetExcel(HttpServletResponse response,

                                                  List<T> dataList,

                                                  Class clazz,

                                                  String fileName,

                                                  String sheetName, String template, AbstractCellWriteHandler abstractCellWriteHandler) throws Exception {

        List exportList = new ArrayList();

        if(dataList.size() < 0){

            throw new Exception("无数据");

        }else if(dataList.size() > MAX_ROW){

            //截取最大长度

            dataList = dataList.subList(0, MAX_ROW);

        }

        if(dataList.size() > 0 ){

            //如果类型不对应  进行转换

            if(!(dataList.get(0).getClass() == clazz)){

                dataList.forEach(n -> {

                    exportList.add(ObjUtil.toBean((Map<String, ?>) n, clazz));

                });

            }

        }



        if(StringUtils.isEmpty(fileName)){

            fileName = "数据导出";

        }

        if(StringUtils.isEmpty(sheetName)){

            sheetName = "sheet1";

        }



        //转码

        fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");

        OutputStream os = response.getOutputStream();

        response.setCharacterEncoding("UTF-8");

        response.setContentType("application/vnd.ms-excel");

        //防止中文乱码

        response.setHeader("Content-Disposition", "attachment;filename="+ fileName + ".xlsx");



        ExcelWriterBuilder builder = EasyExcel.write(os, Contract.class).withTemplate(template);

        builder.excelType(ExcelTypeEnum.XLSX);

        builder.autoCloseStream(true);

        builder.sheet(sheetName);

        builder.registerWriteHandler(abstractCellWriteHandler);

        FillConfig b= FillConfig.builder().forceNewRow(true).build();

        ExcelWriter workbook=builder.build();

        WriteSheet sheet=EasyExcel.writerSheet().build();

        workbook.fill(dataList,b,sheet);

        workbook.finish();



        os.flush();

        os.close();



    }

//    /**

//     * 文件摸板下载

//     * @param response

//     * @param path 文件路径

//     * @param fileName 文件名称

//     * @throws IOException

//     */

//    public static void downloadExcelTemplate(HttpServletResponse response, String path, String fileName) throws IOException {

//        BufferedInputStream bis = null;

//        try {

            ClassPathResource classPathResource = new ClassPathResource(path);

            InputStream inputStream = classPathResource.getInputStream();

//            InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(path);

//            ServletOutputStream out = null;

//            bis = new BufferedInputStream(inputStream);

//            int buffer = 1024 * 10;

//            byte[] data = new byte[buffer];

//            //将文件名称进行转义,否则会造成中文显示异常的问题

//            fileName = URLEncoder.encode(fileName, "UTF-8");

//            response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);

//            int read;

//            out = response.getOutputStream();

//            while ((read = bis.read(data)) != -1) {

//                out.write(data, 0, read);

//            }

//            out.flush();

//        } finally {

//            if(bis != null){

//                bis.close();

//            }

//        }

//    }



}