JavaPOI组件实现多个Excel⽂件整合成⼀个多Sheet的Excel
⽂件
代码:
package com.weichai;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
// 将所有类型的尽调excel⽂件合并成⼀个excel⽂件
String Path = "D:\\ExcelTest";
File file = new File(Path);
File[] tempList = file.listFiles();
String TmpList [] = new String [2];
System.out.println("该⽬录下对象个数：" + tempList.length);
for (int i = 0; i < tempList.length; i++) {
if (tempList[i].isFile()) {
TmpList[i] = tempList[i].toString();
System.out.println("⽂件:"+TmpList[i]+" 待处理");
}
}
XSSFWorkbook newExcelCreat = new XSSFWorkbook();
for (String fromExcelName : TmpList) {    // 遍历每个源excel⽂件，TmpList为源⽂件的名称集合
InputStream in = new FileInputStream(fromExcelName);
XSSFWorkbook fromExcel = new XSSFWorkbook(in);
int length = NumberOfSheets();
if(length<=1){      //长度为1时
XSSFSheet oldSheet = SheetAt(0);
XSSFSheet newSheet = SheetName());
copySheet(newExcelCreat, oldSheet, newSheet);
}else{
for (int i = 0; i < length; i++) {// 遍历每个sheet
XSSFSheet oldSheet = SheetAt(i);
XSSFSheet newSheet = SheetName());
copySheet(newExcelCreat, oldSheet, newSheet);
}
}
}
String allFileName = Path+ "\\New.xlsx";    //定义新⽣成的xlx表格⽂件
FileOutputStream fileOut = new FileOutputStream(allFileName);
newExcelCreat.write(fileOut);
fileOut.flush();
fileOut.close();
//  // 删除各个源⽂件
//  for (String fromExcelName : TmpList) {// 遍历每个源excel⽂件
/
/  File Existfile = new File(fromExcelName);
//  File Existfile = new File(fromExcelName);
//  if (ists()) {
//    Existfile.delete();
//  }
//  }
System.out.println("运⾏结束!");
}
public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
toStyle.cloneStyleFrom(fromStyle);// 此⼀⾏代码搞定
// 下⾯统统不⽤
/
*
* //对齐⽅式 toStyle.Alignment()); //边框和边框颜⾊
* toStyle.BorderBottom());
* toStyle.BorderLeft());
* toStyle.BorderRight());
* toStyle.BorderTop());
* toStyle.TopBorderColor());
* toStyle.BottomBorderColor());
* toStyle.RightBorderColor());
* toStyle.LeftBorderColor()); //背景和前景
* //toStyle.FillPattern());
* //填充图案，不起作⽤，转为⿊⾊
* toStyle.FillBackgroundColor());
* //不起作⽤
* toStyle.FillForegroundColor());
* toStyle.DataFormat()); //数据格式
* //toStyle.Font()); //不起作⽤
* toStyle.Hidden());
* toStyle.Indention());//⾸⾏缩进
* toStyle.Locked());
* toStyle.Rotation());//旋转
* toStyle.VerticalAlignment());
* //垂直对齐 toStyle.WrapText()); //⽂本换⾏
*/
}
/**
* 合并单元格
* @param fromSheet
* @param toSheet
*/
public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {  int num = NumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
cellR = MergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
/**
* 复制单元格
* @param wb
* @param fromCell
* @param toCell
*/
public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {  XSSFCellStyle newstyle = wb.createCellStyle();
CellStyle(), newstyle);
//  toCell.StringCelllValue());
// 样式
toCell.setCellStyle(newstyle);
if (CellComment() != null) {
toCell.CellComment());
toCell.CellComment());
}
// 不同数据类型处理
int fromCellType = CellType();
toCell.setCellType(fromCellType);
if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
if (XSSFDateUtil.isCellDateFormatted(fromCell)) {
toCell.DateCellValue());
} else {
toCell.NumericCellValue());
}
} else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
toCell.RichStringCellValue());
} else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
toCell.BooleanCellValue());
} else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
toCell.ErrorCellValue());
} else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
toCell.CellFormula());
} else { // nothing29
}
}
/**
* ⾏复制功能
* @param wb
* @param oldRow
* @param toRow
*/
public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
toRow.Height());
for (Iterator cellIt = llIterator(); cellIt.hasNext();) {
XSSFCell tmpCell = (XSSFCell) ();
XSSFCell newCell = ColumnIndex());
copyCell(wb, tmpCell, newCell);
}
}
/
**
* Sheet复制
* @param wb
* @param fromSheet
* @param toSheet
*/
public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {  mergeSheetAllRegion(fromSheet, toSheet);
// 设置列宽
int length = FirstRowNum()).getLastCellNum();
for (int i = 0; i <= length; i++) {
toSheet.setColumnWidth(i, ColumnWidth(i));
}
for (Iterator rowIt = wIterator(); rowIt.hasNext();) {
XSSFRow oldRow = (XSSFRow) ();
XSSFRow newRow = RowNum());
htmlbordercopyRow(wb, oldRow, newRow);
}
}
public class XSSFDateUtil extends DateUtil {
}
}
}
运⾏截图:
将Student1和Student2的数据整合到⼀个Excel表单中
⽣成的New.xlsx⽂件如下图所⽰: