PDA

View Full Version : Datastore to CSV client side conversion



DarkStarDS
15 Jul 2009, 5:21 AM
Hello, I now want to implement a functionlity that allows the end user to get a csv file from the current content of the grid.
The final goal is to gain time of loading so I want that the code necessary to do that to be executed client side. But I am a newbie in extJS and I have no idea about how to do. Can you give me hints, examples, tutorials ? I have look at similar threads but the code is server side.
Thank you !

saJoshua
15 Jul 2009, 6:05 AM
I'm pretty sure that you can't write to the file system from javascript. Something about security and priviledges?

If you wanted, you could parse the store's data and just append a string.
Something like


var csv = [];
store.each(function(rec){
csv.push(Ext.encode(rec.data).replace(/[{}]/,''));
})
return csv.join('\n');


You probably need to send the request back to the server, prepare the file there and return the URL for the download. I'm also pretty sure server side parsing of data to CSV is a common practice and you'll find plenty of ways to skin that cat.

Animal
15 Jul 2009, 6:38 AM
CSV? Wouldn't Excel be better?

But either way, you can create a data URL with any data you choose in it.

Only works on Webkit, Firefox 3 and IE8.

The principle is shown here: http://extjs.com/forum/showthread.php?t=32400

It will be simpler to create CSV, but anyway, you see how to do it.

DarkStarDS
15 Jul 2009, 6:44 AM
Sure, usually it's done on server side but I want to try something new...
let me explain:


switch( $tid )
{
case "1" : $ctype = "application/pdf"; break;
case "2" : $ctype = "application/xml"; break;
default : return "The file type is corrupt. This is a fairly big problem - please contact us!";
}

header( "Pragma: public" );
header( "Expires: 0" );
header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header( "Cache-Control: private", false );
header( "Content-Type: $ctype" );
header( "Content-Disposition: attachment; filename=\"" . basename( $fileDir ) . "\";" );
header( "Content-Transfer-Encoding: binary" );
header( "Content-Length: " . filesize( $fileDir ) );

$byteCount = @readfile( "$fileDir" );

That is PHP code that takes something in memory or on server filesystem and feeds it via HTTP to make it look like a file download. I need to make a JavaScript version (there must be a javascript version of the "header" function, which sends an HTTP header) and instead of feeding a file I feed the variable into which I generated the data. So in JavaScript I would do the following process:
1. get access to the grid data source
2. process it in memory into the CSV format and store that in a variable
3. send all the HTTP headers to the browser
4. send the data from the variable
This will cause the browser to show a "Save file to xxxxx" dialog box, like with a normal file you download.

Animal
15 Jul 2009, 6:51 AM
A Data URL.

That's the only way you can do this. So you cannot get 100% cross-browser coverage.

Condor
15 Jul 2009, 6:51 AM
3. send all the HTTP headers to the browser
4. send the data from the variable

Javascript already runs in the browser. Javascript can't send anything to itself!

There are 2 options:
1. Show a new window with the .csv data and make the user use File -> Save.
2. Use a data URL as Animal suggested (but only works on new browsers).

DarkStarDS
16 Jul 2009, 1:40 AM
Thanks for your answers,
I looked at your code Animal, interesting despite of the fact that I haven't understood everyting. It is not commented. And I still have no idea how to convert datastore to csv !
So, what i got to do is :
Retrieve infos from datasource, look at how is generated a csv file, convert the data this way in ASCII characters in a variable and send it in the dataurl, am I right...?

Condor
16 Jul 2009, 2:56 AM
In another thread somebody suggested another option (which I didn't even consider, because it uses 'forbidden' technology):

You can create files using ActiveX, but this is only supported on IE browsers which don't have ActiveX disabled.

DarkStarDS
16 Jul 2009, 6:01 AM
Forbidden ?
Well let's forget it then.
So I have implemented the csv conversion. It's working well. I give the code for some people that would want to do the same as me :



Ext.override(Ext.grid.GridPanel, {
getCSV: function(includeHidden) {
return this.createWorksheet(includeHidden);
},

createWorksheet: function(includeHidden) {

// Calculate cell data types and extra class names which affect formatting
var cellType = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
switch(fld.type) {
case "int":
cellType.push("Number");
cellTypeClass.push("int");
break;
case "float":
cellType.push("Number");
cellTypeClass.push("float");
break;
case "bool":
case "boolean":
cellType.push("String");
cellTypeClass.push("");
break;
case "date":
cellType.push("DateTime");
cellTypeClass.push("date");
break;
default:
cellType.push("String");
cellTypeClass.push("");
break;
}
}
}

// Generate worksheet header details.
var cm = this.getColumnModel();
var colXml = '';
var headerCSV = '"' + this.title + '"\n';
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
if(i == cm.getColumnCount()-1)
headerCSV += '"' + cm.getColumnHeader(i) + '"';
else
headerCSV += '"' + cm.getColumnHeader(i) + '",';
}
}
headerCSV += '\n';

// Generate the data rows from the data in the Store
dataCSV = '';
var k = 0;
for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
r = it[i].data; // Get the row
k = 0;
for (var j = 0; j < cm.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] == 'DateTime')
v = v.format('d-m-Y');
if(j == cm.getColumnCount()-1)
dataCSV += '"' + v + '"';
else
dataCSV += '"' + v + '",';
k++;
}
}
dataCSV += '\n';
}
dataCSV += '\n';

var result = headerCSV + dataCSV;
return result;
}
});

Now, I want to reload the store when the user sorts a column. But I don't find the element that throws the event of sorting... Because I need to reload the page after I have sorted to get good values in my csv file. One last thing, can we put an name and an extension to the file to download ? Thank you !

DarkStarDS
16 Jul 2009, 11:46 PM
Any help ? :-|
Please, I try many ways to fix that problem but nothing works, I still need to reload the page before changes are considered...


Edit:
Ok, I've fixed it myself, here is the code:


var loadLinkButton = function()
{
linkButton.getEl().child('a', true).href = 'data:text/plain;base64,' +
Base64.encode(grid.getCSV());
};
linkButton.on('click', loadLinkButton);

It was just that simple, add a listener to the LinkButton that triggers the generation of the csv file...

Pekka Karalahti
10 Dec 2009, 1:32 AM
This is working great but how can I delete empty row before the header(columns).
var headerCSV = '"' + this.title + '"\n';

I have replaced this with cause I dont need header:

var headerCSV = '';

-------------------------------------
***THIS EMPTY ROW**
columns
data
data
data

Pekka Karalahti
14 Dec 2009, 5:01 AM
Anyone got any idea for this ? This sounds so stupid and little bug somewhere.

Pekka Karalahti
30 Dec 2009, 1:03 AM
I am using DarkStars code but i am getting error that says "fld is undefined", I have fixed my store and grid many times but no success. Anybody knows what is wrong ?

Store...



var store = new Ext.data.Store({
// load using HTTP
proxy : proxy,
reader : new Ext.data.JsonReader({
root:'rivit'
}, [{
name : 'nimi_fi',
type : 'string'
},{
name : 'lpnimi_fi',
type : 'string'
},{
name : 'lpid',
type : 'string'
},{
name : 'onimi_fi',
type : 'string'
},{
name : 'lpvuosi',
type : 'string'
},{
name : 'knimi_fi',
type : 'string'
},{
name : 'kid',
type : 'string'
},{
name : 'mnimi_fi',
type : 'string'
},{
name : 'lnimi_fi',
type : 'string'
}] ),baseParams: Paramas
});


This is csv creator....


Ext.override(Ext.grid.GridPanel, {
getCSV: function(includeHidden) {
return this.createWorksheet(includeHidden);
},

createWorksheet: function(includeHidden) {

// Calculate cell data types and extra class names which affect formatting
var cellType = [];
var cellTypeClass = [];
var cm = this.getColumnModel();
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
switch(fld.type) {
case "int":
cellType.push("Number");
cellTypeClass.push("int");
break;
case "float":
cellType.push("Number");
cellTypeClass.push("float");
break;
case "bool":
case "boolean":
cellType.push("String");
cellTypeClass.push("");
break;
case "date":
cellType.push("DateTime");
cellTypeClass.push("date");
break;
default:
cellType.push("String");
cellTypeClass.push("");
break;
}
}
}

// Generate worksheet header details.
var cm = this.getColumnModel();
var colXml = '';
var headerCSV = '"' + this.title + '"\n';
for (var i = 0; i < cm.getColumnCount(); i++) {
if (includeHidden || !cm.isHidden(i)) {
if(i == cm.getColumnCount()-1)
headerCSV += '"' + cm.getColumnHeader(i) + '"';
else
headerCSV += '"' + cm.getColumnHeader(i) + '",';
}
}
headerCSV += '\n';

// Generate the data rows from the data in the Store
dataCSV = '';
var k = 0;
for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
r = it[i].data; // Get the row
k = 0;
for (var j = 0; j < cm.getColumnCount(); j++) {
if (includeHidden || !cm.isHidden(j)) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] == 'DateTime')
v = v.format('d-m-Y');
if(j == cm.getColumnCount()-1)
dataCSV += '"' + v + '"';
else
dataCSV += '"' + v + '",';
k++;
}
}
dataCSV += '\n';
}
dataCSV += '\n';

var result = headerCSV + dataCSV;
return result;
}
})