Export to Excel - Willing to help

Misiu

If this is a duplicate of existing discussion then please apologise.

I've notice that when I use examples of TableTools and try to export table to excel I get CSV file. Searching on github I found out that this functionality will be rewritten.
I wound this library: http://code.google.com/p/as3xls/ it's bit old but looks promising.

I would like to help somehow with coding, especially that with working pdf export this will be much easier , but would like to know if source for export.swf is avilable.

allan

This is indeed a relatively common feature request, so your offer of help is extremely welcome :-). I've not yet had a chance to have a go at it myself, but the library you linked to does indeed look promising.

Have you had a look at how the TableTools SWF works? This is probably the more interesting function to start with: https://github.com/DataTables/TableTools/blob/master/media/as3/ZeroClipboardPdf.as#L135 . In these you will be able to intercept an 'xls' type export and pass the data through the as3xls library.

Regards,
Allan

Misiu

I'll try to do my best to create this functionality. I'll be needing this also :)
I'll write back if I'll have working code to test it.

Misiu

I've just found a javascript based solution to export grid data to excel without flash, just client-side code.
http://www.sencha.com/forum/showthread.php?32400-GridPanel-directly-to-Excel.

This is done in ExtJS, but maybe it will be possible to move this functionality to jQuery and add it to DataTables.

I think that this will be better, because not all devices support flash (hello Apple).

What do You think?

allan

That requires the data: pseudo protocol, which does not work in older IE (hello Microsoft). As such, unfortunately it is not an option that can currently be put into TableTools core, but a plug-in which provides that ability would certainly be an option.

Allan

timtucker

Are you still using an older version of office? I would think if you can use a newer version writing to the newer xml formats would be a much simpler effort.

http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

allan

Yup - I think that when I do this (unless Misiu or anyone else gets there first! :-) ) I'd want the output to be in the Office xml formats. The only difficulty with that is that the file needs to be zipped, but that is far from insurmountable.

The real problem is saving the file. There just isn't a good, cross platform way, of creating and saving files in Javascript. Some of the new APIs look like they might be viable, but they just aren't available cross browsers.

Allan

Misiu

@allan - I found never version of this plugin, that supports IE browsers, but it requires special security options.
I've posted code here: http://pastebin.com/1DUA229r

As You can see it only encodes xml file into base64, no zipping required (I may be wrong on this one :) )

As You wrote before there isn't a real cross browser/platform solution.
We can use flash, but then Apple devices won't work, or use pure JavaScript, but then IE won't work.

For now I think that I can try to build flash solution.

@timtucker - Of course that plugin should support Office XML format, that was intended from the beginning :)

allan

I guess ideally what is wanted is a Javascript implementation that will create the files (possibly including zipping?) and from there all that needs to be done is use a method to state the base64 data (be it Flash, data: or something else). At the moment I've got the ActionScript creating the output files - perhaps that is wrong...

Allan

Misiu

There is a library to zip files with JavaScript (http://stuartk.com/jszip/) but again IE doesn't support it.

I think I'll stay with flash for now :)

Misiu

I've managed to do a simple Excel using server-side.
I've added this function to my code:
[code]
function getData(oSettings) {

var aColumnsInc = getVisibleColumns(oSettings);
var aRow, aData = [], sLoopData = '';
//
//Header
//
aRow = [];
for (i = 0, iLen = oSettings.aoColumns.length; i < iLen; i++) {
if (aColumnsInc[i]) {
sLoopData = oSettings.aoColumns[i].sTitle.replace(/\n/g, " ").replace(/<.*?>/g, "").replace(/^\s+|\s+$/g, "");
aRow.push(sLoopData);
}
}
aData.push({ type: 'thead', cells: aRow });
//
//Body
//
for (j = 0, jLen = oSettings.aoData.length; j < jLen; j++) {

aRow = [];

for (i = 0, iLen = oSettings.aoColumns.length; i < iLen; i++) {
if (aColumnsInc[i]) {
if (oSettings.aoColumns[i].mData !== null) {
sLoopData = oSettings.aoData[j]._aData[oSettings.aoColumns[i].mData];
aRow.push(sLoopData);
} else {
aRow.push('');
}
}
}
aData.push({ type: 'tbody', cells: aRow });
}
//
//Footer
//
var vLen = getVisibleColumnsCount(oSettings);
for (j = 0, jLen = oSettings.nTFoot.children.length; j < jLen; j++) {
aRow = [];
for (i = 0; i < vLen; i++) {
sLoopData = oSettings.nTFoot.children[j].children[i].innerHTML.replace(/\n/g, " ").replace(/<.*?>/g, "").replace(/^\s+|\s+$/g, "");
aRow.push(sLoopData);
}
aData.push({ type: 'tfoot', cells: aRow });
}
return aData;
}


function getVisibleColumns(oSettings) {

var aColumns = [];
for (i = 0, iLen = oSettings.aoColumns.length; i < iLen; i++) {
aColumns.push(oSettings.aoColumns[i].bVisible && oSettings.aoColumns[i].sClass.indexOf('last') === -1 ? true : false);
}
return aColumns;
}

function getVisibleColumnsCount(oSettings) {
var vColumns = 0;
for (i = 0, iLen = oSettings.aoColumns.length; i < iLen; i++) {
if (oSettings.aoColumns[i].bVisible && oSettings.aoColumns[i].sClass.indexOf('last') === -1) {
vColumns++;
}
}
return vColumns;
}
[/code]

Above function gets all data from specific table, multiline header and footer, and checks only for visible columns.

Then using jquery.fileDownload.js (http://johnculviner.com/post/2012/03/22/Ajax-like-feature-rich-file-downloads-with-jQuery-File-Download.aspx) I passed the data to server-side function like so:

[code]
$("button#export").on("click", function(event) {
//$("div#loading").show();
var myData = JSON.stringify({ rows: getData(oTable1.fnSettings()) });
//console.log(dane);

$.fileDownload('Excel.ashx', {
httpMethod: "POST",
data: { data: myData },
successCallback: function(url) {
$("div#loading").hide();
alert('ok');
},
failCallback: function(responseHtml, url) {
$("div#loading").hide();
alert('Error while generating excel file');
}
});
});
[/code]

Server-side is little tricky, because I had to map json string to classes.
I've created 2 classes:
[code]
public class Row
{
public Row()
{
cells = new List();
}
public string type { get; set; }
public List cells { get; set; }
}

public class Table
{
public Table()
{
rows = new List();
}
public List rows { get; set; }
}
[/code]

Then the main ashx file looks like so:

[code]
public class Excel : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string data = string.Empty;
if (context.Request.Form["data"] != null && context.Request.Form["data"] != "")
{
data = context.Request.Form["data"];
}

JavaScriptSerializer jsonSerializer = new JavaScriptSerializer();

Table table = jsonSerializer.Deserialize(data);

StringBuilder sb = new StringBuilder();

sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine(@".textmode { mso-number-format:\@; }");
sb.AppendLine(@"td { border: 1px solid #D3D3D3; font-family: Verdana;font-size: 7pt; padding: 3px;}");
sb.AppendLine(".odd { background-color: #D6D2D2; color: #000000;text-align: right;}");
sb.AppendLine(".even { background-color: #FFFFFF; color: #000000;text-align: right;}");
sb.AppendLine(".head { background-color: #4B4B4B; color: #FFFFFF;font-weight: bold;text-align: center;white-space:pre-wrap;}");
sb.AppendLine("");
sb.AppendLine("");

int i = 0;
foreach (Row row in table.rows)
{
sb.AppendLine("");
foreach (string cell in row.cells)
{
if (row.type == "thead")
{
sb.AppendLine(String.Format("{1}", "head", cell));
}
else
if (row.type == "tbody")
{
sb.AppendLine(String.Format("{1}", i % 2 == 0 ? "odd" : "even", cell.Replace(".",",")));
}
else
{
sb.AppendLine(String.Format("{1}", "head", cell.Replace(".", ",")));
}
}
sb.AppendLine("");
if (row.type == "tbody")
i++;
}
sb.AppendLine("");
sb.AppendLine("");

context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=Raport.xls");
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.Write(sb.ToString());
context.Response.End();
}

public bool IsReusable
{
get
{
return true;
}
}
}
[/code]

I know that this isn't client-side excel generating, but for me this was fine, because of flash not installed on all machines. My solution allows to create files and download then with built in download client.

I know that this is a simple solution that can be upgraded, but main idea was to pass data from browser to server-side, there You can do all the magic :)

Any comments are welcome :)

Allan if You have some time could You check my js code? I would like to change this into plugin, so it will be easier to use.

Misiu

@allan is is possible to add this function to datatables? To get all data in some kind of JSON format with all header and footer rows? I've tried my code on 4 tables and it works fine, but if You have some time please take a look at it.

My dream function would look like so:

[code]
var myTable= $('table#awesomeTable').dataTable({...});
var tableData = myTable.getData('full','hf');

// the options could be:
// 1 parameter - export type - (full, visible) : full - all data, visible - only visible rows and columns,
// this could be useful when we would like to export only filtered results

// 2 parameter - include header and footer (h,f,hf,none) : h - header only, f - footer only, hf - both, none - none of them
[/code]

Having a function like so we would be able to do server side generation of file, this could be pdf, excel, word or any custom format.
In my above function I also included row type (thead, tbody, tfoot) so it will be easy to add custom styles.

This could be a standalone plugin, but I don't know how to write one. I would like to get involved, but I think that releasing poorly written code has no sense, so please review my function or maybe You could guide me on how to create DataTables plugin.

Thanks for any tips on this!

allan

You can currently do table.fnGetData(); to get all body information, but there is no way to get the header and footer information. That would be an ideal case for a plug-in API method: http://datatables.net/development/api

Allan

Misiu

I found Your repository of api functions of GitHub and I'm already converting my function to plugin :)

oSettings.aoData contains all the data for table, but it is unsorted, so when I export my data I get default order.
Is sorted data also stored inside oSettings? Or do I must sort it myself?
I would like to export sorted data, exactly as it is sorted in table.

Misiu

I've created a simple repository on github to show my code, it's avilable at:
https://github.com/tjagusz/DataTables/blob/master/api/fnGetTableData.js
I'll try to make it as good as I can, but this can take a while :)

All tips are welcome :)

I need a way to get sorted data from table and filter all columns based on class, so that user will be able to add class to a column (or columns) and then specify that class name in config, so that all those columns won't get exported.

This would be a nice feature if someone (like me) is adding special columns with action buttons or links and don't want that column to be exported.

P.S. This is my first GitHub repository.

allan

> oSettings.aoData contains all the data for table, but it is unsorted, so when I export my data I get default order.

Ah yes - I'd forgotten about that. The underscore function is what you want then: http://datatables.net/docs/DataTables/1.9.4/#_ . For example:

[code]
var d = table._('tr');
[/code]

will get the data in the current order.

The underscore method can also be used to get filtered (or not) data and paged (or not) data. See the documentation link above for full options.

> I need a way to get sorted data from table and filter all columns based on class

Have a look at the fnGetColumnData - I think you'd need to either use that, or some similar method at the moment.

v1.10 is going to introduce a new API for data retrieval which will be much easier to use :-). Indeed, it might be that getting data based on class will be as simple as `table.columns('td.myclass').data()` (I haven't yet formalised how the selectors will work!). That is probably a few months away though, and the underscore method isn't going any where!

> P.S. This is my first GitHub repository.

Welcome to the club :-)

Allan

danielel

thanks Misiu and Allan. Would it be possible to add a demo for the Export to Excel format functionality. I am sure, it would be useful to many.

allan

There is still not xlsx export option - that needs to be developed. So it would be a short demo at the moment :-).

Misiu

What my example is doing is just taking data from DataTaleble and sending it to server. It's basically a serialized JSON object. What I've added is just some extra markup for header and footer.
On server You can do all You want with the data. I've shown way to deserialize data passed from browser to server into nice class, so it is easy to iterate over every row.

You can use EPPlus library (http://epplus.codeplex.com/) to generate xslx file with formulas, tables, charts ad all stuff normal excel can do.

There is also an alternative for PHP - http://phpexcel.codeplex.com/

The above code works fine for asp, but for PHP I'll have to refresh my PHP knowledge :)

allan

Sounds good. Both are these libraries are server-side, so not suitable for inclusion in TableTools core, but good options for anyone looking into this.

Allan