Making pivot tables

Topics: User Forum
Sep 3, 2009 at 9:23 AM

Is it possible to make pivot tables with php excel?

Why does phpexcel writer delete my already created pivot table in Office 2007 or Office 2003?

Coordinator
Sep 3, 2009 at 12:29 PM

At present, PHPExcel has no support for either reading or writing pivot tables, so that the pivot isn't read from your "already created" workbook, and the writer is therefore blissfully aware of its existence and cannot even try to write it.

Support for pivot tables is planned for the future

Feb 1, 2011 at 3:40 PM

so maybe is it difficult to create an excel pivot. but : how about editing the source of an existing excel file that already has a pivot that will autorefresh on open ? all that is there to do, is to create another sheet instead of the original one which has the original source.

to make myself clear: we have a file test.xlsx (or xls >=2003 version). in the file we have a sheet "pivottable" which represent the pivot table with the source "Sheet1!$A$1:$M$65536".

so all we have to do is either to delete the Sheet1 sheet and recreate it, or just to erase the existing data and repopulate the values.

the sheet "pivottable" from my example has checked the option "auto refresh pivot when document opens". the theory works. i use it for the moment with ms sql dts. i delete the sheet and recreate it.

this will not create a pivot, but you may use an already existing template.

Feb 18, 2011 at 4:14 PM

@yo9gnc I thought of your solution just recently. However when i implemented it, PHPExcel saves the modified template but the pivot table is lost.

My guess is that since it's unable to read pivot tables, it cannot write them either for the entire file is loaded into memory before written out again (not just one sheet).

The code i have so far just opens the template file and writes it out to the browser. My excel file only has 2 sheets:- Sheet1 has the pivot table and Sheet2 has the data-source.

Any ideas for a work-around?

<?php
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

define('TEMPLATE_PATH', 'templates');
$template_name = 'report_template';
$template_file = TEMPLATE_PATH . '/' . $template_name . '.xlsx';

// Load template
if (!file_exists($template_file)) {
	exit("Template does not exist.");
}

// Open Excel File
$objPHPExcel = PHPExcel_IOFactory::load($template_file);

// Save Excel 2007 file
// redirect output to client browser
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename={$template_name}");
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output'); 

Feb 21, 2011 at 12:26 PM
Edited Feb 21, 2011 at 12:59 PM

if you will only run the following script, you`ll see that the pivot informations are lost.  because phpexcel doesn`t know how to write any other data then it is built to work with.

<?php
//the template file has sheet2 - a pivot table, sheet1 - the source of the pivot.
require_once("../Classes/PHPExcel.php"); $objPHPexcel = PHPExcel_IOFactory::load('templatefile.xls'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel2007'); $objWriter->save('resultfile.xlsx'); ?> //in this case we will have both sheets in resulfile.xlsx but sheet2 will be just some text with any other information lost when saved.

i was just wondering if it was easyer to develop this aspect instead the whole creating pivot thing.

P.S. same thing in perl with  Spreadsheet::ParseExcel and Spreadsheet::ParseExcel::SaveParser;

Feb 21, 2011 at 3:18 PM

@yo9gnc I have a workaround! (for windows)

We can use the Microsoft Office Primary Interop Assemblies dll (Excel.Application) to open excel, refresh the datasource and then save the updated excel file to disk. I hope this works for you too.

<?php

// Start Excel
$excel = new COM("Excel.Application") or die ("Could not load Excel.Application");

// Make Excel visible.
$excel->Application->Visible = 1; 

// Open  workbook
$Workbook = $excel->Workbooks->Open($template_file) or die("Could not open $template_file");

// Refresh all pivot tables
$Workbook->RefreshAll();

// Save updated excel file out to disk somewhere
$Workbook->SaveAs($updated_template_file);

// Close all instances of excel:
$Workbook->Close(false);
unset($Workbook);

$excel->Workbooks->Close();
$excel->Quit();
unset($excel);
?>

Primary Interop Assemblies

Coordinator
Feb 21, 2011 at 3:32 PM

@islandblaze

If you can use Microsoft Office Primary Interop Assemblies, then surely you have no need to use PHPExcel at all?

Coordinator
Feb 21, 2011 at 3:43 PM
yo9gnc wrote:

if you will only run the following script, you`ll see that the pivot informations are lost.  because phpexcel doesn`t know how to write any other data then it is built to work with.

was just wondering if it was easyer to develop this aspect instead the whole creating pivot thing.

The PHPExcel Readers use exactly the same methods to populate the PHPExcel object as you would use within a script to create/edit the content of that object, so it is actually harder to develop reader capabilities to load pivot table without the create/edit options. The PHPExcel object itself doesn't have any way of knowing whether it is being created/populated by a Reader object, or by a user script.

Jul 25, 2011 at 8:18 AM
Edited Jul 25, 2011 at 9:03 AM

Hi everybody 

I have a question, because I have the same issue: what about VB Code? what if I writte a Macro (or make one with the assistant), and put a "button" in the sheet with the pivot table? Can PHPExcel read and writte back a macro, or any vb code? 

Update: Sorry, I just saw that that is not possible (yet)

Thanks,

Al.

Jul 25, 2011 at 8:25 AM

Hi all

 

You could do it like this. First you make xml spreadsheet. Then you read spreadsheet with Microsoft Office Primary Interop Assemblies and save it like xls or xlsx. That's all.

 

Regards

Jul 26, 2011 at 2:31 PM

or you may have the VBA (macro) already stored in personal.xlsb and then just run it ( or press some predefined keys). the problem is that i needed it to automatically generate xls(x)/reports and send it in emails.

Nov 17, 2011 at 12:40 PM

Hi all,

is there any update on this? Does anyone have an estimation of when the pivot table functionality will be added to PHPExcel?

Thank you in advance

Aug 7, 2012 at 11:27 AM
Edited Aug 7, 2012 at 11:27 AM

Hi all,

 

i really need manipulate a excel with a pivot table, i know is not implemented yet in PHPExcel and i trying the interop workaround, but my instance of excel can't read the template.xlsx.

 

what can i do?

 

Regards.

 

PS: have this problem another solution? maybe another library or else? i cannot found anything.

Aug 7, 2012 at 2:46 PM
Edited Aug 7, 2012 at 2:50 PM

nevermind what i said "can`t you use the pivot separatelly in a file with the source being the file you generate ?".i`m tired :)

Aug 9, 2012 at 10:53 AM
Hi

You can try making xml spreadsheet. There u just need to enter pivot template. How do get template of pivot? Just save some spreadsheet in microsoft excel as xml spreadsheet 2003. Than u can with any editor just copy a part with pivot table. Than u can implement that part as u whish in php.

Regards


From: [email removed]
To: [email removed]
Date: Tue, 7 Aug 2012 06:46:40 -0700
Subject: Re: Making pivot tables [PHPExcel:67827]

From: yo9gnc
can`t you use the pivot separatelly in a file with the source being the file you generate ?
Read the full discussion online.
To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)
To start a new discussion for this project, email PHPExcel@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Aug 9, 2012 at 12:33 PM

Instead of just suggesting alternative tools, utilities, libraries, etc for handling pivot tables in Excel files, why does nobody ever consider that PHPExcel is Open Source. That means you can look at the code, see how it works; change the code; add new features to the code. It means that you can write the code necessary to handle pivot tables yourselves, perhaps even submit it back for inclusion in future versions of PHPExcel. We've made this even easier now by moving all the source to github.

Instead of relying on one or occasionally two people writing all these features in their spare time, and criticising us because we can't provide everything that everyone wants instantly

Aug 10, 2012 at 7:35 AM
Edited Aug 10, 2012 at 1:06 PM

Hi all,

 

I'll try and tell you Joesun99, many thanks!

 

EDIT* How can i export the pivot table to xml? the plug in tell me ist not possible * 

 

EDIT2* I cant export my pivot table to xml, and can do interop work properly... any tip? i feel lost

 

(sorry about my bad english)

 

Hi Mark,

i'm not complaining about the current PHPExcel Features, this is an awesome library, i just have a problem and im asking for help, im sorry if you missundersand me.

 

Regards.

Apr 26, 2013 at 1:18 PM
Mark - answer is quite simple. I'm personally simply way too dumb to handle something that complex :)

Which causes me to have great deal of admiration to people such as yourself.

I've been using phpexcel little bit over the years. While not quite critical, it is handy operationally for such horrible programmers like myself. Thank you for all your hard work.