I have the following situation:
I have set up a series of Cron jobs on an internal company server to run various PHP scripts designed to check data integrity. Each PHP script queries a company database, formats the returned query data into an HTML file containing one or more <tables>, and then mails the HTML file to several client emails as an attachment. From my experience, most of the PHP scripts generate HTML files with only a few tables, however there are a few PHP scripts the create HTML files with around 30 tables. HTML files have been chosen as the distribution format of these scans because HTML makes it easy to view many tables at once in a browser window.
I would like to add the functionality for the clients to download a table in the HTML file as a CSV file. I anticipate clients using this feature when they suspect a data integrity issue based on the table data. It would be ideal for them to be able to take the table in question, export the data out to a CSV file, and then study it further.
Because need for exporting the data to CSV format is at the discretion of the client, unpredictable as to what table will be under scrutiny, and intermittently used I do not want to create CSV files for every table.
Normally creating a CSV file wouldn't be too difficult, using JavaScript/jQuery to preform DOM traversal and generate the CSV file data into a string utilizing a server call or flash library to facilitate the download process; but I have one limiting constraint: The HTML file needs to be "portable."
I would like the clients to be able to take their HTML file and preform analysis of the data outside the company intranet. Also it is likely these HTML files will be archived, so making the export functionality "self contained" in the HTML files is a highly desirable feature for the two previous reasons.
The "portable" constraint of CSV file
generation from a HTML file means:
I cannot make a server call. This means ALL the file
generation must be done client-side.
I want the single HTML file attached to the email to contain all the resources to generate the CSV file. This means I cannot use jQuery or flash libraries to generate the file.
I understand, for obvious security reasons, that writing out files to disk using JavaScript isn't supported by any browser. I don't want to create a file without the user knowledge; I would like to generate the file using JavaScript in memory and then prompt the user the "download" the file from memory.
I have looked into generating the CSV file as a URI however, according to my research and testing, this approach has a few problems:
URIs for files are not supported by IE (See Here)
URIs in FireFox saves the file with a random file name and as a .part file
As much as it pains me, I can accept the fact the IE<=v9 won't create a URI for files. I would like to create a semi-cross-browser solution in which Chrome, Firefox, and Safari create a URI to download the CSV file after JavaScript DOM traversal compiles the data.
My Example Table:
<table>
<thead class="resulttitle">
<tr>
<th style="text-align:center;" colspan="3">
NameOfTheTable</th>
</tr>
</thead>
<tbody>
<tr class="resultheader">
<td>VEN_PK</td>
<td>VEN_CompanyName</td>
<td>VEN_Order</td>
</tr>
<tr>
<td class='resultfield'>1</td>
<td class='resultfield'>Brander Ranch</td>
<td class='resultfield'>Beef</td>
</tr>
<tr>
<td class='resultfield'>2</td>
<td class='resultfield'>Super Tree Produce</td>
<td class='resultfield'>Apples</td>
</tr>
<tr>
<td class='resultfield'>3</td>
<td class='resultfield'>John's Distilery</td>
<td class='resultfield'>Beer</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="3" style="text-align:right;">
<button onclick="doSomething(this);">Export to CSV File</button></td>
</tr>
</tfoot>
</table>
My Example JavaScript:
<script type="text/javascript">
function doSomething(inButton) {
/* locate elements */
var table = inButton.parentNode.parentNode.parentNode.parentNode;
var name = table.rows[0].cells[0].textContent;
var tbody = table.tBodies[0];
/* create CSV String through DOM traversal */
var rows = tbody.rows;
var csvStr = "";
for (var i=0; i < rows.length; i++) {
for (var j=0; j < rows[i].cells.length; j++) {
csvStr += rows[i].cells[j].textContent +",";
}
csvStr += "\n";
}
/* temporary proof DOM traversal was successful */
alert("Table Name:\t" + name + "\nCSV String:\n" + csvStr);
/* Create URI Here!
* (code I am missing)
*/
/* Approach 1 : Auto-download
* downloads CSV data but:
* In FireFox downloads as randomCharacers.part instead of name.csv
* In Chrome downloads without prompting the user
* In Safari opens the files in browser (textfile)
*/
//var hrefData = "data:text/csv;charset=US-ASCII," + encodeURIComponent(csvStr);
//document.location.href = hrefData;
/* Approach 2 : Right-Click Save As...
*/
var hrefData = "data:text/csv;charset=US-ASCII," + encodeURIComponent(csvStr);
var fileLink = document.createElement("a");
fileLink.href = hrefData;
fileLink.innerHTML = "download";
parentTD = inButton.parentNode;
parentTD.appendChild(fileLink);
parentTD.removeChild(inButton);
}
</script>
I am looking for an example solution in which the above example table can be downloaded as a CSV file:
using a URI
the user is prompted to save the file
the default filename is the name of the table.
code works as described in modern versions of FireFox, Safari, & Chrome
I have added a <script> tag with the DOM traversal function doSomething(). The real help I need is with formatting the URI to what I want within the doSomething() function.
