Export to Excel widget

EduardoC_ · ‎04-16-2020

How can I create a widget to export a layer to Excel. I know that currently there exist the option of using the Attributes Table widget that allows to export to CSV, but this CSV is badly formatted in some cases, and I need the Excel format.

Thanks.

LaurynasGedminas2 · ‎04-17-2020

look into CMV widgets:

cmv-widgets/widgets/Export at master · tmcgee/cmv-widgets · GitHub

RickeyFight · ‎04-17-2020

The only downfall is that CMV is not WAB

EricRuberson1 · ‎05-01-2020

hoo boy. I got this to work but the trick was that you actually save an xml table, but with an excel extension. It's basically a sloppy workaround because this isn't supported without either (its been a while so I'm not 100% accurate here): a) a third party plugin doing it or b) having some kind of server side processing that generates the file and then transfers it back to the client to download. Since I couldnt do either of these things, I went this route.

DISCLAIMER:

This will almost 100% generate an error when opening (or maybe saving, can't recall) but the error can be ignored. Excel knows it's not a proper excel file and tells you that, but it can still be opened and re-saved within excel.

HIGHLY recommend you warn your users about this or they will think you are trying to hack them. In my page we have a line of text right below the button.

I have 4 parts that make this work. This is because I'm actually nesting my export to excel button in a sort of report page that my widget generates. Be prepared to alter to your needs. I'm not really going to be able to help anyone with that.

1) a button in a new page that my widget generates, but you should be able to use this like any regular html button. The button takes no input, it just triggers this bit of excel code, which passes worksheet names, the filename, and the software type (I dont know why that's needed) to the code under number 3 down below.

function runTablesToExcel(){
   tablesToExcel(["'+WorksheetNames.toString().replace(/,/g,'","')+'"], "filename.xls","Excel");  
}‍‍‍

2)

this is where I tweak my worksheet names array. Excel has a limit on the length of the names of worksheets and characters that can be in them, or it wont accept the outputted file (I think its 33 characters or so). Because I am generating a new worksheet for every layer that I'm returning, and those are known quantities, I need to replace the names of all of the layers that were causing Excel to reject the created file. If your layernames arent known, you would probably need to trim the lengths of the worksheet names before passing them onto the script in part 3, along with removing the special characters.

var wsheetName= (rowCollection[l].layer.layerName).replace(/[^a-zA-Z0-9]/g,'_').replace(/_{2,}/g,'_');
wsheetName=wsheetName.replace("String that is too long","Abbreviation1");
wsheetName=wsheetName.replace("Another String that is too long","Abbreviation2");
wsheetName=wsheetName.replace("Helicopter_Landing_Zone","Heli_LZ");
wsheetName=wsheetName.replace("National_Monument_and_Landmark","Ntl_Mnmt_and_Landmark");
WorksheetNames.push(wsheetName);‍‍‍‍‍‍

3)

This is the code that does the exporting. Check out the stack over flow question that I link to in it, this is pretty much where I got this from and modified it.

I have the below javascript saved as a file called "TablesToExcel.js" in my WAB App's root directory. I do this because I need to load it up in a new page that my widget creates. You may not have to do this.

Line 34 uses jquery to read the data in from an already formatted html table on the new page, while the loop starting at line 35 processes the table elements so that they can be understood by excel.

/*
Source: https://stackoverflow.com/questions/26909928/export-multiple-html-tables-to-excel?noredirect=1&lq=1

so now when ever I want a page to have an option to be exported to excel i add a refference to that script and i add the following button to my page:

<button onclick="tablesToExcel(['ServerInformatie', 'Relaties'], 'VirtueleMachineInfo.xls', 'Excel')">Export to Excel</button>
so the method:

tablesToExcel(WorksheetNames, fileName, 'Excel')
Where worksheetNames is an array which needs to contain as much names (or more) as there are tables on the page. You could ofcourse chose to create the worksheet names in a different way. And where fileName is ofcourse the name of the file you'll be downloading.

Not having it all in 1 worksheet is a shame but at least this will do for now.

*/

var tablesToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,'    
    , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
      + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
      + '<Styles>'
      + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
      + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
      + '</Styles>'
      + '{worksheets}</Workbook>'
    , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
    , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    return function (wsnames, wbname, appname) {
        var ctx = "";
        var workbookXML = "";
        var worksheetsXML = "";
        var rowsXML = "";
        var tables = $('table');
        for (var i = 0; i < tables.length; i++) {
            for (var j = 0; j < tables[i].rows.length; j++) {
                rowsXML += '<Row>'
                for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                    var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                    var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                    var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                    dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                    var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                    dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                    ctx = {
                        attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
                           , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String'
                           , data: (dataFormula) ? '' : dataValue.replace('<br>', '')
                           , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                    };
                    rowsXML += format(tmplCellXML, ctx);
                }
                rowsXML += '</Row>'
            }
            worksheetNameDirty = (i+1)+"_"+wsnames[i];
            worksheetNameClean=worksheetNameDirty.replace(/[^a-zA-Z0-9]/g,'_').replace(/_{2,}/g,'_').slice(0,31);
            ctx = { rows: rowsXML, nameWS: worksheetNameClean || 'Sheet' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
        }

        ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML };
        workbookXML = format(tmplWorkbookXML, ctx);

        console.log(workbookXML);

        var link = document.createElement("A");
        link.href = uri + base64(workbookXML);
        link.download = wbname || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
})();‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

4) I am adding this external javascript page to my newly generated page with this, where newWin has previously been defined as the new window:

              //add "export to excel" button's required script
              bScript = document.createElement('script'); 
              bScript.type = 'text/javascript';
              bScript.src = 'TablesToExcel.js'; 
              newWin.document.getElementsByTagName("head")[0].appendChild(bScript);‍‍‍‍‍

CodyBiondi · ‎05-04-2020

I need to have an export to excel button, where I preset the widget and it just extracts all the layers under one feature. I know this can be done through the attribute table, however I was asked into making it just a single button. Only function would be the end user clicks it, and it auto downloads the csv and opens it. Any ideas? Eric Ruberson

EricRuberson1 · ‎05-06-2020

Cody, if it can be a CSV, check out the Select Widget first, and maybe the Geoprocessing Widget

Select widget—Web AppBuilder for ArcGIS (Developer Edition) | ArcGIS for Developers

If it needs to be excel, I refer you to my post above on this thread.

I don't think Javascript will let you attempt to open any software on the user's computer, so opening automatically is out. Auto downloading is handled by this code:

 var link = document.createElement("A");
        link.href = uri + base64(workbookXML);
        link.download = wbname || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);‍‍‍‍‍‍‍

Every variation of "downloading a file via javascript" that I've seen relies on some variation of that. It's essentially a link with the download tag on it which clicks itself, prompting the user to download the file.

CodyBiondi · ‎05-11-2020

Eric,

Thank you. That is insanely helpful. I appreciate your time. Let me provide more detail, my users want an export that takes all the data associated with an entire feature (that does not apply any filters that are set on the app through the filter widget) and exports that to excel in one click. They also want to have it only being certain fields, in certain formats being exported. Would you be able to guide me anymore with that?

EricRuberson1 · ‎05-11-2020

So in that script above,

 var tables = $('table');‍

What this boils down to is that its using jquery (the dollar sign '$' is shorthand for jquery code) to search for any elements with the name 'table' in them. That's just a standard html formatted table. It finds all of the elements with the table tag and puts them into the 'tables' variable.

After that it's using that large complicated looking for loop that comes next to recode the contents of the table so it can export it properly for the xml standard (again: this is an xml table it's exporting which excel can read).

        for (var i = 0; i < tables.length; i++) {
            for (var j = 0; j < tables[i].rows.length; j++) {
                rowsXML += '<Row>'
                for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                    var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                    var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                    var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                    dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                    var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                    dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                    ctx = {
                        attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
                           , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String'
                           , data: (dataFormula) ? '' : dataValue.replace('<br>', '')
                           , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                    };
                    rowsXML += format(tmplCellXML, ctx);
                }
                rowsXML += '</Row>'
            }
            worksheetNameDirty = (i+1)+"_"+wsnames[i];
            worksheetNameClean=worksheetNameDirty.replace(/[^a-zA-Z0-9]/g,'_').replace(/_{2,}/g,'_').slice(0,31);
            ctx = { rows: rowsXML, nameWS: worksheetNameClean || 'Sheet' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
        }‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So you would want to have your widget create an html table (it can be hidden so the user doesnt actually see it) in your page somewhere, and you would want to feed the info that you want returned into that html table. If you want to exclude certain rows or certain columns, you would need to program those in as if loops. I would exclude them from the table you create

You can have multiple tables. The code will iterate through all of them, any one of them that has the html tag for 'table'.

For help on generating a table, I'd refer you to the internet.

HTML Tables - Learn web development | MDN

javascript - Populate table element using string - Stack Overflow

Dynamically creating a HTML table with JavaScript

Styling tables - Learn web development | MDN (Note: Styles will not transfer to the generated excel file. There's no way to programattically make that thing look the way you want it to. But this is good for if you want to show the table on a page for the user to view)

Look at the source code on this page for the definitive example of an html table in existence Planets data (this is linked to via one of those first MDN links)

CodyBiondi · ‎05-11-2020

Eric, thank you so much for your time. This is amazing.