admin_api/app/Http/helper/ExcelHelper.php

<?php


namespace App\Http\helper;


class ExcelHelper
{

    public static $styleFormat = [];

    /**
     * @see \PHPExcel_Style_NumberFormat
     */
    public static function setStyleFormat($format)
    {
        self::$styleFormat = $format;
    }

    /**
     * 导出
     * @see    https://www.cnblogs.com/tujia/p/11358096.html
     * @param array $titles 标题，一维数组，可传map或单纯标题
     * @param array $dataArray 数据，二维数组，可传map或单纯数据
     * @param string $filename 文件名，要带后缀
     * @param string $bigTitle 居中加粗的大标题，默认为空
     * @param array $extra 扩展数据
     * @return file
     */
    public static function export(array $titles, $dataArray, $filename, $bigTitle = '', $extra = [])
    {
        set_time_limit(0);
        ini_set('memory_limit', '512M');

        // 后缀
        $suffix = substr($filename, strrpos($filename, '.'));
        empty($titles) && die('标题数组不能为空！');
        empty($dataArray) && die('数据数组不能为空！');
        !in_array($suffix, ['.xls', '.xlsx']) && die('文件名格式错误！');

        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
        $cacheSettings = array('memoryCacheSize ' => '512MB');
        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $oExcel = new \PHPExcel();
        $oExcel->setActiveSheetIndex(0);
        $sheet = $oExcel->getActiveSheet();

        // 设置列数据格式
        if (!empty(self::$styleFormat)) {
            $fields = array_keys($titles);
            foreach (self::$styleFormat as $field => $formatCode) {
                $offset = array_search($field, $fields);
                $col = chr(65 + $offset);
                $sheet->getStyle($col)->getNumberFormat()->setFormatCode($formatCode);
            }
        }

        // 行索引
        $rowIndex = $bigTitle != '' ? 2 : 1;

        $chr = [
            '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',
            'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        ];

        // 设置大标题
        if ($bigTitle != '') {
            $sheet->mergeCells('A1:' . $chr[count($titles) - 1] . '1');
            $sheet->getStyle('A1')->applyFromArray([
                'font' => ['bold' => true],
                'alignment' => ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]
            ]);
            $sheet->setCellValue('A1', $bigTitle);
        }


        // 设置标题 A1 B1 C1 ....
        $colIndex = 0;
        $fieldsMap = [];
        foreach ($titles as $key => $title) {

            $fieldsMap[] = $key;
            $sheet->setCellValue($chr[$colIndex] . $rowIndex, $title);
            $colIndex++;
        }

        // 设置内容 A1 B1 C1 ....   A2 B2 C2 ....
        $rowIndex++;

        foreach ($dataArray as $key => $value) {
            foreach ($fieldsMap as $colIndex => $field) {
                if (strrpos($field, '|') !== false) {
                    $temp1 = explode('|', $field);
                    $pos = strrpos($temp1[1], '.');
                    $pos === false && $pos = strlen($temp1[1]);
                    $temp2 = [];
                    $temp2[0] = substr($temp1[1], 0, $pos);
                    $temp2[1] = substr($temp1[1], $pos + 1);
                    $val = $value[$temp1[0]];
                    //$val = self::$temp2[0]($extra, $temp2[1], $val);
                    $val = call_user_func_array(array('\common\helpers\ExcelHelper', $temp2[0]), array($extra, $temp2[1], $val, $value));
                } else {
                    $val = $field ? $value[$field] : $value;
                }
                $sheet->setCellValue($chr[$colIndex] . $rowIndex, $val);
            }
            $rowIndex++;
        }
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        if ($suffix == '.xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } else {
            header('Content-Type: application/vnd.ms-excel');
        }
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header("Content-Transfer-Encoding: binary");
        header("Pragma: no-cache");
        $oWriter = \PHPExcel_IOFactory::createWriter($oExcel, 'Excel2007');
        $oWriter->save('php://output');
        $oExcel->disconnectWorksheets();
        exit;
    }

    /**
     * 导出
     * @see    https://www.cnblogs.com/tujia/p/5999806.html
     * @param array $titles 标题，一维数组，可传map或单纯标题
     * @param array $dataArray 数据，二维数组，可传map或单纯数据
     * @param string $filename 文件名，要带后缀
     * @param array $extra 扩展数据
     * @return file
     */
    public static function exportSimple(array $titles, $dataArray, $filename, $extra = [])
    {
        // 后缀
        $suffix = substr($filename, strrpos($filename, '.'));
        empty($titles) && die('标题数组不能为空！');
        empty($dataArray) && die('数据数组不能为空！');
        !in_array($suffix, ['.xls', '.xlsx', '.csv']) && die('文件名格式错误！');

        // 导出准备
        set_time_limit(0);
        ini_set('memory_limit', '512M');

        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition: attachment; filename=' . $filename);
        if ($suffix == '.xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($suffix == '.xls') {
            header('Content-Type: application/vnd.ms-excel');
        } elseif ($suffix == '.csv') {
            header('Content-Type: application/vnd.ms-excel; charset=gb18030');
        }
        header("Content-Transfer-Encoding: binary");
        header("Pragma: no-cache");

        $isCsv = ($suffix == '.csv');
        $fieldsCount = count($titles);
        if ($isCsv) {
            echo mb_convert_encoding(implode(',', array_values($titles)), 'gb18030') . "\n";
        } else {
            echo '<table>';
            echo '<tr>';
            foreach ($titles as $key => $value) {
                echo sprintf('<td>%s</td>', $value);
            }
            echo '</tr>';
        }
        foreach ($dataArray as $key => $value) {
            $i = 0;
            $isCsv == false && print('<tr>');
            foreach ($titles as $field => $title) {
                if (strrpos($field, '|') !== false) {
                    $temp1 = explode('|', $field);
                    $pos = strrpos($temp1[1], '.');
                    $pos === false && $pos = strlen($temp1[1]);
                    $temp2 = [];
                    $temp2[0] = substr($temp1[1], 0, $pos);
                    $temp2[1] = substr($temp1[1], $pos + 1);
                    $val = $value[$temp1[0]];
                    //$val = self::$temp2[0]($extra, $temp2[1], $val);
                    $val = call_user_func_array(array('\common\helpers\ExcelHelper', $temp2[0]), array($extra, $temp2[1], $val, $value));
                } else {
                    $val = $field ? $value[$field] : $value;
                }
                if ($isCsv) {
                    echo mb_convert_encoding($val . ($i == $fieldsCount - 1 ? "\n" : ','), 'gb18030');
                } else {
                    if (isset(self::$styleFormat[$field])) {
                        echo sprintf("<td style='mso-number-format:\"%s\";'>%s</td>", self::$styleFormat[$field], $val);
                    } else {
                        echo sprintf('<td>%s</td>', $val);
                    }
                }
                $i++;
            }
            $isCsv == false && print('</tr>');
        }
        $isCsv == false && print('</table>');
        exit;
    }

    public static function extra($extra, $extra_key, $val, $row)
    {
        $arr = ArrayHelper::getValue($extra, $extra_key, []);
        return ArrayHelper::getValue($arr, $val, '');
    }

    public static function dateIsEmpty($extra, $extra_key, $val, $row)
    {
        return strtotime($val) > 1000 ? $val : '';
    }

    public static function toFixed($extra, $extra_key, $val, $row)
    {
        return (string)sprintf("%.{$extra_key}f", floatval($val));
    }

    public static function dateFormat($extra, $extra_key, $val, $row)
    {
        return date('Y-m-d H:i:s', $val / 1000);
    }

    public static function trim($extra, $extra_key, $val, $row)
    {
        return str_replace(["\r", "\n", ","], ["", "", "，"], $val);
    }

    public static function shopNameIsEmpty($extra, $extra_key, $val, $row)
    {
        return !empty($val) ? $val : '个人发布';
    }

    public static function extraConcat($extra, $extra_key, $val, $row)
    {
        $arr = explode('-', $extra_key);
        foreach ($arr as $key => $value) {
            $val .= ArrayHelper::getValue($extra[$value . 'Options'], $row[$value], '');
        }
        return $val;
    }

}