PHP将Excel导入数据库中
发布网友
发布时间：2022-04-22 03:38
我来回答
共3个回答
懂视网
时间：2022-04-08 00:15
<?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‘);
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],
‘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(
1 ‘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) {
1 $_indexCache[$pColumnIndex] = chr( + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);
190 } else { //开源软件:phpfensi.com
191 $_indexCache[$pColumnIndex] = chr( + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);
192 }
193 }
194 return $_indexCache[$pColumnIndex];
195 }
196
197
198 ?>
php读取excel文件并导入数据库（表头任意设定）
标签：
热心网友
时间：2022-04-07 21:23
如果小的话，可以直接导入excel文件，如果大的话，请先存成csv文件，然后通过mysql 的load data导入数据库
热心网友
时间：2022-04-07 22:41
我一般用软件来导入，没有用语句，帮不上忙了~~
