web 百万条数据导出Excel代码示例

原创

劳者 2023-03-17 09:11:17 ©著作权

文章标签 .net 文章分类 软件研发

©著作权归作者所有：来自51CTO博客作者劳者的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、参考连接

easyExcel官网：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write

easyExcel导出Excel:https://blog.csdn.net/Cr1556648487/article/details/127085925

easyExcel和Poi导出Excel性能对比：https://blog.csdn.net/qq_24948625/article/details/125410127

2、代码示例

项目使用的依赖

<!--    poi依赖    -->
      <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!--EasyExcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

Controller层导出代码

package com.mt.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.mt.entity.PcIot;
import com.mt.mapper.PcIotMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

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

@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试" )
@RequestMapping("/api/export")
public class PcIotExcelController {
    @Autowired
    PcIotMapper pcIotMapper;
    private int currentIndex = 0;
    /**
     * 设置excel下载响应头属性
     */
    private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    //easyExcel实现百万条数据导出方法。
    @SneakyThrows(IOException.class)
    @ApiOperation(value = "物联网卡信息EasyExcel导出")
    @RequestMapping(value = "/pcIotEasyExcel", method = RequestMethod.GET)
    public void exportPcIotEasyExcel(HttpServletResponse response) {
        //设置excel下载响应头属性
        setExcelRespProp(response, "物联网卡信息EasyExcel导出");

        PcIot pcIot =  new PcIot();
        int pageInex = 0;//查询的页码
        pcIot.setPageNo(0);//分页查询的起始行号
        pcIot.setPageSize(10000);//分页查询每页行数
        List<PcIot> list = pcIotMapper.pageExcel(pcIot);//从数据库分页查询出的数据

        int sheet_max_row = 1000000;//设置sheet的最大记录条数
        int writeCount = 0;
        int sheetIndex = 0;//当前导出的是第几个sheet
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), PcIot.class).build();//指定输出流，和导出数据格式
        WriteSheet sheet = null;
        while(list.size()>0){
            writeCount += list.size();
            if(writeCount > sheetIndex*sheet_max_row){//当前sheet写入的数据超过最大值，新建一个新的sheet
                sheetIndex ++;
                sheet = EasyExcel.writerSheet("物联网卡信息"+sheetIndex).build();//新建一个sheet
            }
            excelWriter.write(list,sheet);//将查询的数据写入excel
            pageInex++;//查询页码加1
            pcIot.setPageNo(pageInex*pcIot.getPageSize());
            list = pcIotMapper.pageExcel(pcIot);//从数据库分页查询出的数据
        }
        excelWriter.finish();//从输出流写出数据。
    }

    //Poi方式实现百万条数据Excel导出
    @SneakyThrows(IOException.class)
    @ApiOperation(value = "物联网卡信息Poi导出")
    @RequestMapping(value = "/pcIotPoi", method = RequestMethod.GET)
    public void exportPcIotPoiList(HttpServletResponse response) {
        //设置excel下载响应头属性
        setExcelRespProp(response, "物联网卡信息Poi导出");

        PcIot pcIot =  new PcIot();
        int pageInex = 0;//查询的页码
        pcIot.setPageNo(0);//分页查询的起始行号
        pcIot.setPageSize(10000);//分页查询每页条数

        List<PcIot> list = pcIotMapper.pageExcel(pcIot);//从数据库分页查询出的数据
        int sheet_max_row = 1000000;
        int writeCount = 0;
        int sheetIndex = 0;
        Sheet sheet = null;
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        while(list.size()>0){
            writeCount += list.size();
            if(writeCount > sheetIndex*sheet_max_row){//当前sheet写入的数据超过最大值，新建一个新的sheet
                currentIndex = 0;
                sheetIndex ++;
                sheet = workbook.createSheet("物联网卡信息Poi导出"+sheetIndex);//新建一个sheet
            }
            writeExcel(sheet,list);//将查询的数据写入excel
            pageInex++;//分页查询页码加1
            pcIot.setPageNo(pageInex*pcIot.getPageSize());
            list = pcIotMapper.pageExcel(pcIot);//从数据库分页查询出的数据
        }
        workbook.write(response.getOutputStream());//写出数据到输出流
        workbook.dispose();
    }

    //将查询的数据写入excel
    private void writeExcel(Sheet sheet ,List<PcIot> pcIotExcels ){
        for( int i=0;i<pcIotExcels.size();i++){
            Row row = sheet.createRow(currentIndex++);
            int c = 0;
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getId());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getCompany()==null?"":pcIotExcels.get(i).getCompany());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getOrderId()==null?"":pcIotExcels.get(i).getOrderId());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getPlatform()==null?"":pcIotExcels.get(i).getPlatform());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getProduct()==null?"":pcIotExcels.get(i).getProduct());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getProductid()==null?"":pcIotExcels.get(i).getProductid().toString());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getIp1()==null?"":pcIotExcels.get(i).getIp1());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getIp2()==null?"":pcIotExcels.get(i).getIp2());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getSupplier()==null?"":pcIotExcels.get(i).getSupplier());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getTelecom()==null?"":pcIotExcels.get(i).getTelecom());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getName()==null?"":pcIotExcels.get(i).getName());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getNumberStart()==null?"":pcIotExcels.get(i).getNumberStart());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getNumberEnd()==null?"":pcIotExcels.get(i).getNumberEnd());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getCount()==null?"":pcIotExcels.get(i).getCount().toString());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getIccid()==null?"":pcIotExcels.get(i).getIccid());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getUsim()==null?"":pcIotExcels.get(i).getUsim());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getImei()==null?"":pcIotExcels.get(i).getImei());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getSetMeal()==null?"":pcIotExcels.get(i).getSetMeal());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getPackageCycle()==null?"":pcIotExcels.get(i).getPackageCycle());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getOpenTime()==null?"":pcIotExcels.get(i).getOpenTime());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getRenewalTime()==null?"":pcIotExcels.get(i).getRenewalTime());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getExpireTime()==null?"":pcIotExcels.get(i).getExpireTime());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getMeterType()==null?"":pcIotExcels.get(i).getMeterType());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getMeterId()==null?"":pcIotExcels.get(i).getMeterId());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getAccessNumber()==null?"":pcIotExcels.get(i).getAccessNumber());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getActivationDate()==null?"":pcIotExcels.get(i).getActivationDate());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getReturnDate()==null?"":pcIotExcels.get(i).getReturnDate());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getScrapDate()==null?"":pcIotExcels.get(i).getScrapDate());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getStopDate()==null?"":pcIotExcels.get(i).getStopDate());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getRemark()==null?"":pcIotExcels.get(i).getRemark());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getRemark1()==null?"":pcIotExcels.get(i).getRemark1());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getComputer()==null?"":pcIotExcels.get(i).getComputer());
            row.createCell(c++).setCellValue(pcIotExcels.get(i).getCreateDate()==null?"":pcIotExcels.get(i).getCreateDate().toString());
        }
    }

}

PcIot数据实体类

实体类中使用到了EasyExcel的核心注解，我们分别来了解下：

@ExcelProperty：核心注解，value属性可用来设置表头名称，converter属性可以用来设置类型转换器；

@ColumnWidth：用于设置表格列的宽度；

@ExcelIgnore表示Excel导入导出的时候忽略该字段。

package com.mt.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

/**
 * (PcIot)表实体类
 */
@SuppressWarnings("serial")
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class PcIot extends BaseEntity {

    @ExcelProperty("ID")//POI方式实现的导出可以不需要这个注解
    @ColumnWidth(10)//POI方式实现的导出可以不需要这个注解
    private Integer id;

    @ExcelProperty("客户名称")
    @ColumnWidth(10)
    private String company;

    @ExcelProperty("订单号")
    @ColumnWidth(10)
    private String orderId;

    @ExcelProperty("平台名称")
    @ColumnWidth(10)
    private String platform;

    @ExcelProperty("产品id")
    @ColumnWidth(10)
    private Integer productid;

    @ExcelProperty("产品")
    @ColumnWidth(10)
    private String product;

    @ExcelProperty("ip1")
    @ColumnWidth(10)
    private String ip1;

    @ExcelProperty("ip2")
    @ColumnWidth(10)
    private String ip2;

    @ExcelProperty("代理商")
    @ColumnWidth(10)
    private String supplier;

    @ExcelProperty("三大运营商")
    @ColumnWidth(10)
    private String telecom;

    @ExcelProperty("客户名称")
    @ColumnWidth(10)
    private String name;

    @ExcelProperty("起始号码")
    @ColumnWidth(10)
    private String numberStart;

    @ExcelProperty("结束号码")
    @ColumnWidth(10)
    private String numberEnd;

    @ExcelProperty("数量")
    @ColumnWidth(10)
    private Integer count;

    @ExcelProperty("CCID")
    @ColumnWidth(30)
    private String iccid;

    @ExcelProperty("USIM")
    @ColumnWidth(10)
    private String usim;

    @ExcelProperty("IMEI")
    @ColumnWidth(10)
    private String imei;

    @ExcelProperty("套餐")
    @ColumnWidth(10)
    private String setMeal;

    @ExcelProperty("套餐周期")
    @ColumnWidth(10)
    private String packageCycle;

    @ExcelProperty("开卡时间")
    @ColumnWidth(10)
    private String openTime;

    @ExcelProperty("续费时间")
    @ColumnWidth(10)
    private String renewalTime;

    @ExcelProperty("到期时间")
    @ColumnWidth(10)
    private String expireTime;

    @ExcelProperty("表类型")
    @ColumnWidth(10)
    private String meterType;

    @ExcelProperty("表号")
    @ColumnWidth(10)
    private String meterId;

    @ExcelProperty("接入号码")
    @ColumnWidth(10)
    private String accessNumber;

    @ExcelProperty("激活日期")
    @ColumnWidth(10)
    private String activationDate;

    @ExcelProperty("返厂日期")
    @ColumnWidth(10)
    private String returnDate;

    @ExcelProperty("报废日期")
    @ColumnWidth(10)
    private String scrapDate;

    @ExcelProperty("停费日期")
    @ColumnWidth(10)
    private String stopDate;

    @ExcelProperty("备注")
    @ColumnWidth(10)
    private String remark;

    @ExcelProperty("备注")
    @ColumnWidth(10)
    private String remark1;

    @ExcelProperty("计算机名称")
    @ColumnWidth(10)
    private String computer;

    @ExcelProperty("包装IP")
    @ColumnWidth(10)
    private String ip;

    @ExcelProperty("日期")
    @ColumnWidth(10)
    private Integer dateInt;

    @ExcelProperty("用户编号")
    @ColumnWidth(10)
    private Integer userId;

    @ExcelProperty("用户登录名")
    @ColumnWidth(10)
    private String userLogin;

    @ExcelProperty("用户姓名")
    @ColumnWidth(10)
    private String userName;

}