How to generate an Excel document with multiple worksheets from PHP?

Question

I want to generate an MS Excel file from PHP. I know one can do something like this:

header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-Disposition: attachment; filename=foo_bar.xls" );

But it will generate a file with just one Sheet. What I want is generating a file with multiple sheets. How can I do that? Maybe there's a third party library, but I haven't found too much.

Answer 1

Try looking at PHPExcel. This is a simple example that creates an Excel file with two sheets:

<?php
require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Something');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');

// 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', 'More data');

// Rename 2nd sheet
$objPHPExcel->getActiveSheet()->setTitle('Second sheet');

// 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');

Answer 2

If you mean like have your PHP script create an Excel file, write some stuff to it on any sheet, etc, then offer that up for the client to download, you can just use PHP's built-in COM extension. See: http://us2.php.net/manual/en/class.com.php for all sorts of examples. However, you will need Excel (or a clone like OpenOffice) installed on the server. If you don't, perhaps Mark Baker's answer above will work instead without it.

Answer 3

Solution

<?php
    require_once 'PHPExcel.php';
    require_once 'PHPExcel/IOFactory.php';
    //Update the multiple sheets in PHP excel
    $report_file = 'Report_' . date('Y-m-d') . '.xlsx';
    $report_file_exists  = 0;
    //If the file doesnot exist , create new otherwise append the data at last
    if (!file_exists($report_file)) {
        objPHPExcel = new PHPExcel();
    }
    else {
        $report_file_exists = 1;
        $objPHPExcel = PHPExcel_IOFactory::load($report_file);
    }
    $columns = [
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
   ];
    //Sheet details
    $sheet_details = [
        //1st sheet details
        0 => [
            'sheet_title'   => 'Products',
            'sheet_heading' => ['Article_Number','Name'],
            'sheet_data'    => ['1234','Pen']
        ],
        //2nd Sheet Details
        1 => [
            'sheet_title'   => 'Categories',
            'sheet_heading' => ['Category Id','Name'],
            'sheet_data'    => [123,'Accessories']
        ]
    ];                      
    $sheet_count = 0;
    $row = 1;
    $column = 0;
    while ($sheet_count <= count($sheet_details)) {
        $objWorkSheet = '';
        if ($report_file_exists == 0) {
            if ($sheet_count > 0) {
                $objWorkSheet = $objPHPExcel->createSheet($sheet_count);
            } 
            else {
                $objWorkSheet = $objPHPExcel->getActiveSheet();
            }
            $row = 1;
            $column = 0;
            foreach ($sheet_details[$sheet_count]['sheet_heading'] as $head) {
                $objWorkSheet->setCellValue($columns[$column] . $row, $head);
                $column++;
            }
       }
       else {
           $objPHPExcel->setActiveSheetIndex($sheet_count);
           $objWorkSheet = $objPHPExcel->getActiveSheet($sheet_count);
       }
       $row = $objWorkSheet->getHighestRow() + 1; //row count
       foreach ($sheet_details[$sheet_count]['sheet_data'] as $report_details) {
           $column = 0;
           foreach ($report_details as $data) {
                $objWorkSheet->setCellValue($columns[$column] . $row, $data);
                $column++;
            }
            $row++;
        }
        $objWorkSheet->setTitle($sheet_details[$sheet_count]['sheet_title']);
        $sheet_count++;
    }
    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
    $objWriter->save($report_file);
?>

Answer 4

Solution

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("creater");
$objPHPExcel->getProperties()->setLastModifiedBy("Middle field");
$objPHPExcel->getProperties()->setSubject("Subject");
$objWorkSheet = $objPHPExcel->createSheet();
$work_sheet_count=3//number of sheets you want to create
$work_sheet=0;
while($work_sheet<=$work_sheet_count){ 
    if($work_sheet==0){
        $objWorkSheet->setTitle("Worksheet$work_sheet");
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValue('A1', 'SR No. In sheet 1')
            ->getStyle('A1')
            ->getFont()
            ->setBold(true);
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
    }
    if($work_sheet==1){
        $objWorkSheet->setTitle("Worksheet$work_sheet");
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValue('A1', 'SR No. In sheet 2')
            ->getStyle('A1')
            ->getFont()
            ->setBold(true);
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
    }
    if($work_sheet==2){
        $objWorkSheet = $objPHPExcel->createSheet($work_sheet_count);
        $objWorkSheet->setTitle("Worksheet$work_sheet");
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValue('A1', 'SR No. In sheet 3')
            ->getStyle('A1')
            ->getFont()
            ->setBold(true);
        $objPHPExcel->setActiveSheetIndex($work_sheet)
            ->setCellValueByColumnAndRow($col++, $row++, $i++);//setting value by column and row indexes if needed
    }
    $work_sheet++;
}
$filename='file-name'.'.xls'; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache`
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');