Adding sheets from existing Excel File

Hi to all,


i’ve an excel file with some sheet and in first time, i’ve loaded it:

FileInputStream fis = new FileInputStream(filename);
Workbook sourceWorkbook = new Workbook(fis);

Then, i create the destination Workbook and get their worksheets:

Workbook destWokbook = new Workbook();
WorksheetCollection sourceCollection = sourceWorkbook.getWorksheets();

Now, for example, in my sourceWorkbook i have one sheet called “First Sheet” and i want to copy it in my destWorkbook.

How can i do it?

Looking in examples i’ve find the solution:


Workbook sourceWorkbook = new Workbook(fis);
Workbook destWokbook = new Workbook();
WorksheetCollection destCollection = destWokbook.getWorksheets();
destCollection.get(0).copy(sourceWorkbook.getWorksheets().get(2));

Mmmmm i find a little issue. Whith the code above, i lost the sheet name. I’ve tried this but dont work:


Worksheet sourceSheet = sourceWorkbook.getWorksheets().get(2); // Get source sheet
sourceSheet.setName(sourceSheet.getName()); // Copy the name
destCollection.get(0).copy(sourceSheet); // Adding to new file

But, the name remain “Sheet1”

Hi Francesco,

Thank you for contacting support.

Please use the below provided code snippet to merge the worksheets from different workbooks into one workbook while retaining the worksheet names as of source.

Java


//Create an ArrayList of files to be merged
ArrayList list = new ArrayList();
list.add(myDir + “WorkBook1.xlsx”);
list.add(myDir + “WorkBook2.xlsx”);
list.add(myDir + “WorkBook3.xlsx”);
//Create an instance of Workbook for merged worksheets
Workbook outputWorkbook = new Workbook();
//Clear the worksheet collection, as by default there is one empty worksheet in every new workbook
outputWorkbook.getWorksheets().clear();
//Loop over the input files
for(int fileIndex = 0; fileIndex < list.size(); fileIndex++)
{
System.out.println("FileIndex " + fileIndex);
//Load the indexed input workbook
Workbook inputWorkook = new Workbook(list.get(fileIndex));
//Loop over the worksheets in the loaded workbook
for(int worksheetIndex = 0; worksheetIndex<inputWorkook.getWorksheets().getCount(); worksheetIndex++)
{
System.out.println("worksheetIndex " + fileIndex);
//Get the indexed worksheet name
String worksheetName = inputWorkook.getWorksheets().get(worksheetIndex).getName();
System.out.println("worksheetName " + worksheetName);
//Add a new worksheet in the collection and get the index
int index = outputWorkbook.getWorksheets().add();
//Copy the worksheet into output workbook
outputWorkbook.getWorksheets().get(index).copy(inputWorkook.getWorksheets().get(worksheetIndex));
//Rename the worksheet in output workbook as it was in original spreadsheet
outputWorkbook.getWorksheets().get(index).setName(worksheetName);
}
}

//Save output
outputWorkbook.save(myDir+“output.xlsx”);

Please feel free to write back in case you need our further assistance.