Ask Question

Menu


Need a Website Or Web Application Or Any Help In Code As Per Your Budget, Contact Us: +91 9437911966 (Whatsapp)

Export Excel document with multiple worksheets in PHP


Here we using 2 file for Export Excel document with multiple worksheets in PHP.

database.php

<?php
	$url='localhost';
	$username = "root";
	$password = "";
	$dbname = "school";
	$conn = mysqli_connect($url, $username, $password, $dbname);
?>

index.php

<?php
require_once 'database.php';
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
$result = mysqli_query($conn,"SELECT * FROM user_data");
$result1 = mysqli_query($conn,"SELECT * FROM salary_data");
/* Create new PHPExcel object*/
$objPHPExcel = new PHPExcel();

/* Create a first sheet, representing sales data*/
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Email');
$i=2;
while($row = mysqli_fetch_array($result)) {
	$name=$row['name'];
	$email=$row['name'];
	$objPHPExcel->getActiveSheet()->setCellValue("A$i",$name);
	$objPHPExcel->getActiveSheet()->setCellValue("B$i",$email);
$i++;
}



/*Rename sheet*/
$objPHPExcel->getActiveSheet()->setTitle('Emplyoee profile');

/* Create a new worksheet, after the default sheet*/
$objPHPExcel->createSheet();

/* Add some data to the second sheet, resembling some different data types*/
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Salary');
$i=2;
while($row1= mysqli_fetch_array($result1)) {
	$salary=$row1['salary'];
	$objPHPExcel->getActiveSheet()->setCellValue("A$i",$salary);
$i++;
}

/* Rename 2nd sheet*/
$objPHPExcel->getActiveSheet()->setTitle('Emplyoee Salary');

/* Redirect output to a client’s web browser (Excel5)*/
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="name_of_file.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');