Skip to content

Instantly share code, notes, and snippets.

@wizard04wsu
Created June 28, 2018 16:33
Show Gist options
  • Save wizard04wsu/1f7dae7bafa008d45c4fecb5d327dcad to your computer and use it in GitHub Desktop.
Save wizard04wsu/1f7dae7bafa008d45c4fecb5d327dcad to your computer and use it in GitHub Desktop.
Convert an HTML table element to an Excel spreadsheet
function createExcelDownloadLink(xml, filename){
var a = document.createElement('a');
a.href = 'data:application/vnd.ms-excel, '+encodeURIComponent(xml);
a.download = filename;
return a;
}
//note: this doesn't work with complicated tables (e.g., merged cells)
function tableToExcel(tableElem, title, sheetname){
var xml,
elem, e,
row, r,
headerRows = 0, headerComplete,
rowCount = 0, columnCount = 0;
function addRow(row, styleID){
var cell, c, data, dataType;
xml += '<Row>',
cell = row.children;
for(c=0; c<cell.length; c++){
data = cell[c].textContent;
dataType = /^-?\d+(\.\d+)?$/.test(data) ? 'Number' : 'String';
xml += '<Cell'+(styleID ? ' ss:StyleID="'+styleID+'"' : '')+'><Data ss:Type="'+dataType+'">'+data+'</Data></Cell>';
}
xml += '</Row>';
rowCount++;
if(!columnCount) columnCount = cell.length;
}
xml = '<?xml version="1.0" encoding="UTF-8"?><?mso-application progid="Excel.Sheet"?>'+
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'+
' xmlns:o="urn:schemas-microsoft-com:office:office"'+
' xmlns:x="urn:schemas-microsoft-com:office:excel"'+
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'+
' xmlns:html="http://www.w3.org/TR/REC-html40">';
if(title){ xml +=
'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'+
'<Title>'+title+'</Title>'+
'</DocumentProperties>';
}
xml += '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'+
'<ProtectStructure>False</ProtectStructure>'+
'<ProtectWindows>False</ProtectWindows>'+
'</ExcelWorkbook>'+
'<Styles>'+
'<Style ss:ID="Default" ss:Name="Normal">'+
'<Alignment ss:Vertical="Top" ss:Horizontal="Left" ss:WrapText="0"/>'+
'</Style>'+
'<Style ss:ID="th">'+
'<Alignment ss:Vertical="Bottom"/>'+
'<Font ss:Bold="1"/>'+
'</Style>'+
'</Styles>'+
'<Worksheet ss:Name="'+(sheetname || sheetname === 0 ? sheetname : 'Sheet1')+'">'+
'<Table>';
elem = tableElem.children;
for(e=0; e<elem.length; e++){
if(elem[e].tagName === 'THEAD'){
row = elem[e].children;
for(r=0; r<row.length; r++){
if(!headerComplete) headerRows++;
addRow(row[r], 'th');
}
}
else if(elem[e].tagName === 'TBODY'){
headerComplete = true;
row = elem[e].children;
for(r=0; r<row.length; r++){
addRow(row[r]);
}
}
else if(elem[e].tagName === 'TR'){
if(elem[e].children[0].tagName === 'TH'){
if(!headerComplete) headerRows++;
addRow(elem[e], 'th');
}
else{
headerComplete = true;
addRow(elem[e]);
}
}
}
xml += '</Table>'+
'<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">'+
'<Selected/>';
if(headerRows){ xml +=
'<FreezePanes/>'+
'<FrozenNoSplit/>'+
'<SplitHorizontal>'+headerRows+'</SplitHorizontal>'+
'<TopRowBottomPane>1</TopRowBottomPane>'+
'<ActivePane>2</ActivePane>'+
'<Panes>'+
'<Pane><Number>3</Number></Pane>'+
'<Pane><Number>2</Number></Pane>'+
'</Panes>';
}
xml += '<ProtectObjects>False</ProtectObjects>'+
'<ProtectScenarios>False</ProtectScenarios>'+
'</WorksheetOptions>';
if(headerRows){ xml +=
'<AutoFilter xmlns="urn:schemas-microsoft-com:office:excel" x:Range="R'+headerRows+'C1:R'+rowCount+'C'+columnCount+'"/>';
}
xml += '</Worksheet>'+
'</Workbook>';
return xml;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment