Excel文件导出

  $excel = new \PHPExcel();

  //Excel表格式,这里简略写了8列

  //表头数组

  $letter = array('A','B','C','D');

  //设置表头宽度

  $excel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

  $excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);

  $excel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

  $excel->getActiveSheet()->getColumnDimension('D')->setWidth(20);

  //设置水平居中

  $excel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $excel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $excel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $excel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $excel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);

  $excel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);

  //填充表头信息

  $excel->getActiveSheet()

                                        ->setCellValue('A1','体育')

                                        ->setCellValue('B1','文学')

                                        ->setCellValue('C1','画画')

                                        ->setCellValue('D1','音乐');

  $a = 0;

  $excel->getActiveSheet()->setCellValue("A"."3","共计:".5."本");

  $excel->getActiveSheet()->setCellValue("B"."3","共计:".6."本");

  $excel->getActiveSheet()->setCellValue("C"."3","共计:".33."本");

  $excel->getActiveSheet()->setCellValue("D"."3","共计:".21."本");

  //创建Excel输入对象

  $write = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');

  header("Pragma: public");

  header("Expires: 0");

  header("Cache-Control:must-revalidate, post-check=0, pre-check=0");

  header("Content-Type:application/force-download");

  header("Content-Type:application/vnd.ms-excel");

  header("Content-Type:application/octet-stream");

  header("Content-Type:application/download");;

  header('Content-Disposition:attachment;filename="信息.xls"');

  header("Content-Transfer-Encoding:binary");

  $write->save('php://output');

   exit;

代码执行后，会直接生成Excel，并提示下载或打开

Excel文件导入

$phpexcel = new \PHPExcel;

$excelReader = \PHPExcel_IOFactory::createReader('Excel2007');

$phpexcel = $excelReader->load($rootPath . $randName)->getSheet(0);//载入文件并获取第一个sheet

$total_line = $phpexcel->getHighestRow();            //多少行

$total_column = $phpexcel->getHighestColumn();      //多少列

for($row = 2; $row <= $total_line; $row++) {

$oneUser = array();

for($column = 'A'; $column <= 'K'; $column++) {

$oneUser[] = trim($phpexcel->getCell($column.$row)->getValue());

}

$id = $oneUser[0];               //id

$name = $oneUser[1];         //姓名

$existUser = Student::findOne(['id' => $id]);

 if($existUser){

  $record = 'ID:'.$id. '姓名:'.$name.'已存在';

  Yii::$app->db->createCommand()->insert('excel_record', [

  'record' => $record,

  'type' => 2,

  'timeline' => time(),

  ])->execute();

 }else{

  $Model = new Relese;

  $Model->id = $id;

  $Model->name = $name;

  $Model->isNewRecord = true;

   if(!$Model->save()){

    $record = 'ID:'.$id.' 姓名:'.$name.'导入失败';

     Yii::$app->db->createCommand()->insert('excel_record', [

     'record' => $record,

     'type' => 2,

     'timeline' => time(),

   ])->execute();

   }else{

   $record = 'ID:'.$id.' 姓名:'.$name.'导入成功';

    Yii::$app->db->createCommand()->insert('excel_record', [

   'record' => $record,

   'type' => 2,

   'timeline' => time(),

  ])->execute();

  }

 }

}