如何使用SXSSF更改单元格颜色来编辑大数据的excel文件

问题描述

我已经阅读了所有先前的提问，但是找到了解决方案。

How to Edit existing large excel file with SXSSF Streaming api

How to write to an existing file using SXSSF?

我有一个现有文件，需要更新某些内容的内容更改单元格颜色。

我需要修改具有40,000行以上大数据的excel文件。

在下面的代码中完成的步骤。

• 为结果创建新文件，将文件2复制为结果以突出显示等单元格
• 为2个比较excel文件的Temp创建工作簿
• XSSFWorkbook XSSF cellStyleRed，因为SXSSFWorkbook不能具有单元格颜色
• 在内存中保留100行，超过的行将被刷新到磁盘上
两个Excel文件中的
• compareTwoRows不相等，结果文件上的单元格样式颜色将变为红色。

问题出在下面的代码中结果文件为空，没有内容。我了解我的代码不正确，因为SXSSFWorkbook正在创建新工作表。

我如何通过更改单元格颜色来更新结果文件？

    package pageobjects;

    import java.awt.Color;
    import java.io.BufferedWriter;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.nio.file.FileSystem;
    import java.nio.file.FileSystems;
    import java.nio.file.Files;
    import java.nio.file.Path;
    import java.nio.file.StandardCopyOption;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.sl.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.DataFormatter;
    import org.apache.poi.ss.usermodel.FillPatternType;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.util.CellReference;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.testng.Reporter;

    import property.IHomePage;
    import utility.SeleniumUtils;

    public class Excelcom2try extends SeleniumUtils implements IHomePage {
            public static FileOutputStream opstr = null;
            XSSFCellStyle cellStyleRed = null;
            SXSSFWorkbook sxssfWorkbook = null;
            SXSSFSheet sheet = null;
            SXSSFRow row3edit = null;
            SXSSFCell Cell = null;
            @SuppressWarnings("resource")
    public void compare() {
            try {
                    // Create new file for Result
                    XSSFWorkbook workbook = new XSSFWorkbook();
                    FileOutputStream fos = new FileOutputStream(new File("\\\\sd\\comparisonfile\\ResultFile.xlsx"));
                    workbook.write(fos);
                    workbook.close();
                    Thread.sleep(2000);
                    // get input for 2 compare excel files
                    FileInputStream excellFile1 = new FileInputStream(new File("new File("\\\\sd\\comparisonfile\\UAT_Relationship.xlsx"));
                    FileInputStream excellFile2 = new FileInputStream(new File(""\\\\sd\\comparisonfile\\Prod_Relationship.xlsx"));
                    // Copy file 2 for result to highlight not equal cell
                    FileSystem system = FileSystems.getDefault();
                    Path original = system.getPath(""\\\\sd\\comparisonfile\\Prod_Relationship.xlsx");
                    Path target = system.getPath(""\\\\sd\\comparisonfile\\ResultFile.xlsx");

                    try {
                            // Throws an exception if the original file is not found.
                            Files.copy(original, target, StandardCopyOption.REPLACE_EXISTING);
                            Reporter.log("Successfully Copy File 2 for result to highlight not equal cell");
                            Add_Log.info("Successfully Copy File 2 for result to highlight not equal cell");
                    } catch (IOException ex) {
                            Reporter.log("Unable to Copy File 2 ");
                            Add_Log.info("Unable to Copy File 2 ");
                    }
                    Thread.sleep(2000);
                    FileInputStream excelledit3 = new FileInputStream(new File("\\\\sd\\comparisonfile\\ResultFile.xlsx"));
                    // Create Workbook for 2 compare excel files
                    XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
                    XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);
                    // Temp workbook
                    XSSFWorkbook workbook3new = new XSSFWorkbook();
                    //XSSF cellStyleRed as  SXSSFWorkbook cannot have cellstyle  color
                    cellStyleRed = workbook3new.createCellStyle();
                    cellStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
                    cellStyleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                    // Get first/desired sheet from the workbook to compare both excel sheets
                    XSSFSheet sheet1 = workbook1.getSheetAt(0);
                    XSSFSheet sheet2 = workbook2.getSheetAt(0);
                    //XSSFWorkbook workbook3new temp convert to SXSSFWorkbook
                    // keep 100 rows in memory, exceeding rows will be flushed to disk
                    sxssfWorkbook = new SXSSFWorkbook(100);
                    sxssfWorkbook.setCompressTempFiles(true);
                    sheet = sxssfWorkbook.createSheet();
                    // Compare sheets
                    if (compareTwoSheets(sheet1, sheet2, sheet)) {

                            Reporter.log("\\n\\nThe two excel sheets are Equal");
                            Add_Log.info("\\n\\nThe two excel sheets are Equal");
                    } else {
                            Reporter.log("\\n\\nThe two excel sheets are Not Equal");
                            Add_Log.info("\\n\\nThe two excel sheets are Not Equal");

                    }

                    // close files
                    excellFile1.close();
                    excellFile2.close();
            // excelledit3.close();

                    opstr.close();
                     // dispose of temporary files backing this workbook on disk

            }catch (Exception e) {
                    e.printStackTrace();
            }
            Reporter.log("Successfully Close All files");
            Add_Log.info("Successfully Close All files");
    }

    // Compare Two Sheets
    public boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2, SXSSFSheet sheet) throws IOException {
            int firstRow1 = sheet1.getFirstRowNum();
            int lastRow1 = sheet1.getLastRowNum();
            boolean equalSheets = true;
            for (int i = firstRow1; i 

}

解决方法：

一般而言，40,000行是非常小的数据量，我不会建议在这种情况下建议使用流式传输。相反，您可以轻松地在XSSF工作簿中容纳40,000行提供足够的内存。使用XSSF工作簿，您可以修改直接尝试即可满足内容。

但是，如果您要处理1 Mill的真正大数据。行很多列，那么以下方法将有所帮助：

1）组合Excel Streaming Reader2）同时读取文件F1和F2并逐行比较逐个单元3）根据发现的差异，用新单元格创建一个新行，并将其写入结果文件F3

您无法修改SXSSFWorkbook中的现有单元。