How do I create a variable length excel sheet

Topics: User Forum
Feb 15, 2011 at 3:46 PM

I'm completely new to this

I'm using phpexcel 1.7.5 and want to achieve the following.

I'm pulling data down from a mysql database using a form which then displays the total of the results on my site, but I want to be able to offer an xls download of the data.

The problem I've got is the form can display a range of data rows, with a fixed number of columns, depending on the date range selected.

I'll try and show in an example of how my data is stored on the database.

Date:          Column 1:     Column 2:     Column 3:     Column 4:     Notes:
20/01/10     123.45          432.45          1234.23        4321.34        test notes
23/02/10     432.54          876.76          9834.23        1980.93
28/02/10     457.32          9867.23        1239.23        7653.23        notes test
08/03/10     4321.56        654.34          5654.54        2345.23        I'm a teapot

And the select is either 1 or a range of dates.

I want the xls to display it pretty much as above, but clueless how to achieve it using a variable number of rows.

If someone could point me in the right direction, I'll figure the formatting out etc after, just need the basics of how to do it please.

Many thanks

usandstuff

Coordinator
Feb 15, 2011 at 9:57 PM

// connection with the database 
$dbhost = "localhost"; 
$dbuser = "root"; 
$dbpass = ""; 
$dbname = "database"; 
 
mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 
 
// require the PHPExcel file 
require 'Classes/PHPExcel.php'; 
 
// simple query 
 
$query = "SELECT id FROM users ORDER by id DESC"; 
 
if ($result = mysql_query($query) or die(mysql_error())) { 
    // Create a new PHPExcel object 
   $objPHPExcel = new PHPExcel(); 
   $objPHPExcel->getActiveSheet()->setTitle('List of Cities'); 
 
   // Loop through the result set 
    $rowNumber = 1; 
    while ($row = mysql_fetch_row($result)) { 
       $col = 'A'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
   } 
   // Save as an Excel BIFF (xls) file 
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
 
   header('Content-Type: application/vnd.ms-excel'); 
   header('Content-Disposition: attachment;filename="myFile.xls"'); 
   header('Cache-Control: max-age=0'); 
 
   $objWriter->save('php://output'); 
   exit(); 
} 
echo 'a problem has occurred... no data retrieved from the database'; 

alternatively:

// Loop through the result set 
$rowNumber = 1; 
while ($row = mysql_fetch_row($result)) { 
    $objPHPExcel->getActiveSheet()->fromArray(array($row),NULL,'A'.$rowNumber++); 
} 

To add a heading row.

$rowNumber = 1;
$headings = array('Name','EMail','Phone'); 
$objPHPExcel->getActiveSheet()->fromArray(array($headings),NULL,'A'.$rowNumber);  

$rowNumber++  
// Loop through the result set 
while ($row = mysql_fetch_row($result)) {
    $col = 'A';
    foreach($row as $cell) {
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
       $col++;
    }
    $rowNumber++; 
} 
 

 

 

Feb 16, 2011 at 5:46 AM

Thanks for that mark, I'll have a play tonight and see what happens :)

Feb 17, 2011 at 6:15 PM
Edited Feb 17, 2011 at 8:30 PM

that works a treat for one excel file :)

Now I need to work out how to swap X&Y so data populates down the rows instead of across the colums since there are 50+ fields in the database I want to pull in.

 

Wohoo, sorted that ok, so now to work out formatting to make it look something like.