<?php
namespace app\common\helper;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use think\Exception;
class Excel
{
    // 導(dǎo)出
    public function outPut($data, $columns, $table = '導(dǎo)出文件')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // 設(shè)置第一欄的標(biāo)題
        foreach ($columns as $k => $v) {
            $sheet->setCellValue($k . "1", $v['title']);
        }
        //第二行起 設(shè)置內(nèi)容
        $baseRow = 2; //數(shù)據(jù)從N-1行開(kāi)始往下輸出 這里是避免頭信息被覆蓋
        foreach ($data as $key => $value) {
            foreach ($columns as $k1 => $v1) {
                $i = $key + $baseRow;
                $sheet->setCellValue($k1 . $i, $value[$v1['field']]);
            }
        }
        $writer = new Xlsx($spreadsheet);
        $filename = $table . date("Y-m-d", time()) . '_' . time() . '.xlsx';
        $writer->save('./excel/' . $filename);
        return '/excel/' . $filename;
    }
    // 導(dǎo)入
    public function importExcel($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
    {
        try {
            $file = iconv("utf-8", "gb2312", $file);
            if (empty($file) OR !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }
            $objRead = IOFactory::createReader('Xlsx');
            if (!$objRead->canRead($file)) {
                $objRead = IOFactory::createReader('Xls');
                if (!$objRead->canRead($file)) {
                    throw new \Exception('只支持導(dǎo)入Excel文件！');
                }
            }
            /* 如果不需要獲取特殊操作，則只讀內(nèi)容，可以大幅度提升讀取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel對(duì)象 */
            $obj = $objRead->load($file);
            /* 獲取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);
            //$currSheet = $obj->getSheetByName($sheet);      // 根據(jù)名字
            if (isset($options['mergeCells'])) {
                /* 讀取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }
            if (0 == $columnCnt) {
                /* 取得最大的列號(hào) */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原邏輯，循環(huán)時(shí)使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }
            /* 獲取總行數(shù) */
            $rowCnt = $currSheet->getHighestRow();
            $data = [];
            /* 讀取內(nèi)容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;
                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId = $cellName . $_row;
                    $cell = $currSheet->getCell($cellId);
                    if (isset($options['format'])) {
                        /* 獲取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 記錄格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }
                    if (isset($options['formula'])) {
                        /* 獲取公式，公式均為=號(hào)開(kāi)頭數(shù)據(jù) */
                        $formula = $currSheet->getCell($cellId)->getValue();
                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }
                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻轉(zhuǎn)處理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }
                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }
                if ($isNull) {
                    unset($data[$_row]);
                }
            }
            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }
}

return [
    // 導(dǎo)入的表格標(biāo)題
    "bidding" => [
        "stock_no" => "編號(hào)",
        "price" => "價(jià)格",
        "mobile" => "手機(jī)",
        "nickname" => "姓名"
    ]
];
// 格式化指定列數(shù)據(jù)(默認(rèn)第一行表頭)
public static function formattingCells(array $data, array $cellConfig)
{
    $res = array_values($data);
    // 表頭
    $header = $res[0];
    $cellKeys = [];
    foreach ($header as $key => $value) {
        foreach ($cellConfig as $k => $v) {
            if ($value == $v) {
                $cellKeys[$key] = $k;
            }
        }
    }
    if (count($cellKeys) != count($cellConfig)) {
        throw new Exception('表格不完整');
    }
    // 需要添加過(guò)濾
    $temp = [];
    for ($i = 1; $i <= count($res) - 1; $i++) {
        foreach ($cellKeys as $m => $n) {
            $temp[$i][$n] = $res[$i][$m];
        }
    }
    return array_values($temp);
}

// 導(dǎo)入表格，上傳接口
public function importExcel()
{
    $upload_file = $_FILES['files']['tmp_name'];
    $input = $this->input;
    // ID
    $id = isset($input['id']) ? $input['id'] : 0;
    // 默認(rèn)取第一工作表
    $excelData = (new Excel())->importExcel($upload_file, 0);
    // 取Excel字段
    $config = config('excel_export.bidding');
    $price_offer = Excel::formattingCells($excelData, $config);
    // 判斷每條記錄的手機(jī)和價(jià)格格式
    // ……
    $jsonList = json_encode(compact('id', 'price_offer'));
    //$jsonList = json_encode($price_offer);
    // 入MQ
    $host = config("mq.host");
    $options = config("mq.price_offer_import");
    try {
        $mq = new ProductMQ($host, $options);
        $mq->publish($jsonList);
        $mq->close();
    } catch (\Exception $e) {
        return $this->jsonData(200, $e->getMessage());
    }
    // 入MQ
    return $this->jsonData(200, '導(dǎo)入成功');
}