I have read all previous ask question but there is no solution I found. I need to modify excel file with large data over 40,000 rows. Steps done in below code. Create new file for Result Copy file 2 for result to highlight not equal cell Create Workbook for 2 compare excel files Temp XSSFWorkbook XSSF cellStyleRed as SXSSFWorkbook cannot have cellstyle color keep 100 rows in memory, exceeding rows will be flushed to disk compareTwoRows from both excel file not equal will change cellstyle color to red on Result file. 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. HOW CAN I update result file with change cell color? 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 <= 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 < 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) && (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 <= lastCell1; i++) { XSSFCell cell1 = row1.getCell(i); XSSFCell cell2 = row2.getCell(i); for(int cellnum = 0; cellnum < 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) && (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; } } -- Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org