PHPExcel_Reader_PDO

Jul 21, 2008 at 10:40 AM
Hello!
I would like to discuss new Reader class from Database (PHPExcel_Reader_PDO). Or, maybe somebody has written it already. Then upload your class on server.
Let’s imagine, that any SQL- query is correspond to the database View. This imagine View is very similar to Excel file. The same columns and rows both in View as in Excel file. So my PHPExcel_Reader_PDO will  take SQL-command (or prepared statement) instead of file name, read data from DB and resume filled and formatted PHPExcel object. This will be very useful data export mechanism for simplest tasks.
If anybody has done something like PHPExcel_Reader_PDO, upload your code please.

Coordinator
Jul 22, 2008 at 5:19 PM
This should not be too hard to implement without creating a reader for it. Also check  where we are discussing a data dumper which copies database data in Excel.
Jul 23, 2008 at 11:54 AM
Unfortunly I have not found discussion about database dump in Excel.
I’m talking about Reader like this:

//initing query. anyway we did it every time
$pdo= new PDO('addr db port user pass');
$q= $pdo->query(“select id, name, name2 form emp”);

//now lets imagine that we need to dump the query result into Excel. The easest way is to use PHPExcel with new reader
//read data
$reader= Excel_IOFactory::getReader('PDO');
$excel= $reader->load($q);

//write data
$writer= Excel_IOFactory::getWriter($excel, ‘Excel2007’);
$writer->write(’employes.xlsx’)

And result is excel file with formatted data:
id      name             name2       (header is bolded and centered)
 1    Nikolay            Valuev          (cells slyled depending on PDO column data type money to money, date to date and so on)
 2    Mike                Tyson

Best regards
alexey_baranov
Aug 1, 2008 at 8:35 AM
Well, this is my Reader class. It support
any non-UTF-8 Encoding!
And provides multi-query workbook.
PDO Query and prepared statment.

See sample bellow.


class PHPExcel_Reader_PDO implements PHPExcel_Reader_IReader{
    private $_encoding;
    
    public function getEncoding(){
        return $this->_encoding;
    }
    public function setEncoding($encoding){
        $this->_encoding= $encoding;
    }
    public function load($st, &$appendTo= null){
        // Initialisations
        if ($appendTo){
            $sheet= $appendTo->createSheet();
        }
        else{
            $appendTo= new PHPExcel();
            $sheet= $appendTo->getActiveSheet();
        }
        //filling headers
        for($c=0; $c < $st->columnCount(); $c++){
            $meta= $st->getColumnMeta($c);
                $this->printHeader($sheet->getCellByColumnAndRow($c, 1), $this->getEncoding()?iconv($this->getEncoding(), 'UTF-8', $meta['name']):$meta['name'], $meta);
        }
        //filling data
        $r=2;
        while($rA=$st->fetch()){
            for($c=0; $c< $st->getColumnMeta($c); $c++){
                $this->printCell($sheet->getCellByColumnAndRow($c, $r), $this->getEncoding()?iconv($this->getEncoding(), 'UTF-8', $rA[$c]):$rA[$c], $st->getColumnMeta($c));
                $cc=1;
            }
            $r++;
        }
        return $appendTo;
    }
    protected function printHeader(PHPExcel_Cell &$cell, $data, $meta){
        $cell->setValue($data);
    }
    protected function printCell(PHPExcel_Cell &$cell, $data, $meta){
        $cell->setValue($data);
    }
}




Sample 1 (tasted on PostgreSQL 8.3.3, windows-1251 encoding):

$pdo = new PDO(dsn,usr,pwd);
$st= $pdo->query("select * from pg_catalog.pg_type limit 2");
$st2= $pdo->prepare("select * from pg_catalog.pg_type limit 20 offset ?");
$st2->execute(array(3));

$reader= new PHPExcel_Reader_PDO();
$reader->setEncoding('windows-1251');
$excel= $reader->load($st);
$reader->load($st2, $excel);

$writer= PHPExcel_IOFactory::createWriter($excel, 'Excel2007Ex');
$writer->write('sample1.xlsx');


Very simple! The point is to implement data formatting dependend on $meta in printHeader(), printCell() functions. Something like

class PHPExcel_Reader_PDOEx extends PHPExcel_Reader_PDO{
    protected function printHeader(PHPExcel_Cell &$cell, $data, $meta){
        swith($meta['type']){
            'money':
            $cell->setValue($data, $format);
            break;
            'text':
            ...
            ...
        }
    }
    protected function printCell(PHPExcel_Cell &$cell, $data, $meta){
        swith($meta['type']){
            'money':
            $cell->setValue($data, $format);
            break;
            'text':
            ...
            ...
        }
    }
}


alexey_baranov
Feb 12, 2014 at 10:06 AM
<html>
<body>
<?php

$con = mysql_connect('localhost', 'root', 'xxx');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
mysql_select_db("kasy", $con);
$sql="SELECT DISTINCT ProgCode, Program FROM exam ORDER BY  `exam`.`Program` ASC "  ;
$result = mysql_query($sql); 
 
$con = mysql_connect('localhost', 'root', 'xxx');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
mysql_select_db("kasy", $con);
$sql="SELECT DISTINCT Sessi from exam ORDER BY  `exam`.`Sessi` ASC "  ;
$result1 = mysql_query($sql); 

$con = mysql_connect('localhost', 'root', 'xxx');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
mysql_select_db("kasy", $con);
$sql="SELECT DISTINCT Intake from exam ORDER BY  `exam`.`Intake` ASC "  ;
$result2 = mysql_query($sql); 

?>



<form name="myform" method="GET">
<b>Select Program </b> :    <select name="ProgCode">
                 <option value="">-Please Select-</option>
<?php while($row = mysql_fetch_array($result))
{

        ?>
    <option value="<?php echo $row['ProgCode']?>"><?php echo $row['ProgCode']?> &nbsp &nbsp <?php echo $row['Program']?></option>
<?php
}
?>  
</select>
<br>    
<b>Select Session </b> &nbsp;&nbsp;:    <select name="Sessi">
                 <option value="">-Please Select-</option>
<?php while($row1 = mysql_fetch_array($result1))
{
?>
    <option value="<?php echo $row1['Sessi']?>"><?php echo $row1['Sessi']?></option>
<?php
}
?>  
</select>
<br>
<b>Select Intake </b>&nbsp &nbsp :  <select name="Intake">
                 <option value="">-Please Select-</option>
<?php while($row2 = mysql_fetch_array($result2))
{
?>
    <option value="<?php echo $row2['Intake']?>"><?php echo $row2['Intake']?></option>
<?php
}
?>  
</select>       

    <br><input name="BtnSubmit" type="submit" value="Submit">

<br>

    </form>


<?php
$con = mysql_connect('localhost', 'root', 'xxx');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
   
$a = $_GET['Intake'];
$b = $_GET['ProgCode'];
$c = $_GET['Sessi'];


   
mysql_select_db("kasy", $con);
$query2=("SELECT DISTINCT(SubCode) FROM exam where Intake='$a' and ProgCode='$b' and Sessi='$c'")  ;
$query2testing = mysql_query($query2);  

try {
    $db = new PDO('mysql:host=localhost;dbname=kasy;charset=utf8', 'root', 'xxx');
} catch (PDOException $e) {
    echo $e->getMessage();
}
 
//get the SubCodes
$stmt = $db->query("SELECT DISTINCT(SubCode) FROM exam where Intake='$a' and ProgCode='$b' and Sessi='$c'");
$row_count = $stmt->rowCount();

 
//generate pivot sql statement
$sql = "SELECT Nama,Intake,Sessi,matricNo, ";
$dynamic_fields = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $dynamic_fields[] = $row['SubCode'];
    $sql .= "MAX(CASE SubCode when '{$row['SubCode']}' then grade end) AS '{$row['SubCode']}'";
    if ($row_count > 1) {
        $sql .=',';
    }
    $row_count--;
}
$sql .= " FROM exam where Intake='$a' and ProgCode='$b' and Sessi='$c' GROUP BY Nama ";
//echo $sql;



while ($row2 = mysql_fetch_assoc($query2testing))
{   
    $pixx =explode(" ", $row2['SubCode']);
    foreach ($pixx as $subject) {
     $rezz .= '<th>'.$subject.'</th>'; 
    } 
} 
echo '<table border=1 bordercolor=FFCC00>

 <tr>
 <th> No </th>
 <th> Nama </th>
 <th> MatricNo </th>
 <th> Intake </th>
 <th> Semester </th>
 '.$rezz.'

 ';
 
$i=1;

//SELECT Nama, MAX(CASE SubCode when 'BM' then grade end) 
//AS BM,MAX(CASE SubCode when 'Sejarah' then grade end) AS Sejarah,MAX(CASE SubCode when 'KH' then grade end) AS KH FROM exam GROUP BY Nama

//echo $sql;
$pivot = $db->query($sql);
while ($row = $pivot->fetch(PDO::FETCH_ASSOC)) {
    $display= "<tr>";
    $display .= "<td>$i</td>";  
    $display .= "<td>".$row['Nama'] . PHP_EOL ."</td>";
    $display .= "<td>".$row['matricNo'] . PHP_EOL ."</td>";
    $display .= "<td>".$row['Intake'] . PHP_EOL ."</td>";
    $display .= "<td>".$row['Sessi'] . PHP_EOL ."</td>";
    foreach ($dynamic_fields as $field) {
        $display .= "<td><center>" . $row[$field];
    }
    $display .= '<br/>';
 $display .= "</tr>";
 echo $display;
  $i++;
  }
      ?>

    
This is my full coding, can you tell me how to generate to excel..
special thanks if you can give the full code + button Download to Excel from this code to generate to excel.