7

Recently i have used phpExcel library to generate reports in excel format in cakephp. Every thing is working fine except pivot Tables.

I am using a master excel sheet (Which contain pivot table) to clone/generate other excel sheet. In newly generated sheet other information is looking fine but pivot table is not being generated (only names on header are being display. There is no filter options). Here is the code i am using. 

        $filename = WWW_ROOT."files/master_report_template/compliance_workflow_master_template.xlsx";
        $reportFileName = WWW_ROOT."files/documents/reports/compliance_workflow_template.xlsx";
        $this->PhpExcel->loadWorksheet($filename); 
        $this->PhpExcel->getActiveSheet()->setCellValue('B1',$this->request->data['fromDate']);
        $this->PhpExcel->getActiveSheet()->setCellValue('B2',$this->request->data['toDate']);
        $this->PhpExcel->getActiveSheet()->setCellValue('B3',date('D-M-y'));
        $row=6;
        foreach($repostData as $rows)
        {
            $col = 0;
            foreach($rows as $key =>$value)
            {   
                $this->PhpExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($col, $row, $value);
                $col++;
            }
            $row++;
        }
        $this->PhpExcel->save($reportFileName);
        $this->PhpExcel->output();

I thought that generated excel sheet would be the clone of Master excel sheet but both are not same (due to pivot tables). Is there any good tutorial or documentations to generate pivot table ?

Abhijeet Sharma
  • 173
  • 1
  • 2
  • 8
  • 1
    There is a PR for pivot tables on github, but it needs some additional work before it's ready for release because pivots with styling will fail to save, it only works if there is no styling in the defined pivot – Mark Baker Sep 02 '14 at 19:00
  • @MarkBaker user [vlasvlasvlas](http://stackoverflow.com/users/863505/vlasvlasvlas) is interested in the link to that "PR for pivot tables on github" (but can't post a comment himself). – Cimbali Jan 20 '15 at 19:34
  • It's [PR #226](https://github.com/PHPOffice/PHPExcel/pull/226): however, it only applies to OfficeOpenXML (.xlsx) files, and won't work (as I said previously) if there are any styles applied in the pivot table – Mark Baker Jan 20 '15 at 19:50

1 Answers1

1

Albeit this is an old issue, I'd like to give an answer: I had the same issue some months ago and finally decided that I need to do my own implementation (so this is self-advertisement..)

This solution is far from perfect - since I'm maybe the only person using it - but it is doing exactly what you describe: Writing raw data into one sheet of a "master file" allowing you to create reports:

https://github.com/svrnm/exceldatatables

svrnm
  • 1,036
  • 6
  • 17
  • 2
    Awesome this helped a lot. Small suggestion, the spreadsheets if saved won't auto calculate formulas upon opening. This can be trivially fixed. I am sending you a pull request, hopefully you can accept. – nawfal Apr 02 '16 at 06:39
  • Thanks for the suggestion, I'll look into this soon:-) – svrnm Apr 20 '16 at 19:42
  • I should tell you of all the few different php spreadsheet libraries I have tested this is the only one which worked. Do kudos. But I should say this is a very basic library only meant for this exact case. – nawfal Apr 21 '16 at 10:38