Apache POI: Storing key and sum in a Map

Question

I'm have an excel spreadsheet that looks something like this:

+-----------------+--------+----------------------+
| Title           | Value  |  Average Price List  |
+-----------------+--------+----------------------+
|                 |        |                      |   
| Item 1          |   10   |        9.99          |
| Item 2          |   20   |        9.99          |
| Item 3          |   30   |        8.99          |
| Total Royalty A |        |                      |
|                 |        |                      |
+-----------------+--------+----------------------+
| Item 1          |   10   |        9.90          |
| Item 2          |   20   |        5.69          |
| Item 3          |   30   |        9.99          |
| Total Royalty B |        |                      |
|                 |        |                      |
+-----------------+--------+----------------------+

I want to get the total value of the average column. However I need to the total to be separated using 'Total Royalty A' and 'Total Royalty B'; meaning in the end the result should be something like this:

Total Royalty A = 10.99
Total Royalty B = 11.88

I used a HashMap in which the keys are 'Total Royalty A' from 'Total Royalty B'. I'm having problems with my code in the if statement:

if(totalCell.getCellType() == Cell.CELL_TYPE_STRING)

The problem I believe it is that there is an empty/blank cell under title. I've tried to use different techniques to skip empty cells but the problem keeps on happening on that line. I would appreciate if anyone can give me any suggestions on how to approach this issue.

This is the method that is supposed to process do what explained above:

 public HashMap monthlyAverageUnitsTotal (HSSFSheet sheet, int colAvgNum, int colTitle )
    {


        HashMap hm = new HashMap();


        double sum = 0.0;
        String key = null;


        for(Row row : sheet)
        {

            Cell saleAverageCell = row.getCell(colAvgNum);
            Cell totalCell = row.getCell(colTitle);

            if(totalCell.getCellType() == Cell.CELL_TYPE_STRING)
            {
               String totalCellValue = totalCell.getStringCellValue().toString().trim();

               if(totalCellValue.startsWith("Total Royalty"))
               {
                   key = totalCell.getStringCellValue().toString().trim();

               }
            }

            if(saleAverageCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
            {
                double saleAverageCellValue = saleAverageCell.getNumericCellValue();
                sum += saleAverageCellValue;

                // put the element in the map
                hm.put(key, sum);

                // return the value of sum to 0
                sum = 0.0;


            }


        }


        return hm;
    }

Answer 1

I had a functional requirement that involved to parse Excel spreadsheets with lot of data. To leverage the cell value problem, I made a function that returns a cell value in String format, so I won't have problems with string, numeric, date, boolean or blank cells (it does not handle formula values):

//Cell celdaActual is the actual cell in a row in a sheet in the book, the class
//has Spanish names and comments and is big for a post, so I'm just posting
//this function
public String getCellValue() {
    String strValor = "";
    int intValor;
    double dblValor;
    SimpleDateFormat objSimpleDateFormat;
    if (celdaActual != null) {
        strValor = celdaActual.toString();
        switch (celdaActual.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strValor = strValor.trim();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(celdaActual)) {
                    objSimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
                    strValor = objSimpleDateFormat
                        .format(celdaActual.getDateCellValue());
                } else {
                    dblValor = celdaActual.getNumericCellValue();
                    if (Math.floor(dblValor) == dblValor) {
                        intValor = (int)dblValor;
                        strValor = String.valueOf(intValor);
                    } else {
                        strValor = String.valueOf(dblValor);
                    }
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                strValor = "";
                break;
            case Cell.CELL_TYPE_ERROR:
                strValor = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strValor = String.valueOf(celdaActual.getBooleanCellValue());
                break;
        }
    }
    return strValor;
}

Hope it works for you.