php 导入 excel 文件

<?php
/**
 * User: wp
 * Date: 20/3/23
 * Time: 下午 16:14
 */

namespace Business;

use Core\Base\Business;


\Yaf\Loader::import(APP_PATH . '/application/plugins/PHPExcel/PHPExcel.php');
\Yaf\Loader::import(APP_PATH . '/application/plugins/PHPExcel/Writer/Excel2007.php');
\Yaf\Loader::import(APP_PATH . '/application/plugins/PHPExcel/Writer/PHPExcel_IOFactory.php');

use Core\Base\Log;
use Mongo\Dao\GoddessDaoModel;

class ExcelBusinessModel extends Business
{
    /**
     * @description:
     * @author: zhangxueqing
     * @dateTime: 2020/9/22 17:02
     * @params: $sheelname excel工作表名字
     * @params: $excelData excel工作表 数据
     * @return:
     */
    public function reKeyData($sheelname, $excelData)
    {
        write_message($sheelname . PHP_EOL . var_export($excelData, true) . "\n");
        $filename = sprintf("Config%sDao", ucfirst($sheelname));
        $class = "\\Mongo\\Dao\\Goddess\\" . $filename . "Model";
        $obj = new $class();
        //获得字段转化
        $chineseField = $obj->getExcelField();
        //弹出汉语key
        $keyName = array_shift($excelData);
        foreach ($keyName as $k => $v) {
            $keyName[$k] = $chineseField[$v] ?? '';

        }
        //excel 行数

        $excelRows = count($excelData);

        for ($i = 3; $i < $excelRows; $i++) {
            $item = [];
            foreach ($excelData[$i] as $key => $value) {
                if (is_float($value)) {
                    $item[$keyName[$key]] = (int)$value;
                } else {
                    $item[$keyName[$key]] = $value;
                }

            }
            $result[] = $item;
        }

        $dataResult = $obj->valueStringToArr($result, $filename);
        file_put_contents('excel.log', var_export($dataResult, true), FILE_APPEND);
        $data[$class] = ['_table' => $obj->_table, '_data' => $dataResult];

        //插入数据
        foreach ($data as $class => $item) {

            //删除原来的数据表
            GoddessDaoModel::getInstance()->db->{$item['_table']}->drop();
            //生成新的数据表并插入数据
            $insertOneResult = GoddessDaoModel::getInstance()->db->{$item['_table']}->insertMany($item['_data']);
            $msg[$class] = $insertOneResult->getInsertedCount();
        }

    }

    /**
     * 导入excel文件
     * @param string $file excel文件路径
     * @return array    excel文件内容数组
     */
    public function importExcel($file = '')
    {
        if (!file_exists($file)) {
            exit("文件" . $file . "不存在");
        }
        // 判断文件是什么格式
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        if ($type == 'xlsx') {
            $type = 'Excel2007';
        } elseif ($type == 'xls') {
            $type = 'Excel5';
        }
        //注意 setInputEncoding('GBK') 不设置将导致中文列内容返回boolean(false)或乱码
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file);

        $sheetCount = $objPHPExcel->getSheetCount();//获取sheet工作表总个数
        $sheetNames = $objPHPExcel->getSheetNames();//获取sheet工作表名称数组
        $rowData = array();
        $RowNum = 0;

        /*读取表格数据*/
        for ($i = 0; $i <= $sheetCount - 1; $i++) {//循环sheet工作表的总个数
            $sheet = $objPHPExcel->getSheet($i);
            $highestRow = $sheet->getHighestRow();
            $RowNum += $highestRow - 1;//计算所有sheet的总行数
            $highestColumn = $sheet->getHighestColumn();
            //从第$i个sheet的第1行开始获取数据
            for ($row = 1; $row <= $highestRow; $row++) {
                //把每个sheet作为一个新的数组元素 键名以sheet的索引命名 利于后期数组的提取
                $excelData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                $excelData = array_values(array_pop($excelData));

                if (!empty($excelData)) {
                    $rowData[$sheetNames[$i]][] = $excelData;

                }
            }
        }
        file_put_contents('excel.log', var_export($rowData, true), FILE_APPEND);
        foreach ($rowData as $k => $v) {

            $this->reKeyData($k, $v);
        }


    }


}