POI之SXSSFWorkbook大量数据导出至excel

淡淡的烟草味﹌ 2023-07-24 03:21 15阅读 0赞

POI之SXSSFWorkbook大量数据导出至excel  
  
一：简介

SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，  
          SXSSFWorkbook专门处理大数据，对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。  
          它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming  
          版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到  
          硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不  
          可访问的。只有还保存在内存里的才可以被访问到。  
          注:HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)  
               是1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据，  
               甚至上千条数据就可以考虑使用SXSSFWorkbook了。  
        注意：首先需要引入依赖：注意：4.0.0版本的JDK需要1.8以上，如果JDK是1.7的，那么就使用3.9版本的依赖

<dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                    <version>4.0.0</version>
                </dependency>
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                    <version>4.0.0</version>
                </dependency>
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>4.0.0</version>
                </dependency>

二：实例一，我们使用SXSSFWorkbook向Excel中写入50万条数据，只需要         34秒左右，内存占用率最多在700M左右，CPU使用率在25%左右             
        ![20200409144714822.png][]  
           代码如下：

package com.test.POI;
         
        import java.io.BufferedOutputStream;
        import java.io.File;
        import java.io.FileNotFoundException;
        import java.io.FileOutputStream;
        import java.io.IOException;
         
        import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
        import org.apache.poi.xssf.streaming.SXSSFRow;
        import org.apache.poi.xssf.streaming.SXSSFSheet;
        import org.apache.poi.xssf.streaming.SXSSFWorkbook;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;
         
        public class SXSSFWORKBookUtils {
         
            @SuppressWarnings("resource")
            public static void main(String[] args) throws FileNotFoundException, InvalidFormatException {
                long startTime = System.currentTimeMillis();
                String filePath = "E:\\txt\\111.xlsx";
                SXSSFWorkbook sxssfWorkbook = null;
                BufferedOutputStream outputStream = null;
                try {
                    //这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
                     sxssfWorkbook = new SXSSFWorkbook(getXSSFWorkbook(filePath),100);
                     //获取第一个Sheet页
                     SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
                     for (int i = 0; i < 50; i++) {
                         for (int z = 0; z < 10000; z++) {
                             SXSSFRow row = sheet.createRow(i*10000+z);
                             for (int j = 0; j < 10; j++) {
                                 row.createCell(j).setCellValue("你好："+j);
                             }
                         }
                    }
                     outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
                     sxssfWorkbook.write(outputStream);
                     outputStream.flush();
                     sxssfWorkbook.dispose();// 释放workbook所占用的所有windows资源
                } catch (IOException e) {
                    e.printStackTrace();
                }finally {
                    if(outputStream!=null) {
                        try {
                            outputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
                long endTime = System.currentTimeMillis();
                System.out.println(endTime-startTime);
            }
            
            
            /**
             * 先创建一个XSSFWorkbook对象
             * @param filePath
             * @return
             */
            public static XSSFWorkbook getXSSFWorkbook(String filePath) {
                XSSFWorkbook workbook =  null;
                BufferedOutputStream outputStream = null;
                try {
                    File fileXlsxPath = new File(filePath);
                    outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
                    workbook = new XSSFWorkbook();
                    workbook.createSheet("测试Sheet");
                    workbook.write(outputStream);
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    if(outputStream!=null) {
                        try {
                            outputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return workbook;
            }
            
        }

效果：  
      ![20200409144734275.png][]  
三：我们使用XSSFWorkbook常规的方法分批向excel中写入50万条数据，内         存占用率最多在  2.1个G左右（占用了很大的内存），CPU使用率在90%           左右 ，最后内存 溢出了

![20200409144815843.png][]  
          代码如下：

package com.test;
         
        import java.io.BufferedOutputStream;
        import java.io.File;
        import java.io.FileInputStream;
        import java.io.FileNotFoundException;
        import java.io.FileOutputStream;
        import java.io.IOException;
         
        import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
        import org.apache.poi.xssf.usermodel.XSSFRow;
        import org.apache.poi.xssf.usermodel.XSSFSheet;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;
         
        public class POIController {
            /**
             * 这种方式效率比较低并且特别占用内存，数据量越大越明显
             * @param args
             * @throws FileNotFoundException
             * @throws InvalidFormatException
             */
            public static void main(String[] args) throws FileNotFoundException, InvalidFormatException {
                long startTime = System.currentTimeMillis();
                BufferedOutputStream outPutStream = null;
                XSSFWorkbook workbook = null;
                FileInputStream inputStream = null;
                String filePath = "E:\\txt\\666.xlsx";
                try {
                     workbook = getWorkBook(filePath);
                     XSSFSheet sheet = workbook.getSheetAt(0);
                     for (int i = 0; i < 50; i++) {
                         for (int z = 0; z < 10000; z++) {
                             XSSFRow row = sheet.createRow(i*10000+z);
                             for (int j = 0; j < 10; j++) {
                                 row.createCell(j).setCellValue("你好："+j);
                             }
                         }
                         //每次要获取新的文件流对象，避免将之前写入的数据覆盖掉
                         outPutStream = new BufferedOutputStream(new FileOutputStream(filePath));
                         workbook.write(outPutStream);
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }finally {
                    if(outPutStream!=null) {
                        try {
                            outPutStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if(inputStream!=null) {
                        try {
                            inputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if(workbook!=null) {
                        try {
                            workbook.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
                long endTime = System.currentTimeMillis();
                System.out.println(endTime-startTime);
            }
            /**
             * 先创建一个XSSFWorkbook对象
             * @param filePath
             * @return
             */
            public static XSSFWorkbook getWorkBook(String filePath) {
                XSSFWorkbook workbook =  null;
                try {
                    File fileXlsxPath = new File(filePath);
                    BufferedOutputStream outPutStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
                    workbook = new XSSFWorkbook();
                    workbook.createSheet("测试");
                    workbook.write(outPutStream);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                return workbook;
            }
            
        }

效果：  
   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1Y2FpZnUxOTg5_size_16_color_FFFFFF_t_70][]

[20200409144714822.png]: /images/20230528/57f0e4d57bca4661aebfbead23245331.png
[20200409144734275.png]: /images/20230528/477e135665a340388ed6b584c16a13b0.png
[20200409144815843.png]: /images/20230528/4ffabae6f4964c08986c37d75a06a4b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1Y2FpZnUxOTg5_size_16_color_FFFFFF_t_70]: /images/20230528/37c6017073e54ef083c17e8db28be923.png