前言
项目中遇到需求,需要将多个excel的sheet合并到一个excel里面。网上看了一下文章,但是很多都是断章取义,不是代码不全,就是jar包版本不同一,为此自己解决这个问题后,把解决方案记录下来,供后来的童鞋参考:
第一步:导入poi相关jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
第二步:复制工具类
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
/**
* @description: 多个Excel合并Sheet
* @author: wyj
* @time: 2020/9/18 15:28
*/
public class ExcelUtil {
public static void main(String[] args) {
List<String> list = Arrays.asList(
new File("D:\\test\\a.xlsx").toString(),
new File("D:\\test\\b.xlsx").toString(),
new File("D:\\test\\c.xlsx").toString()
);
mergexcel(list,"杨洪-家庭贷-20190908(报告).xlsx","D:\\test");
System.out.println("OJBK");
}
/**
* * 合并多个ExcelSheet
*
* @param files 文件字符串(file.toString)集合,按顺序进行合并,合并的Excel中Sheet名称不可重复
* @param excelName 合并后Excel名称(包含后缀.xslx)
* @param dirPath 存储目录
* @return
* @Date: 2020/9/18 15:31
*/
public static void mergexcel(List<String> files, String excelName, String dirPath) {
XSSFWorkbook newExcelCreat = new XSSFWorkbook();
// 遍历每个源excel文件,TmpList为源文件的名称集合
for (String fromExcelName : files) {
try (InputStream in = new FileInputStream(fromExcelName)) {
XSSFWorkbook fromExcel = new XSSFWorkbook(in);
int length = fromExcel.getNumberOfSheets();
if (length <= 1) { //长度为1时
XSSFSheet oldSheet = fromExcel.getSheetAt(0);
XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
copySheet(newExcelCreat, oldSheet, newSheet);
} else {
for (int i = 0; i < length; i++) {// 遍历每个sheet
XSSFSheet oldSheet = fromExcel.getSheetAt(i);
XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
copySheet(newExcelCreat, oldSheet, newSheet);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
// 定义新生成的xlxs表格文件
String allFileName = dirPath + File.separator + excelName;
try (FileOutputStream fileOut = new FileOutputStream(allFileName)) {
newExcelCreat.write(fileOut);
fileOut.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
newExcelCreat.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 合并单元格
*
* @param fromSheet
* @param toSheet
*/
private static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet 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
*/
private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
XSSFCellStyle newstyle = wb.createCellStyle();
// 复制单元格样式
newstyle.cloneStyleFrom(fromCell.getCellStyle());
// 样式
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellType();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(fromCell)) {
toCell.setCellValue(fromCell.getDateCellValue());
} else {
toCell.setCellValue(fromCell.getNumericCellValue());
}
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
// nothing21
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
} else {
// nothing29
}
}
/**
* 行复制功能
*
* @param wb
* @param oldRow
* @param toRow
*/
private static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
XSSFCell tmpCell = (XSSFCell) cellIt.next();
XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb, tmpCell, newCell);
}
}
/**
* Sheet复制
*
* @param wb
* @param fromSheet
* @param toSheet
*/
private static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
mergeSheetAllRegion(fromSheet, toSheet);
// 设置列宽
int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
for (int i = 0; i <= length; i++) {
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
XSSFRow oldRow = (XSSFRow) rowIt.next();
XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb, oldRow, newRow);
}
}
}
附加 :提供一个创建空白excel的方法
/**
* *创建空的excel文件,可自定义sheet名称
*
* @param filePath 文件路径
* @param sheetList sheet名称集合(名称不可重复)
* @return
* @Date: 2020/9/21 17:36
*/
public static void createBlankExcel(String filePath, List<String> sheetList) {
try (FileOutputStream out = new FileOutputStream(new File(filePath))) {
XSSFWorkbook workbook = new XSSFWorkbook();
if (sheetList != null && sheetList.size() > 0) {
for (String sheet : sheetList) {
workbook.createSheet(sheet);
}
} else {
// 默认3个sheet
workbook.createSheet("sheet1");
workbook.createSheet("sheet2");
workbook.createSheet("sheet3");
}
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setColor(Font.COLOR_RED);
cellStyle.setFont(font);
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}