excel sheet style changed after mergin it into one new excel file

Question

I have written the following code which reads excel files from excelFilesPath, put the files with the same prefix in one excel file and rename the Excel file to have the prefix only. Example: The code merges the files a.b.1.xlsx and a.b.2.xlsx to one single excel file with the new name a.b.xlsx

private void mergeExcelFiles(String excelFilesPath) {
    Map<String, List<List<String>>> moduleReportFiles = fileNames.entrySet().stream().collect(groupingBy(
            e -> e.getKey().substring(0, e.getKey().lastIndexOf(".")), mapping(e -> e.getValue(), toList())));

    Workbook workbookTo;
    Workbook workbookFrom;

    for (Entry<String, List<List<String>>> moduleNameReportsList : moduleReportFiles.entrySet()) {
        List<String> files = moduleReportFiles.get(moduleNameReportsList.getKey()).stream().flatMap(List::stream)
                .collect(Collectors.toList());

        files = files.stream().sorted().collect(toList());
        File f;
        if (files.size() < 1) {// consider only modules that have at least 2 reports
            continue;
        } else if (files.size() == 1) { // if a module has only one report then rename it to the module name
            f = new File(files.get(0));
            f.renameTo(new File(excelFilesPath + "/" + moduleNameReportsList.getKey() + ".xlsx"));
        } else {
            try {
                workbookTo = new XSSFWorkbook(new File(files.get(0)));

                // workbookTo.setSheetOrder(files.get(0), 0);
                f = new File(files.get(0));
                f.delete();
                files.remove(0);
                for (String file : files) {
                    workbookFrom = new XSSFWorkbook(new File(file)); // workbook that needs to be merged
                    for (int sheetIndex = 0; sheetIndex < workbookFrom.getNumberOfSheets(); sheetIndex++) {
                        XSSFSheet sheetTo = (XSSFSheet) workbookTo
                                .createSheet(workbookFrom.getSheetAt(sheetIndex).getSheetName());
                        XSSFSheet sheetFrom = (XSSFSheet) workbookFrom.getSheetAt(sheetIndex);
                        XSSFRangeCopier xssfRangeCopier = new XSSFRangeCopier(sheetFrom, sheetTo);
                        int lastRow = sheetFrom.getLastRowNum();
                        int lastCol = 0;
                        for (int i = 0; i < lastRow; i++) {
                            Row row = sheetFrom.getRow(i);
                            if (row != null) {
                                if (row.getLastCellNum() > lastCol) {
                                    lastCol = row.getLastCellNum();
                                }
                                sheetTo.setDefaultRowHeight(sheetFrom.getDefaultRowHeight());
                            }
                        }

                        for (int j = 0; j < lastCol; j++) {
                            sheetTo.setColumnWidth(j, sheetFrom.getColumnWidth(j));
                        }

                        CellRangeAddress cellAddresses = new CellRangeAddress(0, lastRow, 0, lastCol);
                        xssfRangeCopier.copyRange(cellAddresses, cellAddresses, true, true);
                        workbookTo.write(new FileOutputStream(
                                new File(excelFilesPath + "/" + moduleNameReportsList.getKey() + ".xlsx")));
                    }
                    f = new File(file);
                    if(f.delete())// delete older file
                    {
                        log.info("report file deleted successfully");
                    }
                }

            } catch (InvalidFormatException e1) {
                log.error(e1.getMessage());
            } catch (IOException e1) {
                log.error(e1.getMessage());
            } // the workbook that we merge all excel files into it
        }
    }
}

The code does that perfect. The Problem that I am facing now is that only one excel sheet has the original style. all other sheets shows borders some how which is wrong (find attached screenshot). How can I get the origin layout of all sheets?

Answer 1

From your picture the source sheets seems have set display gridlines to false. This is stored in sheet view settings of CTWorksheet for XSSFSheet. So if the copied sheet also shall have set that so, then the sheet view settings of CTWorksheet also needs to be copied. To do so do:

...
sheetTo.getCTWorksheet().setSheetViews(sheetFrom.getCTWorksheet().getSheetViews());
...

But sheetViews also stores the setting about selected sheets, so set selected of sheetTo false, else multiple sheets will be selected in the result workbook

...
sheetTo.setSelected(false); 
...

But additional you might get problems with the code lne:

...
XSSFSheet sheetTo = (XSSFSheet) workbookTo..createSheet(workbookFrom.getSheetAt(sheetIndex).getSheetName());
...

What if multiple workbookFrom workbooks have sheets with the same sheet name? Then this line will throw java.lang.IllegalArgumentException because the workbookTo workbook already contains a sheet with this name. So the need is to consider that sheets from multiple workbookFrom could have the same name. If so, then append (n), where n is a counter number, to the name to make it unique in the result workbook.

Complete example as Minimal, Reproducible Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.*;

class ExcelMergeFiles {
    
    private void mergeExcelFiles(String excelFilesPath, List<String> files) {
        File f1 = new File(files.get(0));
        try (
                Workbook workbookTo = WorkbookFactory.create(new FileInputStream(f1)); // workbook that will be the result
                FileOutputStream out = new FileOutputStream(new File(excelFilesPath + "/" + "result" + ".xlsx"));
            ) {
            files.remove(0); // first file is workbookTo and not needs to be merged into the result
            for (String file : files) {
                File f = new File(file);
                try (
                        Workbook workbookFrom = WorkbookFactory.create(new FileInputStream(f)); // workbook that needs to be merged into the result
                    ) {
                    for (int sheetIndex = 0; sheetIndex < workbookFrom.getNumberOfSheets(); sheetIndex++) {
                        // get sheetFrom
                        XSSFSheet sheetFrom = (XSSFSheet) workbookFrom.getSheetAt(sheetIndex);
                        
                        //consider that sheets from multiple workbookFrom could have the same name, if so, then append (n)
                        int n = 1;
                        String sheetName = sheetFrom.getSheetName();
                        while (workbookTo.getSheet(sheetName) != null) {
                            if (sheetName.matches("^.*? \\(\\d*\\)$")) {
                                sheetName = sheetName.replaceAll(" \\(\\d*\\)", " ("+ ++n +")");
                            } else {
                                sheetName = sheetName + " ("+ ++n +")";
                            }
                        }
                        
                        // create sheetTo
                        XSSFSheet sheetTo = (XSSFSheet) workbookTo.createSheet(sheetName);
                        
                        //get last used column of sheetFrom
                        int lastRow = sheetFrom.getLastRowNum();
                        int lastCol = 0;
                        for (int i = 0; i < lastRow; i++) {
                            Row row = sheetFrom.getRow(i);
                            if (row != null) {
                                if (row.getLastCellNum() > lastCol) {
                                    lastCol = row.getLastCellNum();
                                }
                            }
                        }

                         // copy used range from sheetFrom to sheetTo
                        if (lastRow >= 0 && lastCol >= 0) { // only if there is content in the sheet
                            CellRangeAddress cellAddresses = new CellRangeAddress(0, lastRow, 0, lastCol);
                            XSSFRangeCopier xssfRangeCopier = new XSSFRangeCopier(sheetFrom, sheetTo);
                            xssfRangeCopier.copyRange(cellAddresses, cellAddresses, true, true);
                        }
                        
                        // copy the column widths from sheetFrom to sheetTo
                        for (int j = 0; j < lastCol; j++) {
                            sheetTo.setColumnWidth(j, sheetFrom.getColumnWidth(j));
                        }
                        
                        // copy default row height from sheetFrom to sheetTo
                        sheetTo.setDefaultRowHeight(sheetFrom.getDefaultRowHeight());
                        
                        // copy row heights from sheetFrom to sheetTo
                        for (int i = 0; i < lastRow; i++) {
                            Row rowFrom = sheetFrom.getRow(i);
                            Row rowTo = sheetTo.getRow(i);
                            if (rowFrom != null && rowTo != null) {
                                rowTo.setHeight(rowFrom.getHeight());
                            }
                        }

                        // copy the sheetViews which stores the setting about visibility of grid lines for example
                        sheetTo.getCTWorksheet().setSheetViews(sheetFrom.getCTWorksheet().getSheetViews());
                        // sheetViews also stores the setting about selected sheets, so set selected of sheetTo false, else multiple sheets will be selected in result workbook
                        sheetTo.setSelected(false);
                        
                        // workbookFrom is closeables which closes because of try with resources
                    }   
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
                f.delete();                
            }
            // write the workbookTo as the result.xlsx
            workbookTo.write(out);
            // workbookTo and out are closeables which closes because of try with resources
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        f1.delete();
    }

    public static void main(String[] args) {
        List<String> files = new ArrayList<String>();
        files.add("./ExcelFiles/a.xlsx");
        files.add("./ExcelFiles/b.xlsx");
        files.add("./ExcelFiles/c.xlsx");
        ExcelMergeFiles app = new ExcelMergeFiles();
        app.mergeExcelFiles("./ExcelFiles", files);
    }
}

Expects files a.xlsx, b.xlsx, c.xlsx in directory ./ExcelFiles and merges sheets form b.xlsx and c.xlsx into a.xlsx and stores the result as result.xlsx.