public function actionImport()//excel導入
{
$model = new CrmCustomerImportForm();
$ok = "";
if (Yii::$app->request->isPost) {
// if ($model->load(Yii::$app->request->post())) {
$file = UploadedFile::getInstance($model, 'file'); //擷取上傳的檔案執行個體
if ($file) {
$filename = time().'.'.$file->extension;
$filename = time();
$file->saveAs($filename.'.'.$file->extension);//儲存檔案
/*exit;
$format = $file->extension;*/
if(in_array($file->extension,array('xls','xlsx','csv'))){
if($file->extension =='xlsx')
{
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader ->load($file);
}
else if ($file->extension =='xls')
{
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader ->load($file);
}
else if ($file->extension=='csv')
{
$PHPReader = new \PHPExcel_Reader_CSV();
$PHPReader->setInputEncoding('GBK');//預設輸入字元集
$PHPReader->setDelimiter(',');//預設的分隔符
$objPHPExcel = $PHPReader->load($file);//載入檔案
}
else
{
die('檔案格式不對!');
}
$objWorksheet = $objPHPExcel->getSheet(0);//載入檔案并擷取第一個sheet
$highestRow = $objWorksheet->getHighestRow();//總行數
$highestColumn = $objWorksheet->getHighestColumn();//總列數
// $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //将字母變為數字
if($highestRow > 1){
$transaction=Yii::$app->db->beginTransaction();
try {
$tableData = [];
$err = [];
for($row = 2; $row <= $highestRow; $row++){
$data = [];
for($col = 'A'; $col <= $highestColumn; $col++){
$data[] = trim($objWorksheet->getCell($col.$row)->getValue());//列字母不轉換為數字時的寫法
}
$tableData[] = $data;
$customer = new CrmCustomerImportForm();
$customer->name = $data[0];
$customer->gender = $data[1];
$customer->get_way = $data[2];
$customer->phone = $data[3];
$customer->tel = $data[4];
$customer->wechat = $data[5];
$customer->email = $data[6];
$customer->qq = $data[7];
$customer->birthday = str_replace('/', '-', $data[8]);
$customer->caller = $data[9];
$customer->street = $data[10];
$customer->remark = $data[11];
//判斷是否達到上限
// if (!$this->checkmaxuser((new OrgSearch())->getOrg())){
// throw new Exception('達到上限了!');
// };
if(!$customer->validate())
{
throw new Exception(reset($customer->getFirstErrors()));
}
//判斷使用者是否已存在
$existUser = $customer->getCrmCustomer();
if ($existUser) {
// $objectPHPExcel = new PHPExcel();
// $objectPHPExcel->setActiveSheetIndex(0);
// header('Content-Type : application/vnd.ms-excel');
// header('Content-Disposition:attachment;filename="'.$filename.'"');
// if($file->extension =='xlsx')
// {
//// $objReader = new \PHPExcel_Reader_Excel2007();
//// $objPHPExcel = $objReader ->load($file);
// $objWriter= PHPExcel_IOFactory::createWriter($filename,'Excel2007');
// $objWriter->save('php://output');
//
// }
// else if ($file->extension =='xls')
// {
//// $objReader = new \PHPExcel_Reader_Excel5();
//// $objPHPExcel = $objReader ->load($file);
// $objWriter= PHPExcel_IOFactory::createWriter($filename,'Excel5');
// $objWriter->save('php://output');
//
// }
// else if ($file->extension=='csv')
// {
//// $PHPReader = new \PHPExcel_Reader_CSV();
//// $PHPReader->setInputEncoding('GBK');//預設輸入字元集
//// $PHPReader->setDelimiter(',');//預設的分隔符
//// $objPHPExcel = $PHPReader->load($file);//載入檔案
// $objWriter= PHPExcel_IOFactory::createWriter($filename,'CSV');
// $objWriter->save('php://output');
// }
// throw new Exception('使用者已存在'.$existUser->phone);
//儲存可保不同檔案名,格式不變
// $objWriter = new PHPExcel_Writer_Excel2007($PHPExcel,'PDF');
// $objWriter->save('./sndemo1.xlsx');
// dump($PHPExcel);
// var_dump($row);die;
if($file->extension =='xlsx')
{
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader ->load($filename.'.'.$file->extension);
$currentSheet = $objPHPExcel->getSheet(0);
$currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB('D1E17E'); //設定标題背景顔色
/** 輸出到指定目錄 */
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($filename.'.'.$file->extension);//檔案儲存路徑
/** 輸出到浏覽器直接下載下傳打開 */
header('Content-Type:application/vnd.ms-excel'); //指定下載下傳檔案類型
header('Content-Disposition: attachment; filename="'.$filename.'.xlsx"'); //指定下載下傳檔案的描述
header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下載下傳檔案的大小
/** 将檔案内容讀取出來并直接輸出，以便下載下傳 */
readfile($filename.'.'.$file->extension);
}
else if ($file->extension =='xls')
{
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader ->load($filename.'.'.$file->extension);
$currentSheet = $objPHPExcel->getSheet(0);
$currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB('D1E17E'); //設定标題背景顔色
/** 輸出到指定目錄 */
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($filename.'.'.$file->extension);//檔案儲存路徑
/** 輸出到浏覽器直接下載下傳打開 */
header('Content-Type:application/vnd.ms-excel'); //指定下載下傳檔案類型
header('Content-Disposition: attachment; filename="'.$filename.'.xls"'); //指定下載下傳檔案的描述
header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下載下傳檔案的大小
/** 将檔案内容讀取出來并直接輸出，以便下載下傳 */
readfile($filename.'.'.$file->extension);
}
else if ($file->extension=='csv')
{
$PHPReader = new \PHPExcel_Reader_CSV();
// $PHPReader->setInputEncoding('GBK');//預設輸入字元集
// $PHPReader->setDelimiter(',');//預設的分隔符
$objPHPExcel = $PHPReader->load($filename.'.'.$file->extension);//載入檔案
$currentSheet = $objPHPExcel->getSheet(0);
$currentSheet->getStyle('A'.$row.':'.$col.$row)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB('D1E17E'); //設定标題背景顔色
// $currentSheet->setCellValue($col.$row,'CPU使用率:');//表頭指派//
/** 輸出到指定目錄 */
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
$objWriter->save($filename.'.'.$file->extension);//檔案儲存路徑
/** 輸出到浏覽器直接下載下傳打開 */
header('Content-Type:application/vnd.ms-excel'); //指定下載下傳檔案類型
header('Content-Disposition: attachment; filename="'.$filename.'.csv"'); //指定下載下傳檔案的描述
header('Content-Length:'.filesize($filename.'.'.$file->extension)); //指定下載下傳檔案的大小
/** 将檔案内容讀取出來并直接輸出，以便下載下傳 */
readfile($filename.'.'.$file->extension);
// $objPHPExcel->getActiveSheet()->setTitle('User');
// $objPHPExcel->setActiveSheetIndex(0);
// header('Content-Type: application/vnd.ms-excel');
// header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
// header('Cache-Control: max-age=0');
// $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// $objWriter->save('php://output');
//
}
// unlink($filename.'.'.$file->extension);
// $objWriter = new PHPExcel_Writer_Excel2007($PHPExcel,'PDF');
// $objWriter->save('./sndemo1.xlsx');
// dump($PHPExcel);
// $objPHPExcel = PHPExcel_IOFactory::load($filename);
// $sheet = $objPHPExcel->getSheet(0); // 讀取第一個工作表
// $highestColumm = $sheet->getHighestColumn(); // 取得總列數
// $highestRow = $sheet->getHighestRow(); // 取得總行數
//
/** 循環讀取每個單元格的資料 */
// $i = 2;
// foreach ($list as $key => $value) {
// $objPHPExcel->setActiveSheetIndex(0)
// ->setCellValue('Z'.$i, $value['demo'])
// ->setCellValue('AA'.$i, $value['demo']);
// $i++;
// }
// $objPHPExcel->getActiveSheet()->setTitle('Simple');
// $objPHPExcel->setActiveSheetIndex(0);
/** 輸出到指定目錄 */
// $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// $objWriter->save('simple.xlsx');//檔案儲存路徑
// /** 輸出到浏覽器直接下載下傳打開 */
// header('Content-Type:application/vnd.ms-excel'); //指定下載下傳檔案類型
// header('Content-Disposition: attachment; filename="'.$filename.'"'); //指定下載下傳檔案的描述
// header('Content-Length:'.filesize($filename)); //指定下載下傳檔案的大小
//
// /** 将檔案内容讀取出來并直接輸出，以便下載下傳 */
// readfile($filename);
}
//crm_customer表添加客戶
if(!$customer->saveCustomer()){
throw new Exception('客戶導入失敗');
};
}
$transaction->commit();
} catch (Exception $e) {
$transaction->rollBack();
// throw $e;
$err= $e->getMessage();
}
}
// unlink($filename);
if ($ok == 1){
// $this->redirect(array('index'));
} else{
Yii::$app->session->setFlash('error', $err);
}
}
}
else
{
Yii::$app->session->setFlash('error', '請上傳檔案!');
}
}else{
return $this->render('import',[
'model'=>$model
]);
}
return $this->render('import',[
'model'=>$model
]);
} 複制
G
M
T
| Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu | AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu |
|---|
Text-to-speech function is limited to 200 characters
| Options : History : Feedback : Donate | Close |
|---|