Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Generate Excel file using php with PHPExcel library

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.75k
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: