后端开发|php教程
phpexcel导入excel
后端开发-php教程
将Excel文件数据进行读取，并且返回错误的信息
win8导航源码带后台,win VScode,ubuntu ape,tomcat未连接应用程序,sqlite cte查询,网页设计作品rar,下载网站的服务器文件,图标时钟插件代码,快速前端开发框架,崔庆才爬虫2,php连接2个数据库,seo强,盈利网站源码,网页成绩查询系统源码,登陆页面模板,js禁止页面缩放,asp文档管理系统,门户网站程序下载lzw
/**
* 导入商品基本信息
*/
public function importProductBasicInfo($data){
include_once PHPExcel.php;
include_once PHPExcel/IOFactory.php;
include_once PHPExcel/Reader/Excel5.php;
// 定义一个错误集合.
$error = array();
$resultInfo = null;
$needNext = true;
//上传文件到服务器指定位置
$fileName = $_FILES["productinfo"][ ame];
$filePath = CBase_Common_UploadPicture::uploadFile($data["productinfo"], product);
//如果上传文件成功，就执行导入excel操作
if($filePath == 1) {
$error[1] = "上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值";
}else if($filePath == 4){
$error[4] = "没有文件被上传";
}else{
$objReader = PHPExcel_IOFactory::createReader(Excel5);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($filePath);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$colums = array();
$data = array();
$excelAllId = array();
$excelIdRow = array();
$execlAllShopLinkedId = array();
for($i=0;$i<$highestColumnIndex;$i++){
$cValue = trim($objWorksheet->getCellByColumnAndRow($i,1)->getValue());
switch ($cValue) {
case self::PRODUCT_SAP_CODE : $colums[$i] = "sap_code"; break;
case self::PRODUCT_NAME : $colums[$i] = "pname"; break;
case self::PRODUCT_GROUP : $colums[$i] = "product_group"; break;
case self::PRODUCT_BRAND : $colums[$i] = product_brand; break;
case self::PRODUCT_PROXY_FLAG : $colums[$i] = "product_proxy_flag"; break;
case self::PRODUCT_BINNING : $colums[$i] = "product_binning"; break;
case self::PRODUCT_SELL_PICK : $colums[$i] = "product_sell_pick"; break;
case self::PRODUCT_ATTRIBUTE : $colums[$i] = "product_attribute"; break;
case self::PRODUCT_SUPPLIER_CODE : $colums[$i] = "vendor_code"; break;
case self::PRODUCT_SUPPLY_ADDRESS : $colums[$i] = "zzwerk_code"; break;
case self::PRODUCT_BATCH : $colums[$i] = "zzlgort_code"; break;
default : $error[3][] = $cValue; break;
}
}
//检测Excel中的基本信息是否存在
$dataCount = $highestRow - 1;
if(count($colums) == 0) {
$error[5] = "没有表头";
}
else if(!in_array(sap_code,$colums)){
$error[2] = "表头中商品SAP编码不存在";
}
else if($dataCount <= 0){
$error[6] = "Excel文件中没有数据";
}
else if(count($error)==0){
for ($i=2;$i<=$highestRow;$i++){
$colkey = array_search(sap_code);
$shopLinkedIdValue = trim($objWorksheet->getCellByColumnAndRow($colkey,$i)->getValue());
if(!$shopLinkedIdValue) {
continue;
}
if(in_array($shopLinkedIdValue,$execlAllShopLinkedId)){
$error[7][$shopLinkedIdValue][duplicate] = true;
$error[7][$shopLinkedIdValue][excelRow][] = $i;
$execlAllShopLinkedId[$i] = $shopLinkedIdValue;
$error[7][$shopLinkedIdValue][ oId] = true;
}else {
$excelIdRow[$shopLinkedIdValue] = $i;
$execlAllShopLinkedId[$i] = $shopLinkedIdValue;
}
}
$dealMultiple = ceil($dataCount / 1000);
$allProduct = array();
for($i=0;$i<$dealMultiple;$i++){
$offset = $i*1000+2;
$max = ($i+1)*1000+1;
$max = ($max > $dataCount) ? $highestRow : $max;
$allShopLinkedId = array();
for($j=$offset;$j<=$max;$j++){
if($execlAllShopLinkedId[$j]){
$allShopLinkedId[] = $execlAllShopLinkedId[$j];
}
}
// 根据SAP商品编码查询在库中的记录数.
$dbShopProducts = $this->getShopLinkedByIds($allShopLinkedId);
php开源 源码,ubuntu光标的模式,网络爬虫java原理,pixy php,seo招聘常州lzw
for($j=$offset;$j<=$max;$j++){
$product = array();
for($k=0;$k<$highestColumnIndex;$k++){
$tempV = trim($objWorksheet->getCellByColumnAndRow($k,$j)->getValue());
if($tempV && $tempV != \) {
$product[$colums[$k]] = $tempV;
}
}
//获取文件中的SAP编码
$id = $product[sap_code];
if(!$id){
continue;
}
//检测商品SAP编码是否已经存在
if(!in_array($id,$dbShopProducts)){
$allProduct[$id] = $product;
}else{
$error[7][$id][hasId] = true;
}
//商品名是否为空
if(!isset($product[pname])){
$error[7][$id][emptyName] = true;
}
//商品类目（商品组）是否为空
if(!isset($product[product_group])){
$error[7][$id][emptyProductGroup] = true;
}
//产品层次（品牌）是否为空
if(!isset($product[product_brand])){
$error[7][$id][emptyProductBrand] = true;
}
//经代销标志是否为空
if(!isset($product[product_proxy_flag])){
$error[7][$id][emptyProductProxyFlag] = true;
}
//装箱清单是否为空
if(!isset($product[product_binning])){
$error[7][$id][emptyProductBinning] = true;
}
//先销后采标识是否为空
if(!isset($product[product_sell_pick])){
$error[7][$id][emptyProductSellPick] = true;
}
//商品属性是否为空
if(!isset($product[product_attribute])){
$error[7][$id][emptyProductAttribute] = true;
}
//供应商编码是否为空
if(!isset($product[vendor_code])){
$error[7][$id][emptyVendorCode] = true;
}
//供应地点是否为空
if(!isset($product[zzwerk_code])){
$error[7][$id][emptyZzwerkCode] = true;
}
//库区是否为空
if(!isset($product[zzlgort_code])){
$error[7][$id][emptyZzlgortCode] = true;
}
if(isset($error[7][$id])){
$error[7][$id][excelRow] = $j;
}
}
}
}
}
$resultInfo[fileName] = $fileName;
//返回错误信息
if(count($error)>0){
if(isset($error[1])){
$resultInfo[ ype] = 1;
$resultInfo[msg] = $error[1];
}else if(isset($error[2])){
$resultInfo[ ype] = 2;
$resultInfo[msg] = $error[2];
}else if(isset($error[3])){
$resultInfo[ ype] = 3;
$resultInfo[msg] = 表头【.implode(,,$error[3]).】不存在;
}else if(isset($error[4])){
$resultInfo[ ype] = 4;
$resultInfo[msg] = $error[4];
}else if(isset($error[6])){
$resultInfo[ ype] = 6;
$resultInfo[msg] = $error[6];
}else if(isset($error[7])){
$excelName = null;
$objPHPWriteExcel = new PHPExcel();
$objPHPWriteExcel->getProperties()->setCreator("yuer")
->setLastModifiedBy("yuer")->setTitle("")->setSubject("")
->setDescription("")->setKeywords("")->setCategory("");
$prefix = substr($fileName,0,strrpos($fileName,.));
$suffix = substr($fileName,strrpos($fileName,.));
$excelName = date("Y_m_d_H_i_s").\_.mt_rand(1,99).\_.$prefix.ErrorReport.$suffix;
$excelName = Base_Tool_Pinyin::getPinyin($excelName);
$objPHPWriteExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPWriteExcel->getActiveSheet();
$activeSheet->setTitle(错误报告);
$activeSheet->setCellValueByColumnAndRow(0,1,self::PRODUCT_SAP_CODE);
$activeSheet->setCellValueByColumnAndRow(1,1,原excel行号);
$activeSheet->setCellValueByColumnAndRow(2,1,第几行编码存在重复);
$activeSheet->setCellValueByColumnAndRow(3,1,self::PRODUCT_NAME);
$activeSheet->setCellValueByColumnAndRow(4,1,self::PRODUCT_GROUP);
$activeSheet->setCellValueByColumnAndRow(5,1,self::PRODUCT_BRAND);
$activeSheet->setCellValueByColumnAndRow(6,1,self::PRODUCT_PROXY_FLAG);
$activeSheet->setCellValueByColumnAndRow(7,1,self::PRODUCT_BINNING);
$activeSheet->setCellValueByColumnAndRow(8,1,self::PRODUCT_SELL_PICK);
$activeSheet->setCellValueByColumnAndRow(9,1,self::PRODUCT_ATTRIBUTE);
$activeSheet->setCellValueByColumnAndRow(10,1,self::PRODUCT_SUPPLIER_CODE);
$activeSheet->setCellValueByColumnAndRow(11,1,self::PRODUCT_SUPPLY_ADDRESS);
$activeSheet->setCellValueByColumnAndRow(12,1,self::PRODUCT_BATCH);
$activeSheet->setCellValueByColumnAndRow(13,1,其他原因);
$activeSheet->getColumnDimensionByColumn(0)->setWidth(15);
$activeSheet->getColumnDimensionByColumn(1)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(2)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(3)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(4)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(5)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(6)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(7)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(8)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(9)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(10)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(11)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(12)->setWidth(20);
$activeSheet->getColumnDimensionByColumn(13)->setWidth(20);
$writeExcelIndex = 2;
foreach ($error[7] as $pId=>$pInfo){
if(isset($pInfo[hasId])){
$activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId.-此供应商编码已经存在);
} else {
$activeSheet->setCellValueByColumnAndRow(0,$writeExcelIndex,$pId);
}
$activeSheet->setCellValueByColumnAndRow(1,$writeExcelIndex,$pInfo[excelRow]);
if(isset($pInfo[duplicate])){
$activeSheet->setCellValueByColumnAndRow(2,$writeExcelIndex,$excelIdRow[$pId]);
}
if(isset($pInfo[emptyName])){
$activeSheet->setCellValueByColumnAndRow(3,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductGroup])){
$activeSheet->setCellValueByColumnAndRow(4,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductBrand])){
$activeSheet->setCellValueByColumnAndRow(5,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductProxyFlag])){
$activeSheet->setCellValueByColumnAndRow(6,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductBinning])){
$activeSheet->setCellValueByColumnAndRow(7,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductSellPick])){
$activeSheet->setCellValueByColumnAndRow(8,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyProductAttribute])){
$activeSheet->setCellValueByColumnAndRow(9,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyVendorCode])){
$activeSheet->setCellValueByColumnAndRow(10,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyZzwerkCode])){
$activeSheet->setCellValueByColumnAndRow(11,$writeExcelIndex,-为空);
}
if(isset($pInfo[emptyZzlgortCode])){
$activeSheet->setCellValueByColumnAndRow(12,$writeExcelIndex,-为空);
}
黄页网站源码大全,双系统ubuntu卡死,linux防止爬虫脚本,hbuiler php,如何整站seolzw
if(isset($pInfo[other])){
$activeSheet->setCellValueByColumnAndRow(13,$writeExcelIndex,$pInfp[other]);
}
$writeExcelIndex++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPWriteExcel, Excel5);
$excelPath = FILE_PATH.DS.feedback.DS.$excelName;
$objWriter->save($excelPath);
$resultInfo[ ype] = 7;
$resultInfo[msg] = $fileName."文件中存在错误";
$resultInfo[errorReport] = $excelName;
// 日志操作，暂时空着
}
}else{
//导入数据
$logIds = \;
$i = 0;
foreach ($allProduct as $pId => $pInfo){
$updateProductSql = insert into yr_product set ;
if(isset($pInfo[pname]) && trim($pInfo[pname])){
$updateProductSql = $updateProductSql.pname=\\.str_replace(\\,\\\\,$pInfo[pname]).\\,;
}
//如果SAP编码不足18位，则用0从左开始补全
if(isset($pInfo[sap_code])){
if(strlen($pInfo[sap_code])<18){
$pInfo[sap_code] = str_pad($pInfo[sap_code], 18, "0", STR_PAD_LEFT);
$updateProductSql = $updateProductSql.sap_code=\\.str_replace(\\,\\\\,$pInfo[sap_code]).\\,;
}
}
if(isset($pInfo[product_group])){
$updateProductSql = $updateProductSql.product_group=\\.$pInfo[product_group].\\,;
}
if(isset($pInfo[product_brand])){
$updateProductSql = $updateProductSql.product_brand=\\.$pInfo[product_brand].\\,;
}
if(isset($pInfo[product_proxy_flag])){
$updateProductSql = $updateProductSql.product_proxy_flag=\\.$pInfo[product_proxy_flag].\\,;
}
if(isset($pInfo[product_binning])){
$updateProductSql = $updateProductSql.product_binning=\\.$pInfo[product_binning].\\,;
}
if(isset($pInfo[product_sell_pick])){
$updateProductSql = $updateProductSql.product_sell_pick=\\.$pInfo[product_sell_pick].\\,;
}
if(isset($pInfo[product_attribute])){
$updateProductSql = $updateProductSql.product_attribute=\\.$pInfo[product_attribute].\\,;
}
if(isset($pInfo[vendor_code])){
$updateProductSql = $updateProductSql.vendor_code=\\.$pInfo[vendor_code].\\,;
}
if(isset($pInfo[zzwerk_code])){
$updateProductSql = $updateProductSql.zzwerk_code=\\.$pInfo[zzwerk_code].\\,;
}
if(isset($pInfo[zzlgort_code])){
$updateProductSql = $updateProductSql.zzlgort_code=\\.$pInfo[zzlgort_code].\\;
}
//最终的SQL语句
$result = $this->excuteMultiInsertSql($updateProductSql);
}
$resultInfo[ ype] = 8;
$resultInfo[msg] = "导入商品基本信息成功";
/*
* // 日志操作.
* $content = 批量新建商品导入操作成功：导入的供应商品编码有->;
* $logData[content] = $content.$logIds;
*/
}
return $resultInfo;
}
