<?
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 ?>
php读取excel文件并导入数据库（表头任意设定）
标签：
