Is it possible to create a new PHPExcel writer with a "blind" export from a database table?

Topics: Developer Forum, Project Management Forum
May 31, 2012 at 10:29 AM
Edited May 31, 2012 at 10:35 AM

Hi Mark and other contributors.

I was wondering if it is possible to do something like that. Assume that you know nothing about the table fields.Get the rows from the database with select count query, get the columns with show columns query

and then

for example:

 

$re=mysql_query('select count(*) from table');
$res=mysql_query('show  columns from table');
$r=mysql_query('select * from table');
$excel = new PHPExcel();
$excel->setActiveSheetIndex(0);
$worksheet = $excel->getActiveSheet();
$row=1;
$col=0;
 while($q=mysql_fetch_assoc($db,$r)){
 //i mean if there is a rowIterator like for the reader interfaces
for($row=1;$row< $rowCount;$row++){
for($col=o;$col< $colCount;$col++){
$worksheet->setValue("value goes here");
} }}
header('Last-Modified:' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: no-store, no-cache, must-revalidate');
 header('Cache-Control: post-check=0, pre-check=0', false);
 header('Pragma: no-cache');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='Report.xlsx'');
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');
$excel->disconnectWorksheets();
unset(\$excel);

 

                     
                                   would it be hard to do something like that or is there already a defined class or any methods for such an operation?

Jun 1, 2012 at 9:29 AM
Edited Jun 1, 2012 at 9:46 AM

Ok i am almost there but with a slight issue

here is my code

 

 

 

         error_reporting(E_ALL);
            $re=mysqli_query($dbco,"select count(*) from table") or die("Error:".  mysqli_error($dbco));

$res=mysqli_query($dbco,"show columns from table") or die("Error:".  mysqli_error($dbco));
$a1=mysqli_fetch_array($re,MYSQLI_NUM) or die("Error while fetching".mysqli_error($dbco));
$a2=  mysqli_num_rows($res) or die("Error while returning nums".mysqli_error($dbco));
                       
             $sq="SELECT * FROM table ";
                $result = mysqli_query($dbco, $sq) or die('ERROR IN GET QUERY: '.mysqli_error($dbco));
               
                $r=array();
              
                   
            
            $bindCount=array(1=>"A",2=>"B",3=>"C",4=>"D",5=>"E",6=>"F",7=>"G",8=>"H",9=>"I",10=>"J");
            
            
            require_once 'PHPExcel/Autoloader.php';
            require_once 'PHPExcel/IOFactory.php';                require_once 'PHPExcel.php';
            PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
               
                
                         $excel = new PHPExcel();
    $excel->setActiveSheetIndex(0);
    $worksheet = $excel->getActiveSheet();
    
 while($rows= mysqli_fetch_array($result,MYSQLI_NUM)){
                   $r[]=$rows;
               }
                                   for($row=0; $row<$a1.length; $row++){
                       for($col=0; $col<$a2.length; $col++){
                          for($bind=0; $bind<$bindCount.length; $bind++){    
                 $worksheet->getCell("{$bindCount[$bind]}.{$row++}")->setValue("{$r[$row][$col]}");
                        }
                       }
                                   }
               
                
                
                        
        header('Last-Modified:' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: no-store, no-cache, must-revalidate');
        header('Cache-Control: post-check=0, pre-check=0', false);
        header('Pragma: no-cache');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment;filename='Report.xlsx'");
    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    ob_end_clean();
    $objWriter->save('php://output');
    $excel->disconnectWorksheets();
    unset($excel);     
                     

 

 

 

In the generated excel file i get a

Parse error: syntax error, unexpected '}' in /opt/lampp/lib/php/PHPExcel/PHPExcel/Shared/PCLZip/pclzip.lib.php on line 5692

i assume because of the curly braces in ->getCell("{$bind}.{$row++}"). Am i correct?

Do i have to explicitly set a value for a cell with ' ' and not ""?

Is there a workaround for that?

 

P.S: I saw that there is graph support now in the newer version, Congrats for that.

Thanks for any help,regards George.