How to convert a 2 rows header into a single row header from a 'xls' file?

Question

I am trying create a header from an excel file which has the table headers on two different rows. I have to make a single header row from those two.

The approach i am following is i am appending a @ to the first header and trying to replace it with the second header value.

My excel file looks like this

 Bank   Positive    Name of    Local Approah
 Value  Customer    Civilian   Remote

 //some data

And here is the code I use

public List<String> processSheet(Sheet sheet) {
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " started");
    List<String> rows = new ArrayList<String>();
    Iterator<Row> rowIterator = sheet.iterator();
    List<String>firstHeader = new ArrayList<String>();
    List<String>secondHeader =  new ArrayList<String>();
    List<String>finalHeader = new ArrayList<String>();

    while (rowIterator.hasNext()) {
        Row currentRow = rowIterator.next();
        StringBuilder row = new StringBuilder();
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {
            if(currentRow.getRowNum()==0 || currentRow.getRowNum()==1 || currentRow.getRowNum()==3 || currentRow.getRowNum()==2) {  
                continue;
            } else {
                Cell currentCell = currentRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String cellValue = excelManager.evalCell(currentCell);

                //inserting the alternate row into the text file to make a continues header
                if(currentCell.getRowIndex()==4 ) {
                    if(cellValue.equals("Local Risk")) {
                        row.append(cellValue);
                        row.append("@");
                        break;
                    } else {
                        row.append(cellValue);
                        row.append("@");
                    }
                }
                if(currentCell.getRowIndex()==5) {
                    if(cellValue.equals("rating")) {
                        row.append(cellValue);
                        row.append("@");
                        break;
                    } else {
                        int pos = row.indexOf("@");
                        if(pos >=0) {
                            row.replace(pos, 1, cellValue);
                        }
                    }
                }
            }
        }
        if (!row.toString().isEmpty()) {
            rows.add(row.toString());
        }
    }
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " completed");
    return rows;
}

Currently, I get this output :

Bank@Positive@Name of@Local Approah@
Value   Customer    Civilian   Remote 

But my aim is to make

Bank Value Positive Customer Name of Civilian Approach Remote 

Can somebody help me solve this?

Answer 1

External link for a fiddle :

Here is a fiddle I made with hard-coded data that uses pretty much the same algorithm than the bellow examples.

---

For the header, you would better make a list with the two rows and append the strings when you are done.

Try this code instead :

public List<String> processSheet(Sheet sheet) {
    List<String> headers = new ArrayList<>();
    Iterator<Row> rowIterator = sheet.iterator();
    int rowCnt = 0;
    while (rowIterator.hasNext()) {
        Row currentRow = rowIterator.next();
        for (int i = 0; i < currentRow.getLastCellNum(); ++i) {
            Cell currentCell = currentRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellValue = excelManager.evalCell(currentCell);

            headers.add(rowCnt + (i * (rowCnt+1)), cellValue);
        }
        ++rowCnt;
    }

    return headers;
}

This algorithm will work for any number of rows and any number of columns, as long as the number of columns per row is the same. here's a fiddle with hard-coded example data that follows the same algorithm. When this is done, you can append the strings 2 by 2 (or 3 by 3 if the headers are 3 rows high). Something like this algorithm should be alright for that :

private static List<String> appendStrings(List<String> original, int group) throws Exception {
    if (original.size() % group == 0) {
        List<String> newList = new ArrayList<>();
        for (int i = 0; i < original.size(); i += group) {
            StringBuilder sb = new StringBuilder();
            for (int j = 0; j < group; ++j) {
                if (sb.length() > 0) {
                    sb.append(" ");
                }
                sb.append(original.get(i + j));
            }
            newList.add(sb.toString());
        }

        return newList;
    } else {
        throw new Exception("MyClass::appendStrings() ---> the group value must be a factor of the size of the original list");
    }
}

If your rows may contain empty cells and you don't want the empty string to be added, but still want to respect the order, you will need to remove the data after the list is generated. Like this :

public List<String> processSheet(Sheet sheet) {
    /*... same code as previous example ...*/

    // the following removes all "" or null values
    while(headers.remove(null) || headers.remove("")){}

    return headers;
}