功能：
汇总多个excel中，名称相同的sheet页的数据到一个sheet页中，并且原有单元格格式（颜色、边框...）不变。
适用场景：
汇总多个组员提交的模板一致（列字段一致）的表格。（其中最好不要有单元格合并，这个功能还没搞）。
多ecxel同名sheet.png
POI相关所有依赖：可能有多余的，不过我懒得按需去取了，全部拿来吧。
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>stax</groupId>
<artifactId>stax-api</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
全部代码如下：
public class ExcelUtils {
private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
// sheet页的标题行有几行 (从标题行的下一行开始复制数据)
private static int HEAD_ROW_NUM = 1;
// 各个sheet页的数据合并到一个sheet页后，各表数据间隔行数，默认为0
private static int DIF_SHEET_BLANK = 0;
/**
* 不同excel文件中相同sheet页，汇总到一个excel中
*/
public static void gatherSheet(File[] files, int headRowNum, int difSheetBlank) {
DIF_SHEET_BLANK = difSheetBlank;
HEAD_ROW_NUM = headRowNum;
ArrayList<File> srcFiles = new ArrayList<>();
Collections.addAll(srcFiles, files);
// 过滤掉非xls/xlsx文件
for (File srcFile : srcFiles) {
if (!filtMergList(srcFile)) {
log.warn("[" + srcFile.getName() + "]文件格式不为xls/xlsx，将跳过该文件的汇总...");
srcFiles.iterator().remove();
}
}
int srcSize = srcFiles.size();
log.info("excel汇总开始，有[" + srcSize + "]个excel文件等待汇总...");
for (int i = 1; i < srcSize; i++) {
log.info("正在将[" + srcFiles.get(i).getName() + "]中的数据汇总到[" + srcFiles.get(0).getName() + "]...");
gatherSameSheet(srcFiles.get(i), srcFiles.get(0));
}
log.info("excel汇总结束，有[" + srcSize + "]个excel文件汇总完成...");
log.info("汇总文件为：[" + srcFiles.get(0).getName() + "],请查看...");
}
/**
* 汇总两个excel文件的同名sheet
*
* @param srcFile 被汇总文件
* @param tarFile 汇总后文件
*/
public static void gatherSameSheet(File srcFile, File tarFile) {
try {
Workbook srcBook = getWorkbook(srcFile);
Workbook tarBook = getWorkbook(tarFile);
for (int i = 0; i < srcBook.getNumberOfSheets(); i++) {
for (int j = 0; j < tarBook.getNumberOfSheets(); j++) {
if (srcBook.getSheetAt(i).getSheetName().equals(tarBook.getSheetAt(j).getSheetName())) {
log.info("sheet页[" + srcBook.getSheetAt(i).getSheetName() + "]开始合并...");
gatherSheetData(srcBook.getSheetAt(i), tarBook.getSheetAt(j), tarBook);
}
}
}
// 输出流 建议放在最后面，输出流创建后，tarFile所在文件就会变为空，遇到异常就GG
FileOutputStream targetOut = new FileOutputStream(tarFile);
tarBook.write(targetOut);
} catch (IOException e) {
e.printStackTrace();
log.error("汇总两个excel文件的同名sheet时发生IO异常，请联系技术人员", e.getMessage());
}
}
/**
* 针对不同格式（xls/xlsx）格式创建不同的Workbook
* @param srcFile
* @return
* @throws IOException
*/
private static Workbook getWorkbook(File srcFile) throws IOException {
Workbook srcBook;
if (".xls".equals(getSuffix(srcFile.getName()))) srcBook = new HSSFWorkbook(new FileInputStream(srcFile));
else srcBook = new XSSFWorkbook(new FileInputStream(srcFile));
return srcBook;
}
/**
* 汇总sheet页行数据
*
* @param srcSheet
* @param tarSheet
* @param tarBook
*/
public static void gatherSheetData(Sheet srcSheet, Sheet tarSheet, Workbook tarBook) {
// 获取当前sheet页的最后一行的索引
int tarSheetRowsNum = getSheetRowsNum(tarSheet);
int srcSheetRowsNum = getSheetRowsNum(srcSheet);
// 第一行为标题字段，不复制。从第二行数据开始复制
for (int i = HEAD_ROW_NUM; i <= srcSheetRowsNum; i++) {
// 从目标sheet页的空数据行开始复制
Row tarRow = tarSheet.createRow(tarSheetRowsNum + 1 + DIF_SHEET_BLANK);
Row srcRow = srcSheet.getRow(i);
copyRow(srcRow, tarRow, tarBook);
}
}
/**
* 获取tarSheet页最后一行的索引
* @param tarSheet
* @return
*/
private static int getSheetRowsNum(Sheet tarSheet) {
int tarSheetRowsNum = tarSheet.getLastRowNum();
for (int i = tarSheetRowsNum; i >= 0; i--) {
Row row = tarSheet.getRow(i);
if (row == null) continue;
// 判断是不是最后一行
if (!isRealLastRow(row)) {
tarSheetRowsNum = i;
break;
}
}
return tarSheetRowsNum;
}
/**
* 判断是不是真正的最后一行
* 判断标准：此行的任意单元格是否有数据
* @param row
* @return
*/
private static boolean isRealLastRow(Row row) {
short lastCellNum = row.getLastCellNum();
for (int i = 0; i <= lastCellNum; i++) {
// 如果单纯用sheet.getLastRowNum来获取最后一行的话，会不正确。比如说你单元行之前有数据，你delete后，此函数还是认为delete后的数据行也是存在的
// 此处来判断单元格中是否有值，有值就返回false，表示此行是最后一行有数据的行。
if (row.getCell(i) != null && row.getCell(i).getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
}
/**
* 复制行
* @param oldRow
* @param newRow
* @param wb
*/
private static void copyRow(Row oldRow, Row newRow, Workbook wb) {
if (oldRow.getRowStyle() != null) {
newRow.setRowStyle(oldRow.getRowStyle());
}
newRow.setHeight(oldRow.getHeight());
for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
if (i < 0) {
System.out.println(oldRow.getFirstCellNum());
System.out.println(oldRow.getLastCellNum());
}
Cell oldCell = oldRow.getCell(i);
if (null != oldCell) {
copyCell(oldCell, newRow.createCell(i), wb);
}
}
}
/**
* 复制单元格
* @param oldRow
* @param newRow
* @param wb
*/
private static void copyCell(Cell oldCell, Cell newCell, Workbook wb) {
//注意样式创建太多报错，容量大概4000
CellStyle newstyle = wb.createCellStyle();
// 复制单元格样式
newstyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newstyle);
if (oldCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
switch (oldCell.getCellTypeEnum()) {
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
default:
break;
}
}
swing控件做个简易UI：
package com.pcf.spdemo.common.commonutils.fileutils;
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;
import java.util.ArrayList;
import java.util.Collections;
public class GatherSheets {
private static ArrayList<File> fileList = new ArrayList();
// sheet页的标题行有几行 (从标题行的下一行开始复制数据) 默认为1
private static int HEAD_ROW_NUM = 1;
// 各个sheet页的数据合并到一个sheet页后，各表数据间隔行数，默认为0
private static int DIF_SHEET_BLANK = 0;
public static void main(String[] args) {
createWindow();
}
private static void createWindow() {
JFrame frame = new JFrame("sheet页数据汇总");
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setBounds(400, 180, 400, 400);
createUI(frame);
frame.setSize(500, 250);
frame.setLocationRelativeTo(null);
frame.setVisible(true);
}
private static void createUI(final JFrame frame){
JPanel panel = new JPanel();
// 流式布局
// LayoutManager layout = new FlowLayout();
// panel.setLayout(layout);
panel.setLayout(null);
JLabel headNum = new JLabel("sheet页标题行数:");
headNum.setBounds(10,5,120,25);
JTextArea headNumArea = new JTextArea();
headNumArea.setBounds(140,5,150,25);
headNumArea.setText("仅数字类型，默认为1");
JLabel blankNum = new JLabel("sheet页数据间隔:");
blankNum.setBounds(10,35,120,25);
JTextArea blankNumArea = new JTextArea();
blankNumArea.setBounds(140,35,150,25);
blankNumArea.setText("仅数字类型，默认为0");
panel.add(headNum);
panel.add(headNumArea);
panel.add(blankNum);
panel.add(blankNumArea);
JButton selFileBtn = new JButton("选择文件");
selFileBtn.setMargin(new Insets(0, 0, 0, 0));
selFileBtn.setBounds(10,65,80,25);
JTextArea gatherArea = new JTextArea();
gatherArea.setBounds(100,65,350,100);
gatherArea.setWrapStyleWord(true);
gatherArea.setLineWrap(true);
panel.add(selFileBtn);
panel.add(gatherArea);
selFileBtn.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
JFileChooser fileChooser = new JFileChooser();
fileChooser.setMultiSelectionEnabled(true);
int option = fileChooser.showOpenDialog(frame);
if(option == JFileChooser.APPROVE_OPTION){
File[] files = fileChooser.getSelectedFiles();
Collections.addAll(fileList, files);
String filePaths = "";
for(File file: files){
filePaths += file.getAbsolutePath() + "\r\n ";
}
gatherArea.setText(filePaths);
}else{
gatherArea.setText("打开命令取消");
}
}
});
JButton gatherBtn = new JButton("开始合并");
gatherBtn.setMargin(new Insets(0, 0, 0, 0));
gatherBtn.setBounds(10,95,80,25);
panel.add(gatherBtn);
gatherBtn.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
int headNum = -1;
int blankNum = -1;
if ("".equals(headNumArea.getText()) || "仅数字类型，默认为1".equals(headNumArea.getText())) {
headNum = HEAD_ROW_NUM;
} else if (!headNumArea.getText().matches("[0-9]*")) {
JOptionPane.showMessageDialog(null, "[sheet页标题行数]栏只能填写数字!");
} else {
headNum = Integer.valueOf(headNumArea.getText());
}
if ("".equals(blankNumArea.getText()) || "仅数字类型，默认为0".equals(blankNumArea.getText())) {
blankNum = DIF_SHEET_BLANK;
} else if (!blankNumArea.getText().matches("[0-9]*")) {
JOptionPane.showMessageDialog(null, "[sheet页数据间隔]栏只能填写数字!");
} else {
blankNum = Integer.valueOf(blankNumArea.getText());
}
ExcelUtils.gatherSheet(fileList, headNum, blankNum);
}
});
headNumArea.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent e) {//当点击文本域时..弹出对话框
if (!headNumArea.getText().matches("[0-9]*")){
headNumArea.setText("");
}
// JOptionPane.showMessageDialog(null, "点击了文本域");
}
});
blankNumArea.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent e) {
if (!blankNumArea.getText().matches("[0-9]*")) {
blankNumArea.setText("");
}
// JOptionPane.showMessageDialog(null, "点击了文本域");
}
});
frame.getContentPane().add(panel, BorderLayout.CENTER);
}
}
sheet数据汇总UI.png
知识点总结：
1、HSSFworkbook,XSSFworkbook,SXSSFworkbook区别总结
⽤JavaPOI导出Excel时，我们需要考虑到Excel版本及数据量的问题。针对不同的Excel版本，要采⽤不同的⼯具类，如果使⽤错了，会出 现错误信息。JavaPOI导出Excel有三种形式，他们分别是1.HSSFWorkbook 2.XSSFWorkbook 3.SXSSFWorkbook。 HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls； XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx； SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx； 第⼀种：HSSFWorkbook poi导出excel最常⽤的⽅式；但是此种⽅式的局限就是导出的⾏数⾄多为65535⾏，超出65536条后系统就会报错。此⽅式因为⾏数不⾜七 万⾏所以⼀般不会发⽣内存不⾜的情况（OOM）。 第⼆种：XSSFWorkbook 这种形式的出现是为了突破HSSFWorkbook的65535⾏局限。其对应的是excel2007(1048576⾏，16384列)扩展名为“.xlsx”，最多可以导出104万⾏，不过这样就伴随着⼀个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。
第三种：SXSSFWorkbook 从POI 3.8版本开始，提供了⼀种基于XSSF的低内存占⽤的SXSSF⽅式。对于⼤型excel⽂件的创建，⼀个关键问题就是，要确保不会内存 溢出。其实，就算⽣成很⼩的excel（⽐如⼏Mb），它⽤掉的内存是远⼤于excel⽂件实际的size的。如果单元格还有各种格式（⽐如，加 粗，背景标红之类的），那它占⽤的内存就更多了。对于⼤型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简 单，⽤硬盘空间换内存（就像hash map⽤空间换时间⼀样）。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存⾥供查看，在此之前的excel rows都会被写⼊到硬 盘⾥（Windows电脑的话，是写⼊到C盘根⽬录下的temp⽂件夹）。被写⼊到硬盘⾥的rows是不可见的/不可访问的。只有还保存在内存⾥的才可以被访问到。 SXSSF与XSSF的对⽐： a. 在⼀个时间点上，只可以访问⼀定数量的数据 b. 不再⽀持Sheet.clone() c. 不再⽀持公式的求值
d. 在使⽤Excel模板下载数据时将不能动态改变表头，因为这种⽅式已经提前把excel写到硬盘的了就不能再改了
当数据量超出65536条后，在使⽤HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该⽤SXSSFworkbook。
（总结摘抄自：https://wenku.baidu.com/view/127c0fde0142a8956bec0975f46527d3240ca628.html）
遇到的问题记录：
1、This Style does not belong to the supplied Workbook Stlyes Source. Are you trying to assign a style from one workbook to the cell of a differnt workbook?
这个是最常见的，因为要兼容xls和xlsx，有时用XSSFWorkbook，有时用HSSFWorkbook，具体情况具体对待吧。
2、巨坑：如果你发现了getNumberOfSheets()、getLastRowNum、getRowStyle、getRow.......等等明明有值，取出来确实空。那么，肯定是XSSFWorkbook这个对象的问题。
XSSFWorkbook只会加载⼀部分数据到内存，其余的数据全部持久化到本次磁盘，所以获取不到。此时用⽤sxssfWorkbook.getXSSFWorkbook()去获取才能获取到。总之用着就是很扯，来回切换。
关于sxssfWorkbook中遇到的错误：https://wenku.baidu.com/view/1458fb68757f5acfa1c7aa00b52acfc789eb9f24.html
3、getLastRowNum获取最后一行索引，获取的可能是错误的。
因为你只要对某个单元格做过操作，这个函数就会认为该行是存在的。就比如说你单元行之前有数据，你delete后，此函数还是认为已经被delete了的行也是存在的。此时可以根据具体需要去加个判断。比如我：要找没有值的最后一行，这一行的所有单元格没有值就认为是空的。代码在前面代码块里写了，直接就可以用。
