tp3.2.3运用phpexcel将excel文件导入mysql数据库

1,下载PHPExcel
2,配置将下载好的PHPExcel文件与PHPExcel.php 放到thinkphp 根目录 include/Library/Org/Util/下面
3,同时将PHPExcel.php 改名为 PHPExcel.class.php
以上是将PHPexcel扩展添加到tp里面(本人tp版本为3.2.3) 4,html代码
tp标签
<!-- $Id: brand_list.htm 15898 2009-05-04 07:25:41Z liuhui $ -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>零件管理</title>
<meta name="robots" content="noindex, nofollow">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="__PUBLIC__/css/page.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/styles/general.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/styles/main.css" rel="stylesheet" type="text/css" />
<link href="__PUBLIC__/css/style.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="__PUBLIC__/js/jquery.js"></script>
</head>
<body>
<form method="post" action="" name="theForm" id="uploadForm" enctype="multipart/form-data">
<input type="file" class="se2" name="file" id="file" size="45" onchange="checkField(this.value)" style="height: 30px; width: 300px;"/>
<label for="f_file">
<input class="se1" type="button" value="导入" />
</label>
</form>
</body>
</html>
<script>
function checkField(val)
{
UpladFile();
changev();
}
function UpladFile() {
var fileObj = document.getElementById("file").files[0]; // 获取文件对象
var FileController = "__URL__/doleadin"; // 接收上传文件的后台地址
// FormData 对象
var form = new FormData();
form.append("author", "hooyes"); // 可以增加表单数据
form.append("file", fileObj); // 文件对象
// XMLHttpRequest 对象
var xhr = new XMLHttpRequest();
xhr.open("post", FileController, true);
xhr.onload = function () {
alert('上传完成!');
};
xhr.send(form);
}
function changev(){
$('#file').attr('value','');
}
</script> 5,tp后台接受并处理上传文件代码
//处理文件上传 并导入mysql
public function doleadin()
{
$upload = new \Think\Upload();// 实例化上传类
$upload->maxSize = 3145728 ;// 设置附件上传大小
$upload->exts = array('xls', 'xlsx');// 设置附件上传类
$upload->savePath = '/'; // 设置附件上传目录
// 上传文件
$info = $upload->uploadOne($_FILES['file']);
$filename = './Uploads/'.$info['savepath'].$info['savename'];
$exts = $info['ext']; if(!$info) {// 上传错误提示错误信息
$this->error($upload->getError());
}else{// 上传成功
$this->import_excel($filename, $exts);
}
} /**
* 导入excel文件
* @param string $file excel文件路径
* @return array excel文件内容数组
*/
public function import_excel($filename, $exts='xls')
{
//导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.IOFactory");
//创建PHPExcel对象,注意,不能少了\
$PHPExcel=new \PHPExcel();
//如果excel文件后缀名为.xls,导入这个类
if($exts == 'xls'){
import("Org.Util.PHPExcel.Reader.Excel5");
$PHPReader=new \PHPExcel_Reader_Excel5();
}else if($exts == 'xlsx'){
import("Org.Util.PHPExcel.Reader.Excel2007");
$PHPReader=new \PHPExcel_Reader_Excel2007();
} //载入文件
$PHPExcel=$PHPReader->load($filename);
//获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推
$currentSheet=$PHPExcel->getSheet(0);
//获取总列数
$allColumn=$currentSheet->getHighestColumn();
//获取总行数
$allRow=$currentSheet->getHighestRow();
//循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始
$data = array();
for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){ //循环读取每个单元格的内容。注意行从1开始,列从A开始
for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
$addr = $colIndex.$rowIndex;
$cell = $currentSheet->getCell($addr)->getValue();
if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
$cell = $cell->__toString();
}
$data[$rowIndex][$colIndex] = $cell;
}
}
if(is_file($filename)){
unlink($filename);
}
$this->save_import($data);
} //保存数据到数据库
public function save_import($data)
{
//零件配置表
$obj_dlaccess=new \Model\Dl_accessModel();
//零件系列表
$obj_info = new \Model\Dl_infoModel();
//城市MOdel
$city_info = new \Model\CityModel();
$result_msg = array(); foreach ($data as $k=>$v){
// $v['C'] = (String)$data[$k]['C']; if($k >= 2){
//$v['C'] = (String)$data[$k]['C']; $data = '';
//系列id
$info_name = (String)htmlspecialchars(trim($v['B']));
$info_id = $obj_info->file_exist($info_name);
if($info_id){
$data['info_id'] = $info_id;
$data['access_name'] = htmlspecialchars(trim($v['C']));
$city_name = htmlspecialchars(trim($v['D']));
$data['city_id'] = $city_info->city_id($city_name);
$data['access_mark'] = htmlspecialchars(trim($v['E']));
$access_num = (int)htmlspecialchars(trim($v['F']));
if(is_int($access_num)){
$data['access_num'] = $access_num;
$access_price = $v['G'];
$arr = explode('/',$access_price);
if(is_array($arr)){
$data['access_price']=$arr[0];
if($arr[1]=='元'){
$data['price_stu'] = 1;
}else{
$data['price_stu'] = 2;
}
$data['access_dealer'] = htmlspecialchars(trim($v['H']));
$data['access_phone'] = htmlspecialchars(trim($v['I'])); $arr1 = htmlspecialchars(trim($v['J']));
$arr1 = explode('/',$arr1);
if(is_array($arr1)){ $data['inventory']=$arr[0];
if($arr[1]=='个'){
$data['inventory_stu'] = 1;
}else{
$data['inventory_stu'] = 2;
}
//查询是否已存在该零件
$return = $obj_dlaccess->filed_exsit($data); if(empty($return)){
$data['up_time'] = time();
$result = $obj_dlaccess->to_add($data);
if($result){
array_push($result_msg,'第'.($k-1).'条导入成功!<br/>');
}
}else{
array_push($result_msg,'第'.($k-1).'条导入失败!<br/>');
}
} }else{
return 'excel格式错误';
}
}else{
return 'excel格式错误';
}
}else{
return 'excel格式错误';
}
}
}
// return $result_msg;
}
注意:如遇到例如:
object(PHPExcel_RichText)[98]
private '_richTextElements' =>
array
0 =>
object(PHPExcel_RichText_TextElement)[99]
private '_text' => string '96018270' (length=8)
1 =>
object(PHPExcel_RichText_Run)[608]
private '_font' =>
object(PHPExcel_Style_Font)[164]
...
private '_text' (PHPExcel_RichText_TextElement) => string '/96018290' (length=9)
此类型的数据 则将该数据 强制转换为(string)string字符串即可!
上一篇:RelativeLayout经常使用属性介绍及实例解说


下一篇:js 空数组是true还是false