php读取excel⽂件并导⼊数据库（表头任意设定）
最近收到⼀个很奇葩的需求，要求上传excel员⼯⼯资表，表格表头不固定，导⼊后字段名为表头的拼⾳，每⽉导⼊⼀次，当⽉重复导⼊则覆盖现有的当⽉表头，并且可以按照在界⾯上按照⽉份筛选显⽰，我写的代码主要包含了⼏个稍微复杂的地⽅：
1、excel⽂件导⼊后表头汉字转拼⾳作为字段名
2、phpexcel类读取excel⽂件中如果列数超过26个（也就是超出A-Z的范围）
3、当⽉数据上传多次，最近⼀次会覆盖上⼀次的全部数据，并且字段重新建⽴
下⾯是我实现的代码，包含了phpexcel类，数据库类，上传类，这些类⽂件可以⽹上到，所以不再贴出来
1 <?php
2include_once("PHPExcel.php");//引⼊PHP EXCEL类
3include_once("medoo.php");//引⼊数据库类
4include_once("UploadFile.php");//引⼊上传类
5define ('UPLOAD_PATH','./Uploads/');
6$fieldArr = array('shenfenzhenghao', 'zhigongbianhao', 'gongjijinzhanghao', 'danwei', 'banzu', 'xingming');
7
8if (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()).'/';
13if (!is_dir($dirname) && !mkdir($dirname, 0777, true)) {
14echo '<script type="text/javascript">alert("⽬录没有写⼊权限!!");</script>';
15 }
16$upload->savePath = $dirname;
17$message = $upload->getErrorMsg();
18if(!$upload->upload()) {
19echo '<script type="text/javascript">alert("{$message}");</script>';
20 }else{
21$info = $upload->getUploadFileInfo();
22 }
23drop table if exists admin
24if(is_array($info[0]) && !empty($info[0])){
25$savePath = $dirname . $info[0]['savename'];
26 }else{
27echo '<script type="text/javascript">alert("上传失败");</script>';
28 };
29
30if(empty($savePath) or !file_exists($savePath)){die('file not exists');}
31$PHPReader = new PHPExcel_Reader_Excel2007(); //建⽴reader对象
32if(!$PHPReader->canRead($savePath)){
33$PHPReader = new PHPExcel_Reader_Excel5();
34if(!$PHPReader->canRead($savePath)){
35echo 'no Excel';
36return ;
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第⼀⾏全部字段
48while(stringFromColumnIndex($i) != $allColumn) {
49$addr = stringFromColumnIndex($i) . $row;
50$cell = (String)$currentSheet->getCell($addr)->getValue();
51if($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();
62foreach($rowOne[1] as$key => $value) {
63$tmp = Pinyin($value,'utf-8');
64if(!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]);
81if(!empty($result)) {
82$db->query("delete from fields where time = {$time}");
83 }
84foreach($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();
97foreach($newArr as$key => $value) {
98foreach($rowOne[1] as$list => $content) {
99if($key == $list) {
100$infoArr[$value] = $content;
101 }
102 }
103 }
104$infoSql = '';
105foreach($infoArr as$key => $value) {
106if(!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]]);
120foreach($field_list as$key => $value) {
121foreach($rowOne[1] as$list => $content) {
122if($content == $value['name']) {
123$rowArr[$list] = $value['field'];
124 }
125 }
126 }
127
128$db->query("delete from info where time = {$time}");
129for($rowIndex=2;$rowIndex<=$allRow;$rowIndex++){ //循环读取每个单元格的内容。注意⾏从1开始，列从A开始130
131$i = 0;
132// 取出excel第⼀⾏全部字段
133while(stringFromColumnIndex($i) != $allColumn) {
134$colnum = stringFromColumnIndex($i);
135$addr = stringFromColumnIndex($i) . $rowIndex;
136$cell = (String)$currentSheet->getCell($addr)->getValue();
137if($cell instanceof PHPExcel_RichText){ //富⽂本转换字符串
138$cell = $cell->__toString();
139 }
140if(!empty($cell)) {
141if(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();
150if(!empty($cell)) {
151if(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]);
162if(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);
171if($userid) {
172$data2['userid'] = $userid;
173$last_user_id = $db->insert("info_{$time}", $data2);
174 }
175 }
176echo "<script language=javascript>" .
177 "alert('上传成功！'),parent.location.href='../main.php' " .
178 "</script>";
179 }
180
181function stringFromColumnIndex($pColumnIndex = 0)
182 {
183static$_indexCache = array();
184
185if (!isset($_indexCache[$pColumnIndex])) {
186if ($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
191$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26); 192 }
193 }
194return$_indexCache[$pColumnIndex];
195 }
196
197
198 ?>
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论