Ambiguous rows while exporting data from Xpages to Excel

Question

When I am exporting the data to excel in Xpages from a view, first few rows of the excel shows just angle brackets and then the real data.. Below is the code snippet.

var exCon = facesContext.getExternalContext();
var writer = facesContext.getResponseWriter();
var response = exCon.getResponse();

response.setContentType("application/vnd.ms-excel");
response.setHeader("Cache-Control", "no-cache");
response.setHeader("Content-Disposition","attachment; filename=export.xls");

writer.write("<html>");
writer.write("<head>");
writer.write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=utf-8\"/>")
writer.write("</head>");
writer.write("<body>");
// set-up the table header
writer.write("<table>");
writer.write("<thead><tr>");

//Loop over view columns
var totalColumns = vwCurrent.getColumnCount();
for(var x=1;x<=totalColumns;x++) {
var column:NotesViewColumn = vwCurrent.getColumn(x);
writer.write("<td><b>" + column.getTitle() + "</b></td>");
}
writer.write("</tr></thead>");

// loop returned docs
var docCurrent:NotesDocument = vwCurrent.getFirstDocument();

//Variables for Multivalued Columns
var multiValueColSize = 0;
var multiValueColIndex = 1;  //Initialized to 1 as first value is just an indicator

while( null != docCurrent){
    var docNext:NotesDocument = vwCurrent.getNextDocument( docCurrent);

    // output data...
    writer.write("<tr>");
    // get columns, use view to format data ;-)
    var vectColumns:java.util.Vector = docCurrent.getColumnValues();
    for( intIndex = 0; intIndex < vectColumns.size(); intIndex++){
        var colValue = vectColumns.get( intIndex);
        /*Calculated columns*/
        switch(@Left(colValue,4)) {
        case "$PO$":
            var data = colValue.split(";");
            colValue = getStatusDate(data[1], data[2]);
            break;

        case "$MO$":
            var data = colValue.split(";");
            // Condition to ensure that, multivalued column with maximum size is set as multiValueColSize 
            if(multiValueColSize < data.length)
                multiValueColSize = data.length;
            //condition to ensure we do not get arrayindexOutofBond exception
            if (multiValueColIndex < data.length){  
                colValue = data[multiValueColIndex].split("##")[0];
            } else {
                colValue = "";
            }
            break; 
        }
        writer.write("<td>" + colValue + "</td>");
    }   
    writer.write("</tr>");

    if (multiValueColSize == 0 || ((multiValueColSize-1) == multiValueColIndex)){
        // re-assign next
        docCurrent.recycle();
        docCurrent = docNext;
        // Re-initialize variables for Multivalued Columns
        multiValueColSize = 0;
        multiValueColIndex = 1;
    } else {
        //increment the index by 1 to populate next values in the list
        multiValueColIndex = multiValueColIndex + 1;
    }
}

// close the table and the document
writer.write("</table>");

writer.write("<body>");
writer.write("</html>");
writer.endDocument()

Where am I doing wrong ? Any clues ?

2 remarks: if you use a thead, you should use a tbody too. Secondly: remove the header that turns it into Excel and have a look what the browser has to say -> or use curl, curl is your friend (then you can leave the header) — stwissel, Sep 22 '16 at 02:37
I tried both but it does not help. However, I am able to figure out what was causing an issue. It was a multivalue field which was coming up in the view properly but while writing to excel producing an error. So I simply used @Implode in the view and the error was gone. Thanks anyways for your time. — Charanjeet Singh, Sep 26 '16 at 14:26

score 1 · Answer 1 · answered Sep 21 '16 at 19:42

1

I would use the poi4xpages project on openntf istead of trying to print out html excel file. Some versions of Excel will give errors if this is used.

https://poi4xpages.openntf.org/

answered Sep 21 '16 at 19:42

Fredrik Norling

3,461
17
21

score 0 · Accepted Answer · answered Sep 26 '16 at 14:30

It is the multi value field which was causing an issue. When seen in a view it was coming out to be comma separated properly but when the data was written to excel, the same was causing an issue. Hence I used @Implode in the view and the error got solved.

Ambiguous rows while exporting data from Xpages to Excel

2 Answers2