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.
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:
bScript = document.createElement('script');
bScript.type = 'text/javascript';
bScript.src = 'TablesToExcel.js';
newWin.document.getElementsByTagName("head")[0].appendChild(bScript);
