CODEWITHSUNDEEP
php
Varinder Kaur
Varinder Kaur

Reputation: 43

Function to export a data report to excel in php with dynamic no of fields and rows

I am trying to develop a function for exporting the report contents to a xls file and giving users an option to have an xls file downloaded for the results they are seeing on webpage. Below is the code i have tried:

<?php
class export
{
 public function exportxls($cols,$values)
{
function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}
function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
}
function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
}
function xlsWriteLabel($Row, $Col, $Value) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
} 
// prepare headers information
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=\"export_".date("Y-m-d").".xls\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
header("Expires: 0");
// start exporting
xlsBOF();

$j=1;
$ret = array_map (
  function ($_) {return explode (',', $_);},
  explode (';', $values)
);




$colarray=explode(',',$cols);



$number=count($colarray);



for($i=0;$i<$number;$i++)
{
   xlsWriteLabel(0,$i,$colarray[$i]); 
}
foreach($ret as $key->$value)
{ 

xlsWriteLabel($j,$key,$value[$key]);
$j=$j+1;
}

xlsEOF();
}
}
?>

and here is the index file from which function is called:

<?php
include 'newxls.php';
$obj=new export();
$obj->exportxls("id,name,class","1,var,btech;2,man,mtech");
?>

Please do help me; I am not getting the required output in excel file. Thanks in advance

Upvotes: 1

Views: 3981

Answers (2)

Amit Kumar Sharma
Amit Kumar Sharma

Reputation: 262

Your code looking good except few changes needed

Change this code

foreach($ret as $key->$value)
{ 

 xlsWriteLabel($j,$key,$value[$key]);
 $j=$j+1;
}

Replace with this

foreach ($ret as $key => $value) {
    foreach($value as $k=>$v){
        xlsWriteLabel($j, $k, $v);                
    }            
     $j = $j + 1;
}

All remaining code looking fine.

Upvotes: 1

Manish Chauhan
Manish Chauhan

Reputation: 570

Try This,

$filename ="excelreport";
function exportexcel($fields = array(), $values = array()){

  foreach($fields as $field){
    $contents .= $field." \t";
  }

  $contents. = " \n";

  foreach($values as $value){
     $contents.= $value[0]. " \t";
     $contents.= $value[1]. " \t";                             
     $contents.= $value[2]. " \n";                             
  }
  return $contents;
}

header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=". $filename . date("Y-m-d-H-i") .".xls");
header("Cache-Control: no-cache, must-revalidate");
$contents = exportexcel(array("id,name,class"),array(array('1','test','btech'),array('2','man','mtech')));
echo $contents;

Upvotes: 0

Related Questions