java 合并到一个新的excel文件后excel工作表样式改变

x6yk4ghg  于 2023-06-28  发布在  Java
关注(0)|答案(1)|浏览(176)

我写了下面的代码,它从excelFilesPath读取excel文件,将具有相同前缀的文件放在一个excel文件中并将Excel文件重命名为仅具有前缀。示例:代码a.b.1.xlsxa.b.2.xlsx两个文件合并为一个单个excel文件,新文件名为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
        }
    }
}

代码做得很完美。我现在面临的问题是只有一个excel工作表具有原始样式所有其他工作表****显示边框一些是错误的(找到所附的屏幕截图)。如何获取所有图纸的原点布局?

h7appiyu

h7appiyu1#

从你的图片来看,源工作表似乎已经将显示网格线设置为false。这存储在XSSFSheet的CTWorksheet的工作表视图设置中。因此,如果复制的工作表也应该有这样的设置,那么CTWorksheet的工作表视图设置也需要复制。为此,请执行以下操作:

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

但是sheetViews也存储了关于所选工作表的设置,因此将sheetTo的selected设置为false,否则结果工作簿中将选择多个工作表

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

但是,您可能会遇到代码lne的其他问题:

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

如果多个workbookFrom工作簿的工作表具有相同的工作表名称,该怎么办?然后这一行将抛出java.lang.IllegalArgumentException,因为workbookTo工作簿已经包含一个具有此名称的工作表。因此,需要考虑来自多个workbookFrom的工作表可能具有相同的名称。如果是,则将(n)(其中n是计数器编号)附加到名称,以使其在结果工作簿中唯一。
完整的示例为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) {
        Workbook workbookTo;
        Workbook workbookFrom;   
        try {
            File f1 = new File(files.get(0));
            workbookTo = WorkbookFactory.create(new FileInputStream(f1)); // workbook that will be the result
            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);
                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
                    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);
                    
                    // close workbookFrom
                    workbookFrom.close();
                    
                    f.delete();
                    
                }
                
            }
            // write the workbookTo as the result.xlsx
            FileOutputStream out = new FileOutputStream(new File(excelFilesPath + "/" + "result" + ".xlsx"));
            workbookTo.write(out);
            out.close();
            workbookTo.close();
            f1.delete();

        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        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);
    }
}

在目录./ExcelFiles中期望文件a.xlsxb.xlsxc.xlsx,并将表单b.xlsxc.xlsx合并到a.xlsx中,并将结果存储为result.xlsx

相关问题