Excel to String xls/xlsx different result

Question

I want to compare Excel files with each other just to see if they are the same or not. I can choose my Excel Files and Read them. I have 2 Excel Sheets with the same Content but one in .xls and on in .xlsx format. I use the following Code to read my files (for xls with HSSFWorkbook and so on)

private String xlsx(File inputFile) {
        String outputString = "";

        // For storing data into String
        StringBuffer data = new StringBuffer();
        try {
            // Get the workbook object for XLSX file
            XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));

            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            Row row;
            Cell cell;

            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();

            while (rowIterator.hasNext()) {
                row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    cell = cellIterator.next();

                    data.append(cell + ";");
                }
                data.append("\n");
            }
            System.out.println(data.toString());
            outputString = data.toString();
            wBook.close();
        } catch (Exception ioe) {
            ioe.printStackTrace();
        }

        return outputString;
    }

In my Excel I have blank cells - when i read them with xls I get DATA;;;;;DATA which is correct but When i Do the same in xlsx I get DATA;DATA

Somehow the Code skips empty cells?! How can I fix this Problem? Thanks in Advance

Answer 1

After some more Google research and trying different things i found a solution to my Problem. The Iterator skips empty Cells because they have no value - they are null - however in a xls File it seems like they are not null - Whatever

My Code:

private String xlsx(File inputFile) {
    String outputString = "";
    System.out.println("start");
    // For storing data into String
    StringBuffer data = new StringBuffer();
    try {
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));

        // Get first sheet from the workbook
        XSSFSheet sheet = wBook.getSheetAt(0);


     // Decide which rows to process
        int rowStart =  0;
        int rowEnd =  sheet.getLastRowNum()+1;

        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
           Row r = sheet.getRow(rowNum);

           int lastColumn = r.getLastCellNum();

           for (int cn = 0; cn < lastColumn; cn++) {
              Cell c = r.getCell(cn);
              if (c == null) {
                 data.append("" + ";");
              } else {
                 data.append(c + ";");
              }
           }
           data.append("\n");
        }

        System.out.println(data.toString());
        outputString = data.toString();
        wBook.close();
    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
    System.out.println("end");
    return outputString;
}