How to Export Data in Multiple Sheets in PHP

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

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 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.
    }
}

PHP exporting data to CSV in multiple sheets

CSV does not support sheets, so you would need to go with something a bit more powerful, like PHPExcel. New sheets can be created using $php_excel->createSheet(); method. You will get a PHPExcel_Worksheet instance that you can populate.

How to export data with multiple worksheet Yii2 ExportMenu

The extension has been built to only export data displayed on the client.
You need to write your own query/code for exporting large data sets.

An alternative is to use the yii2-export extension which uses PHP Excel library to export and directly reads data from dataProvider.

But exporting very large data-sets typically are done faster via direct queries rather than too much processing done to change to other formats.

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++;
    }

---

Related Topics