php读取excel文件并导入数据库（表头任意设定）

最近收到一个很奇葩的需求，要求上传excel员工工资表，表格表头不固定，导入后字段名为表头的拼音，每月导入一次，当月重复导入则覆盖现有的当月表头，并且可以按照在界面上按照月份筛选显示，我写的代码主要包含了几个稍微复杂的地方：

1、excel文件导入后表头汉字转拼音作为字段名

2、phpexcel类读取excel文件中如果列数超过26个（也就是超出A-Z的范围）

3、当月数据上传多次，最近一次会覆盖上一次的全部数据，并且字段重新建立

下面是我实现的代码，包含了phpexcel类，数据库类，上传类，这些类文件可以网上找到，所以不再贴出来

<?php
    include_once("PHPExcel.php");//引入PHP EXCEL类
    include_once("medoo.php");//引入数据库类
    include_once("UploadFile.php");//引入上传类
    define ('UPLOAD_PATH','./Uploads/');
    $fieldArr = array('shenfenzhenghao', 'zhigongbianhao', 'gongjijinzhanghao', 'danwei', 'banzu', 'xingming');

    if (isset($_FILES['excel']['size']) && $_FILES['excel']['size'] != null) {
        $upload = new UploadFile();
        $upload->maxSize = 10240000;
        $upload->allowExts  = array('xls');
        $dirname = UPLOAD_PATH . date('Ym', time()).'/'.date('d', time()).'/';
        if (!is_dir($dirname) && !mkdir($dirname, 0777, true)) {
            echo '<script type="text/javascript">alert("目录没有写入权限!!");</script>';
        }
        $upload->savePath = $dirname;
        $message = $upload->getErrorMsg();
        if(!$upload->upload()) {
            echo '<script type="text/javascript">alert("{$message}");</script>';
        }else{
            $info =  $upload->getUploadFileInfo();
        }

        if(is_array($info[0]) && !empty($info[0])){
            $savePath = $dirname . $info[0]['savename'];
        }else{
            echo '<script type="text/javascript">alert("上传失败");</script>';
        };

        if(empty($savePath) or !file_exists($savePath)){die('file not exists');}
        $PHPReader = new PHPExcel_Reader_Excel2007();        //建立reader对象
        if(!$PHPReader->canRead($savePath)){
                $PHPReader = new PHPExcel_Reader_Excel5();
                if(!$PHPReader->canRead($savePath)){
                        echo 'no Excel';
                        return ;
                }
        }
        $PHPExcel = $PHPReader->load($savePath);        //建立excel对象
        $currentSheet = $PHPExcel->getSheet(0);        //**读取excel文件中的指定工作表*/
        $allColumn = $currentSheet->getHighestColumn();        //**取得最大的列号*/
        $allRow = $currentSheet->getHighestRow();        //**取得一共有多少行*/
        $data = array();
        $row = 1;
        $rowOne = $rowArr = $main = $time = array();
        $i = 0;
        // 取出excel第一行全部字段
        while(stringFromColumnIndex($i) != $allColumn) {
            $addr = stringFromColumnIndex($i) . $row;
            $cell = (String)$currentSheet->getCell($addr)->getValue();
            if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                $cell = $cell->__toString();
            }
            $rowOne[$row][stringFromColumnIndex($i)] = $cell;
            $i++;
        }
        $cell = (String)$currentSheet->getCell($allColumn . $row)->getValue();
        $rowOne[$row][$allColumn] = $cell;

        
        $newArr = array();
        foreach($rowOne[1] as $key => $value) {
            $tmp = Pinyin($value,'utf-8');
            if(!in_array($tmp, $fieldArr)) {
                $newArr[$key] = $tmp; 
            }
        }
        $db = new medoo(array(
            'database_type' => 'mysql',
            'database_name' => 'gzoa',
            'server' => '127.0.0.1',
            'username' => 'root',
            'password' => '',
            'port' => 3306,
            'charset' => 'utf8',
            'option' => array(PDO::ATTR_CASE => PDO::CASE_NATURAL)
        ));
        
        $time = date("Ym", time());
        $result = $db->select("fields", ["field_id","field","name"], ["time[=]" => $time]);
        if(!empty($result)) {
            $db->query("delete from fields where time = {$time}");
        }
        foreach($newArr as $key => $value) {
            $insertData = array(
                'is_main'    =>    0,
                'field'        =>    $value,
                'name'        =>    $rowOne[1][$key],
                'form_type'    =>    'number',
                'time'        =>    $time
            );
            $db->insert("fields", $insertData);
        }
        
        
        $infoArr = array();
        foreach($newArr as $key => $value) {
            foreach($rowOne[1] as $list => $content) {
                if($key == $list) {
                    $infoArr[$value] = $content;
                }
            }
        }
        $infoSql = '';
        foreach($infoArr as $key => $value) {
            if(!empty($value)) {
                $infoSql .= "`{$key}` float(25,2) NOT NULL COMMENT '{$value}',";
            }
        }
        $infoSql = rtrim($infoSql, ',');

        $db->query("DROP TABLE `info_{$time}`");

        $db->query("CREATE TABLE IF NOT EXISTS `info_{$time}` (
  `userid` int(10) unsigned NOT NULL COMMENT '用户id',
  `groupid` int(10) unsigned NOT NULL COMMENT '用户分组id',  {$infoSql}
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;");

        $field_list = $db->select("fields", ["field_id","field","name"], ["OR" => ["is_main[=]" => 1,"time[=]" => $time]]);
        foreach($field_list as $key => $value) {
            foreach($rowOne[1] as $list => $content) {
                if($content == $value['name']) {
                    $rowArr[$list] = $value['field'];
                }
            }
        }

        $db->query("delete from info where time = {$time}");
        for($rowIndex=2;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始
                    
                $i = 0;
                // 取出excel第一行全部字段
                while(stringFromColumnIndex($i) != $allColumn) {
                    $colnum = stringFromColumnIndex($i);
                    $addr = stringFromColumnIndex($i) . $rowIndex;
                    $cell = (String)$currentSheet->getCell($addr)->getValue();
                    if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                        $cell = $cell->__toString();
                    }
                    if(!empty($cell)) {
                        if(in_array($rowArr[$colnum], $fieldArr)) {
                            $data1[$rowArr[$colnum]] = $cell;
                        } else {
                            $data2[$rowArr[$colnum]] = $cell;
                        }
                    }
                    $i++;
                }
                $cell = (String)$currentSheet->getCell($allColumn . $allRow)->getValue();
                if(!empty($cell)) {
                    if(in_array($rowArr[$allColumn], $fieldArr)) {
                        $data1[$rowArr[$allColumn]] = $cell;
                    } else {
                        $data2[$rowArr[$allColumn]] = $cell;
                    }
                }
        
                $data1['time'] = $time;
                $data1['groupid'] = $data2['groupid'] = 0;//设置信息分组id
                $name = isset($data1['xingming']) ? $data1['xingming'] : '';//判断如果帐号不存在，则创建帐号，默认密码123456
                $result = $db->select("admin", ["id","uid","username"], ["username[=]" => $name]);
                if(empty($result)) {
                    $adminData = array(
                        'uid'        =>    3,
                        'username'    =>    $name,
                        'password'    =>    md5('123456')
                    );
                    $db->insert("admin", $adminData);
                }
                $userid = $db->insert("info", $data1);
                if($userid) {
                    $data2['userid'] = $userid;
                    $last_user_id = $db->insert("info_{$time}", $data2);
                }
        }
echo "<script language=javascript>" .
                 "alert('上传成功！'),parent.location.href='../main.php' " .
                 "</script>";
    }

 function stringFromColumnIndex($pColumnIndex = 0)  
{  
        static $_indexCache = array();  
   
        if (!isset($_indexCache[$pColumnIndex])) {  
            if ($pColumnIndex < 26) {  
                $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);  
            } elseif ($pColumnIndex < 702) {  
                $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);  
            } else { //开源软件:phpfensi.com 
                $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);  
            }  
        }  
        return $_indexCache[$pColumnIndex];  
} 


?>