Excel to mysql
来源:互联网 发布:如何进行数据分析 编辑:程序博客网 时间:2024/06/08 04:41
用php上传excel的代码
PHP代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
session_start();
include_once
'./smarty/Smarty.class.php'
;
include_once
'./conn/DbConn.php'
;
require_once
'./Excel/reader.php'
;
//include_once './Upload_file.php';
$smarty
=
new
Smarty();
$upload_err
=
$_FILES
[
"file"
][
"error"
];
//获取文件上传的错误信息
$file_source_name
=
$_FILES
[
"file"
][
"name"
];
//原始文件名
$file_temp_name
=
$_FILES
[
"file"
][
"tmp_name"
];
//临时文件名
$file_type
=
$_FILES
[
"file"
][
"type"
];
//文件mime类型
$file_size
=
$_FILES
[
"file"
][
"size"
];
//文件大小
$file_info
=
$_FILES
[
"file"
];
$f_info
= implode(
$file_info
);
//var_dump($file_info);
//exit();
error_reporting
( E_ALL ^ E_NOTICE );
$data
=
new
Spreadsheet_Excel_Reader ();
$data
->setOutputEncoding (
'utf-8'
);
$data
->read (
$file_temp_name
);
//文件名称是变量
ImportExcelData(
$data
,
'len_budget'
,
array
(
'len_month'
,
'len_Series'
,
'len_total'
,
'len_Quarter'
,
'bud_time'
,
'bud_user'
),true);
/**
* 将读取到的Excel数据导入到数据库中
*
* @access public
* @param hasColumnHeader 是否包含列头
* @param columnArray 要插入的列
* @param tableName 要插入的表
* @param batchSize 每次执行插入语句的条数
* @return bool
*/
function
ImportExcelData(
$data
,
$tableName
,
$columnArray
,
$hasColumnHeader
= false,
$batchSize
= 100){
// 默认不包含列头,起始行就为1
$start
= 1;
if
(
$hasColumnHeader
){
// 如果包含列头,跳过列头,起始行为2
$start
= 2;
}
// 记录循环次数
$loop
= 0;
$sql
=
""
;
// 生成insert语句的前半部分
// 形式如这种:insert into table_name('field1','field2'...) values
$insert_statement
= CreateInsertStatement(
$tableName
,
$columnArray
);
$sql
.=
$insert_statement
;
for
(
$i
=
$start
;
$i
<=
$data
->sheets[0][
'numRows'
];
$i
++){
// 遍历行
$sql
.=
"("
;
for
(
$j
= 1;
$j
<=
$data
->sheets[0][
'numCols'
];
$j
++){
// 遍历列
$sql
.=
"'"
.
$data
->sheets[0]['cells
'][$i][$j]."'
,";
}
$sql
= trimEnd(
$sql
,
","
);
$sql
.=
"),"
;
$loop
++;
// 当loop值等于batchSize时,执行插入操作
if
(
$loop
==
$batchSize
){
$conn
=
new
DbConn();
$res
=
$conn
->executeUpdate(
$sql
);
$conn
->freeResult();
$conn
->close();
//$res = mysql_query ( $sql );
$loop
= 0;
}
//echo $sql;
}
$sql1
=
substr
(
$sql
,0,
strlen
(
$sql
)-1);
//var_dump($sql1);
$Character
=
";"
;
$sql
=
$sql1
.
$Character
;
// 如果有950条记录,执行了前9个batch,剩余50条也应当执行
if
(
$loop
!= 0){
//var_dump($sql);
$conn
=
new
DbConn();
$res
=
$conn
->my_query(
$sql
);
// include_once './save_file.php';
$conn
->freeResult();
$conn
->close();
//$res = mysql_query ($sql);
if
(
$res
== FALSE) {
echo
"<Script language='JavaScript'> alert('Your excel table information is incorrect!');"
;
echo
" window.location='跳转到当前操作页面';"
;
echo
"</Script>"
;
}
else
{
echo
"<Script language='JavaScript'> alert('The import is successful!');"
;
echo
" window.location='summary.php';"
;
echo
"</Script>"
;
}
}
}
- Excel to mysql
- gridview to excel ; excel to ds
- Flex to Excel or Excel to Flex
- Export DataSets to Excel...
- Excel To SqlServer
- jsp to Excel (jxl)
- datagrid to EXCEL
- 字符串To Excel
- export to excel
- Export GridView to Excel
- GridView To Excel
- Export to excel
- asp.net to excel
- pb Excel to datawindow
- c# XML to Excel
- Export to Excel
- C# DataTable to Excel
- DataGridView To Excel
- linux内核驱动编程的Makefile详解
- Jmeter HTTPcookie管理器 Cookie Policy
- jvm fatal error 导致tomcat崩溃
- Android Service Binder交互通信实例
- 设计模式C++学习笔记之二(Proxy代理模式)
- Excel to mysql
- android handler 实现
- 内存使用情况
- Alfresco4.2.b安装
- android shape
- 邮件书写技巧(1)
- Android TextView内容过长加省略号
- android软键盘enter键的替换与事件监听
- Android(SpreadTurm) 信息添加录音附件显示错误