Return to LaurenceHolbrook.com Technical Information Index

JavaScript - read an HTML table and populate Excel or write a CSV file

There are 4 JavaScripts included here - all 4 use DOM (Document Object Model) to read the data from the table on the HTML page - three start Excel and populate Excel with the table values - one of those also executes Columns.AutoFit - one of those also draws a chart - the other uses fso to write a CSV file -

The main difficulty with saving an XLS (binary) file is knowing how to format it - an XLS file contains all the information Excel needs to start and display that workbook, workbook settings (toolbars, menus, etal), data, cell formatting, cell formulas, macros(if there are any), wizard information (charts, pivot tables, etc), yada yada yada - creating a file and putting a MIME type on it is easy enough, but how do you format that native Excel file, such that when Excel opens the file, Excel knows what to do with all the information that's there -

One solution would be to create a CSV file - there is no display or 'control' information in that file, just data - and lots of applications, including Excel, 'know' how to open CSV files -

Use fso (MS File System Object) to open a file, write each data element into the file, write a comma into the file and at the end of a row, put in a CRLF - and so on - if any data element contains an embedded comma, then that element must be prefixed and suffixed with quotes - if a data element contains an embedded quotes, then an additional quotes mark needs to be inserted -

Another approach would be to start Excel, populate it and use Excel's file saveas to create a CSV file -

Done on the client side, no MIME type would be involved - a CSV file is a plain ol' ASCII text file, formatted as mentioned - On most windows computers with Excel installed, double clicking a CSV file will start Excel and open the file - the OS 'knows' how to handle a CSV file through the local file associations -

Implemented on a server, the server would need to add an appropriate MIME type, so that the browser knows what to do with file - I believe the MIME type would be "text/csv" - I've heard some say that it should be "text/plain" which will surely work, but all the commas separating the data elements and the quotes surrounding the data elements would be displayed and that is usually not the intent of a CSV file - if Excel opens a CSV file, it will 'parse out' the separating commas and surrounding quotes - if the MIME is "text/plain", I'm not positive which application MSIE would use, but probably Notepad - It's even possible the browser 'knows' itself how to display a "text/plain" file -

The ONLY way I know to create an XLS file is with Excel (or the Office Windows Components, if Office 2000 is installed) -

This could be done on the server side - Start Excel, populate it, save the file and send that file to a browser with a MIME type of "application/vnd.ms-excel" - the browser would start Excel and display the contents -

This could also be on the client side in the same manner - no MIME type involved -

Notes on the script functions -

When using a local run of Excel, I included a display function and commented out the save and close functions of the workbook, so you can see the result - you can uncomment the save and close and comment the display function such that the whole process runs invisibly -

Because of security, the user will be notified when Excel starts - I don't believe that there is anyway to avert that notifcation -

Also, the save file function may 'balk' because a file exists - in place of the workbooks.add, you could specify a 'unique' filename (strFilename) and use

// If you want to change the spreadsheet use 2 otherwise use 3
xls.Workbooks.Open( strFilename, 2, false )

Writing the CSV file data is not rigorously implemented in this sample script - for example an embeded comma in a data element requires quotes around that data element - embedded quotes will be interpreted as text delimiters and will need to be 'doubled up' (I think) - As implemented here, the file will probably be created without error, but the application reading the file won't retrieve the data properly - 'JavaScript Whiz Kids', which I'm not, will have no problem implementing a more rigourous solution - the main focus of these samples is the usage of MS Objects from JavaScript -

There is no MIME type used, needed or involved with client side scripting - the MIME type tells the browser what kind of file it is receiving from a server - when you're creating a file using client side scripting, the browser has already received the file from the server -

For server side, these procedures would work as well, with some additional considerations -

If Excel contains macros, button customizations and/or other sordid items, then it must be sent as a binary file - Microsoft Knowledge Base Article - 193998 has the details of how to send an "application/x-msexcel" to a browser - it would 'force' the browser to open the file with Excel - it basically involves opening Excel, populating it and saving it as an XLS file - then streaming that data in that file byte by byte to a browser -

If Excel file contains only data, then you can send the data as a table in an HTML file to a browser as an "application/vnd.ms-excel" file - Microsoft Knowledge Base Article - 199841 has the details - the server side HTML file is sent to the browser as an Active Server Page and the browser would be 'forced' to use Excel to open the file -

NB: If the client

then the browser 'may' use (or attempt to use) some other application to open the CSV file - in as much as a CSV file is pure ASCII, there are a lot of applications that will successfully open a CSV file - although the display may be a bit ugly -

For an XLS file, if Excel isn't available, then some kind of 'application can't be found message will probably be generated leading to the 'Open with' dialog box - the 'Open with' dialog box might as well be cancelled as there is no alternative to using Excel to view XLS files - and in the unlikely event a user had reassigned the file association for XLS, a foreign application is probably going to crash trying to open an Excel file -

There is a free Excel viewer file available from MS which would let a non-Excel computer display the contents of an Excel file -



<% Put any table here, your little ol' heart desireth If you change the id, you gotta change it in the script %>
Abe 23
Betty 34
Carol 56
David 24
Edward 16
Floyd 19
Gary 38
Harold 28