SXSSFWorkbook实现百万级数据excel生成,只需要7分钟,话不多说,直接贴代码

本测试采用的技术是springboot,java,maven,SXSSFWorkbook

maven配置:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- 配置apache commons -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
        </dependency>
        <!-- 配置servlet -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
        </dependency>
        <!-- 配置Web 排除内置Tomcat用于打war包 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.application.properties

server.port=8081
r.excel.size=1000

3.入口类

 @RequestMapping("/excel/export")
    public void export(HttpServletResponse response) throws Exception {
        logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
        String fileName= ExcelUtil.exportExcel(getData(),getNameList());
        download(fileName,response);
        logger.info("结束导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }

4.定义表头

    //获取表名
    public String[] getNameList(){
        String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};
        return titles;
    }

5.获取数据

    //获取数据
    public List<Map<String,Object>> getData(){
        List<Map<String,Object>> maps=new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            Map map=new HashMap();
            map.put("账号",i);
            map.put("密码",i);
            map.put("状态",i);
            map.put("昵称",i);
            map.put("职位",i);
            map.put("手机号",i);
            map.put("邮箱",i);
            map.put("创建人ID",i);
            map.put("创建时间",i);
            map.put("修改人ID",i);
            map.put("修改时间",i);
            maps.add(map);
        }
        return maps;
    }

6.导出excel入口方法

    /**
     *
     * 导出
     * @param datas
     * @param columns
     * @return
     * @throws Exception
     */
    public static String exportExcel(List<Map<String, Object>> datas, String[] columns) throws Exception {
        List<String> filePaths;
        String fileName = "D:\\project\\导出优化测试\\demo\\static";
        String name = "数据导出.xls";
        String date="_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        fileName+=File.separator+name.replace(".",date+".");
        filePaths = createExcelAutoSplite(datas, columns, fileName);
        fileName = fileName.replace("xls","zip");
        ZipUtil.zipFiles(filePaths, fileName);
        IOUtil.clearTmpFile(filePaths);
        return fileName;
    }

7.按大小划分生成多少个文件,刚才配置设置的是1000

  /**
     * 这个方法是是生成excel文件，自动进行大小划分。当超过指定大小后会创建下一个文件。
     *
     * @param datas   要写入excel的数据
     * @param columns 将datas中的map的列与模版相对应，这个起到数据列排序的作用
     * @return 本次生成的文件名的集合
     */
    public static List<String> createExcelAutoSplite(List<Map<String, Object>> datas, String[] columns, String fileName) throws Exception {
        int fileSize = datas.size();
        int filePartOrder = 0;
        List<String> filePaths = new ArrayList<String>();
        int generateNumber = fileSize/size==1?fileSize/size:(int)fileSize/size+1;
        while (generateNumber > filePartOrder) {
            String createName = fileName.substring(0,fileName.indexOf("."))+filePartOrder+".xls";
            int endRowCount = (datas.size() - size * filePartOrder) > size ? size * filePartOrder + size : datas.size();
            createName = createExcel(size * filePartOrder, endRowCount, datas, createName, columns, columns);
            filePaths.add(createName);
            filePartOrder++;
        }
        return filePaths;
    }

8.生成excel文件

 /**
     * 生成excel文件
     *
     * @param fileName 文件名称
     * @param titles   标题
     * @throws Exception
     */
    private static String createExcel(Integer startRowCount, Integer endRowCount, List<Map<String, Object>> maps, String fileName, String[] titles, String[] models) throws Exception {
        SXSSFWorkbook wb=null;
        try {
            // 初始化EXCEL
            wb = initHeader(titles);
            SXSSFSheet eachSheet = wb.getSheetAt(0);
            int index = 1;
            CellStyle cs=getCellStyle(wb);
            eachSheet.setDefaultColumnWidth(4);
            for (int i = startRowCount; i < endRowCount; i++) {
                SXSSFRow row = eachSheet.createRow(index);
                Map data = maps.get(i - startRowCount);
                for (int j = 0; j < models.length; j++) {
                    String value=data.get(models[j])!=null ? data.get(models[j]).toString():"";
                    setCell(row,value,cs,j);
                    setColumnWidth(eachSheet,j);
                }
                index++;
            }
            downLoadExcelToLocalPath(wb,fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return fileName;
    }

9.初始化表头

/**
     * 初始化EXCEL(sheet个数和标题)
     *
     * @param titles 标题集合
     * @return XSSFWorkbook对象
     */
    public static SXSSFWorkbook initHeader(String[] titles) {
        SXSSFWorkbook wb = null;
        try {
            wb = new SXSSFWorkbook();
            SXSSFSheet sheet = wb.createSheet("数据导出");
            SXSSFRow row = sheet.createRow(0);
            CellStyle cs=getCellStyle(wb);
            sheet.setDefaultColumnWidth(4);
            for (int j = 0; j < titles.length; j++) {
                setCell(row,titles[j],cs,j);
                setColumnWidth(sheet,j);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

10.设置单元格样式

 /**
     * 设置单元格样式
     *
     */

    private static CellStyle getCellStyle(SXSSFWorkbook sxssfWorkbook) {
        // 设置样式
        CellStyle cellStyle = sxssfWorkbook.createCellStyle();
        // 设置字体
        Font font = sxssfWorkbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

11.设置列值

public static void setCell(SXSSFRow  row, String value, CellStyle cs, int i){
        Cell cell=row.createCell(i);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(value);
        cell.setCellStyle(cs);
    }

12.设置列宽

 public static void setColumnWidth(SXSSFSheet sheet, int i) {
        int maxWidth = 65280;
        int space = 2000;
        sheet.trackAllColumnsForAutoSizing();
        sheet.autoSizeColumn(i, true);
        int width = sheet.getColumnWidth(i);
        if (width < maxWidth - space) {
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + space);
        }
    }

13.下载生成的excel文件到本地

    /**
     * 下载EXCEL到本地指定的文件夹
     *
     * @param wb         EXCEL对象SXSSFWorkbook
     * @param exportPath 导出路径
     */
    public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        try {
            fops = new FileOutputStream(exportPath);
            wb.write(fops);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

14.获取所有的excel文件的路径,然后生成zip,返回zip的路径

 

15.根据路径下载文件

public String download(String name,HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType("application/x-download");
        String filedownload = name;
        String filedisplay = filedownload.substring(filedownload.lastIndexOf("\\")+1,filedownload.length());
        filedisplay = URLEncoder.encode(filedisplay,"UTF-8");
        response.addHeader("Content-Disposition","attachment;filename=" + filedisplay);
        OutputStream outp = null;
        FileInputStream in = null;
        try{
            outp = response.getOutputStream();
            in = new FileInputStream(filedownload);
            byte[] b = new byte[1024];
            int i = 0;
            while((i = in.read(b)) > 0)  {
                outp.write(b, 0, i);
            }
            outp.flush();
            File file=new File(name);
            System.out.println(file.getParentFile());
            IOUtil.clearTmpFile(file.getParentFile().listFiles());
        } catch(Exception e){
            System.out.println("Error!");
            e.printStackTrace();
        }finally{
            if(in != null)
            {
                try {
                    in.close();
                } catch (IOException e) {
// TODO Auto-generated catch block
                    e.printStackTrace();
                }
                in = null;
            }
            if(outp != null)
            {
                try {
                    outp.close();
                } catch (IOException e) {
// TODO Auto-generated catch block
                    e.printStackTrace();
                }
                outp = null;
            }
        }
        return null;

    }

好了，以上就是SXSSFWorkbook的生成方法，觉得写的好麻烦点个赞,加油噢!

不太懂的可以去我的博客那边下载噢

https://download.youkuaiyun.com/my