php中如何将execl的数据导入到数据库中

发布网友

我来回答

共2个回答

懂视网

<?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文件并导入数据库（表头任意设定）

标签：

热心网友

1.使用PHP
Excel
Parser
Pro软件，但是这个软件为收费软件；
2.可将EXCEL表保存为CSV格式，然后通过
phpmyadmin
或者SQLyog导入，SQLyog导入的方法为：
·将EXCEL表另存为CSV形式；
·打开SQLyog，对要导入的表格
右击
，点击“导入”-“导入使用加载本地CSV数据”；
·在弹出的对话框中，点击“改变..”，把选择“填写excel友好值”，点击确定；
·在“从文件导入”中选择要导入的CSV文件路径，点击“导入”即可导入数据到表上；
3.一个比较笨的手工方法，就是先利用excel生成
sql语句
，然后再到mysql中运行，这种方法适用于
excel表格
导入到各类
sql数据库
：
·假设你的表格有A、B、C三
列数据
，希望导入到你的数据库中表格tablename，对应的字段分别是col1、col2、col3
·在你的表格中增加一列，利用excel的公式自动生成sql语句，具体方法如下：
1）增加一列（假设是D列）
2）在第一行的D列，就是D1中输入公式：
=CONCATENATE("insert
into
tablename
(col1,col2,col3)
values
(",A1,",",B1,",",C1,");")
3）此时D1已经生成了如下的sql语句：
insert
into
table
(col1,col2,col3)
values
('a','11','33');
4）将D1的公式复制到所有行的D列（就是用鼠标点住D1单元格的右下角一直拖拽下去啦）
5）此时D列已经生成了所有的sql语句
6）把D列复制到一个纯文本文件中，假设为sql.txt
·把sql.txt放到数据库中运行即可，你可以用命令行导入，也可以用phpadmin运行。