In this tutorial, we're going to write a simple javascript function that export html table to downloadable excel file
Once users clicked our link, the script will generate excel file from the table and prompt for download
We have a simple link which will call our function fnExcelReport
To generate the excel, we need to build up a string in SpreadsheetDataXML format
The first section of our code will define the information of our excel file
The XML structure will be a standard hierarchical level of excel
ExcelWorkbook -> Excelworksheets -> ExcelWorksheet
Since our example will have only 1 sheet, we'll just have each of them declared once
Next tag will be the sheet name - in this example we'll just named it "Test Sheet"
Next is WorksheetOptions which allow us to specify our sheet option such as Freeze Pane, Tab color, Grid line and etc.
But it's not part of this tutorial so we'll leave it for now
Then we'll close all the tags
The next section is our main part to add our table to the excel file.
We can use a simple HTML syntax here to specify the cell color and style as border, text color and etc.
Then I'll just use a html() of jQuery to get the html code of our table
Now that our XML is ready inside "tab_text" variable. We'll move the final part - download link
We need to tell our browser to look at our XML string as an excel file.
This can be done by specify the data type as a header to our XML string
Then set the href attribute of the link with our XML string
Please noted that we need to encodeURI our XML string before we assign to the href.
And finally, set the download attribute as our desired file name - "Test file.xls"
There will be a warning popup when user open the file, but it will be working fine.
Noted that any style defined in css will not work. You'll need to code it in the XML string as inline style
Unfortunately, our current code works with only Firefox and Chrome only.
For Internet Explorer, we need to handle it differently as it not support changing the data type of the href for security purpose
Boo!
Internet Explorer has some security considerations which prevent our data protocal to work correctly
So we'll need a bit of workaround
I've added a short code to check if user is using IE
Then, instead of using changing data protocal, we'll need to use Blob object
The Blob object represents a byte sequence similar to a file and provides methods and properties to create or manipulate the binary data
So, we'll just create a new Blob object with our XML string (noted that Blob contructor accept array so I've to add brackets)
Finally, we'll call a msSaveBlob method and our code is ready
Let's try clicking our link again...
Now it works!!
Thank you for watching! Please subscribe us for more tips and quick tutorials!