<?php
/**
 * Created by PhpStorm.
 * User: chouchou
 * Date: 2019-7-21
 * Time: 19:45
 */

namespace app\impexp\controller;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Shared\Date;

/**
 * 导入模块
 * Class Import
 * @package app\impexp\controller
 */
class Import
{
    /**
     * 解析导入数据
     * @param string|null $file
     * @param int $sheet
     * @return array
     * @throws \Exception
     */
    public function getData(string $file = null,int $sheet = 0){
        try {
            /* 转码 */
            //$file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 只读内容，可以大幅度提升读取Excel效率 */
            $objRead->setReadDataOnly(true);

            /* 建立excel对象 */
            $obj = $objRead->load($file);

            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            /* 取得最大的列号 */
            $columnH = $currSheet->getHighestColumn();
            /* 兼容原逻辑，循环时使用的是小于等于 */
            $columnCnt = Coordinate::columnIndexFromString($columnH);

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data   = [];

            /* 读取内容 */
            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();
                        echo $cell.'-'.$format."<br>";
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    if($_row > 1 && $cellName == 'D'){
                        $cellValue = trim($cell->getValue());
                        $toTimestamp = Date::excelToTimestamp($cellValue);
                        $data[$_row][$cellName] = date('Y-m-d',$toTimestamp);
                    } else {
                        $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;
        }
    }

    public function getFile($file){
        return (new Base)->getFile($file);
    }


    public function importXls($xls_file,$copy = false){
        $file = $this->getFile($xls_file);
        $data = $this->getData($file);
        unset($data[1]);
        $save = new SaveData;

        $message = $save->importAll($data,$copy);
        return $message;
    }
}