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 |
---|