excelexport.php

<?php 
session_start(); 

// before serving, make sure phpspreadsheet library is added to vendor
//      --- composer require phpoffice/phpspreadsheet ---
// usage: php -S localhost:8000 excelexport.php

require 'vendor/autoload.php';

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

$host = "localhost";
$username = "root";
$password = "root";
$database = "database";
$dsn = "/Applications/MAMP/tmp/mysql/mysql.sock";
try  
{  
    //$connect = new PDO("mysql:host=$host; dbname=$database", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));  
    $connect = new PDO("mysql:unix_socket=$dsn; mysql:host=$host; dbname=$database", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));  
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
}  
catch(PDOException $error)  
{  
    die($error->getMessage());  
}  

// define number of columns of the table that will be exported
$table_columns = 95;

$spreadsheet = new Spreadsheet();
$sql = "SELECT * FROM table_name";
$result = $connect->query($sql);

$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
 
$spreadsheet->setActiveSheetIndex(0);

$activeSheet = $spreadsheet->getActiveSheet();

// set rows
$row_names = [
    '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'
];

// get export columns names
$sql_info = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'table_name' AND TABLE_SCHEMA = 'database'";
$sql_info_result = $connect->query($sql_info);
$export_columns = [];
while($single_info_column = $sql_info_result->fetch(PDO::FETCH_ASSOC)) {
    array_push($export_columns, $single_info_column['COLUMN_NAME']);
}

// max size of one row
$max_size = 25;
$current_column = 1;
$cell_index = 0;
$cell_size = 1;
$current_cell_focus_length = 0;
$current_cell_focus_position = 0;
$cells = [];

$cells_increment = 0;
$base_cell = 'A';

// iterate over row names and define cells that will be used for spreadsheet
while(count($cells) < $table_columns){

    // if last letter from alphabet is reached
    if($cell_index > 25){
        // increase cell size
        $cell_size++;
        // set current cell focused for iteration
        $cell_index = 0;
        // increase number of cells
        $cells_increment++;
    }

    if($cell_size > 1){
        $current_cell_focus = $row_names[$cells_increment-1];
        $current_cell       = $current_cell_focus.$row_names[$cell_index].$current_column;
        array_push($cells, $current_cell);
    } else {
        array_push($cells, $row_names[$cell_index].$current_column);
    }

    $cell_index++;
}

$count = 0;
// define cell values for spreadsheet
foreach($export_columns as $column){
    if(isset($cells[$count])){
        $activeSheet->setCellValue($cells[$count], $column);
        $count++;
    }
}

// set auto size for each cell (avoid overflow of text in spreadsheet cells)
foreach($cells as $c){
    $spreadsheet->getActiveSheet()->getColumnDimension(substr($c,0,-1))->setAutoSize(true);
}


$record_index = 2;
$col_index = 0;
$max_cell = 95;

// for each record, assign value to corresponding cell
while($record = $result->fetch(PDO::FETCH_ASSOC)) {

    $counter = 0;
    while($counter < $table_columns){
        $activeSheet->setCellValue(
            substr($cells[$counter], 0,-1).$record_index, 
            $record[$export_columns[$counter]]
        );

        $counter++;
    }

    $record_index++;
}

 
$filename = 'export.xlsx';
// header('Content-Type: application/vnd.ms-excel');
// header('Content-Disposition: attachment;filename="'. $filename);
// header('Cache-Control: max-age=0');
$Excel_writer->save($filename);
echo('Exported successfully');

// file will be saved in the same directory as file
?>