英文:
Merge multiple workbooks with multiple worksheets into a master workbook using Apache poi
问题
我需要一个copysheets方法，可以合并包含多个工作簿和多个工作表的工作簿。
每个工作簿中的工作表名称与我想要合并在一起的其他工作簿的工作表名称相同。
我想要将（workbook1，sheet1），（workbook2，sheet1），（workbook3，sheet1）的内容合并到新的主工作簿的sheet1中，然后将（workbook1，sheet2），（workbook2，sheet2），（workbook3，sheet2）等合并到新的主工作簿的sheet2中，依此类推，以创建一个新的主工作簿。
我希望数据按顺序插入，例如从sheet1中的前10行开始，然后从sheet2中的接下来的10行开始，依此类推。
输出应该是复制的第一个工作表的内容一次又一次。我收到以下错误：
Exception in thread "main" java.lang.IllegalArgumentException: The workbook already contains a sheet named 'Sheet1'
at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetName(XSSFWorkbook.java:890)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:843)
at MergeMultipleXlsFilesInDifferentSheet.mergeExcelFiles(MergeMultipleXlsFilesInDifferentSheet.java:121)
at MergeMultipleXlsFilesInDifferentSheet.main(MergeMultipleXlsFilesInDifferentSheet.java:316)
以下是我的代码：
public class MergeMultipleXlsFilesInDifferentSheet {
public static void mergeExcelFiles(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
System.out.println(file.getName());
String directoryName = "C:Documents\\File.xlsx";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
for (File file1 : fList) {
if (file1.isFile()) {
String ParticularFile = file1.getName();
FileInputStream fin = new FileInputStream(new File(directoryName + "\\" + ParticularFile));
XSSFWorkbook b = new XSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
for (int worksheetIndex = 0; worksheetIndex < b.getNumberOfSheets(); worksheetIndex++) {
XSSFSheet sheet = book.createSheet(b.getSheetName(worksheetIndex));
String worksheetName = b.getSheetName(worksheetIndex);
System.out.println("test " + worksheetName);
copySheets(book.createSheet(), b.getSheetAt(i));
System.out.println("Copying..");
}
}
}
}
try {
writeFile(book, file);
} catch (Exception e) {
e.printStackTrace();
}
}
protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
System.out.println(file + " is written successfully..");
}
private static void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
copySheets(newSheet, sheet, true);
}
// private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){
private static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int newRownumber = newSheet.getLastRowNum();
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i + newRownumber);
if (srcRow != null) {
//copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>();
// for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
int j = srcRow.getFirstCellNum();
if (j < 0) {
j = 0;
}
for (; j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
//System.out.println("New merged region: " + newMergedRegion.toString());
if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
mergedRegions.add(newMergedRegion);
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
if (styleMap != null) {
int stHashCode = oldCell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
//newCellStyle = newWorkbook.createCellStyle();
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
switch (oldCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
new
<details>
<summary>英文:</summary>
- I need a copysheets method that will merge multiple workbooks that contain multiple worksheets.
- Each tab/sheet name in the workbook identical to the sheetnames of the the other workbooks I would like to merge together.
- I would like to merge the contents of (workbook1,sheet1), (workbook2, sheet1), (workbook3, sheet1) into sheet1 of the new master workbook, then (workbook1, sheet2) , (workbook2, sheet2), workbook3, sheet2) into sheet2 of the new master workbook. and so on into a new master workbook.
- I would like the data to be inserted one below the other, like first 10 rows from sheet1, then the next from 10 from sheet2 and so on.
The output would copy the only the first sheet contents appended over and over. I'm receiving the following error:
Exception in thread "main" java.lang.IllegalArgumentException: The workbook already contains a sheet named 'Sheet1'
at org.apache.poi.xssf.usermodel.XSSFWorkbook.validateSheetName(XSSFWorkbook.java:890)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:843)
at MergeMultipleXlsFilesInDifferentSheet.mergeExcelFiles(MergeMultipleXlsFilesInDifferentSheet.java:121)
at MergeMultipleXlsFilesInDifferentSheet.main(MergeMultipleXlsFilesInDifferentSheet.java:316)
Here is my code:
public class MergeMultipleXlsFilesInDifferentSheet{
public static void mergeExcelFiles(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
System.out.println(file.getName());
String directoryName = "C:Documents\\File.xlsx";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
for (File file1 : fList){
if (file1.isFile()){
String ParticularFile = file1.getName();
FileInputStream fin = new FileInputStream(new File(directoryName+"\\"+ParticularFile));
XSSFWorkbook b = new XSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
for(int worksheetIndex = 0; worksheetIndex<b.getNumberOfSheets(); worksheetIndex++)
{
XSSFSheet sheet = book.createSheet(b.getSheetName(worksheetIndex));
String worksheetName = b.getSheetName(worksheetIndex);
System.out.println("test " + worksheetName);
copySheets(book.createSheet(), b.getSheetAt(i));
System.out.println("Copying..");
}
}
}
try {
writeFile(book, file);
}catch(Exception e) {
e.printStackTrace();
}
}
}
protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
System.out.println(file+ " is written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copySheets(XSSFSheet newSheet, XSSFSheet sheet){
copySheets( newSheet, sheet, true);
}
// private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){
private static void copySheets( XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){
int newRownumber = newSheet.getLastRowNum();
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i + newRownumber);
if (srcRow != null) {
//copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow( XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>();
// for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
int deltaRows = destRow.getRowNum()-srcRow.getRowNum();
int j = srcRow.getFirstCellNum();
if(j<0){j=0;}
for (; j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell( oldCell, newCell, styleMap);CellRangeAddress mergedRegion = getMergedRegion( srcSheet, srcRow.getRowNum(), (short)oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow()+deltaRows, mergedRegion.getLastRow()+deltaRows, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
//System.out.println("New merged region: " + newMergedRegion.toString());
if (isNewMergedRegion( newMergedRegion, mergedRegions)) {
mergedRegions.add(newMergedRegion);
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
public static void copyCell( XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
if(styleMap != null) {
int stHashCode = oldCell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if(newCellStyle == null){
//newCellStyle = newWorkbook.createCellStyle();
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
switch(oldCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
public static CellRangeAddress getMergedRegion( XSSFSheet sheet, int rowNum, short cellNum) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress merged = (CellRangeAddress) sheet.getMergedRegion(i);
if (merged.isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Collection<CellRangeAddress> mergedRegions) {
return !mergedRegions.contains(newMergedRegion);
}
public static void main(String[] args) {
try {
mergeExcelFiles(new File("C:\\Documents\\test.xlsx"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
</details>
专注分享java语言的经验与见解，让所有开发者获益！
评论