代碼:

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 = 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);

}

}

}

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);

//if (Existfile.exists()) {

//Existfile.delete();

//}

//}

System.out.println("運行結束!");

}

public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {

toStyle.cloneStyleFrom(fromStyle);// 此一行代碼搞定

// 下面統統不用

/*

* //對齊方式 toStyle.setAlignment(fromStyle.getAlignment()); //邊框和邊框顏色

* toStyle.setBorderBottom(fromStyle.getBorderBottom());

* toStyle.setBorderLeft(fromStyle.getBorderLeft());

* toStyle.setBorderRight(fromStyle.getBorderRight());

* toStyle.setBorderTop(fromStyle.getBorderTop());

* toStyle.setTopBorderColor(fromStyle.getTopBorderColor());

* toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());

* toStyle.setRightBorderColor(fromStyle.getRightBorderColor());

* toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); //背景和前景

* //toStyle.setFillPattern(fromStyle.getFillPattern());

* //填充圖案，不起作用，轉為黑色

* toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());

* //不起作用

* toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

* toStyle.setDataFormat(fromStyle.getDataFormat()); //數據格式

* //toStyle.setFont(fromStyle.getFont()); //不起作用

* toStyle.setHidden(fromStyle.getHidden());

* toStyle.setIndention(fromStyle.getIndention());//首行縮進

* toStyle.setLocked(fromStyle.getLocked());

* toStyle.setRotation(fromStyle.getRotation());//旋轉

* toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());

* //垂直對齊 toStyle.setWrapText(fromStyle.getWrapText()); //文本換行

*/

}

/**

* 合并單元格

* @param fromSheet

* @param toSheet

*/

public 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

*/

public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {

XSSFCellStyle newstyle = wb.createCellStyle();

copyCellStyle(fromCell.getCellStyle(), newstyle);

// toCell.setEncoding(fromCell.getStringCelllValue());

// 樣式

toCell.setCellStyle(newstyle);

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(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

*/

public 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);

}

}

public class XSSFDateUtil extends DateUtil {

}

}

運行截圖:

將Student1和Student2的數據整合到一個Excel表單中

生成的New.xlsx文件如下圖所示:

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的java poi 如何合并多个sheet 为一个sheet_Java POI组件实现多个Excel文件整合成一个多Sheet的Excel文件...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。