POI：使用XSSFWorkbook与SXSSFWorkbook在处理Excel合并时容易出现的问题

野性酷女 2021-11-17 21:46 2410阅读 0赞

最近在做的需求中需要将两个Excel合并。  
首先讲下POI中处理Excel的几种方式吧。  
1.`HSSFWorkbook`，用来处理`.xls`后缀的Excel，即适用于Excel2003以前（包括2003）的版本。因为其最大只能处理65535行的数据，所以现在已经很少使用了，所以本文直接忽略该方式。  
2.`XSSFWorkbook`是现在处理Excel比较常见的方式。其适用于`.xlsx`后缀的Excel，即Excel2007后的版本。能够最多处理104万行数据。但是其在读取/处理Excel时会一口气将Excel内容写入到内存，因此在处理的Excel文件较大时可能打爆内存，造成OOM异常。  
3.`SXSSFWorkbook`。相当于是`XSSFWorkbook`的改良版本，在初始化`SXSSFWorkbook`实例时，需要填写一个缓冲行数参数（默认100行），当读入到内存中的数据超过该数值后，会像队列一样将最前面的数据保存到硬盘中，从而避免出现OOM。这么一看该方式简直完美啊，不过因为超过缓存行的数据都写到硬盘中了，所以如果你想要获取这块的内容（比如复制这块内容到另一个Excel中）就会发现取不到了，因为不在内存中，所以无法通过`SXSSFWorkbook`实例获取该部分内容。

首先讲下Spring Boot中使用POI的方式，在pom中引入如下包，具体适用版本自行选择

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

# 1.SXSSFWorkbook #

`SXSSFWorkbook`是无法直接读取Excel的，需要通过`XSSFWorkbook`读取Excel，然后使用`XSSFWorkbook`实例创建`SXSSFWorkbook`，因此如果想要读取一个超大的Excel，后果你懂的=.=。  
可以来试下，尝试读取一个有50万行数据的Excel（大小14.5M）

public void getExcel() throws IOException { 
            //3.xlsx 有50万行数据
            File file2 = new File("D:\\excel\\3.xlsx");
            FileInputStream inputStream2 =  new FileInputStream(file2);
            XSSFWorkbook wb = new XSSFWorkbook(inputStream2);
    
            SXSSFWorkbook swb = new SXSSFWorkbook(wb);
        }

很明显OOM了。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70]  
我们可以看下`SXSSFWorkbook`的源码：

public static final int DEFAULT_WINDOW_SIZE = 100;
    
    //0
    /** *可以看出实际调用的是也是SXSSFWorkbook(XSSFWorkbook workbook) **/
    public SXSSFWorkbook(){ 
    	this(null /*workbook*/);
    }
    
    //1
    /** *使用XSSFWorkbook新建SXSSFWorkbook，会使用默认缓存行，即100行 **/
    public SXSSFWorkbook(XSSFWorkbook workbook){ 
    	this(workbook, DEFAULT_WINDOW_SIZE);
    }
    
    //2
    /** *默认不压缩 **/
    public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize){ 
    	this(workbook,rowAccessWindowSize, false);
    }
    
    //3
    /** *抱歉不知道useSharedStringsTable是干啥的... *不过从shared string table - a cache of strings in this workbook注释看这个属性和缓存相关 **/
    public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize, boolean compressTmpFiles){ 
    	this(workbook,rowAccessWindowSize, compressTmpFiles, false);
    }
    
    //4
    /** *如果使用XSSFWorkbook 创建SXSSFWorkbook的话是遍历了XSSFWorkbook 的sheet，然后新建了一遍SXSSFWorkbook的sheet。(有点类似拷贝) **/
    public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize, boolean compressTmpFiles, boolean useSharedStringsTable){ 
    	setRandomAccessWindowSize(rowAccessWindowSize);
    	setCompressTempFiles(compressTmpFiles);
    	if (workbook == null) { 
    		_wb=new XSSFWorkbook();
    		_sharedStringSource = useSharedStringsTable ? _wb.getSharedStringSource() : null;
    	} else { 
    		_wb=workbook;
    		_sharedStringSource = useSharedStringsTable ? _wb.getSharedStringSource() : null;
    		for ( Sheet sheet : _wb ) { 
    			createAndRegisterSXSSFSheet( (XSSFSheet)sheet );
    		}
    	}
    }

从源码中能看出创建`SXSSFWorkbook`的过程类似于拷贝。

# 2.Excel合并 #

那么如果我们想将两个Excel合并成一个Excel，多个sheet该怎么做呢？  
最好的方式是在Excel产生前，把数据源都拿到，然后一次性生成一个Excel。但是很多情况是数据源是不同的，那么就只能通过读取Excel来合并了，但是从上面我们知道，当读取一个很大的Excel时，极大可能会出现OOM异常。所以本文介绍的合并，只限定于一个小Excel文件和一个有数据源的超大Excel的合并。或者是多个小Excel的合并。至于两个超大Excel的合并（没有数据源），可以使用阿里的开源项目`EasyExcel`。

### 直接使用`SXSSFWorkbook`合并Excel时出现的问题。 ###

首先放上`SXSSFUtils.java` ，处理`SXSSFWorkbook`拷贝的工具类。

public class SXSSFUtils { 
    		/** * @param fromSheet * @param toSheet */
    		public static void mergeSheetAllRegion(Sheet fromSheet, Sheet toSheet) { 
    			int num = fromSheet.getNumMergedRegions();
    			CellRangeAddress cellR = null;
    			for (int i = 0; i < num; i++) { 
    				cellR = fromSheet.getMergedRegion(i);
    				toSheet.addMergedRegion(cellR);
    			}
    		}
    	 
    		/** * @param wb * @param fromCell * @param toCell */
    		public static void copyCell(SXSSFWorkbook wb, Cell fromCell, Cell toCell) { 
    			toCell.setCellStyle(fromCell.getCellStyle());
    			if (fromCell.getCellComment() != null) { 
    				toCell.setCellComment(fromCell.getCellComment());
    			}
    
    			int fromCellType = fromCell.getCellType();
    			toCell.setCellType(fromCellType);
    			if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) { 
    				if (XSSFDateUtil.isCellDateFormatted(fromCell)) { 
    					toCell.setCellValue(fromCell.getDateCellValue());
    				} else { 
    					toCell.setCellValue(fromCell.getNumericCellValue());
    				}
    			} else if (fromCellType == XSSFCell.CELL_TYPE_STRING) { 
    				toCell.setCellValue(fromCell.getRichStringCellValue());
    			} else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) { 
    				// nothing21
    			} else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) { 
    				toCell.setCellValue(fromCell.getBooleanCellValue());
    			} else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) { 
    				toCell.setCellErrorValue(fromCell.getErrorCellValue());
    			} else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) { 
    				toCell.setCellFormula(fromCell.getCellFormula());
    			} else {  // nothing29
    			}
    	 
    		}
    	 
    		/** * @param wb * @param oldRow * @param toRow */
    		public static void copyRow(SXSSFWorkbook wb, Row oldRow, Row toRow) { 
    			toRow.setHeight(oldRow.getHeight());
    			for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) { 
    				Cell tmpCell = (Cell) cellIt.next();
    				Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
    				copyCell(wb, tmpCell, newCell);
    			}
    		}
    	 
    		/** * @param wb * @param fromSheet * @param toSheet */
    		public static void copySheet(SXSSFWorkbook wb, Sheet fromSheet, Sheet toSheet) { 
    			mergeSheetAllRegion(fromSheet, toSheet);
    
    			for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) { 
    				Row oldRow = (Row) rowIt.next();
    				Row newRow = toSheet.createRow(oldRow.getRowNum());
    				copyRow(wb, oldRow, newRow);
    			}
    		}
    	 
    		public class XSSFDateUtil extends DateUtil { 
    	 
    		}
    	}

#### （1）我们先试下合并两个自己创建的`SXSSFWorkbook`。 ####

如下，创建两个各有5W行数据的Excel，这里`SXSSFWorkbook`缓存行设置为1000，目的是将`swb2`合并到`swb`中。

@RequestMapping("/mergeBySXSSF")
        public void mergeBySXSSF() throws IOException { 
            System.out.println("start");
            SXSSFWorkbook swb = new SXSSFWorkbook(1000);
            SXSSFSheet sheet = swb.createSheet("1");
            
            SXSSFWorkbook swb2 = new SXSSFWorkbook(1000);
            SXSSFSheet sheet2 = swb2.createSheet("2");
    
            for (int i = 0; i < 50000; i++) { 
                SXSSFRow row1 = sheet.createRow(i);
                SXSSFRow row2 = sheet2.createRow(i);
                for (int j = 0; j < 10; j++) { 
                    row1.createCell(j).setCellValue("hello SXSSF：" + j);
                    row2.createCell(j).setCellValue("hello SXSSF：" + j);
                }
            }
    
            for(int i = 0;i<swb2.getNumberOfSheets();i++){ 
                Sheet oldSheet = swb2.getSheetAt(i);
                Sheet newSheet = swb.createSheet(oldSheet.getSheetName());
                SXSSFUtils.copySheet(swb,oldSheet,newSheet);
            }
    
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream("D:\\excel\\23.xlsx"));
            swb.write(outputStream);
            outputStream.flush();
    
            System.out.println("end");
        }

如上所示，会通过遍历`swb2`的sheet，同时在`swb`创建一个同名sheet，`SXSSFUtils.copySheet`的作用是遍历sheet中的行列，将其从老的sheet中复制一份到新的sheet中，从而达到合并Excel的目的。  
结果如下  
sheet 1是`swb`的，没问题。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 1]  
然后是sheet 2，这个是`swb2`的，也是复制合并到`swb`中的，可以看到很有意思的情况，前49000行都是空的，然后后1000行是正确的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 2]  
为什么会这样呢？原因是缓存行，上面的两个`SXSSFWorkbook`的缓存行都是1000，也就是说当超过1000行时，前面的数据都会保存到硬盘中，而我们在复制时用的是`SXSSFWorkbook`实体，是存在内存中的，所以拷贝是就只能复制到最后的1000行了。  
如果我们再创建一个`SXSSFWorkbook`，然后依次把`sbw`和`sbw2`合并到新的`SXSSFWorkbook`中，name两个sheet应该都会缺失前49000行数据。

#### （2）现在我们试下读取两个已有的Excel，然后再合并会发生什么。 ####

20.xlsx和21.xlsx都是有5W行数据的，20.xlsx只有一个sheet(名字是 1)，21.xlsx只有一个sheet(名字是 2)

@RequestMapping("/mergeBySXSSF")
        public void mergeBySXSSF() throws IOException { 
            System.out.println("start");
            File file = new File("D:\\excel\\20.xlsx");
            FileInputStream inputStream =  new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            SXSSFWorkbook swb = new SXSSFWorkbook(wb);
    
            File file2 = new File("D:\\excel\\21.xlsx");
            FileInputStream inputStream2 =  new FileInputStream(file2);
            XSSFWorkbook wb2 = new XSSFWorkbook(inputStream2);
            SXSSFWorkbook swb2 = new SXSSFWorkbook(wb2);
    
            for(int i = 0;i<swb2.getNumberOfSheets();i++){ 
                Sheet oldSheet = swb2.getSheetAt(i);
                Sheet newSheet = swb.createSheet(oldSheet.getSheetName());
                SXSSFUtils.copySheet(swb,oldSheet,newSheet);
            }
    
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream("D:\\excel\\23.xlsx"));
            swb.write(outputStream);
            outputStream.flush();
    
            System.out.println("end");
        }

合并结果  
sheet 1 正常  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 3]  
然后sheet 2 直接就是空的。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 4]  
我们打个断点  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 5]  
可以看到虽然可以获取到`swb2`的sheet数，也能获取到该sheet，但是获取不到这个sheet的最后一行行数，按理说这个数应该是50000，但是查出来是0，这里原因未知。  
因为查不到行信息，所以拷贝时就全是空了。

#### （3）现在我们试下读取两个已有的Excel，然后后面合并的Excel（swb2）用`XSSFWorkbook`会如何呢。 ####

@RequestMapping("/mergeBySXSSF")
        public void mergeBySXSSF() throws IOException { 
            System.out.println("start");
            File file = new File("D:\\excel\\20.xlsx");
            FileInputStream inputStream =  new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            SXSSFWorkbook swb = new SXSSFWorkbook(wb);
    
            File file2 = new File("D:\\excel\\21.xlsx");
            FileInputStream inputStream2 =  new FileInputStream(file2);
            XSSFWorkbook wb2 = new XSSFWorkbook(inputStream2);
         
            for(int i = 0;i<wb2.getNumberOfSheets();i++){ 
                Sheet oldSheet = wb2.getSheetAt(i);
                Sheet newSheet = swb.createSheet(oldSheet.getSheetName());
                SXSSFUtils.copySheet(swb,oldSheet,newSheet);
            }
    
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream("D:\\excel\\23.xlsx"));
            swb.write(outputStream);
            outputStream.flush();
    
            System.out.println("end");
        }

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 7]  
数据终于全了，那么又回到最初的问题了，如果我想要读取一个超大的Excel来进行合并的话 要怎么办呢？因为`SXSSFWorkbook`需要通过`XSSFWorkbook`读取Excel，而`XSSFWorkbook`会将Excel一口气读入内存，一般大于2M的Excel都会抛OOM异常。  
因为我这里的业务场景是会查询数据源，然后生成Excel（这个Excel很大），然后会从其他服务器上下载一个小的Excel合并，所以我这里可以直接在生成大Excel时，在保存前，往`SXSSFWorkbook`中拷贝那个小的Excel（小的Excel下载下来后用`XSSFWorkbook`读取，不包装为`SXSSFWorkbook`），然后就能满足业务需求了。  
大致方式如下

@RequestMapping("/mergeBySXSSF")
        public void mergeBySXSSF() throws IOException { 
            System.out.println("start");
    
    		//用数据源的数据生成Excel，因为数据量多，所以用SXSSFWorkbook操作，这里用50W数据量做演示
            SXSSFWorkbook swb = new SXSSFWorkbook(1000);
            SXSSFSheet sheet = swb.createSheet("1");
            for (int i = 0; i < 500000; i++) { 
                SXSSFRow row1 = sheet.createRow(i);
                //SXSSFRow row2 = sheet2.createRow(i);
                for (int j = 0; j < 10; j++) { 
                    row1.createCell(j).setCellValue("hello SXSSF：" + j);
                }
            }
            
            //下载需要合并的Excel文件到服务器本地，这里用windows下的做演示
    		File file2 = new File("D:\\excel\\21.xlsx");
            FileInputStream inputStream2 =  new FileInputStream(file2);
            XSSFWorkbook wb2 = new XSSFWorkbook(inputStream2);
    
    		//遍历sheet复制
            for(int i = 0;i<wb2.getNumberOfSheets();i++){ 
                Sheet oldSheet = wb2.getSheetAt(i);
                Sheet newSheet = swb.createSheet(oldSheet.getSheetName());
                SXSSFUtils.copySheet(swb,oldSheet,newSheet);
            }
    
            BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream("D:\\excel\\23.xlsx"));
            swb.write(outputStream);
            outputStream.flush();
    
            System.out.println("end");
        }

结果如下：  
![在这里插入图片描述][20190801131909967.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 8]  
没问题，成功拷贝过来了。但是这里还有一个问题，就是拷贝过来的数据有时会丢失样式，这个我还没找到解决方案。

如果我们想要合并两个已经存在的超大Excel，可以尝试下阿里的EasyExcel。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70]: /images/20211116/6a958e67c03e47b4a9d19c9897a170f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 1]: /images/20211116/403bb059af664ee48bc3ca4473f67615.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 2]: /images/20211116/e91bfb9280854b5ebc3f549be2853437.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 3]: /images/20211116/8e3ee3d97b3344f78864770be9d77ee3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 4]: /images/20211116/188dc8e82e3d4c508284e9d211ad9b5e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 5]: /images/20211116/b69dc8a30da8456399896fda29505552.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 6]: /images/20211116/fee5bb178d1946808b2875e0fff8231a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 7]: /images/20211116/33e35f66f47d46889508181f2275469c.png
[20190801131909967.png]: /images/20211116/4f1b50cbf01e4192989a79dd8a2ab684.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleWluZ2xpbmdmZW5n_size_16_color_FFFFFF_t_70 8]: /images/20211116/7afb3b51a7614d59add74a52454eebbe.png