以下内容是基于ThinkPHP框架。。
PHPExcel导入excel数据到数据库中
简述：导入的excel表格文件由四个工作表组成，其中前面三个工作表根据客户名称关联起来，它们共同构成数据库中12个表的每条数据，第四个工作表是独立的产品信息表。12个表都是关联表，含有其他表的外键。
数据库字段结构设计是全部不能为空，主键id都是自增。
1、总体实现思路
为每个数据库表创建一个数组来保存Excel表格中读取的数据，数组的键等于数据库字段名，数组的值等于excel对应的列的内容。先读取第一个工作表的数据，根据工作表行数增加来读取每条数据保存到数组中，然后使用
M()->add($data)方法添加数组到数据库中。通过PHPExcel类库中的$phpexcel->getSheet(0)方法可以读取每个工作表。
2、如何关联数据表的外键
TP中调用模型的add()方法添加数据成功后会返回插入的记录的主键id，保存该id。
一个客户名称存在于3个工作表的数据通过客户名称关联起来，也就是3个工作表中都有客户名称。所以插入其他工作表的数据到数据库时需要根据客户名称获得关联的表的主键。
$condition[‘name’] = $customer_name;
$res = M(‘customer_info’)->where($condition)->find();
$data_procurement_plan[‘customer_id’] = $res[‘id’];//关联customer_info表的id
3、添加相同的客户名称的记录
客户名称在客户信息表中是唯一存在的所以该字段设置唯一索引。Excel中含有数据库中已经存在的客户名称时更新记录，调用删除方法删除存在的12个表中的记录，再添加新记录。
4、excel有的列为空，但是数据库不能有空字段，设置默认值
定义一个函数对数组进行检查，有空的键给设默认值
public function setArrayDefault(&$arr){
foreach($arr a4 $k => $v){
arr[$k] = isset($v)?$v:’’;
}
return $arr;
}
5、数据库表中的每个字段在excel中都为空时，不插入该记录。例如无历史合作信息。
if( trim($data_old_cooperation[‘cooperatiom_time’]) !== ‘’)
|| trim($data_old_cooperation[‘cooperatiom_type’]) !== ‘’
|| trim($data_old_cooperation[‘cooperatiom_rank’]) !== ‘’ ){
// 添加记录代码
...
}
三个字段只要有一个有数据就插入，否则不插入。
trim() 函数是去除字符串两端的空格，防止excel中内容为空格的记录插入。
6、excel中的日期处理
很奇怪在excel中的日期2015-09-09在php代码中会变成42256,得不到正确的日期。
处理如下：首先在excel模版的有日期的列设置单元格格式，选中列头，右键->单元格格式->日期->格式码，设置为YYYY-MM-DD。
function formatData($data){
$data = trim($data);// 去除空格
if(empty($data)) return “0000-00-00”;
$n = intval(($data - 25569)*3600*24);// 转换成1970年以来的秒数
$format_data = gmdata(‘Y-m-d’,$n);// 格式化的时间
return $format_data;
}
$data 是从excel中读取的内容;
return “0000-00-00” 是设置自定义的默认值，如果excel内容为空的话，插入到数据库中得到的结果是1899-12-30,该值是输入日期为空时的默认时间。
注意事项：
1、数据库字段名称统一用小写，代码中字段名要可数据库中的大小写对应，否则可能插入数据失败。
2、PHPExcel插件放在Public目录下，没找到已经定义的指向public文件的路径，所以在index.php中定义了PUBLIC_PATH的路径。
完整代码如下：
控制器代码：
<?php
namespace Home\Controller;
use Think\Controller;
class PhpexcelController extends Controller {
private static function init(){
// 设置字符集编码
header('Content-type:text/html;charset=utf-8');
// 加载类库
include_once PHPEXCEL_PATH.'/PHPExcel.php';
include_once PHPEXCEL_PATH.'/PHPExcel/Style/Fill.php';
}
/**
* 导入一个excel表的多个sheet的数据
*/
public function importmoreexcel(){
self::init();
if($_FILES["morefile"]["error"] > 0){
echo 'ERROR:'.$_FILES["morefile"]["error"]."处错误<br/>请重新导入...";
}else{
// 注意：上传中文文件名出现乱码，暂时不能上传中文文件
//$filename = iconv("GB2312","UTF-8",$_FILES["morefile"]["name"]);
// 文件存放路径
$filepath =UPLOAD_PATH."/".$_FILES["morefile"]["name"];
move_uploaded_file($_FILES["morefile"]["tmp_name"],$filepath);
echo '文件保存在：'.$filepath."<br/>";
// 创建读文件对象
$objReader = new \PHPExcel_Reader_Excel2007();
if(!$objReader->canRead($filepath)){
$objReader = new \PHPExcel_Reader_Excel5();
if(!$objReader->canRead($filepath)){
echo '上传的不是excel文件...';
return;
}
}
// 读取第一张表,客户基本信息excel表
$phpexcel = $objReader->load($filepath);
$current_excel = $phpexcel->getSheet(0);
$allRow = $current_excel->getHighestRow();// 得到总行数
// 插入记录成功的条数
$count_success = 0;
// 插入记录失败的条数
$count_fail = 0;
// 更新的记录树木
$count_upload = 0;
// 实例化模型
$m_customer_info = M('customer_info');
$m_customer_data = M('customer_data');
$m_old_cooperation = M('old_cooperation');
for($row = 3;$row <= $allRow; $row++){
$data_customer_data = array();
$data_customer_info = array();// 客户基本信息
$data_old_cooperation = array();
$data_customer_info['name'] = $current_excel->getCell('B'.$row)->getValue();
$data_customer_info['user_name'] = $current_excel->getCell('C'.$row)->getValue();
$data_customer_info['type'] = $current_excel->getCell('D'.$row)->getValue();
$data_customer_info['account'] = $current_excel->getCell('E'.$row)->getValue();
$data_customer_info['con_name'] = $current_excel->getCell('F'.$row)->getValue();
$data_customer_info['phone'] = $current_excel->getCell('G'.$row)->getValue();
$data_customer_info['qq'] = $current_excel->getCell('H'.$row)->getValue();
$data_customer_info['wechat'] = $current_excel->getCell('I'.$row)->getValue();
$data_customer_info['email'] = $current_excel->getCell('J'.$row)->getValue();
$data_customer_info['company_address'] = $current_excel->getCell('K'.$row)->getValue();
$data_customer_info['company_phone'] = $current_excel->getCell('L'.$row)->getValue();
$data_customer_info['company_email'] = $current_excel->getCell('M'.$row)->getValue();
$data_customer_info['web'] = $current_excel->getCell('N'.$row)->getValue();
$data_customer_info['money'] = $current_excel->getCell('O'.$row)->getValue();
$data_customer_info['is_listed'] = $current_excel->getCell('P'.$row)->getValue();
$data_customer_info['market_value'] = $current_excel->getCell('Q'.$row)->getValue();
$data_customer_info['sales_month'] = $current_excel->getCell('R'.$row)->getValue();
$data_customer_info['storage_facilities'] = $current_excel->getCell('S'.$row)->getValue();
$data_customer_info['it_systems'] = $current_excel->getCell('T'.$row)->getValue();
// 客户附加信息
$data_customer_data['plan'] = $current_excel->getCell('U'.$row)->getValue();
$data_customer_data['other_supplier'] = $current_excel->getCell('V'.$row)->getValue();
$data_customer_data['cooperation_proportion'] = $current_excel->getCell('W'.$row)->getValue();
$data_customer_info['create_time'] = date('Y-m-d h:i:s',time());
$data_customer_info['description'] = $current_excel->getCell('X'.$row)->getValue();
// 历史合作信息
$data_old_cooperation['cooperation_time'] = formatData($current_excel->getCell('Y'.$row)->getValue());
$data_old_cooperation['cooperation_type'] = $current_excel->getCell('Z'.$row)->getValue();
$data_old_cooperation['cooperation_rank'] = $current_excel->getCell('AA'.$row)->getValue();
// 添加前判断
$this->setArrayDefault($data_customer_info);
$this->setArrayDefault($data_customer_data);
// 判断客户名称是否已经存在，存在则删除以前的记录，再添加记录
$condition_c_i['name'] = $data_customer_info['name'];
$result = $m_customer_info->where($condition_c_i)->find();
if(!empty($result)){
$this->delete($result['id']);
$count_upload ++;
}
// 插入数据到数据库中
// 插入到客户基本资料表
$customer_id = $m_customer_info->add($data_customer_info);// 保存插入的id，关联其他表
if($customer_id == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入数据到客户附加信息表
$data_customer_data['customer_id'] = $customer_id;
if($m_customer_data->add($data_customer_data) == false){
$count_fail ++;
}else{
$count_success ++;
}
/**
* 插入到历史合作信息
* 如果历史合作信息字段全没设置则不插入记录到数据库
*/
if(trim($data_old_cooperation['cooperation_time']) !== ''
|| trim($data_old_cooperation['cooperation_type']) !== ''
|| trim($data_old_cooperation['cooperation_rank']) !== ''){
$this->setArrayDefault($data_old_cooperation);
$data_old_cooperation['customer_id'] = $customer_id;
if($m_old_cooperation->add($data_old_cooperation) == false){
$count_fail ++;
}else{
$count_success ++;
}
}
}
// 关键订单excel表
$current_excel = $phpexcel->getSheet(1);
$allRow = $current_excel->getHighestRow();// 得到总行数
$m_procurement_plan = M('procurement_plan');
$m_key_order = M('key_order');
$m_supplier = M('supplier');
$m_r_order = M('r_order_supplier');
$m_r_plan = M('r_plan_product');
for($row = 3;$row <= $allRow; $row++){
$data_procurement_plan = array();
$data_key_order = array();
$data_r_plan = array();
$data_r_order = array();
$data_supplier = array();
// 得到客户名称
$customer_name = $current_excel->getCell('B'.$row)->getValue();
// 采购计划与产品关联表
$data_r_plan['product_id'] = $current_excel->getCell('C'.$row)->getValue();
// 采购计划
$data_procurement_plan['expect_price'] = $current_excel->getCell('D'.$row)->getValue();
$data_procurement_plan['order_time'] = formatData($current_excel->getCell('E'.$row)->getValue());
$data_procurement_plan['expect_delivery_date'] = formatData($current_excel->getCell('F'.$row)->getValue());
$data_procurement_plan['pay'] = $current_excel->getCell('G'.$row)->getValue();
$data_procurement_plan['delivery_address'] = $current_excel->getCell('H'.$row)->getValue();
$data_procurement_plan['transport'] = $current_excel->getCell('I'.$row)->getValue();
$data_procurement_plan['company_doc'] = $current_excel->getCell('J'.$row)->getValue();
$data_procurement_plan['product_doc'] = $current_excel->getCell('K'.$row)->getValue();
$data_procurement_plan['after_sales_agreement'] = $current_excel->getCell('L'.$row)->getValue();
$data_procurement_plan['default_agreement'] = $current_excel->getCell('M'.$row)->getValue();
$data_procurement_plan['is_cooperation'] = $current_excel->getCell('N'.$row)->getValue();
// 供应商信息
$data_supplier['name'] = $current_excel->getCell('O'.$row)->getValue();
$data_supplier['quote'] = $current_excel->getCell('P'.$row)->getValue();
// 关键订单
$data_key_order['our_quote'] = $current_excel->getCell('Q'.$row)->getValue();
$data_key_order['moq'] = $current_excel->getCell('R'.$row)->getValue();
$data_key_order['profit_rate'] = $current_excel->getCell('S'.$row)->getValue();
$data_key_order['type'] = $current_excel->getCell('T'.$row)->getValue();
$data_key_order['status'] = $current_excel->getCell('U'.$row)->getValue();
// 添加前判断
$this->setArrayDefault($data_procurement_plan);
$this->setArrayDefault($data_key_order);
$this->setArrayDefault($data_supplier);
$this->setArrayDefault($data_r_order);
$this->setArrayDefault($data_r_plan);
// 插入数据到数据库
/**
* 插入数据到采购计划表
* 查询客户基本信息表返回客户ID作为采购计划的客户id
*/
$condition['name'] = $customer_name;
$res = $m_customer_info->where($condition)->find();
$data_procurement_plan['customer_id'] = $res['id'];
$procurement_plan_id = $m_procurement_plan->add($data_procurement_plan);
if($procurement_plan_id == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入数据到采购计划与产品关联表
// 插入数据到关键订单表
$data_key_order['plan_id'] = $procurement_plan_id;
$key_order_id = $m_key_order->add($data_key_order);
if($key_order_id == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入到供应商信息表
$supplier_id = $m_supplier->add($data_supplier);
if($supplier_id == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入到关键订单与供应商关联表
$data_r_order['order_id'] = $key_order_id;
$data_r_order['supplier_id'] = $supplier_id;
if($m_r_order->add($data_r_order) == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入到采购计划与产品关联表
$data_r_plan['plan_id'] = $procurement_plan_id;
if($m_r_plan->add($data_r_plan) == false){
$count_fail ++;
}else{
$count_success ++;
}
}
// 促销活动excel表
$current_excel = $phpexcel->getSheet(2);
$allRow = $current_excel->getHighestRow();// 得到总行数
$m_activity = M('activity');
$m_r_activity = M('r_activity_product');
$m_our_prepare = M('our_prepare');
for($row = 3;$row <= $allRow; $row++){
$data_activity = array();
$data_our_prepare = array();
$data_r_activity = array();
// 得到客户名称
$customer_name = $current_excel->getCell('B'.$row)->getValue();
// 活动情况
$data_activity['theme'] = $current_excel->getCell('C'.$row)->getValue();
$data_activity['act_date'] = $current_excel->getCell('D'.$row)->getValue();
$data_activity['act_addr'] = $current_excel->getCell('E'.$row)->getValue();
$data_activity['act_desc'] = $current_excel->getCell('F'.$row)->getValue();
$data_activity['description'] = $current_excel->getCell('G'.$row)->getValue();
$data_activity['model'] = $current_excel->getCell('H'.$row)->getValue();
$data_activity['traffic_sources'] = $current_excel->getCell('I'.$row)->getValue();
$data_activity['conversion_rate'] = $current_excel->getCell('J'.$row)->getValue();
// 活动与产品关联表
$data_r_activity['product_id'] = $current_excel->getCell('K'.$row)->getValue();
$data_activity['quantity'] = $current_excel->getCell('L'.$row)->getValue();
$data_activity['cycle'] = $current_excel->getCell('M'.$row)->getValue();
$data_activity['expect_traffic'] = $current_excel->getCell('N'.$row)->getValue();
$data_activity['hr_cs'] = $current_excel->getCell('O'.$row)->getValue();
$data_activity['hr_it'] = $current_excel->getCell('P'.$row)->getValue();
$data_activity['hr_art'] = $current_excel->getCell('Q'.$row)->getValue();
$data_activity['hr_market'] = $current_excel->getCell('R'.$row)->getValue();
$data_activity['hr_pr'] = $current_excel->getCell('S'.$row)->getValue();
$data_activity['hr_other'] = $current_excel->getCell('T'.$row)->getValue();
$data_activity['money'] = $current_excel->getCell('U'.$row)->getValue();
$data_activity['goods'] = $current_excel->getCell('V'.$row)->getValue();
$data_activity['storage_facilities'] = $current_excel->getCell('W'.$row)->getValue();
$data_activity['it_systems'] = $current_excel->getCell('X'.$row)->getValue();
$data_activity['price_strategy'] = $current_excel->getCell('Y'.$row)->getValue();
$data_activity['ad_channel'] = $current_excel->getCell('Z'.$row)->getValue();
$data_activity['ad_type'] = $current_excel->getCell('AA'.$row)->getValue();
$data_activity['ad_money'] = $current_excel->getCell('AB'.$row)->getValue();
$data_activity['order_feedback'] = $current_excel->getCell('AC'.$row)->getValue();
$data_activity['after_sales_feedback'] = $current_excel->getCell('AD'.$row)->getValue();
$data_activity['faq_feedback'] = $current_excel->getCell('AE'.$row)->getValue();
$data_activity['sales_inventory_feedback'] = $current_excel->getCell('AF'.$row)->getValue();
// 活动的准备
$data_our_prepare['inventory'] = $current_excel->getCell('AG'.$row)->getValue();
$data_our_prepare['procurement_plan'] = $current_excel->getCell('AH'.$row)->getValue();
$data_our_prepare['emergency'] = $current_excel->getCell('AI'.$row)->getValue();
$data_our_prepare['hr_cs'] = $current_excel->getCell('AJ'.$row)->getValue();
$data_our_prepare['hr_it'] = $current_excel->getCell('AK'.$row)->getValue();
$data_our_prepare['hr_purchase'] = $current_excel->getCell('AL'.$row)->getValue();
$data_our_prepare['hr_sales_manager'] = $current_excel->getCell('AM'.$row)->getValue();
$data_our_prepare['hr_art'] = $current_excel->getCell('AN'.$row)->getValue();
$data_our_prepare['hr_market'] = $current_excel->getCell('AO'.$row)->getValue();
$data_our_prepare['hr_pr'] = $current_excel->getCell('AP'.$row)->getValue();
$data_our_prepare['hr_other'] = $current_excel->getCell('AQ'.$row)->getValue();
$data_our_prepare['money'] = $current_excel->getCell('AR'.$row)->getValue();
$data_our_prepare['goods'] = $current_excel->getCell('AS'.$row)->getValue();
$data_our_prepare['storage_facilities'] = $current_excel->getCell('AT'.$row)->getValue();
$data_our_prepare['it_systems'] = $current_excel->getCell('AU'.$row)->getValue();
$data_our_prepare['expect_order'] = $current_excel->getCell('AV'.$row)->getValue();
$data_our_prepare['expect_revenue'] = $current_excel->getCell('AW'.$row)->getValue();
$data_our_prepare['expect_quantity'] = $current_excel->getCell('AX'.$row)->getValue();
$data_our_prepare['expect_popularity'] = $current_excel->getCell('AY'.$row)->getValue();
$data_our_prepare['reach_coop'] = $current_excel->getCell('AZ'.$row)->getValue();
$data_our_prepare['coop_status'] = $current_excel->getCell('BA'.$row)->getValue();
// 添加前判断
$this->setArrayDefault($data_activity);
$this->setArrayDefault($data_r_activity);
$this->setArrayDefault($data_our_prepare);
// 插入到数据库
/**
* 插入到活动情况表
* 查询客户基本信息表返回客户ID作为活动情况的客户id
*/
$condition['name'] = $customer_name;
$res = $m_customer_info->where($condition)->find();
$data_activity['customer_id'] = $res['id'];
$data_activity['traffic'] = 1;
$activity_id = $m_activity->add($data_activity);
if($activity_id == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入到活动与产品关联表
$data_r_activity['activity_id'] = $activity_id;
if($m_r_activity->add($data_r_activity) == false){
$count_fail ++;
}else{
$count_success ++;
}
// 插入到活动的准备表
$data_our_prepare['activity_id'] = $activity_id;
if($m_our_prepare->add($data_our_prepare) == false){
$count_fail ++;
}else{
$count_success ++;
}
}
// 产品信息excel表
$current_excel = $phpexcel->getSheet(3);
$allRow = $current_excel->getHighestRow();// 得到总行数
$m_product = M('product');
for($row = 2;$row <= $allRow; $row++) {
$data_product = array();
$data_product['category'] = $current_excel->getCell('B' . $row )->getValue();
$data_product['brand'] = $current_excel->getCell('C' . $row)->getValue();
$data_product['country'] = $current_excel->getCell('D' . $row)->getValue();
$data_product['name'] = $current_excel->getCell('E' . $row)->getValue();
$data_product['sku'] = $current_excel->getCell('F' . $row)->getValue();
$data_product['model'] = $current_excel->getCell('G' . $row)->getValue();
$data_product['quantity'] = $current_excel->getCell('H' . $row)->getValue();
$data_product['unit'] = $current_excel->getCell('I' . $row)->getValue();
// 添加前判断
$this->setArrayDefault($data_product);
// 插入到产品信息表
if($m_product->add($data_product) == false) {
$count_fail++;
}else{
$count_success ++;
}
}
echo $count_success.'条记录插入成功';
echo "<br/>".$count_fail.'条记录失败';
echo "<br/>更新".$count_upload.'条记录';
$this->success('添加完成',U('Index/index'),3);
}
}
/**
* 给数组中值为空的键设置默认值
*
*/
public function setArrayDefault(&$arr){
foreach($arr as $k => $v){
$arr[$k] = isset($v)?$v:'';
}
return $arr;
}
public function delete($id)
{
// 实例化模型对象
$m_customer_info = M('customer_info');
$m_customer_data = M('customer_data');
$m_old_cooperation = M('old_cooperation');
$m_procurement_plan = M('procurement_plan');
$m_key_order = M('key_order');
$m_r_order = M('r_order_supplier');
$m_r_plan = M('r_plan_product');
$m_activity = M('activity');
$m_r_activity = M('r_activity_product');
$m_our_prepare = M('our_prepare');
// 获取关联customer_info表的activity的id
$res = $m_activity->where('customer_id='.$id)->field('id')->find();
$activity_id = $res['id'];
// 获取关联customer_info表的procurement_plan的id
$res = $m_procurement_plan->where('customer_id='.$id)->field('id')->find();
$procurement_plan_id = $res['id'];
// 获取关联key_order表的r_order_supplier的id
$res = $m_key_order->where('plan_id='.$procurement_plan_id)->field('id')->find();
$key_order_id = $res['id'];
$m_customer_info->where('id='.$id)->delete();
/**
* 判断activity表中是否有关联数据
* 无记录则跳过其他两个关联表的删除操作
*/
if(intval($activity_id)){
$m_activity->where('customer_id='.$id)->delete();
$m_our_prepare->where('activity_id='.$activity_id)->delete();
$m_r_activity->where('activity_id='.$activity_id)->delete();
}
/**
* 判断procurement_plan表中是否有关联数据
* 无记录则跳过其他三个关联表的删除操作
*/
if(intval($procurement_plan_id)){
$m_procurement_plan->where('customer_id='.$id)->delete();
$m_r_plan->where('plan_id='.$procurement_plan_id)->delete();
$m_key_order->where('plan_id='.$procurement_plan_id)->delete();
$m_r_order->where('order_id='.$key_order_id)->delete();
}
$m_customer_data->where('customer_id='.$id)->delete();
$m_old_cooperation->where('customer_id='.$id)->delete();
}
}
函数代码：functions.php
function formatData($data){
$data = trim($data);
// 为空则返回默认值
if(empty($data)) return "0000-00-00";
$n = intval(($data - 25569) * 3600 * 24); //转换成1970年以来的秒数
$format_data = gmdate('Y-m-d', $n);//格式化时间
return $format_data;
}
1510
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
