Export #58

ghost · 2013-11-06T16:33:11Z

Hi there. I'm sorry if I'm writing this at the wrong place, but I was wondering if there's already something implemented to export the pivot table as an Excel file or anything like that. It doesn't have to be fancy or anything. Just the raw data used to form the current pivot table.

Thanks in advance!

nicolaskruchten · 2013-11-06T16:40:14Z

Hi there! Unfortunately there is no built-in feature for this at the moment... Sorry!

ghost · 2013-11-08T11:08:22Z

Hey Nicolas!

Do you mind if I pull a fork to try and implement? Do you have any pointers for me to start? I'll try to work on something over the weekend.

nicolaskruchten · 2013-11-08T13:13:06Z

No problem! I don't really have any pointers... It'll be easier for me to merge your changes in if you do it in CoffeeScript but if you prefer JavaScript I can port them by hand as well.

tgabrielle · 2013-11-14T13:13:38Z

would also be interested in this...thanks guys!

kwesley · 2013-11-21T08:48:21Z

+1

nicolaskruchten · 2013-11-23T03:43:45Z

Are people wanting to export the input data or the resulting pivot table?

tgabrielle · 2013-11-23T06:55:36Z

Should be the pivot table result.

ogamot · 2013-11-23T08:13:58Z

People always want to export :)

Le samedi 23 novembre 2013, Nicolas Kruchten a écrit :

Are people wanting to export the input data or the resulting pivot table?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/58#issuecomment-29124967
.

Cordialement,

Olivier Gamot

06 60 09 53 09 / 09 72 36 97 03
ogYs
www.ogYs.fr http://www.ogys.fr
www.busYtimesheet.com http://www.busytimesheet.com
56 Rue de Londres, 75008 Paris, Plan d'accès http://goo.gl/maps/zwsh

fastcatch · 2013-12-02T21:38:53Z

Guys, this is a holy grail: "everybody" wants to export to Excel from JS and there seems to be no universally applicable solution. IMHO this feature should not be built into this (or any) pivottable component: it generates a proper HTML table that can be exported (as Excel, PDF, etc) via other plugins (if there's any :)).

nicolaskruchten · 2013-12-02T21:49:37Z

+1 to @fastcatch

I should add that the simplest way to "export" to Excel is simple copy-paste. If you select the whole table and copy it, you can then paste it into Excel and it keeps almost all of the formatting intact.

cihadturhan · 2013-12-13T21:54:14Z

tl;dr A live demo
Hi,
Seems like everyone is willing to see an excel export implementation :) I'm sure there are people know how to export to xls better than me. Anyway, I'll propose the steps below.

Build an XML tree.
Why? - Because if you create an xml, it's easy to convert it ~~xml~~ xls. Xls file is a base64 encoded xml file actually.
How? - Every browser is a xml parser already because they parse HTML, a kind of XML. Here is the snippet to create a simple excel-like XML file using jquery:

//create root of xml
$root = $('<xml/>');

//create a workbook
$workbook = $('<ss:Workbook/>').attr({
    "xmlns:ss":"urn:schemas-microsoft-com:office:spreadsheet", 
    "xmlns:x" :"urn:schemas-microsoft-com:office:excel",
    "xmlns:o" :"urn:schemas-microsoft-com:office:office"
});

/*
* Add other elements here
*/

//add workbook to root
$root.append($workbook);

//retrieve full xml string
alert($root.wrap($('<div>')).parent().html())

Now convert it base64 format and append required headers
Why? - As I said above, xls file is a base64 encoded xml string.
How? -Simple: Using web toolkit's small script will suffice. Just one line:

xlsObject = 'data:application/vnd.ms-excel;base64,' + Base64.encode(testXML);

Just say document to download it.
Why? - To download.
How? - like this:

document.location = xlsObject

Prettified xml file and result

Edit: typo corrected

romulosilvacardoso · 2013-12-18T03:37:26Z

Very Simple to resolve this:

Edit de pivot.js

result = $("<table class='table table-bordered pvtTable'>");

to

result = $("<table id='testTable' class='table table-bordered pvtTable'>");

Create a script in local.html

<script type="text/javascript">
        var tableToExcel = (function() {            
          var uri = 'data:application/vnd.ms-excel;base64,'
            , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>'
            , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
            , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }
          return function(table, name) {
            if (!table.nodeType) table = document.getElementById(table)
            var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
            window.location.href = uri + base64(format(template, ctx))
          }
        })()
    </script>

Create a button in local.html

<input type="button" onclick="tableToExcel('testTable', 'W3C Example Table')" value="Export to Excel">

Done!

ken-muturi · 2013-12-30T09:44:09Z

works like a charm

cihadturhan · 2013-12-30T15:34:59Z

Note that this is not a cross-browser solution. Most probably it won't work on ie :(

etwoss · 2014-05-14T07:33:50Z

is this true? does it not work in ie?

ken-muturi · 2014-05-14T07:42:02Z

I have see this, not tried it though..
https://sites.google.com/site/firefoxaddondev/home/export-html-table-to-excel-in-firefox-and-ie

etwoss · 2014-05-14T07:45:16Z

Hi

Thanks. I will take a look at it

Eric

Eric ten Westenend
Senior Developer
[Description: SCREEN_INFOmatch.jpg]http://www.screeninfomatch.com/

Screen INFOmatch
Vughterweg 47f
5211 CK 's-Hertogenbosch
The Netherlands

Tel
Fax
Http
Email

+31 73 6122 940
+31 73 6142 595
www.screeninfomatch.comhttp://www.screeninfomatch.com/
[email protected]:[email protected]

From: muturiken [mailto:[email protected]]
Sent: woensdag 14 mei 2014 9:42
To: nicolaskruchten/pivottable
Cc: Westenend, Eric ten
Subject: Re: [pivottable] Export (#58)

I have see this, not tried it though..
https://sites.google.com/site/firefoxaddondev/home/export-html-table-to-excel-in-firefox-and-ie

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/58#issuecomment-43051191.

cihadturhan · 2014-05-14T07:45:58Z

Try jsfiddle example I provided above and see if it works on ie10+

etwoss · 2014-05-14T07:50:48Z

Problem is that i do not directly use HTML and JavaScript. I use Delphi with CGDevtools. They take care of the created HTML/Javascript

etwoss · 2014-05-16T10:42:24Z

Hi

using the example of romulosilvacardoso i het an error in InnerHTML

Uncaught type error, cannot read poperty ínnerHTML of null

Eric

dipeshchauhan · 2014-10-08T18:35:10Z

I have used romulosilvacardoso method and its working in Firefox, however doesn't work in IE. Does anyone know how to resolve this? When i click on the button nothing happens. One other issue i have with Firefox is when the exel file opens the file name is a temporary file name followed by two extentions of .xls.xls therefore the browser asks "The file you are trying to open 'gGGl9L_M.xls.xls', is in a different format then specified by the file extension.

ashesnz · 2014-10-08T19:40:29Z

Hello there, I've attempted to build a export method into the PivotUI, have a look and let me know what you think..

https://github.com/ashesnz/pivottable/tree/ExportToCSV

gani4ibt · 2014-12-04T06:51:18Z

+1

nicolaskruchten · 2014-12-21T22:39:31Z

Issue was auto-closed by Github, which was a bit premature, but f636484 does implement, finally, reliable export functionality! You can see it in action here: http://nicolas.kruchten.com/pivottable/examples/mps_export.html

brianalu · 2015-07-10T18:51:54Z

I'm currently trying to export using PHP to be 1 worksheet in a much larger report. Was wondering if this was ever completely implemented? :)

-Brian

nicolaskruchten · 2015-07-10T20:14:05Z

This is a client-side only library, so the backend technology (i.e. PHP) does not really come into play... Does the export_renderers functionality meet your needs? If not, what more would you need?

brianalu · 2015-07-10T20:18:14Z

Is there any way to get the output of the table pivot into a string or something that I could easily pass to PHP or a file? Similar to the export method in the branch @ashesnz made
https://github.com/ashesnz/pivottable/tree/ExportToCSV

nicolaskruchten · 2015-07-10T20:59:26Z

Yes, with the export_renderers... have you tried to see if this can meet your needs?

LukeBrumfield · 2016-02-05T21:12:49Z

I've been using this library on a reporting project, and I have something that might help out anyone wanting to export to PDF. This example uses jspdf, and should warn about "pvtAxisLabel" causing some problems if you do not exclude them
This uses jspdf: https://github.com/MrRio/jsPDF

<script src="javascripts/pivot.js"></script>
<script src="javascripts/jsPDF/dist/jspdf.debug.js"></script>
<script src="javascripts/jsPDF/dist/jspdf.min.js">

var genPDF = function() {
  var pdf = new jsPDF('p', 'pt', 'letter');
  source = $(".pvtTable")[0].outerHTML; //pvtTable is the default element class
  pdf.fromHTML(source);
  pdf.save('Test.pdf');
};

nicolaskruchten · 2016-02-12T01:33:25Z

Thanks @LukeBrumfield !

GauravP77 · 2016-05-30T11:10:47Z

@LukeBrumfield @nicolaskruchten thanks for the above snippet for export to pdf. It worked but had challenges with css, grouping and was shrinking so made following changes which worked like charm.

var pdf = new jsPDF('landscape');

var specialElementHandlers = {
'.pvtTable': function(element, renderer){
return true;
}
};
var options = {
pagesplit: true
};

pdf.addHTML($('.pvtTable')[0], 15, 30, {
'elementHandlers': specialElementHandlers,
}, function() {
pdf.save('Test.pdf');
});

meghbrume · 2016-07-12T11:55:16Z

Hi, I am also struggling with the same problem. But I only want to download the output generated after selecting all the renderers and aggregators. How can I solve it?

Thanks in advance :)

nicolaskruchten · 2016-12-21T15:07:17Z

I'm marking this as closed, given that the FAQ addresses it quite well: https://github.com/nicolaskruchten/pivottable/wiki/Frequently-Asked-Questions#exporting-renderer-output-to-excel-or-images

Bill-VA · 2017-11-16T18:51:32Z

romulosilvacardoso, thanks for the code snippets. Instead of editing the pivottable.js file (using CDN), I just did this: $('.pvtTable').attr('id', 'testTable');

To remove the totals columns and bottom row, I did this:

$('#testTable thead tr:first').find('th:last').remove();
$('#testTable tr').find('td:last').remove();
$('#testTable').find('tr:last').remove();

ghost · 2018-01-01T14:12:01Z

Sir,

How can i do excel export of particular cell data in this

susandotmark · 2018-07-18T11:06:28Z

tried romulosilvacardoso solution via typescript, hits the function all codes are executed, no erros but nothing happens, can someone please guide me through this

PRINCEHR · 2019-03-18T08:36:03Z

Guys, You can try this code

// In body

Export Table Data To Excel File

<script type="text/javascript">
  function exportTableToExcel(tableID, filename = ''){
var downloadLink;
var dataType = 'application/vnd.ms-excel';
var tableSelect = document.getElementById(tableID);
var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

filename = filename?filename+'.xls':'excel_data.xls';
downloadLink = document.createElement("a");

document.body.appendChild(downloadLink);

if(navigator.msSaveOrOpenBlob){
var blob = new Blob(['\ufeff', tableHTML], {
  type: dataType
});
navigator.msSaveOrOpenBlob( blob, filename);
}else{

downloadLink.href = 'data:' + dataType + ', ' + tableHTML;

downloadLink.download = filename;

downloadLink.click();
}
}
  </script>

//In pivot.js

pivot.js link in which can be used in which modification is already made
modified pivot js
After Inline No:955
result.setAttribute("id", "testTable");

Hope this will help you guys!

f0rmig4 · 2019-04-12T20:22:59Z

I would like to get the data output values from the Pivot component, which I believe is in json format. Has anyone done this?

eusebiomarquesbenitez · 2019-12-19T13:23:01Z

This export to Excel work fine.

Adding the Javascript function:

function TableToExcel(filename = ''){
        var downloadLink;
        var dataType = 'application/vnd.ms-excel';
        var tableHTML = $(".pvtTable")[0].outerHTML.replace(/ /g, '%20');
        
        filename = filename?filename+'.xls':'excel_data.xls';
        downloadLink = document.createElement("a");
        document.body.appendChild(downloadLink);
        
        if(navigator.msSaveOrOpenBlob){
            var blob = new Blob(['\ufeff', tableHTML], {
                type: dataType
            });
            navigator.msSaveOrOpenBlob( blob, filename);
        }else{
            downloadLink.href = 'data:' + dataType + ', ' + tableHTML;
            downloadLink.download = filename;
            downloadLink.click();
        }
    }

And the Html Code

<input type="button" value="Exportar a Excel" onclick="TableToExcel('TablaDinamica');" id="Exportar" style="font-size: 14px;" />

susandotmark · 2019-12-19T13:23:15Z

Dear nicolaskruchten/pivottable, This is a confirmation that we received your email and registered it as ticket #26248156. You can always reply to this email if you want to add additional information. Kinds regards, Team Dotmark

ichavchavadze · 2019-12-24T12:55:27Z

Hi!
Export to excel works, but my MS Office show me warning that it's not xls file. And it's true, there is html inside!

report.zip

susandotmark · 2019-12-24T12:55:40Z

Dear nicolaskruchten/pivottable, This is a confirmation that we received your email and registered it as ticket #26528206. You can always reply to this email if you want to add additional information. Kinds regards, Team Dotmark

Suganya0207 · 2020-03-19T12:07:36Z

Dear eusebiomarquesbenitez,
Export to Excel works. But i need to change the style in the excel. i.e Header portion should have different color and total portion should be different and rows/columns should be left aligned.

or somone guide me how to edit excel style? using eusebiomarquesbenitez code

susandotmark · 2020-03-19T12:08:09Z

Dear nicolaskruchten/pivottable, This is a confirmation that we received your email and registered it as ticket #32901886. You can always reply to this email if you want to add additional information. Kinds regards, Team Dotmark

nicolaskruchten mentioned this issue Oct 20, 2014

Suggestion for Export to excel #229

Closed

nicolaskruchten closed this as completed in f636484 Dec 21, 2014

nicolaskruchten reopened this Dec 21, 2014

nicolaskruchten closed this as completed Dec 21, 2016

nicolaskruchten mentioned this issue Mar 21, 2018

Export to TSV button not available for download #887

Closed

ishiDACo mentioned this issue Dec 1, 2020

Generate report result to document vulsrepo ishiDACo/vulsrepo#173

Closed

Export #58

Export #58

Comments

ghost commented Nov 6, 2013

nicolaskruchten commented Nov 6, 2013

ghost commented Nov 8, 2013

nicolaskruchten commented Nov 8, 2013

tgabrielle commented Nov 14, 2013

kwesley commented Nov 21, 2013

nicolaskruchten commented Nov 23, 2013

tgabrielle commented Nov 23, 2013

ogamot commented Nov 23, 2013

fastcatch commented Dec 2, 2013

nicolaskruchten commented Dec 2, 2013

cihadturhan commented Dec 13, 2013

romulosilvacardoso commented Dec 18, 2013

ken-muturi commented Dec 30, 2013

cihadturhan commented Dec 30, 2013

etwoss commented May 14, 2014

ken-muturi commented May 14, 2014

etwoss commented May 14, 2014

cihadturhan commented May 14, 2014

etwoss commented May 14, 2014

etwoss commented May 16, 2014

dipeshchauhan commented Oct 8, 2014

ashesnz commented Oct 8, 2014

gani4ibt commented Dec 4, 2014

nicolaskruchten commented Dec 21, 2014

brianalu commented Jul 10, 2015

nicolaskruchten commented Jul 10, 2015

brianalu commented Jul 10, 2015

nicolaskruchten commented Jul 10, 2015

LukeBrumfield commented Feb 5, 2016

nicolaskruchten commented Feb 12, 2016

GauravP77 commented May 30, 2016

meghbrume commented Jul 12, 2016

nicolaskruchten commented Dec 21, 2016

Bill-VA commented Nov 16, 2017

ghost commented Jan 1, 2018

susandotmark commented Jul 18, 2018

PRINCEHR commented Mar 18, 2019 • edited Loading

Guys, You can try this code

//In pivot.js

Hope this will help you guys!

f0rmig4 commented Apr 12, 2019

eusebiomarquesbenitez commented Dec 19, 2019

susandotmark commented Dec 19, 2019 via email

ichavchavadze commented Dec 24, 2019 • edited Loading

susandotmark commented Dec 24, 2019 via email

Suganya0207 commented Mar 19, 2020 • edited Loading

susandotmark commented Mar 19, 2020 via email

PRINCEHR commented Mar 18, 2019 •

edited

Loading

ichavchavadze commented Dec 24, 2019 •

edited

Loading

Suganya0207 commented Mar 19, 2020 •

edited

Loading