PhpSpreadsheet Excel文件导出使用说明

<?php
namespace common\misc;

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

class LPhpSpreadsheet
{
    /**
     * 获取excel
     * 浏览器下载/在服务器保存
     * @param $title
     * @param $data
     * @param $columnMap
     * @param string $fileName
     * @param int $is_browserDown
     * @param int $width
     * @return string
     */
    public static function getExcel( $title, $data, $columnMap, $fileName = '', $is_browserDown = 1, $width = 0 )
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getProperties()
            ->setCreator("tmk")
            ->setLastModifiedBy("tmk")
            ->setTitle( $title )
            ->setSubject( $title )
            ->setDescription( "" )
            ->setKeywords( "" )
            ->setCategory( "" );

        array_unshift($data, $columnMap);

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($data, null, 'A1');

        // 设置列宽
        $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
        if ( empty($width) ) {
            for($i = 'A'; $i <= $toCol; $i++) {
                $sheet->getColumnDimension($i)->setAutoSize(true);
            }
            $sheet->calculateColumnWidths();
        } else {
            for($i = 'A'; $i <= $toCol; $i++) {
                $sheet->getColumnDimension($i)->setWidth($width);
            }
        }

        $writer = new Xlsx($spreadsheet);

        // 浏览器下载/在服务器保存
        if ( $is_browserDown ) {
            $fileName = empty($fileName) ? time() .rand(10000, 99999) .".xlsx" : $fileName;

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  // 告诉浏览器输出07Excel文件
//        header('Content-Type:application/vnd.ms-excel');  // 告诉浏览器将要输出Excel03版本文件
            header('Content-Disposition: attachment;filename=' .$fileName);  // 告诉浏览器输出浏览器名称
            header('Cache-Control: max-age=0');  // 禁止缓存

            $writer->save('php://output');
        } else {
            $fileName = empty($fileName) ? "/tmp/". time() . rand(10000, 99999) .".xlsx" : $fileName;

            $writer->save( $fileName );
        }

        // 清楚内存
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        return $fileName;
    }
}

<?php
/**
 * 数据库转为Excel
 * @param string $filePath
 * @return bool
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 */
public function actionTableToExcel($filePath = 'demo.sql')
{
    if (!file_exists($filePath)) {
        echo  "file is not exist: " .$filePath ."\r\n";
        return true;
    }

    $fileInfo = file_get_contents($filePath);
    preg_match_all("/CREATE TABLE[\d\D]*;[\r]?\n/U", $fileInfo, $matches);

    if (count($matches[0]) == 0) {
        echo "未匹配到数据\r\n";
        return true;
    }

    // 准备excel数据
    $excel_data = [];

    foreach ($matches[0] as $item)
    {
        // 表数据
        $table_data = [];
        // 表头数据
        $table_name_data = [];
        // 表列数据
        $table_column_data[] = ["字段", "数据类型与长度", "字段备注"];

        $explodedArray = explode("\n  ", $item);

        foreach ($explodedArray AS $key => $value)
        {
            if ($key == 0 || $key == (count($explodedArray) -1)) {
                /*分析表名及注释*/
                // 分析表名
                if ($key == 0) {
                    preg_match("/`.+`/", $value, $table_name);
                    $table_name = str_replace("`", "", $table_name[0]);

                    array_push($table_name_data, $table_name);
                }

                // 分析表名注释
                if ($key == count($explodedArray) -1) {
                    preg_match("/COMMENT='.*';/", $value, $table_name_comment);
                    if (!empty($table_name_comment[0])) {
                        $table_name_comment = str_replace("COMMENT='", "", $table_name_comment[0]);
                        $table_name_comment = str_replace("';", "", $table_name_comment);
                    } else {
                        $table_name_comment = "";
                    }

                    array_push($table_name_data, $table_name_comment);
                }
            } else {
                /*分析字段名及注释*/
                preg_match("/^`.+`/", $value, $column_name);
                if (!isset($column_name[0])) {
                    continue;
                }

                $column_name = str_replace("`", "", $column_name[0]);

                preg_match("/` [\S]+/", $value, $column_type);
                $column_type = str_replace("` ", "", $column_type[0]);

                preg_match("/COMMENT '.+'/", $value, $column_comment);
                if (!empty($column_comment[0])) {
                    $column_comment = str_replace("COMMENT '", "", $column_comment[0]);
                    $column_comment = str_replace("'", "", $column_comment);
                } else {
                    $column_comment = "";
                }

                $table_column_data[] = [$column_name, $column_type, $column_comment];
            }
        }

        array_push($table_data, ["表名", "表名备注"], $table_name_data);
        $table_data = array_merge($table_data, $table_column_data);
        $excel_data = array_merge($excel_data, $table_data, [[]], [[]]);

        unset($table_data, $table_name_data, $table_column_data);
    }

    /*导出为excel*/
    $data = $excel_data;
    $title = explode('.',$filePath)[0];

    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $spreadsheet->getProperties()
        ->setCreator("vip")
        ->setLastModifiedBy("vip")
        ->setTitle( $title )
        ->setSubject( $title )
        ->setDescription( "" )
        ->setKeywords( "" )
        ->setCategory( "" );

    $sheet = $spreadsheet->getActiveSheet();
    $sheet->fromArray($data, null, 'A1');

    // 设置单元格样式
    foreach ($sheet->getRowIterator() as $row)
    {
        $cellIterator = $row->getCellIterator();
        foreach ($cellIterator as $cell) {
            $txt = $cell->getValue();
            if ($txt == "表名" || $txt == "表名备注" || $txt == "字段" || $txt == "数据类型与长度" || $txt == "字段备注") {
                // 设置字体加粗
                $cell->getStyle()->getFont()->setBold(true);
                // 设置背景色
                $cell->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('e9e9e9');
                // 设置行高
                $sheet->getRowDimension($cell->getRow())->setRowHeight(20);
            }
        }
    }

    // 设置列宽
    $toCol = $sheet->getColumnDimension($sheet->getHighestColumn())->getColumnIndex();
    for($i = 'A'; $i <= $toCol; $i++) {
        $sheet->getColumnDimension($i)->setAutoSize(true);
    }
    $sheet->calculateColumnWidths();

    // 保存
    $fileName = "./" .$title .".xlsx";
    // 保存为Excel
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save( $fileName );
    // 保存为pdf
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Tcpdf($spreadsheet);
    $writer->save( $fileName );

    // 清除内存
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);

    echo "成功：" .$title .".xlsx\r\n";
    return true;
}