general_channel/src/Helpers/ExcelHelper.php

<?php

namespace General\Helpers;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * excel 工具类
 */
class ExcelHelper
{
    /**
     * web端导出csv文件
     * @param array $column_names 列名
     * @param array $data 导出数据内容
     * @param string $filename 导出文件名
     */
    public static function exportWebFileCsv(array $column_names, array $data, string $filename)
    {
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . $filename . ".csv");
        $column_names = collect($column_names)->map(function ($item) {
            return "\"" . mb_convert_encoding($item, "GBK", "UTF-8") . "\"";
        })->all();
        echo implode(",", $column_names);
        echo "\r\n";
        foreach ($data as $item) {
            $rows = collect($item)->map(function ($row) {
                return "\"" . mb_convert_encoding(is_numeric($row) && strlen($row) > 10 ? "'" . $row : $row, "GBK", "UTF-8") . "\"";
            })->all();
            echo implode(",", $rows);
            echo "\r\n";
        }
        exit();
    }

    /**
     * 导出csv到服务器
     */
    public static function saveFileCsv(array $column_names, array $data, string $file_name, string $path)
    {
        $file_path = $path . '/' . $file_name;
        if (file_exists($file_path)) {
            unlink($file_path);
        }
        $fp = fopen($file_path, "a");
        $column_names = collect($column_names)->map(function ($item) {
            return mb_convert_encoding($item, "GBK", "UTF-8");
        })->all();
        fputcsv($fp, $column_names);
        $limit = 1000;
        $calc = 1;
        foreach ($data as $item) {
            $calc++;
            if ($limit == $calc) {
                ob_flush();
                flush();
                $calc = 0;
            }
            $rows = collect($item)->map(function ($row) {
                return mb_convert_encoding(is_numeric($row) && strlen($row) > 10 ? "'" . $row : $row, "GBK", "UTF-8");
            })->all();
            fputcsv($fp, $rows);
        }
        fclose($fp);
    }


    protected $excel_service;

    public function __construct()
    {
        $this->excel_service = new Spreadsheet();
    }

    /**
     * 保存Excel格式数据表 需要composer require phpoffice/phpspreadsheet
     * @param string $title 
     * @param array $header
     * @param array $data
     * @param string $file_name 
     * @param string $path 
     */
    public function setExcelData(string $title, array $header, array $data, int $sheet_index = 0)
    {
        if ($sheet_index > 0) {
            $this->excel_service->createSheet($sheet_index);
        }
        $this->excel_service->setActiveSheetIndex($sheet_index);
        $sheet = $this->excel_service->getActiveSheet();
        $sheet->setTitle($title);
        $column_index = 1;
        foreach ($header as $value) {
            $sheet->getColumnDimensionByColumn($column_index)->setAutoSize(true);
            $sheet->setCellValueByColumnAndRow($column_index, 1, $value);
            $column_index++;
        }
        $row = 2;
        foreach ($data as $val) {
            $column_index = 1;
            foreach ($val as $item) {
                $sheet->setCellValueByColumnAndRow($column_index, $row, is_numeric($item) && strlen($item) > 10 ?  "'" . $item : $item);
                $column_index++;
            }
            $row++;
        }
    }

    public function saveExcelFile(string $file_name, string $path)
    {
        if (!$path) {
            // Redirect output to a client’s web browser (Xlsx)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename=' . $file_name . ".xlsx");
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0

            $writer = IOFactory::createWriter($this->excel_service, 'Xlsx');
            $writer->save('php://output');
            //return true;
        } else {
            $writer = IOFactory::createWriter($this->excel_service, 'Xlsx');
            $writer->save($path . '/' . $file_name);
        }
    }

    public function saveExcelData(array $header, array $data, string $file_name, string $path)
    {
        $this->setExcelData(substr($file_name, 0, 30), $header, $data);
        $this->saveExcelFile($file_name, $path);
    }

    public function saveExcelDataToMultiSheet(array $param, string $file_name, string $path = '')
    {
        foreach ($param as $item) {
            $this->setExcelData($item['title'], $item['header'], $item['data']);
        }
        $this->saveExcelFile($file_name, $path);
    }
}