Member Avatar for AntonyRayan

Hi, I am new to this concept.

How to export data from database in EXCEL for various standards with "dynamic headings" .

  • 2 Contributors
  • 1 Reply
  • 421 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by broj1

Dani AI

Generated

Brief note: was right that a library makes this easy, but prefer the maintained successor to PHPExcel: PhpSpreadsheet. It is the current PHPOffice project for reading/writing Excel/CSV and is what new projects should target; migration guidance from PHPExcel to PhpSpreadsheet is available. Installation is simplest with Composer (composer require phpoffice/phpspreadsheet). PhpSpreadsheet on GitHub — migration docs. (github.com)

A practical flow for "dynamic headings" across standards:

  1. query the DB for the header labels (either query INFORMATION_SCHEMA/SHOW COLUMNS/DESCRIBE or fetch one sample row and use its keys),
  2. build the header row array, optionally inserting formulas (strings that start with '=' will be treated as formulas unless written explicitly),
  3. stream rows in batches to the sheet, and 4) send to the browser with the correct headers and ->save('php://output'). Minimal example (fill in connection/query logic and paging as needed):
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8','user','pass');

// get headers (example: use first row)
$stmt = $pdo->query("SELECT * FROM standards WHERE id = 1 LIMIT 1");
$first = $stmt->fetch(PDO::FETCH_ASSOC);
$headers = array_keys($first);

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray($headers, null, 'A1');

// write data in pages (example)
$row = 2;
$limit = 1000;
for ($offset = 0; ; $offset += $limit) {
    $data = $pdo->query("SELECT * FROM standards_data LIMIT $limit OFFSET $offset")->fetchAll(PDO::FETCH_ASSOC);
    if (!$data) break;
    foreach ($data as $r) {
        $sheet->fromArray(array_values($r), null, 'A' . $row++);
    }
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

The DB-schema step (DESCRIBE / INFORMATION_SCHEMA / SHOW COLUMNS) is how to determine runtime/standard-specific headers. Use the documented HTTP-output recipe when saving to php://output. (dev.mysql.com)

Troubleshooting & tips: write formulas as formulas when desired, or use setCellValueExplicit() with DataType when a header must look like a formula or must preserve leading zeros. For very large exports, disable formula pre-calculation, write in chunks (use fromArray() for bulk rows), enable cell caching or export CSV if formatting/formulas are not needed, and increase execution/memory limits for batch jobs. See the PhpSpreadsheet docs for data types, formula handling and writer options. (phpspreadsheet.readthedocs.io)

References: PhpSpreadsheet docs and samples linked above provide examples for streaming, data types, and migration.

Member Avatar for broj1

Use PHPExcel to create Excel file from the data you read from the database. Dynamic headers are just headers that contain text and formulae in them (the header changes if the information in the respective column changes). You have to build those formulas yourself.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.