Validating excel files using java……….

[Nanda]

Hi,

I have excel files with 2 sheets.  Need to compare a row of 1 sheet row in second sheet and display value if same.using Java.

Require suggestion/inputs…

 

 

[Ronan Healy]

One common solution that I have heard of is to insert the formulas in the cells through JAVA?

That might work?

[Tassawer]

Normally VBA is used as backed programming language for excel, may be you can google for some VBA code to get an idea how this compression should be performed.

[Archana]

Apache POI may help to read and write Excel files using java…

[Alin]

Hi Nanda,

You have to use apache POI. Below you can see a very simple example to start from (it is not complete; you have to add some more validations / data handlings):

        String excelFilePath = "D://ExcelFile.xls";
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = new HSSFWorkbook(inputStream);

        Sheet firstSheet = workbook.getSheetAt(0);
        Sheet secondSheet = workbook.getSheetAt(1);

        for (int i = firstSheet.getFirstRowNum(); i <= firstSheet.getLastRowNum(); i++) {
            HSSFRow rowFirstSheet = (HSSFRow) firstSheet.getRow(i);
            HSSFRow rowSecondSheet = (HSSFRow) secondSheet.getRow(i);

            for (int j = rowFirstSheet.getFirstCellNum(); j < rowFirstSheet.getLastCellNum(); j++) {
                HSSFCell cellFirstSheet = rowFirstSheet.getCell(j);
                HSSFCell cellSecondSheet = rowSecondSheet.getCell(j);
                if (cellFirstSheet.toString().equals(cellSecondSheet.toString())) {
                    System.out.println(cellFirstSheet.toString());
                    //or use any other display way like logs, files, reports etc.
                }
            }
        }
        workbook.close();
        inputStream.close();

Regards,
Alin

[Author]

Posts