php读取excel文件并导入数据库（表头任意设定）
最近收到一个很奇葩的需求，要求上传excel员工工资表，表格表头不固定，导入后字段名为表头的拼音，每月导入一次，当月重复导入则覆盖现有的当月表头，并且可以按照在界面上按照月份筛选显示，我写的代码主要包含了几个稍微复杂的地方：
1、excel文件导入后表头汉字转拼音作为字段名
2、phpexcel类读取excel文件中如果列数超过26个（也就是超出A-Z的范围）
3、当月数据上传多次，最近一次会覆盖上一次的全部数据，并且字段重新建立
下面是我实现的代码，包含了phpexcel类，数据库类，上传类，这些类文件可以网上找到，所以不再贴出来
1 <?php 2 include_once("PHPExcel.php");//引入PHP EXCEL类 3 include_once("medoo.php");//引入数据库类 4 include_once("UploadFile.php");//引入上传类 5 define ('UPLOAD_PATH','./Uploads/'); 6 $fieldArr = array('shenfenzhenghao', 'zhigongbianhao', 'gongjijinzhanghao', 'danwei', 'banzu', 'xingming'); 7 8 if (isset($_FILES['excel']['size']) && $_FILES['excel']['size'] != null) { 9 $upload = new UploadFile(); 10 $upload->maxSize = 10240000; 11 $upload->allowExts = array('xls'); 12 $dirname = UPLOAD_PATH . date('Ym', time()).'/'.date('d', time()).'/'; 13 if (!is_dir($dirname) && !mkdir($dirname, 0777, true)) { 14 echo '<script type="text/javascript">alert("目录没有写入权限!!");</script>'; 15 } 16 $upload->savePath = $dirname; 17 $message = $upload->getErrorMsg(); 18 if(!$upload->upload()) { 19 echo '<script type="text/javascript">alert("{$message}");</script>'; 20 }else{ 21 $info = $upload->getUploadFileInfo(); 22 } 23 24 if(is_array($info[0]) && !empty($info[0])){ 25 $savePath = $dirname . $info[0]['savename']; 26 }else{ 27 echo '<script type="text/javascript">alert("上传失败");</script>'; 28 }; 29 30 if(empty($savePath) or !file_exists($savePath)){die('file not exists');} 31 $PHPReader = new PHPExcel_Reader_Excel2007(); //建立reader对象 32 if(!$PHPReader->canRead($savePath)){ 33 $PHPReader = new PHPExcel_Reader_Excel5(); 34 if(!$PHPReader->canRead($savePath)){ 35 echo 'no Excel'; 36 return ; 37 } 38 } 39 $PHPExcel = $PHPReader->load($savePath); //建立excel对象 40 $currentSheet = $PHPExcel->getSheet(0); //**读取excel文件中的指定工作表*/ 41 $allColumn = $currentSheet->getHighestColumn(); //**取得最大的列号*/ 42 $allRow = $currentSheet->getHighestRow(); //**取得一共有多少行*/ 43 $data = array(); 44 $row = 1; 45 $rowOne = $rowArr = $main = $time = array(); 46 $i = 0; 47 // 取出excel第一行全部字段 48 while(stringFromColumnIndex($i) != $allColumn) { 49 $addr = stringFromColumnIndex($i) . $row; 50 $cell = (String)$currentSheet->getCell($addr)->getValue(); 51 if($cell instanceof PHPExcel_RichText){ //富文本转换字符串 52 $cell = $cell->__toString(); 53 } 54 $rowOne[$row][stringFromColumnIndex($i)] = $cell; 55 $i++; 56 } 57 $cell = (String)$currentSheet->getCell($allColumn . $row)->getValue(); 58 $rowOne[$row][$allColumn] = $cell; 59 60 61 $newArr = array(); 62 foreach($rowOne[1] as $key => $value) { 63 $tmp = Pinyin($value,'utf-8'); 64 if(!in_array($tmp, $fieldArr)) { 65 $newArr[$key] = $tmp; 66 } 67 } 68 $db = new medoo(array( 69 'database_type' => 'mysql', 70 'database_name' => 'gzoa', 71 'server' => '127.0.0.1', 72 'username' => 'root', 73 'password' => '', 74 'port' => 3306, 75 'charset' => 'utf8', 76 'option' => array(PDO::ATTR_CASE => PDO::CASE_NATURAL) 77 )); 78 79 $time = date("Ym", time()); 80 $result = $db->select("fields", ["field_id","field","name"], ["time[=]" => $time]); 81 if(!empty($result)) { 82 $db->query("delete from fields where time = {$time}"); 83 } 84 foreach($newArr as $key => $value) { 85 $insertData = array( 86 'is_main' => 0, 87 'field' => $value, 88 'name' => $rowOne[1][$key], 89 'form_type' => 'number', 90 'time' => $time 91 ); 92 $db->insert("fields", $insertData); 93 } 94 95 96 $infoArr = array(); 97 foreach($newArr as $key => $value) { 98 foreach($rowOne[1] as $list => $content) { 99 if($key == $list) { 100 $infoArr[$value] = $content; 101 } 102 } 103 } 104 $infoSql = ''; 105 foreach($infoArr as $key => $value) { 106 if(!empty($value)) { 107 $infoSql .= "`{$key}` float(25,2) NOT NULL COMMENT '{$value}',"; 108 } 109 } 110 $infoSql = rtrim($infoSql, ','); 111 112 $db->query("DROP TABLE `info_{$time}`"); 113 114 $db->query("CREATE TABLE IF NOT EXISTS `info_{$time}` ( 115 `userid` int(10) unsigned NOT NULL COMMENT '用户id', 116 `groupid` int(10) unsigned NOT NULL COMMENT '用户分组id', {$infoSql} 117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;"); 118 119 $field_list = $db->select("fields", ["field_id","field","name"], ["OR" => ["is_main[=]" => 1,"time[=]" => $time]]); 120 foreach($field_list as $key => $value) { 121 foreach($rowOne[1] as $list => $content) { 122 if($content == $value['name']) { 123 $rowArr[$list] = $value['field']; 124 } 125 } 126 } 127 128 $db->query("delete from info where time = {$time}"); 129 for($rowIndex=2;$rowIndex<=$allRow;$rowIndex++){ //循环读取每个单元格的内容。注意行从1开始，列从A开始 130 131 $i = 0; 132 // 取出excel第一行全部字段 133 while(stringFromColumnIndex($i) != $allColumn) { 134 $colnum = stringFromColumnIndex($i); 135 $addr = stringFromColumnIndex($i) . $rowIndex; 136 $cell = (String)$currentSheet->getCell($addr)->getValue(); 137 if($cell instanceof PHPExcel_RichText){ //富文本转换字符串 138 $cell = $cell->__toString(); 139 } 140 if(!empty($cell)) { 141 if(in_array($rowArr[$colnum], $fieldArr)) { 142 $data1[$rowArr[$colnum]] = $cell; 143 } else { 144 $data2[$rowArr[$colnum]] = $cell; 145 } 146 } 147 $i++; 148 } 149 $cell = (String)$currentSheet->getCell($allColumn . $allRow)->getValue(); 150 if(!empty($cell)) { 151 if(in_array($rowArr[$allColumn], $fieldArr)) { 152 $data1[$rowArr[$allColumn]] = $cell; 153 } else { 154 $data2[$rowArr[$allColumn]] = $cell; 155 } 156 } 157 158 $data1['time'] = $time; 159 $data1['groupid'] = $data2['groupid'] = 0;//设置信息分组id 160 $name = isset($data1['xingming']) ? $data1['xingming'] : '';//判断如果帐号不存在，则创建帐号，默认密码123456 161 $result = $db->select("admin", ["id","uid","username"], ["username[=]" => $name]); 162 if(empty($result)) { 163 $adminData = array( 164 'uid' => 3, 165 'username' => $name, 166 'password' => md5('123456') 167 ); 168 $db->insert("admin", $adminData); 169 } 170 $userid = $db->insert("info", $data1); 171 if($userid) { 172 $data2['userid'] = $userid; 173 $last_user_id = $db->insert("info_{$time}", $data2); 174 } 175 } 176 echo "<script language=javascript>" . 177 "alert('上传成功！'),parent.location.href='../main.php' " . 178 "</script>"; 179 } 180 181 function stringFromColumnIndex($pColumnIndex = 0) 182 { 183 static $_indexCache = array(); 184 185 if (!isset($_indexCache[$pColumnIndex])) { 186 if ($pColumnIndex < 26) { 187 $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex); 188 } elseif ($pColumnIndex < 702) { 189 $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26); 190 } else { //开源软件:phpfensi.com 191 $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26); 192 } 193 } 194 return $_indexCache[$pColumnIndex]; 195 } 196 197 198 ?>
