phpexcel を使用して Excel データをインポートする方法の例_PHP チュートリアル

Excelファイルのデータを読み取り、エラー情報を返します

コードをコピーします コードは次のとおりです:

/**
* 基本的な製品情報をインポートします
*/
public function importProductBasicInfo($data){
include_once 'PHPExcel.php';
include_once 'PHPExcel/IOFactory.php';
include_once 'PHPExcel/Reader/Excel5.php'; // エラー コレクションを定義します。
$error = array() $resultInfo = null; $ FilePath = CBASE_COMMON_UPLOADPICTURE :: UploadFile ["製品情報"]、' 製品 ' ); = 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 = FromString($highestColumn);
$colums = array( );
$data = array();
$excelAllId = array();
$excelIdRow = array();
$execlAllShopLinkedId = array();
($i=0;$i $cValue = trim($objWorksheet->getCellByColumnAndRow($i,1)->getValue());
switch ($cValue) {
case self::PRODUCT_SAP_コード: $colums[$i] = " sap_コード"; 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;
デフォルト : $error[3][] = $cValue; Break;
}
}
//Excel 内の基本情報が存在するかどうか
$dataCount = $highestRow - 1;
if(count($colum s) == 0) {
$error[5] = "没有表头";
}
else if(!in_array('sap_code',$colums)){
$error[2] = "表头中商品SAP编码不存在";
}
else if($dataCount $error[6] = "Excel文件中有データ";
}
else if(count($error)==0){
for ($i=2 ;$i<=$highestRow;$i++){
$colkey = array_search('sap_code');
$shopLinkedIdValue =rim($objWorksheet->getCellByColumnAndRow($colkey,$i)->get値());
if(!$shopLinkedIdValue) {
continue;
}
if($shopLink edIdValue,$execlAllShopLinkedId)){
$error[7][$shopLinkedIdValue]['重複'] = true;
$error[7] [$shopLinkedIdValue]['excelRow'][] = $i;
$execlAllShopLinkedId[$i] = $shopLinkedIdValue;
$error[7][$shopLinkedIdValue]['noId'] = 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($execl AllShopLinkedId[$j]){
$allShopLinkedId[] = $execlAllShopLinkedId[$j];
}
}
// SAP 製品コードに従ってデータベース内のレコード数をクエリします。
for($ j=$offset;$j<=$max;$j++) {
$product = array();トリム($objWorksheet - ＆gt; getCellByColumnandRow（$ k、$ j） - ＆gt; getValue（））;

] [emptyproductgroup '] = true; EERROR [7] [$ ID] ['Emptyproductbinning'] = true; isset($product['product_sell_pick'])){
//商品属性が空かどうか 'product_tribute'])){
空ですか？）{$エラー[7] [$ id] ['emptyvendorcode'] = true;

emptyzzwerkcode '] = true;
;エラーメッセージ= $エラー[1]; resultinfo ['type']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 et->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->setCell ValueByColumnAndRow(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, '-である');
}

if(isset($pInfo['other'])){
$activeSheet->setCellValueByColumnAndRow( 13,$writeExcelIndex,$pInfp['other']);
}
$writeExcelIndex++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPWriteExcel, 'Excel5'); $excelPath = FILE_PATH.DS.'フィードバック'.DS.$excelName; $objWriter->save($excelPath); fo[ 'タイプ'] = 7;
$ resultinfo ['msg'] = $ ファイル名。 "ファイルにエラーがあります。"; $updateProductSql = 'ins ert を yr_product セットに入れる';
isset($pInfo['pname']) && Trim($pInfo['pname'])){
$updateProductSql = $updateProductSql.'pname=''.str_replace(''','''', $pInfo[ 'pname']).'',';
if(strlen ($pInfo['sap_code'])<18){
$updateProductSql = $updateProductSql.'.str_replace('' ,'''',$pInfo['sap_code']).'',';

$updateProductSql = $updateProductSql.'product_group =''.$pInfo['product_group'].'','; $updateProductSql = $updateProductSql.' product_brand=''.$pInfo['product_brand'].'','; ql.'product_proxy_flag=''.$pInfo ['product_proxy_flag'].'','; 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['type'] = 8;
$resultInfo['msg'] = "导入商品基本信息成功";

/*
* // 日志操作.
* $content = '批量新建商品导入操作成功：导入的提供应商品编码有-> ' ;
* $logData['content'] = $content.$logIds;
*/
}
return $resultInfo;
}

http://www.bkjia.com/PHPjc/621672.htmlwww.bkjia.comtru​​ehttp://www.bkjia.com/PHPjc/621672.html技術記事 Excel 文セキュリティを読み取って、その情報を返します。 /*** 基本的な製品情報をインポートします*/ public function importProductBasicInfo($data){ include_o...