Excel file is not opening while export data in excel, but working fine in localhost

27 Views Asked by At

I face some problem in excel file. I am exporting excel file with data. This code is working fine in localhost. But in live server, the excel file is not opening and display message as

Excel cannot open the file "users_download_.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

My controller code is:

<?php

declare(strict_types=1);

namespace App\Controller\Admin;

use App\Controller\Admin\AppController;
use Cake\Datasource\ConnectionManager;
use Cake\Utility\Hash;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Cake\Http\CallbackStream;

class UsersController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();
        $this->loadComponent('Paginator');
    }

    public function exportUsers()
    {
        // Create a new spreadsheet
        $spreadsheet = new Spreadsheet();
        // Add value in a sheet inside of that spreadsheet. 
        // // (It's possible to have multiple sheets in a single spreadsheet)
        $sheet = $spreadsheet->getActiveSheet();
        // styles:
        $from = "A1";
        $to = "J1";
        $spreadsheet->getActiveSheet()->getColumnDimension("B")->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension("C")->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension("D")->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension("E")->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension("F")->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension("G")->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension("H")->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension("I")->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension("J")->setWidth(15);
        $spreadsheet->getActiveSheet()->getStyle("$from:$to")->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle("$from:$to")->getFont()->setSize(14);
        $spreadsheet
            ->getActiveSheet()
            ->getStyle('A1:J1')
            ->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('ff6a98fc');
        // styles end

        $sheet->setCellValue('A1', 'Sr.No.');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Email');
        $sheet->setCellValue('D1', 'Mobile');
        $sheet->setCellValue('E1', 'Total steps');
        $sheet->setCellValue('F1', 'Age group');
        $sheet->setCellValue('G1', 'Centre');
        $sheet->setCellValue('H1', 'State');
        $sheet->setCellValue('I1', 'Country');
        $sheet->setCellValue('J1', 'Gender');

        $connection = ConnectionManager::get('default');
        $users_list = $connection->execute(
            "SELECT
                fname,
                lname,
                email,
                agegroup_id,
                agegroups.NAME AS age_group_name,
                center,
                centremaster.centre AS centre_name,
                states.name AS state_name,
                countries.name AS country,
                gender,
                parent_email,
                mobile,
                user_name,
                parent_mobile,
                (SELECT SUM(steps) FROM steps WHERE steps.users_id=users.id) AS total_steps
            FROM
                `users`
                LEFT JOIN agegroups ON agegroups.id = users.agegroup_id
                LEFT JOIN centremaster ON centremaster.`id` = users.centreid
                LEFT JOIN countries ON countries.`id` = users.country_id
                LEFT JOIN states ON states.`id` = users.stateid
                "
        )->fetchAll('assoc');

        // $centres_list = [];
        foreach ($users_list as $key => $user) {
            $keyCell = $key + 2;
            if ($user['agegroup_id'] == 1) {
                // SSE CHILDREN
                $sheet->setCellValue('A' . $keyCell, $key + 1);
                $sheet->setCellValue('B' . $keyCell, $user['user_name']);
                $sheet->setCellValue('C' . $keyCell, $user['parent_email']);
                $sheet->setCellValue('D' . $keyCell, $user['parent_mobile']);
                $sheet->setCellValue('E' . $keyCell, $user['total_steps'] ?: 0);
                $sheet->setCellValue('F' . $keyCell, $user['age_group_name']);
                $sheet->setCellValue('G' . $keyCell, $user['centre_name']);
                $sheet->setCellValue('H' . $keyCell, $user['state_name']);
                $sheet->setCellValue('I' . $keyCell, $user['country']);
                $sheet->setCellValue('J' . $keyCell, $user['gender']);
            } else {
                $sheet->setCellValue('A' . $keyCell, $key + 1);
                $sheet->setCellValue('B' . $keyCell, $user['fname'] . " " . $user['lname']);
                $sheet->setCellValue('C' . $keyCell, $user['email']);
                $sheet->setCellValue('D' . $keyCell, $user['mobile']);
                $sheet->setCellValue('E' . $keyCell, $user['total_steps'] ?: 0);
                $sheet->setCellValue('F' . $keyCell, $user['age_group_name']);
                $sheet->setCellValue('G' . $keyCell, $user['centre_name']);
                $sheet->setCellValue('H' . $keyCell, $user['state_name']);
                $sheet->setCellValue('I' . $keyCell, $user['country']);
                $sheet->setCellValue('J' . $keyCell, $user['gender']);
            }
        }

        $writer = new Xlsx($spreadsheet);
        // ↓↓ Added new code from here in the eariler sample code
        // Save the file in a stream
        $stream = new CallbackStream(function () use ($writer) {
            $writer->save('php://output');
        });
        $filename = 'users_download_' . date('Y-m-d') . time();
        $response = $this->response;
        // Return the stream in a response
        return $response->withType('xlsx')
            ->withHeader('Content-Disposition', "attachment;filename=\"{$filename}.xlsx\"")
            ->withBody($stream);
    }
}

Any help will be appreciate. Thank you.

0

There are 0 best solutions below