Export to Excel

You can export MySQL table data to an Excel file (XLSX format) in PHP using the PhpSpreadsheet library. Here's a step-by-step guide on how to do it:

Step 1: Install PhpSpreadsheet

First, you'll need to install PhpSpreadsheet using Composer. If you haven't installed Composer, download and install it from getcomposer.org.

Run the following command in your project directory to install PhpSpreadsheet:

composer require phpoffice/phpspreadsheet

Step 2: Export MySQL Table to Excel

Here's an example code snippet to export MySQL table data to an Excel file:

<?php

require 'vendor/autoload.php'; // Include PhpSpreadsheet library

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Database connection

$servername = "localhost";

$username = "your_username";

$password = "your_password";

$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

// Select data from your table

$sql = "SELECT * FROM your_table";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    // Create a new Spreadsheet object

    $spreadsheet = new Spreadsheet();

    $sheet = $spreadsheet->getActiveSheet();

    // Add headers

    $column = 'A';

    while ($fieldinfo = $result->fetch_field()) {

        $sheet->setCellValue($column . '1', $fieldinfo->name);

        $column++;

    }

    // Add data

    $row = 2;

    while ($rowdata = $result->fetch_assoc()) {

        $column = 'A';

        foreach ($rowdata as $value) {

            $sheet->setCellValue($column++ . $row, $value);

        }

        $row++;

    }

    // Save the Excel file

    $writer = new Xlsx($spreadsheet);

    $filename = 'exported_data.xlsx';

    $writer->save($filename);

    // Download the file

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    header('Content-Disposition: attachment;filename="' . $filename . '"');

    header('Cache-Control: max-age=0');

    readfile($filename);

    unlink($filename); // Delete file after download

    exit;

} else {

    echo "No data found!";

}

$conn->close();

?>

Replace 'your_username', 'your_password', 'your_database', and 'your_table' with your MySQL credentials and table details.

This code fetches data from a MySQL table, creates an Excel spreadsheet using PhpSpreadsheet, adds the table data to the spreadsheet, saves it as 'exported_data.xlsx', and then initiates a download for the user. Finally, it deletes the file from the server after download.

Make sure the PHP script has write permissions in the directory where it is saved to generate the Excel file.