Export to Excel - Willing to help
Export to Excel - Willing to help
Misiu
Posts: 68Questions: 4Answers: 2
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.
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.
This discussion has been closed.
Replies
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
I'll write back if I'll have working code to test it.
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
http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
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
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 think I'll stay with flash for now :)
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.
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
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.
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.
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
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