Exporting from database to excel, missing row.

Topics: Developer Forum, Project Management Forum, User Forum
Feb 28, 2013 at 4:14 PM
Edited Feb 28, 2013 at 4:18 PM
I come across the code below on this website, and have modified it slightly to show text instead of id's.
But now it seems to be missing a row. I would be so grateful if someone could help me out and show me where I have gone wrong.
<?php

/** require the PHPExcel file 1.0 */
    require '../../classes/PHPExcel.php';

/** Set Memory Limit 1.0 */
    ini_set("memory_limit","500M"); // set your memory limit in the case of memory problem

/** Caching to discISAM 1.0*/
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
$cacheSettings = array( 'dir'  => '/usr/local/tmp' // If you have a large file you can cache it optional
                      );
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

/** connection with the database 1.0 */
include 'db_log_conn.php';
/** Query 1.0 */

    $query = "SELECT id, sku, name, description, qty, busid, catid, locid FROM tlbitems";
    
    if ($result = mysql_query($query) or die(mysql_error())) {
/** Create a new PHPExcel object 1.0 */
   $objPHPExcel = new PHPExcel();
   $objPHPExcel->getActiveSheet()->setTitle('Logistics');
   $objPHPExcel->getActiveSheet()->getStyle("A1:H1")->getFont()->setBold(true);
   }  
  
/** Loop through the result set 1.0 */
    $rowNumber = 1; //start in cell 1
while ($row = mysql_fetch_assoc($result)) {       
  $col = 'A'; // start at column A       

    // returns title row
       if ( $rowNumber == 1 ){
        $headers = array_keys($row);
            foreach($headers as $header) {
            if($header == "busid") { $header = "Business"; }
            if($header == "catid") { $header = "Category"; }
            if($header == "locid") { $header = "Warehouse Location"; }
            if($header == "sku") { $header = "SKU"; }
            if($col != 'A') // ALL EXCEPT ID
                {
                    $header = ucfirst($header);
                }
            $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$header);
            $col++;
            }
        $rowNumber++;
      }else{ //returns content rows
       $col = 'A';
        foreach($row as $cell) {          
        if($col == 'F') // BUSINESS
            {
                $sql = "SELECT name FROM tlbbusiness WHERE id='$cell'";
                $result1 = mysql_query($sql);
                $row1 = mysql_fetch_array($result1);
                $cell = $row1['name'];
            }
        if($col == 'G') // CATEGORY
            {
                $sql = "SELECT name FROM tlbcategories WHERE id='$cell'";
                $result1 = mysql_query($sql);
                $row1 = mysql_fetch_array($result1);
                $cell = $row1['name'];
            }
        if($col == 'H') // WAREHOUSE LOCATION
            {
                $sql = "SELECT name FROM tlblocations WHERE id='$cell'";
                $result1 = mysql_query($sql);
                $row1 = mysql_fetch_array($result1);
                $cell = $row1['name'];
            }
        $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);      
        $col++;       
        } 
      $rowNumber++;       
      }
      
}



   
/** Create Excel 2007 file with writer 1.0 */
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Technical.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
exit;

?>
ive just noticed that if i initialise rowNumber to 2, instead of 1. all the data is exported but not the headers.
Any help?
Coordinator
Feb 28, 2013 at 4:22 PM
Edited Feb 28, 2013 at 4:23 PM
Your row number 1 is also a data row, not just headers... get rid of the else condition for your if ( $rowNumber == 1 ) test
Feb 28, 2013 at 5:21 PM
MarkBaker wrote:
Your row number 1 is also a data row, not just headers... get rid of the else condition for your if ( $rowNumber == 1 ) test
Thanks Mark, worked a charm.. :)
Feb 28, 2013 at 5:38 PM
MarkBaker wrote:
Your row number 1 is also a data row, not just headers... get rid of the else condition for your if ( $rowNumber == 1 ) test
Thanks Mark, worked a charm.. :)