How to edit excel file with large data using SXSSF change cell color

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to edit excel file with large data using SXSSF change cell color

amishad02
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to edit excel file with large data using SXSSF change cell color

Andreas Reichel
Greetings,

in general 40'000 rows is a very small volume of data and I would not
like to suggest employing Streaming in that case.
Instead, you can hold 40'000 rows in a XSSF workbook easily if you just
provide enough memory. Using a XSSF workbook, you can modify the
content directly as you have tried.

However, if you work with really large data of 1 Mill. rows and many
columns, then the following approach will help:

1) engange the Excel Streaming Reader
2) read both files F1 and F2 simultaneously and compare row by row and
cell by cell
3) based on the found difference, create a new row with new cells and
write that to your result file F3

You can not modify existing cells in a SXSSFWorkbook.

Best regards
Andreas

On Thu, 2020-02-20 at 14:01 -0700, amishad02 wrote:

> 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?