-
-
Save davidsommer/7805574 to your computer and use it in GitHub Desktop.
public static void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException { | |
HSSFWorkbook book = new HSSFWorkbook(); | |
HSSFSheet sheet = book.createSheet(file.getName()); | |
for (FileInputStream fin : list) { | |
HSSFWorkbook b = new HSSFWorkbook(fin); | |
for (int i = 0; i < b.getNumberOfSheets(); i++) { | |
copySheets(book, sheet, b.getSheetAt(i)); | |
} | |
} | |
try { | |
writeFile(book, file); | |
} catch (Exception e) { | |
// TODO Auto-generated catch block | |
e.printStackTrace(); | |
} | |
} | |
protected static void writeFile(HSSFWorkbook book, File file) throws Exception { | |
FileOutputStream out = new FileOutputStream(file); | |
book.write(out); | |
out.close(); | |
} | |
private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet){ | |
copySheets(newWorkbook, newSheet, sheet, true); | |
} | |
private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){ | |
int newRownumber = newSheet.getLastRowNum() + 1; | |
int maxColumnNum = 0; | |
Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; | |
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { | |
HSSFRow srcRow = sheet.getRow(i); | |
HSSFRow destRow = newSheet.createRow(i + newRownumber); | |
if (srcRow != null) { | |
copyRow(newWorkbook, 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(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { | |
destRow.setHeight(srcRow.getHeight()); | |
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { | |
HSSFCell oldCell = srcRow.getCell(j); | |
HSSFCell newCell = destRow.getCell(j); | |
if (oldCell != null) { | |
if (newCell == null) { | |
newCell = destRow.createCell(j); | |
} | |
copyCell(newWorkbook, oldCell, newCell, styleMap); | |
} | |
} | |
} | |
public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { | |
if(styleMap != null) { | |
int stHashCode = oldCell.getCellStyle().hashCode(); | |
HSSFCellStyle newCellStyle = styleMap.get(stHashCode); | |
if(newCellStyle == null){ | |
newCellStyle = newWorkbook.createCellStyle(); | |
newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); | |
styleMap.put(stHashCode, newCellStyle); | |
} | |
newCell.setCellStyle(newCellStyle); | |
} | |
switch(oldCell.getCellType()) { | |
case HSSFCell.CELL_TYPE_STRING: | |
newCell.setCellValue(oldCell.getRichStringCellValue()); | |
break; | |
case HSSFCell.CELL_TYPE_NUMERIC: | |
newCell.setCellValue(oldCell.getNumericCellValue()); | |
break; | |
case HSSFCell.CELL_TYPE_BLANK: | |
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); | |
break; | |
case HSSFCell.CELL_TYPE_BOOLEAN: | |
newCell.setCellValue(oldCell.getBooleanCellValue()); | |
break; | |
case HSSFCell.CELL_TYPE_ERROR: | |
newCell.setCellErrorValue(oldCell.getErrorCellValue()); | |
break; | |
case HSSFCell.CELL_TYPE_FORMULA: | |
newCell.setCellFormula(oldCell.getCellFormula()); | |
break; | |
default: | |
break; | |
} | |
} |
This code is taking so much time to merge.
This code is taking so much time to merge.
can you share your code
This code is taking so much time to merge.
can you share your code
Hi @aashu45
Here is the code:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
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;
import org.springframework.stereotype.Service;
@service("ExcelMergeService")
public class ExcelMergeService{
private static final Logger log = LogManager.getLogger(ExcelMergeService.class);
public void mergeExcelFiles(File file,String path, List mergeReporsList) throws IOException {
log.info("file path:{}",path);
XSSFWorkbook book = new XSSFWorkbook();
String directoryName = path;
File directory = new File(directoryName);
//get all the files from a directory
File[] fList = directory.listFiles();
int count = 0; int startOfRow = 0;
XSSFSheet sheet = null;
Calendar cl = Calendar. getInstance();
cl.add(Calendar.DATE, -7);
cl.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
for (String filename : mergeReporsList){
File file1 = new File(path + file.separator + filename);
if (file1.isFile()){
String ParticularFile = file1.getName();
if(mergeReporsList.contains(ParticularFile)){
log.info("{} file started merging",file1.getName());
FileInputStream fin = new FileInputStream(new File(directoryName+file.separator+ParticularFile));
XSSFWorkbook b = new XSSFWorkbook(fin);
String fileName = ParticularFile.replaceFirst("[.][^.]+$", "");
int index = 0;
if(fileName.contains("-")){
index = fileName.indexOf("-");
fileName = fileName.substring(index + 1).trim();
if(fileName.contains("")){
index = fileName.indexOf("");
fileName = fileName.substring(0, index).trim();
}
}
for (int i = 0; i < b.getNumberOfSheets(); i++) {
if(fileName.contains("Summary"))
{
if(count == 0 )
{
sheet = book.createSheet("Week of "+(cl.get(Calendar.MONTH)+1)+"-"+cl.get(Calendar.DAY_OF_MONTH));
}
if(fileName.contains("Summary_4"))
{
sheet = book.createSheet("Week of "+(cl.get(Calendar.MONTH)+1)+"-"+cl.get(Calendar.DAY_OF_MONTH)+" Details");
startOfRow = 0;
}
}
else{
if(index == 0){
sheet = book.createSheet(count + "_" + fileName);
startOfRow = 0;
}else{
if(i == 0){
sheet = book.createSheet(fileName);
startOfRow = 0;
}
else{
sheet = book.createSheet(fileName + "-" + i);
startOfRow = 0;
}
}
}
count = count + 1;
copySheets(book, sheet, b.getSheetAt(i), startOfRow);
startOfRow = startOfRow + 1;
}
}
}
try {
writeFile(book, file);
//deleteFiles(path, mergeReporsList);
}catch(Exception e) {
log.error("Excelption at Excel Merge: {}",e);
}
}
log.info("copying is done");
deleteFiles(path, mergeReporsList);
}
protected void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
}
private void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, int startOfRow){
copySheets(newWorkbook, newSheet, sheet, true, startOfRow);
}
private void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle, int startOfRow){
int newRownumber = newSheet.getLastRowNum();
if(startOfRow != 0){
log.info("row:nul:{}",startOfRow);
newRownumber = newSheet.getLastRowNum() + 2;
}
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);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); 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(newWorkbook, oldCell, newCell, styleMap);
}
}
}
public void copyCell(XSSFWorkbook newWorkbook, 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.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 void buildMerger(String path, List mergeReporsList, String outputFile) {
try {
mergeExcelFiles(new File(path + File.separator + outputFile), path, mergeReporsList);
} catch (IOException e) {
// TODO Auto-generated catch block
log.error("Excelption at Excel Merge: {}",e);
}
}
public void deleteFiles(String path, List mergeReporsList){
File dir = new File(path);
File[] files = dir.listFiles();
for (int i = 0; i < files.length; i++) {
if(!files[i].isDirectory() && !files[i].getName().endsWith(".zip") && mergeReporsList.contains(files[i].getName())) {
File deleteFile = new File(files[i].toString());
if (deleteFile.delete()) {
log.info("File: {} deleted successfully.",
files[i].toString());
} else {
log.info("File: {} not deleted.", files[i].toString());
}
}
}
}
}
For xssf i used that code:
public static void mergeExcelFiles(File file, List list) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet(file.getName());
for (FileInputStream fin : list) {
XSSFWorkbook b = new XSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
copySheets(book, sheet, b.getSheetAt(i));
}
}
try {
writeFile(book, file);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
}
private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet){
copySheets(newWorkbook, newSheet, sheet, true);
}
private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){
int newRownumber = newSheet.getLastRowNum() + 1;
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);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); 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(newWorkbook, oldCell, newCell, styleMap);
}
}
}
public static void copyCell(XSSFWorkbook newWorkbook, 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.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(HSSFCell.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;
}
}
can you please provide code for combining two excel sheets from left to right instead of top to bottom.
Note: Just two files no need directories of files. Combine two excel and store it in third excel file
can I know how to merge this files on column wise
did you got how to merge column wise . Can you please give reply
Its not like i do this as a free service :-) Try yourself
update for implementation("org.apache.poi:poi-ooxml:5.2.5")
package org.example;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Main {
public static void main(String[] args) throws IOException {
FileInputStream[] filePaths = {new FileInputStream("C:\\temp\\rel\\file1.xlsx"), new FileInputStream("C:\\temp\\rel\\file2.xlsx")};
mergeExcelFiles(new File("C:\\temp\\rel\\all.xlsx"), Arrays.asList(filePaths));
System.out.println("Done!");
}
public static void mergeExcelFiles(File file, List<FileInputStream> list) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
XSSFSheet sheet = book.createSheet(file.getName());
for (FileInputStream fin : list) {
XSSFWorkbook b = new XSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
copySheets(book, sheet, b.getSheetAt(i));
}
}
try {
writeFile(book, file);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected static void writeFile(XSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
}
private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet) {
copySheets(newWorkbook, newSheet, sheet, true);
}
private static void copySheets(XSSFWorkbook newWorkbook, XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int newRownumber = newSheet.getLastRowNum() + 1;
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);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); 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(newWorkbook, oldCell, newCell, styleMap);
}
}
}
public static void copyCell(XSSFWorkbook newWorkbook, 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.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
switch (oldCell.getCellType()) {
case STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case BLANK:
newCell.setCellType(CellType.BLANK);
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
}
when i use the above code, the output excel file is getting corrupted, unable to open the file