aliEasyexcel 导出表格，无表头，动态表头导出，导出多个sheet表头

原创已于 2023-05-16 09:13:08 修改 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#servlet #excel #java

于 2023-04-13 19:21:11 首次发布

easyexcel 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了如何利用aliEasyexcel库在Java环境中导出Excel表格，包括无表头和动态表头的设置，以及如何从数据库查询数据并导出到多个sheet。通过提供的工具类，可以方便地实现数据导出功能。

     最近在做数据的导出 ，有的导出是一个对象 有的是无对象 ，并且有的是动态的表头

在这我使用的eaxyexcle 最新版

依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.servlet</groupId>
                    <artifactId>servlet-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
       	<dependency>     
		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

导出表格数据

工具类

/**
 * @Title: SheetDTO
 * @Description:  这里是为了接受不同的实体对象，直接循环集合写入
 * @Date: 2023/4/13 15:17
 */
@Data
public class SheetDTO {

    /**
     * 类型 区分有没有表头 1对象 2无表头 3定义表头
     */
    private Integer type;
    /**
     * sheet页面
     */
    private Integer index;
    /***
     * sheet名字
     */
    private String fileName;
    /**
     * 类型
     */
    private Class<? extends Object> classType;
    /**
     * 数据
     */
    private List<? extends Object> dataList;

}

package com.jianfan.module.edc.util;

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.metadata.WriteSheet;
import com.jianfan.module.edc.excel.conver.CustomSheetWriteHandler;
import com.jianfan.module.edc.vo.statistic.dto.SheetDTO;
import com.jianfan.module.edc.vo.statistic.excleVo.DatabaseVisitTableExportVO;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

import java.net.URLEncoder;
import java.util.List;



public class EasyExcelUtils {


    /**
     * 单个表格数据导出
     * @param response
     * @param sheetFileName sheet名
     * @param classVo 表格实体对象
     * @param dataList 表格数据
     * @throws IOException
     */
    public  static void responseExcel(HttpServletResponse response, String sheetFileName, Integer no,Class<? extends Object> classVo,
                                      List<? extends Object> dataList)
            throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode(sheetFileName, "UTF-8")
                .replaceAll("\\+","%20");
        response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + fileName + ExcelTypeEnum.XLSX.getValue());
        //响应的输入流
        ServletOutputStream outputStream = response.getOutputStream();
        // workbook
        ExcelWriterBuilder writeWorkBook = EasyExcel.write(outputStream, classVo).head(classVo)
                .useDefaultStyle(false)
                .registerWriteHandler(new CustomSheetWriteHandler());
        // sheet
        writeWorkBook.sheet().sheetName(sheetFileName).sheetNo(no).doWrite(dataList);
    }

    /**
     * 多个sheet导出
     * @param response
     * @param fileName 表格名称
     * @param dataList 表格数据
     * @throws IOException
     */
    public  static void responseBatchExcel(HttpServletResponse response,String fileName,
                                           List<SheetDTO> dataList)
            throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String newFileName = URLEncoder.encode(fileName, "UTF-8")
                .replaceAll("\\+","%20");
        response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + newFileName + ExcelTypeEnum.XLSX.getValue());
        //响应的输入流
        ServletOutputStream outputStream = response.getOutputStream();
        // workbook 共用一个ExcelWriter对象
        ExcelWriter writeWorkBook = EasyExcel.write(outputStream)
                .useDefaultStyle(false)
                .registerWriteHandler(new CustomSheetWriteHandler()).build();
        WriteSheet writeSheet=null;
        for(SheetDTO dto:dataList){
             writeSheet=new WriteSheet();
             if(dto.getType().equals("1")){ // 这里是为了判断 集合的对象类型 判断是都是对象
                 EasyExcel.writerSheet(dto.getIndex(), dto.getFileName()).head(dto.getClassType()) // 指定表头对象
                 .build();
             }else if(dto.getType().equals("2")){
                 writeSheet = EasyExcel.writerSheet(dto.getIndex(), dto.getFileName()).needHead(false) // 不需要表头
                 .build();
             }

            writeWorkBook.write(dto.getDataList(),writeSheet);
        }
//			如果不用sheetDto对象 就使用这样访视 手动指定
//        WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1").needHead(false).build();
//        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2").needHead(false).build();
//        writeWorkBook.write(dataList.get(0).getDataList(),writeSheet);
//        writeWorkBook.write(dataList.get(1).getDataList(),writeSheet2);
        writeWorkBook.finish();
        outputStream.close();
    }
	/**
     *  单个无表头导出
     * @param response
     * @param fileName sheet名
     * @param classDto 导出对象 
     * @param dataList 数据集合
     * @throws IOException
     */
    public  static void responseNoNeedHeadExcel(HttpServletResponse response,String fileName,
                                           Class<? extends Object> classDto,List< ? extends Object> dataList)
            throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String newFileName = URLEncoder.encode(fileName, "UTF-8")
                .replaceAll("\\+","%20");
        response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + newFileName + ExcelTypeEnum.XLSX.getValue());
        //响应的输入流
        ServletOutputStream outputStream = response.getOutputStream();
        // workbook
        ExcelWriter writeWorkBook = EasyExcel.write(outputStream)
                .useDefaultStyle(false)
                .registerWriteHandler(new CustomSheetWriteHandler()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0, fileName).needHead(false).build();
        writeWorkBook.write(dataList,writeSheet);

        writeWorkBook.finish();
        outputStream.close();
    }
	 /**
     * 上传文件服务器
     * @param fileName
     * @param dataList
     * @throws IOException
     */
    public  static String uploadResponseBatchExcel(String fileName,
                                           List<SheetDTO> dataList)
            throws IOException {

        //响应的输入流
        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        String filePath=null;
        // workbook
        ExcelWriter writeWorkBook = EasyExcel.write(stream)
                .useDefaultStyle(false)
                .registerWriteHandler(new CustomSheetWriteHandler()).build();
        WriteSheet writeSheet=null;
        for(SheetDTO dto:dataList){
            writeSheet=new WriteSheet();
            if(dto.getType().equals(NumYESNoEnums.ONE.getNum())){
                writeSheet = EasyExcel.writerSheet(dto.getIndex(), dto.getFileName()).head(dto.getClassType()).build();
            }else if(dto.getType().equals(NumYESNoEnums.TWO.getNum())){
                writeSheet = EasyExcel.writerSheet(dto.getIndex(), dto.getFileName()).needHead(false).build();
            }
            writeWorkBook.write(dto.getDataList(),writeSheet);
        }
        writeWorkBook.finish();
        FileApi fileApi = SpringUtil.getBean(FileApi.class);//文件服务器接口
        String newFileName=fileName+System.currentTimeMillis()+ExcelTypeEnum.XLSX.getValue();
        String path="file/excel/"+newFileName;
         filePath = fileApi.createFile(newFileName, path, stream.toByteArray());
        stream.close();
        return filePath;
    }


}

测试

导出单个和多个数据从数据库中查询出来，使用工具即可

测试无表头

  String projectCode = edcProject.getProjectCode();
        String projectName = edcProject.getProjectName();
        List<List<String>> dataList=new ArrayList<>();
        List<String> data=new ArrayList<>();
        data.add("项目代码");data.add(projectCode);
        dataList.add(data);
        List<String> data2=new ArrayList<>();
        data2.add("项目名称");data2.add(projectName);
        dataList.add(data2);
        List<String> data3=new ArrayList<>();
        data3.add("版本名称");data3.add(projectCode);
        dataList.add(data3);
        List<String> data4=new ArrayList<>();
        data4.add("导出人");data4.add("测试");
        dataList.add(data4);
        List<String> data5=new ArrayList<>();
        data5.add("导出时间");data5.add(DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
        dataList.add(data5);
        EasyExcelUtils.responseNoNeedHeadExcel(response,"数据库版本信息",List.class,dataList);

结果：
在这里插入图片描述