How to Generate an Excel Document with Multiple Worksheets from PHP

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

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

How to export data in multiple sheets in php

You can use PHPSpreadhsheet. This is what the currently deprecated PHPExcel library has been continued as.

---

According to the docs. You will need to use the Composer package manager to install this library on your machine, by running the following command at the location of installation -

composer require phpoffice/phpspreadsheet

Include the library in your .php file in the following way -

<?php

    // load the classes provided by PHPSpreadSheet
    require 'vendor/autoload.php';

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    $spreadsheet = new Spreadsheet();

    // code to create and use worksheet goes here
?>

To create/add new worksheets you can use this function -

$spreadsheet->createSheet();

You can create multiple worksheets by using the above command in a for() or while() loop.

To get the worksheet and edit it, you can fetch it by index in the following way -

$spreadsheet->getSheet(1);

The above command will fetch the second sheet from the workbook (since the worksheets are always indexed from "0").

Exporting data to excel (multiple sheets) using php

i would suggest you to use PHPExcel library.supports variety of formats, can do visual formatting and is easy to use.
You can find more about it at their webpage: http://phpexcel.codeplex.com/

You can do a lot more of course, reading excel files, setting visual styles, creating plots, expressions and lot more.

you can even use fgetcsv http://php.net/manual/en/function.fgetcsv.php

Generate excel file containing multiple sheets using PhpExcel

use ob_clean() before, php://output as (CORRECT):

ob_clean();
$objWriter->save('php://output');

You may using like (WRONG):

  $objWriter->save('php://output');
ob_clean();

Creating Multiple Excel Sheets using Pure PHP - Possible?

You can create the file from scratch - for which you should know the file format and since office 2007 this standard is open.

You can refere http://en.wikipedia.org/wiki/Office_Open_XML to get started!

PHPExcel - creating multiple sheets by iteration

You dont need call addSheet() method. After creating sheet, it already add to excel. Here i fixed some codes:

    //First sheet
    $sheet = $objPHPExcel->getActiveSheet();

    //Start adding next sheets
    $i=0;
    while ($i < 10) {

      // Add new sheet
      $objWorkSheet = $objPHPExcel->createSheet($i); //Setting index when creating

      //Write cells
      $objWorkSheet->setCellValue('A1', 'Hello'.$i)
                   ->setCellValue('B2', 'world!')
                   ->setCellValue('C1', 'Hello')
                   ->setCellValue('D2', 'world!');

      // Rename sheet
      $objWorkSheet->setTitle("$i");

      $i++;
    }

how to export to excel file with multiple sheets wherein data is from database using mysqli

Increment $row at the while loop. I hope it will work.

Edited:
I am going to mention your slice of code where I have added $row++.

I have added $row++ at the end of while loop and it is working for me as I tested it at my computer. I implemented your code by creating table like your table. I also tested different type of table in this code and that worked perfect.

$q = $conn->query("SELECT * FROM student");
if (mysqli_num_rows($q) > 0) {
    foreach ($heading as $h) {
        $objPHPExcel->getActiveSheet()->setCellValue($colH . $rowNumberH, $h);
        $objPHPExcel->getActiveSheet()->getColumnDimension($colH)->setWidth(25);
        $colH++;
    }
    $row = 2;
    while ($row_q = mysqli_fetch_assoc($q)) {
        $i = 0;
        foreach ($row_q as $key => $value) {
            if ($key == 'location')
                continue;
            $objPHPExcel->getActiveSheet()->setCellValue($columns[$i] . $row, $row_q[$key]);
            $i++;
        }
        $row++;  // $row is added only in your code.
    }
}

PhpExcel creates multiple worksheets

You basically create a PHPExcel object which has already en empty sheet with index 0.

Then you create a new sheet with index 1.

Then you write all your stuff to sheet with index 0 and add the picture on second sheet (newly created).

This should solve your problem:

$objPHPExcel->setActiveSheetIndex(1); 

Note, that you still create a new sheet, even the first one already exists.
If you want to use already existing worksheet, just do the following:

Remove:

   $objWorkSheet = $objPHPExcel->createSheet();            
   $objPHPExcel->setActiveSheetIndex(0); 

And do all the stuff with already existing sheet.

$sheet = $objPHPExcel->getSheet(0);
$sheet->setCellValue('D'.'1', 'Firm')//Etc all the stuff.

Give the drawing the same sheet:

$objDrawing->setWorksheet($sheet);

---

Related Topics