Use phpExcel to import and export Excel data

This article mainly introduces the use of phpExcel to import and export Excel data. It has certain reference value. Now I share it with you. Friends in need can refer to it.

My routine is If used in the Thinkphp development framework, if the same method is used in other frameworks, many people may not be able to correctly implement the import and export of Excel. The problem is basically an error in the reference path of the core class of phpExcel. If there is a problem, everyone must correct it. Test whether Lu Jin is quoted correctly

Many articles mention using phpExcel to import and export Excel data. Most of the articles are similar, or they are reprinted, and there will be some problems. The following is my research on phpExcel The usage routines summarize the usage methods, and then go directly to the topic.

First of all, let me say that this routine of mine is used in the development framework of Thinkphp. If it is used in other frameworks in the same way, many people may not be able to correctly implement the import and export of Excel. The problem is basically All of the above are errors in the reference path of the core class of phpExcel. If there is a problem, you must test whether Lu Jin is referenced correctly.

(1) Import Excel

First, upload the file on the front html page: such as:

Copy code The code is as follows:

<form method="post" action="php文件" enctype="multipart/form-data">
         <h3 id="导入Excel表">导入Excel表：</h3><input  type="file" name="file_stu" />
           <input type="submit"  value="导入" />
</form>

Second, process the file in the corresponding php file

Copy code The code is as follows :

 if (! empty ( $_FILES ['file_stu'] ['name'] ))
 {
    $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
    $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
    $file_type = $file_types [count ( $file_types ) - 1];
     /*判别是不是.xls文件，判别是不是excel文件*/
     if (strtolower ( $file_type ) != "xls")              
    {
          $this->error ( '不是Excel文件，重新上传' );
     }
    /*设置上传路径*/
     $savePath = SITE_PATH . '/public/upfile/Excel/';
    /*以时间来命名上传的文件*/
     $str = date ( 'Ymdhis' ); 
     $file_name = $str . "." . $file_type;
     /*是否上传成功*/
     if (! copy ( $tmp_file, $savePath . $file_name )) 
      {
          $this->error ( '上传失败' );
      }
    /*
       *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
      注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
    */
  $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );
   /*
        重要代码 解决Thinkphp M、D方法不能调用的问题  
        如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码
    */
   //spl_autoload_register ( array ('Think', 'autoload' ) );
   /*对生成的数组进行数据库的写入*/
   foreach ( $res as $k => $v ) 
   {
       if ($k != 0) 
      {
           $data ['uid'] = $v [0];
           $data ['password'] = sha1 ( '111111' );
           $data ['email'] = $v [1];
           $data ['uname'] = $v [3];
          $data ['institute'] = $v [4];
         $result = M ( 'user' )->add ( $data );
         if (! $result) 
         {
              $this->error ( '导入数据库失败' );
          }
      }
   }
}

Third: ExcelToArrary class, used to reference phpExcel and process Excel data

Copy code The code is as follows:

class ExcelToArrary extends Service{
 public function __construct() {
     /*导入phpExcel核心类    注意 ：你的路径跟我不一样就不能直接复制*/
     include_once('./Excel/PHPExcel.php');
 }
/**
* 读取excel $filename 路径文件名 $encode 返回数据的编码 默认为utf8
*以下基本都不要修改
*/
public function read($filename,$encode='utf-8'){
          $objReader = PHPExcel_IOFactory::createReader('Excel5');
          $objReader->setReadDataOnly(true);
          $objPHPExcel = $objReader->load($filename);
          $objWorksheet = $objPHPExcel->getActiveSheet();
　　　 $highestRow = $objWorksheet->getHighestRow(); 
　　　 $highestColumn = $objWorksheet->getHighestColumn(); 
　　    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); 
 　　   $excelData = array(); 
 　　　for ($row = 1; $row <= $highestRow; $row++) { 
    　　  for ($col = 0; $col < $highestColumnIndex; $col++) { 
                 $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
           } 
         } 
        return $excelData;
    }    
 }

Fourth, the above is all the imported content, the phpExcel package is attached at the end.

(2) Excel export (much simpler than import)

First, find out the database to generate Excel Data, such as:

$data= M('User')->findAll();   //查出数据
$name='Excelfile';    //生成的Excel文件文件名
$res=service('ExcelToArrary')->push($data,$name);

Second, ExcelToArrary class, used to reference phpExcel and process data

Copy code The code is as follows:

class ExcelToArrary extends Service{
       public function __construct() {
              /*导入phpExcel核心类    注意 ：你的路径跟我不一样就不能直接复制*/
               include_once('./Excel/PHPExcel.php');
       }
     /* 导出excel函数*/
    public function push($data,$name='Excel'){
          error_reporting(E_ALL);
          date_default_timezone_set('Europe/London');
         $objPHPExcel = new PHPExcel();
        /*以下是一些设置 ，什么作者  标题啊之类的*/
         $objPHPExcel->getProperties()->setCreator("转弯的阳光")
                               ->setLastModifiedBy("转弯的阳光")
                               ->setTitle("数据EXCEL导出")
                               ->setSubject("数据EXCEL导出")
                               ->setDescription("备份数据")
                               ->setKeywords("excel")
                              ->setCategory("result file");
         /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
        foreach($data as $k => $v){
             $num=$k+1;
             $objPHPExcel->setActiveSheetIndex(0)
                         //Excel的第A列，uid是你查出数组的键值，下面以此类推
                          ->setCellValue('A'.$num, $v['uid'])    
                          ->setCellValue('B'.$num, $v['email'])
                          ->setCellValue('C'.$num, $v['password'])
            }
            $objPHPExcel->getActiveSheet()->setTitle('User');
            $objPHPExcel->setActiveSheetIndex(0);
             header('Content-Type: application/vnd.ms-excel');
             header('Content-Disposition: attachment;filename="'.$name.'.xls"');
             header('Cache-Control: max-age=0');
             $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
             $objWriter->save('php://output');
             exit;
      }

Third, the above is the entire content of the export, the phpExcel package is attached at the end.

Related recommendations:

thinkPHP phpexcel implements excel report output function example

Thinkphp5 PHPExcel implements batch upload table data function

The above is the detailed content of Use phpExcel to import and export Excel data. For more information, please follow other related articles on the PHP Chinese website!