Master Excel Import/Export in Webman with PhpSpreadsheet

The article introduces using the PhpSpreadsheet library within the Webman PHP framework to read and write Excel files, providing a practical reference for developers.

Installation

Install the library via Composer by running the following command in the project root:

composer require phpoffice/phpspreadsheet

Excel Import

A controller class demonstrates how to handle an uploaded Excel file. It validates the file existence, checks supported extensions (xlsx, pptx, docx, pdf), ensures the file can be read, loads the first sheet, skips the first two rows, and converts the remaining rows into an array of user data. Errors such as missing files, unsupported formats, or empty data trigger exceptions. The resulting data structure is dumped for verification.

<?php
/**
 * @desc Excel import operation
 */
declare(strict_types=1);

namespace app\console\controller;

use PhpOffice\PhpSpreadsheet\Exception;
use support\Request;

class Excel {
    /**
     * @desc import
     * @param Request $request
     */
    public function import(Request $request) {
        $file = $request->file('file');
        if (empty($file) || !$file->isValid()) {
            throw new \Exception('Invalid file');
        }
        if (!in_array($file->getUploadExtension(), ['xlsx','pptx','docx','pdf'], true)) {
            throw new \Exception('Unsupported file format');
        }
        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        if (false === $reader->canRead($file->getRealPath())) {
            throw new \Exception('Illegal Excel file format');
        }
        $excel = $reader->load($file->getRealPath());
        $sheetList = $excel->getSheet(0)->toArray();
        $importSheetList = array_slice($sheetList, 2);
        if (empty($importSheetList)) {
            throw new \Exception('Data empty, please upload again');
        }
        $importData = [];
        foreach ($importSheetList as $sheet) {
            array_push($importData, [
                'user_id' => (string)$sheet[0],
                'username' => (string)$sheet[1],
                'create_time' => time(),
            ]);
        }
        var_dump($importData);
    }
}
?>

Excel Export

Another controller method shows how to generate an Excel file from database records. It queries user data, creates a new Spreadsheet, sets default row height, applies bold styling to the header row, defines column headers, adjusts column widths, and fills each row with explicit string values to preserve formatting. After writing the workbook to a memory buffer, it returns a JSON response containing the filename and a base64‑encoded data URI for download.

<?php
/**
 * @desc Excel export operation
 */
declare(strict_types=1);

namespace app\console\controller;

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use support\Request;
use support\Response;

class Excel {
    /**
     * @desc export Excel
     * @param Request $request
     * @return Response
     */
    public function import(Request $request) {
        $exportList = UserModel::where(['channel_id' => 10086])->order('id desc')->select();
        if ($exportList->isEmpty()) {
            throw new \Exception('No data');
        }
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getDefaultRowDimension()->setRowHeight(18);
        $sheet->getStyle("A1:J1")->getFont()->setBold(true);
        $sheet->setCellValue('A1', '用户账号');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '观看时长');
        $sheet->setCellValue('D1', '开始时间');
        $sheet->setCellValue('E1', '结束时间');
        $sheet->setCellValue('F1', '观看IP');
        $sheet->setCellValue('G1', '观看地区');
        $sheet->setCellValue('H1', '观看终端（版本号）');
        $sheet->setCellValue('I1', '事件');
        $sheet->setCellValue('J1', '场次ID');
        $sheet->getColumnDimension('A')->setWidth(15);
        $sheet->getColumnDimension('B')->setWidth(10);
        $sheet->getColumnDimension('C')->setWidth(10);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(25);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(40);
        foreach ($exportList as $key => $user) {
            $sheet->setCellValueExplicit("A" . ($key + 2), $user['user_id'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('B' . ($key + 2), $user['username'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('C' . ($key + 2), gmstrftime('%H:%M:%S', $user['duration']), DataType::TYPE_STRING)
                  ->setCellValueExplicit('D' . ($key + 2), $user['create_time'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('E' . ($key + 2), $user['update_time'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('F' . ($key + 2), $user['client_ip'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('G' . ($key + 2), $user['region'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('H' . ($key + 2), $user['browser'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('I' . ($key + 2), $user['event'], DataType::TYPE_STRING)
                  ->setCellValueExplicit('J' . ($key + 2), $user['session_id'], DataType::TYPE_STRING);
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        ob_start();
        $writer->save("php://output");
        $xlsData = ob_get_contents();
        ob_end_clean();
        return json([
            'filename' => '[开源技术]导出直播观看记录.xlsx',
            'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),
        ]);
    }
}
?>