title: How to edit excel file with large data using SXSSF change cell color
body: <p>I have read all previous ask question but there is solution I found.</p>
  
  <p><a href="https://stackoverflow.com/questions/58500415/how-to-edit-existing-large-excel-file-with-sxssf-streaming-api">How to Edit existing large excel file with SXSSF Streaming api</a></p>
  
  <p><a href="https://stackoverflow.com/questions/32029374/how-to-write-to-an-existing-file-using-sxssf">How to write to an existing file using SXSSF?</a></p>
  
  <p>I have a existing file which I required to update the content change cell color for some content.</p>
  
  <p>I need to modify excel file with large data over 40,000 rows.</p>
  
  <p>Steps done in below code.</p>
  
  <ul>
  <li>Create new file for Result, Copy file 2 for result to highlight not
  equal cell </li>
  <li>Create Workbook for 2 compare excel files Temp</li>
  <li>XSSFWorkbook XSSF cellStyleRed as  SXSSFWorkbook cannot have cellstyle  color </li>
  <li>keep 100 rows in memory, exceeding rows will be flushed to disk </li>
  <li>compareTwoRows from both excel file not equal will change cellstyle color to red on Result file. </li>
  </ul>
  
  <p>Problem is with below code Result file is blank there is no content. I understand my code
     is incorrect as SXSSFWorkbook is creating new sheet. </p>
  
  <p>HOW CAN I update result file by change cell color?</p>
  
  <pre><code>    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 &lt;= lastRow1; i++) {
  
                      Reporter.log("\n\nComparing Row " + i);
                      Add_Log.info("\n\nComparing Row " + i);
                      XSSFRow row1 = sheet1.getRow(i);
                      XSSFRow row2 = sheet2.getRow(i);
                      //row3edit = sheet.getRow(i);
                      for(int rownum = 0; rownum &lt; 100; rownum++){
                              row3edit= sheet.createRow(rownum);
                      }
                      if (!compareTwoRows(row1, row2, row3edit)) {
                              equalSheets = false;
                              // Write if not equal
      // Get error here java.lang.NullPointerException for row3edit.setRowStyle(cellStyleRed);
                              //if disable test is completed Successfully without writing result file
                              row3edit.setRowStyle(cellStyleRed);
                              Reporter.log("Row " + i + " - Not Equal");
                              Add_Log.info("Row " + i + " - Not Equal");
                              // break;
                      } else {
                              Reporter.log("Row " + i + " - Equal");
                              Add_Log.info("Row " + i + " - Equal");
                      }
              }
  
              // Write if not equal
              opstr = new FileOutputStream(""\\\\sd\\comparisonfile\\ResultFile.xlsx");
              sxssfWorkbook.write(opstr);
  
              opstr.close();
  
              return equalSheets;
      }
  
      // Compare Two Rows
      public boolean compareTwoRows(XSSFRow row1, XSSFRow row2, SXSSFRow row3edit) throws IOException {
              if ((row1 == null) &amp;&amp; (row2 == null)) {
                      return true;
              } else if ((row1 == null) || (row2 == null)) {
                      return false;
              }
  
              int firstCell1 = row1.getFirstCellNum();
              int lastCell1 = row1.getLastCellNum();
              boolean equalRows = true;
  
              // Compare all cells in a row
  
              for (int i = firstCell1; i &lt;= lastCell1; i++) {
                      XSSFCell cell1 = row1.getCell(i);
                      XSSFCell cell2 = row2.getCell(i);
                       for(int cellnum = 0; cellnum &lt; 10; cellnum++){
                               Cell = row3edit.createCell(cellnum);
                     String address = new CellReference(Cell).formatAsString();
                     Cell.setCellValue(address);
                 }
                      if (!compareTwoCells(cell1, cell2)) {
                              equalRows = false;
                              Reporter.log("       Cell " + i + " - NOt Equal " + cell1 + "  ===  " + cell2);
                              Add_Log.info("       Cell " + i + " - NOt Equal " + cell1 + "  ===  " + cell2);
                              break;
                      } else {
                              Reporter.log("       Cell " + i + " - Equal " + cell1 + "  ===  " + cell2);
                              Add_Log.info("       Cell " + i + " - Equal " + cell1 + "  ===  " + cell2);
                      }
              }
              return equalRows;
      }
  
      // Compare Two Cells
      @SuppressWarnings("deprecation")
      public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
              if ((cell1 == null) &amp;&amp; (cell2 == null)) {
                      return true;
              } else if ((cell1 == null) || (cell2 == null)) {
                      return false;
              }
  
              boolean equalCells = false;
              int type1 = cell1.getCellType();
              int type2 = cell2.getCellType();
              if (type2 == type1) {
                      if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
                              // Compare cells based on its type
                              switch (cell1.getCellType()) {
                              case HSSFCell.CELL_TYPE_FORMULA:
                                      if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
  
                              case HSSFCell.CELL_TYPE_NUMERIC:
                                      if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
                              case HSSFCell.CELL_TYPE_STRING:
                                      if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
                              case HSSFCell.CELL_TYPE_BLANK:
                                      if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                                              equalCells = true;
  
                                      } else {
                                      }
                                      break;
                              case HSSFCell.CELL_TYPE_BOOLEAN:
                                      if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
                              case HSSFCell.CELL_TYPE_ERROR:
                                      if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
                              default:
                                      if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                                              equalCells = true;
                                      } else {
                                      }
                                      break;
                              }
                      } else {
                              return false;
                      }
              } else {
                      return false;
              }
              return equalCells;
      }
  </code></pre>
  
  <p>}</p>