To generate an excel file in core php we can use PHPExcel library.
download library from the following link here - Download PHPExcel library
Lets cut the chase and see how can we do it :
First of all extract the .zip folder of the library file which we have just downloaded and put it in the setup folder where we need to include.
Now, create a html form. In case we need to submit the data from the form and simultaneously save it in an excel format and download it.
For that create a form file form.html and add the bellow code.
<!DOCTYPE html>
<html>
<head>
<title>Generate Excel using PHP</title>
<script type="text/javascript">
function changeMessage() {
document.getElementById('msg').innerHTML='Sent and processed! Thanks '+document.frm1.txtName.value+'!';
}
</script>
</head>
<body>
<form action="process.php" method="post" name="frm1">
Your name: <input type="text" name="txtName" size="40"/> <br />
Say hello: <textarea name="txtMessage" rows="4" cols="80"></textarea><br />
<input type="submit" name="btnSubmit" value="Send!" onclick="changeMessage();" />
</form>
<div id="msg"></div>
</body>
</html>
In the above simple form we have two input fields "Name" and "Message". The form has action as "excel-process.php" and method as "post".
Afterwards, in our action file excel-process.php we will going to generate the excel file there. For the same add the below code in it.
<?php
date_default_timezone_set('Asia/Kolkata');
// include PHPExcel library and set its path accordingly.
require('../PHPExcel.php');
$objPHPExcel = new PHPExcel;
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$currencyFormat = '#,#0.## \;[Red]-#,#0.## \';
$numberFormat = '#,#0.##;[Red]-#,#0.##';
$objSheet = $objPHPExcel->getActiveSheet();
$objSheet->setTitle('My Form');
$objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(12);
$objSheet->getCell('A1')->setValue('Name');
$objSheet->getCell('B1')->setValue('Message');
$objSheet->getCell('A2')->setValue($_POST['txtName']);
$objSheet->getCell('B2')->setValue($_POST['txtMessage']);
$objSheet->getColumnDimension('A')->setAutoSize(true);
$objSheet->getColumnDimension('B')->setAutoSize(true);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="file.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
?>
When the form gets submitted our action file "excel-process.php" will get executed and the fields values gets in there in post parameters.
In this file code we have included our PHPExcel library file PHPExcel.php with its path using php require() method.
Then we created a new PHPExcel object and from this object we set default font, set default font size and then created an object of writer from PHPExcel_IOFactory as $objWriter.
The writer already created the first sheet for us, now we have to perform operation, for that we created an active sheet object as
$objSheet = $objPHPExcel->getActiveSheet();
Using the properties of the sheet we used some library functions and set the sheets Title, Font style and then the cells value using function set.
and finally using the writer object we saved the output file with the statement
$objWriter->save('php://output');
In this way you can manipulate the code for your use and can generate the excel file.
0 Comment(s)