CODEWITHSUNDEEP
phpmysql
Sachin
Sachin

Reputation: 1708

How to fetch column names with mysql data using phpexcel?

I am exporting mysql data to an excel file using phpexcel library. The code works fine. But the excel file created doesn't have column name. I want to fetch them as well. Is there any built-in method for it? What adjustment do i need to make my code? Here is my current working code:

<?php

require_once 'PHPExcel/classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);

$rowcount = 1;

while($row = mysqli_fetch_array($rs))
{
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $row['id']);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['title']); 
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, $row['date']);
    $rowcount++; 
} 

$filename = "backup-" . date("h.i.s.d-m-Y") . ".xls";
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=" . $filename);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

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

?>

Upvotes: 2

Views: 1848

Answers (3)

Dharmesh Goswami
Dharmesh Goswami

Reputation: 1265

Use mysqli_fetch_fields for getting the field names.

I have edited your code and add column names in code. It might help you.

<?php

require_once 'PHPExcel/classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);

$rowcount = 1;

$fieldinfo=mysqli_fetch_fields($rs);

foreach ($fieldinfo as $val)
{
   $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $val->id);
   $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $val->title); 
   $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, $val->date);
}

$rowcount = 2;

while($row = mysqli_fetch_array($rs))
{
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowcount, $row['id']);
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowcount, $row['title']); 
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowcount, $row['date']);
$rowcount++; 
} 

$filename = "backup-" . date("h.i.s.d-m-Y") . ".xls";
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=" . $filename);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

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

?>

Upvotes: 4

pspatel
pspatel

Reputation: 518

You need to change the code something like below to show the column name and data

//this is for showing the column name
$q = mysql_query("SHOW COLUMNS FROM table_name");
if (mysql_num_rows($q) > 0) {
    while ($row_q = mysql_fetch_assoc($q)) {
        $col='A';
        $objPHPExcel->getActiveSheet()->SetCellValue($col.$rowcount, $row_q['Field']);
        $col++;
    }
    $rowcount++;
}

//this is for showing coulmn data based in results
while($row_data1 = mysql_fetch_assoc($rs)){
    $col=0;
    foreach($row_data1 as $key=>$value){
        $objPHPExcel->getActiveSheet()->SetCellValueByColumnAndRow($col, $rowcount, $value);
        $col++;
    }
    $rowcount++;
}

Upvotes: 1

Rohit Kumar
Rohit Kumar

Reputation: 1958

If i am right , you were searching for something like below

Insert Title Row

   $f=0;
    $fChar='A';
    while($f<mysql_num_fields($rs)){
            $objPHPExcel->getActiveSheet()->setCellValue($fChar.'1', mysql_fetch_field($rs,$f++)->name);
            $objPHPExcel->getActiveSheet()->getStyle($fChar.'1')->applyFromArray(
            array(
                'fill' => array(
                            'type' => PHPExcel_Style_Fill::FILL_SOLID,
                            'color' => array('rgb'=>'E1E0F7'),
                            ),
                'font' => array(
                    'bold' => true,
                    )
                )   
            );


        $fChar++;
    }

Now insert values

$fC=2;
while($row=mysql_fetch_array($rs,MYSQL_BOTH)){
$fChar='A';
$f=0;
    while($f<mysql_num_fields($rs)){

        $objPHPExcel->getActiveSheet()->setCellValue($fChar++.''.$fC, $row[$f++]);
    }

$fC++;  
}

Upvotes: 1

Related Questions