Creating excel report from mysql database using phpexcel

Question

I have a sql query that displays records of attendance per employee so what I want now is generate an excel report from it but I don't really understand how does phpexcel works and I also used in drag and drop reporting tools like crystal reports. Could someone help to make it in phpexcel. Here's my complete code from mysql database:

try{
$con = new PDO("mysql:host=$host;dbname=$db",$user,$pass);
$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$con->exec('SET NAMES "utf8"');
}
catch(PDOException $e){
echo $e->getMessage();
exit();
}
//print first employee table
try{
$query = $con->prepare("SELECT * FROM emptb WHERE Department = :dept AND  SectionName = :section AND LineName = :line ORDER BY id ASC");
$query->bindParam(':dept',$dept);
$query->bindParam(':section',$section);
$query->bindParam(':line',$line);
$query->execute();
}
catch(PDOException $e){
echo $e->getMessage();
exit();
}
while($row = $query->fetch())
{
echo <<<_END
ID: <input type='text' value='$row[EmpID]' readonly='readonly'/> Employee:  <input type='text' value='$row[Lastname], $row[Firstname]' readonly='readonly'/> <p/>
Section: <input type='text' value='$row[SectionName]' readonly='readonly'/>  Line: <input type='text' value='$row[LineName]' readonly='readonly'/><p/>
_END;

try{
$qry = $con->prepare("SELECT * FROM attendance WHERE EmpID = :id AND  ValidDate BETWEEN DATE('2015-08-01') AND DATE('2015-08-30') GROUP BY ValidDate  ORDER BY ValidDate ASC");
$qry->bindParam(':id',$row['EmpID']);
$qry->execute();
}
catch(PDOException $e){
echo $e->getMessage();
exit();
}
echo "<table>";
while($subrow = $qry->fetch())
{
echo <<<_END
<tr>
<td>$subrow[ValidDate]</td>
<td>$subrow[TimeIn]</td>
<td>$subrow[LunchOut]</td>
<td>$subrow[LunchIn]</td>
<td>$subrow[TimeOut]</td>
</tr>
_END;
}
echo "</table>";
}

How did you try to generate an excel file using phpexcel and what was the error message you received or the unexpected behaviour you encountered? — Shadow
– Shadow, Commented Oct 6, 2015 at 7:54
I didn't tried it yet but i know how to generate excel file but not using phpexcel and its only in tabular format. — Newbie N
– Newbie N, Commented Oct 6, 2015 at 7:58
It will not have any formatting and only phpexcel is applicable for that. — Newbie N
– Newbie N, Commented Oct 6, 2015 at 8:08
@NewbieN: You need to try first and if you encounter any issues, then we can help you along. — Shadow
– Shadow, Commented Oct 6, 2015 at 8:17

Mark Baker · Accepted Answer · 2015-10-06 08:24:35Z

You need to replace

echo "<table>";
while($subrow = $qry->fetch())
{
    echo <<<_END
    <tr>
    <td>$subrow[ValidDate]</td>
    <td>$subrow[TimeIn]</td>
    <td>$subrow[LunchOut]</td>
    <td>$subrow[LunchIn]</td>
    <td>$subrow[TimeOut]</td>
    </tr>
_END;
}
echo "</table>";

with something like:

$objPHPExcel = new PHPExcel();
$row = 1;
while($subrow = $qry->fetch())
{
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['ValidDate']);
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['TimeIn']);
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['LunchOut']);
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['LunchIn']);
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $subrow['TimeOut']);
    $row++;
}
$excelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('myFileName.xlsx');

You'll probably need to play with it a bit to convert dates/times to MS Excel serialized timestamps, and apply format masks but that's the basics of creating an Excel file from a database query using PHPExcel

Dinesh Belkare · Accepted Answer · 2015-10-06 10:11:24Z

1

Create and download excel file or report using phpExcel

First of all download excel-report.zip and extract it.
You will find index.php in root and one more folder.
Open index.php and take a look at the code.
Code itself is self descriptive and well commented.
You can add column heading and records.
You can also add color in particular cell or entire row.
If you want to set bold font for particular cell or row,it is also given in code.
Width of any column in excel file is adjustable.I have set it to auto adjust.

answered Oct 6, 2015 at 10:11

Dinesh Belkare

6318 silver badges24 bronze badges

Collectives™ on Stack Overflow

Creating excel report from mysql database using phpexcel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related