A reader posed an interesting question on one of my ColdFusion posts recently. The post (Creating an Excel File) demonstrated how to take form data on the front end and create an Excel file via ColdFusion when the form is submitted. The user wanted to know if this could be done entirely client-side.
The latest versions of Office use an XML based file format contained within a zip file. There is a good JS library for working with zips and as it turns out - there is a JS library for XLSX as well: https://github.com/stephen-hardy/xlsx.js. While this would possibly work, I thought there might be a simpler way using CSV, or comma-separated value files.
About two years ago I wrote a piece on exporting data from IndexedDB. It used string data and a data url to create a link to a virtual representation of the data. Combining this with the download attribute of the anchor tag, it is possible to push a download of a fake file to the user.
Therefore - all we need to do is create a CSV string. I built a simple, but ugly demo, that consists of a few rows of text fields.
The user can then enter values into the columns and get a CSV file by clicking the button. Let's look at the JavaScript code.
$(document).ready(function() {
var $link = $("#dataLink");
var $nameFields = $(".nameField");
var $cookieFields = $(".cookieField");
$("#downloadLink").on("click", function(e) {
var csv = "";
//we should have the same amount of name/cookie fields
for(var i=0; i<$nameFields.length; i++) {
var name = $nameFields.eq(i).val();
var cookies = $cookieFields.eq(i).val();
if(name !== '' && cookies !== '') csv += name + "," + cookies + "\n";
}
console.log(csv);
$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();
});
});The code begins with the click handler for the download button. All I have to do is generate my CSV string by looping over the rows of fields. To be fair, my CSV handling could be a bit nicer. A name can include a comma so I should probably wrap the value in quotes, but I think you get the idea.
Once we have the CSV, we then use the same method I used on the IndexedDB post and force the download. On my system, this creates a file that opens in Excel just fine.
It also worked in OpenOffice once I told it to use commas. Want to try it yourself? Hit the demo link below.
Edit: Big thanks to @Laurence below who figured out that I didn't need a particular hack to get my click event working for downloads. That cut out about 50% of the code I had before. Thanks Laurence!
Archived Comments
I think you can just chain a click() event to your link attribute setting.
$link.attr("href",'data:Application/octet-stream,'+encodeURIComponent(csv)).click();
http://jsfiddle.net/L54tm/
Never mind. I just realized that I copied document.ready into the fiddle twice, but the second one was off screen so didn't see it.
Nice work Ray. Can definitely find some use of this snippet of code you created. Not sure just when yet, but I am sure I can think of something to make use of this code.
@Laurence: So are you saying there IS a better way for my hack or there isn't?
I thought just adding .click() to the end of your $link call was working but then realized I had the call $(document).ready() twice and the second instance still referenced your fakeClick() function, and so I what I though was working was not.
So I played around with the code a bit more and if you change to the following, it seems to work (at least in Chrome and FF)
$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();
http://jsfiddle.net/L54tm/8/
Damn tootin - yeah - that worked. Will update the post now.
Then I'll delete your comments, block anyone with your name, take credit, sell to Facebook, become rich, AND BUY SHARKS WITH LASER BEAMS!
This is not the comment you are looking for ;-) Call off the sharks. You're still referencing fakeClick() in the code above, the demo looks correct. Thanks Ray!
Oops, fixed for reals this time.
thats a sleek solution for csv files via jquery.. small and powerful.. thanks for sharing! :)
Get some pet dolphins if you need to call off the sharks. :)
Not working in IE
What version of IE? When you open up IE's dev tools, what error do you see? If no error, use debugging and figure out where it is failing.
what if i want to run that code on android device
Did you try it?
Can we use this code in phonegap?
Yes.
This code work well in browsers, but in phonegap nothing happended. I used :
<a href=# class="ui-btn" id="save"> Save to a text file </a>
<a href="" id="dataLink" download="data.csv"></a>
in index.html file of www folder, and:
$("#save").on ("tap", function (event)
{
var $link = $("#dataLink");
var csv = "";
db.transaction (function (transaction)
{
var sql = "SELECT * FROM datastorage";
transaction.executeSql (sql, undefined,
function (transaction, result)
{
if (result.rows.length)
{
for (var i = 0; i < result.rows.length; i++)
{
var row = result.rows.item (i);
var id = row.id;
var data = row.data;
var book = row.book;
var page = row.page;
csv += id + "," + data + "," + book + "," + page + "\n";
}
console.log(csv);
$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();
}
}, error);
});
});
in a .js file.
Did you see anything when you debugged? Use Remote Debugging.
if this workes in mobile app?
Yes.
As i tried in my App ,its working Great on Browser But not downloading any file in App.
When you check with remote debugging, do you see anything in the console?
Ok, so I tested in both Android/iOS, and it doesn't work. Of course, if you use Cordova/PhoneGap you could easily save it.
Excuse me for delay,
I tested my app (created by phonegap) in an android 4.2.2 device. Chrome Remote Debugging is used with an Android 4.4 device. I should test the app again with this device.
Thank you a lot,
Mohsen
You don't really need to - it fails. :) But in PhoneGap I'd just use the FileSystem API.
Hello,
I test the app with File plugin for phonegap. It works well.
code:
$("#save").on ("tap", function (event)
{
/*var $link = $("#dataLink");*/
var csv = "";
db.transaction (function (transaction)
{
var sql = "SELECT * FROM datastorage";
transaction.executeSql (sql, undefined,
function (transaction, result)
{
if (result.rows.length)
{
for (var i = 0; i < result.rows.length; i++)
{
var row = result.rows.item (i);
var id = row.id;
var data = row.data;
var book = row.book;
var page = row.page;
csv += id + "," + data + "," + book + "," + page + "\n";
}
console.log(csv);
/*$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();*/
// request the persistent file system
window.requestFileSystem(LocalFileSystem.PERSISTENT, 0, gotFS, fail);
function gotFS(fileSystem) {
console.log(fileSystem.name);
fileSystem.root.getFile("data.csv", {create: true, exclusive: false, append: true}, gotFileEntry, fail);
}
function gotFileEntry(fileEntry) {
fileEntry.createWriter(gotFileWriter, fail);
}
function gotFileWriter(writer) {
writer.onwriteend = function(evt) {
console.log("write success");
};
writer.seek(writer.length);
writer.write(csv);
}
function fail(error) {
console.log(error.code);
alert (error.code);
}
}
}, error);
});
});
Thank you,
Mohsen
Hey Raymond, I have been trying this code but seems to be working ONLY in Chrome and Firefox, it doesnt work in Internet Explorer. I verified in the Developer Tools and found the below error message:
"The data area passed to a system call is too small."
Do you have any suggestions or any sample on fiddle that works with IE, would appreciate if you can share.
Thanks in advance!!
Regards,
Manish
Where did that call come from? In other words, where exactly does it break down?
hey Raymond...thank you for the code.i got some link..but Im trying to add some more input fields at that time i got stucked ..please give me some hint...
I'm sorry - can you provide a bit more detail?
Hello..actually I want to create dynamic table and save that table to csv file
Ok I still need more details though. What did you try, what happened, etc.