DataList

Topics: Developer Forum, User Forum
Nov 22, 2010 at 6:58 PM

Hi guys,

I'm trying out this program and i'm having a little trouble.

My setup:

I'm using the current version (downloaded today) of PHPExcel.

I have a myphpadmin database and im extracting information from that to excel. (all works good)

I have a template and are inputting the data into the template.

Now, I have a data list First name Last Name Company ect..  and the information is being correctly inputted,


However, i created a data list so that the user (who ever uses it) will be able to sort ascending, descending ect.. and i eventually plan on using pivot tables to display certain data.


When the data is imported and saved to my computer, the data list fields doesn't work, and i can't sort, however the field names are still there.

Is there a way to select a certain range of values and create the data list? Whats the function to do this? Is it possible?

 

Hope this made sense.

Note: data list can be created by going to data ->list -> create list in excel.

Coordinator
Nov 23, 2010 at 7:50 AM

Can you provide a sample worksheet to demonstrate this problem

Nov 23, 2010 at 1:16 PM

Here both the files are. One is the template with the data list fields, and the other is the one that phpexcel exports without the correct formatting.

 

http://www.megaupload.com/?f=Y2Y7NODP

Nov 25, 2010 at 6:46 PM
Edited Nov 25, 2010 at 6:48 PM

Could anybody please help me?

The template has formatting which is lost when writing the data and saving it. Im using Excel5 format. Also the macros that I created within the template gets lost aswell, so i can't use a macro to fix the issue. PLease help!

Here is the code if it helps...

 

/** Error reporting */
            error_reporting(E_ALL);

            date_default_timezone_set('Europe/London');

            /** PHPExcel */
            require_once ('../php/classes/PHPExcel.php');

            $objPHPexcel = PHPExcel_IOFactory::load('../templates/excel/membership.xls');

            // redirect output to client browser
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="membership.xls"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
            $objWorksheet = $objPHPexcel->getActiveSheet();


            $curseurMax = $mysql->get_num_rows();

           

            $rset_members = $mysql->query($query_members);

            $num_rows = mysql_num_rows($rset_members);

            $loop_members = array();

     
            $counter = 2;

            while(($parse_members = $mysql->fetch_record($rset_members)) !== false)

            {
                $objWorksheet->setCellValueByColumnAndRow(0, $counter, $parse_members['company']);
                $objWorksheet->setCellValueByColumnAndRow(1, $counter, $parse_members['last_name']);
                $objWorksheet->setCellValueByColumnAndRow(2, $counter, $parse_members['first_name']);
                $objWorksheet->setCellValueByColumnAndRow(3, $counter, $parse_members['address']);
                $objWorksheet->setCellValueByColumnAndRow(4, $counter, $parse_members['city']);
                $objWorksheet->setCellValueByColumnAndRow(5, $counter, $parse_members['country']);
                $objWorksheet->setCellValueByColumnAndRow(6, $counter, $parse_members['postal_code']);
                $objWorksheet->setCellValueByColumnAndRow(7, $counter, $parse_members['work_phone']);
                $objWorksheet->setCellValueByColumnAndRow(8, $counter, $parse_members['fax']);
                $objWorksheet->setCellValueByColumnAndRow(9, $counter, $parse_members['email']);
                $objWorksheet->setCellValueByColumnAndRow(10, $counter, $parse_members['city']);
                $objWorksheet->setCellValueByColumnAndRow(19, $counter, $parse_members['member_type']);
               
                $counter+=1;
            
          

            }


            $objWriter->save('php://output'); 

 

Coordinator
Nov 27, 2010 at 9:52 PM

Macros are not supported within PHPExcel. Any feature of Excel that isn't supported is ignored when reading from a template file; so the macros aren't loaded, and so can't be written when you save the workbook. Other features that aren't currently supported include Pivot tables.

Nov 29, 2010 at 1:05 AM

Oh damn, thats sorta inconvenient.

Is there any other way to do this? Get a template and write the data to it saving the macros? Or any way to write the macro to save in excel? Perhaps there is another program to do this?

Coordinator
Nov 29, 2010 at 7:58 AM
mace519 wrote:

Is there any other way to do this? Get a template and write the data to it saving the macros? Or any way to write the macro to save in excel? Perhaps there is another program to do this?

 The only way that I know is to use PHPs COM interface, which also requires a copy of MS Excel running on the server... hence you also need a Windows server.