0
I have several files . xls with 1 sheet each, I would like to merge all sheets into a single file with several sheets.
1 answer
0
You can create multiple sheets in a single file, and each sheet can contain the information related to each file.
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
Sheet sheet3 = wb.createSheet(safeName);
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
https://poi.apache.org/spreadsheet/quick-guide.html#NewSheet
Browser other questions tagged java
You are not signed in. Login or sign up in order to post.
Rodrigo, have you worked on anything yet? If not, I recommend viewing the POI library: https://poi.apache.org/. The easiest way to do this is to create a new document and copy all the cells on the sheet to it. When copying everything, create a new tab and repeat the procedure with the other files.
– Csorgo
I have a working code, but it is losing all the formatting when merging the other files (Heets). Especially when there are Charts that I have many. It’s complicated
– Rodrigo de Bastos
When you merge, does it generate another xlsx or a csv? I saw a discussion about copying the entire sheet, including formatting: https://stackoverflow.com/questions/33749323/how-to-copy-xlsx-complete-workbook, maybe help you.
– Csorgo