ThinkPHP uses PHPExcel to implement Excel data import and export complete instances

The example described in this paper is used in the development framework of Thinkphp. If it is used in other frameworks, many people may not be able to import and export Excel correctly. The problem is basically caused by the error of the reference path of the core class of phpExcel. If there is a problem, we must test whether the reference of Lu Jin is correct.

The specific operation steps are as follows:

(1) Import Excel

First, upload files on the foreground html page: such as:

<form method="post" action="php Documents " enctype="multipart/form-data">
 <h3> Import Excel Table: </h3><input type="file" name="file_stu" />

 <input type="submit" value=" Import " />
</form>

Second, file processing is performed in the corresponding php file

 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];
  /* Distinguish whether it is .xls File, judge whether it is excel Documents */
  if (strtolower ( $file_type ) != "xls")    
 {
   $this->error ( ' No Excel File, re-upload ' );
  }
 /* Set the upload path */
  $savePath = SITE_PATH . '/public/upfile/Excel/';
 /* Name uploaded files by time */
  $str = date ( 'Ymdhis' ); 
  $file_name = $str . "." . $file_type;
  /* Is the upload successful */
  if (! copy ( $tmp_file, $savePath . $file_name )) 
  {
   $this->error ( ' Upload failed ' );
  }
 /*
  * For uploaded Excel Data is processed to generate programming data , This function will be listed in the following number 3 Stepwise ExcelToArray Class 

   Note: The call here executes the 3 Inside the step class read Function, put Excel Convert to an array and return to $res, Then write to the database 
 */
 $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );
 /*
   Important code   Solve Thinkphp M , D The problem that the method cannot be called  
   If in thinkphp Encountered in M  , D When the method fails, add the following 1 Sentence code 
 */
 //spl_autoload_register ( array ('Think', 'autoload' ) );
 /* Write to the database to the generated array */
 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 ( ' Failed to import database ' );
   }
  }
 }
}

Class 3: ExcelToArrary, used to reference phpExcel and process Excel data
Note here that the ExcelToArrary class is built in addons/services/ExcelToArrary. class. php under the root directory

class ExcelToArrary extends Service{
 public function __construct() {
  /* Import phpExcel Core class   Attention   Your path is different from mine 1 Sample can't be copied directly */
  include_once('./Excel/PHPExcel.php');
 }
/**
*  Read excel $filename  Path file name  $encode  Returns the encoding of the data   Default to utf8
* Do not modify the following basically 
*/ 
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 contents of the import, and the phpExcel package is attached at the end.

(2) Export of Excel (much simpler than import)

First, first find out the data to generate Excel in the database, such as:

$data= M('User')->findAll(); // Find out the data 
$name='Excelfile'; // Generated Excel File name 
$res=service('ExcelToArrary')->push($data,$name);

Class 2, ExcelToArrary, used to reference phpExcel and process data

class ExcelToArrary extends Service{
public function __construct() {
  /* Import phpExcel Core class   Attention   Your path is different from mine 1 Sample can't be copied directly */
  include_once('./Excel/PHPExcel.php');
}
/*  Export excel Function */
public function push($data,$name='Excel'){
 error_reporting(E_ALL);
 date_default_timezone_set('Europe/London');
 $objPHPExcel = new PHPExcel();

/* The following are 1 Some settings   What author   Titles and things like that */
 $objPHPExcel->getProperties()->setCreator(" Turning sunshine ")
   ->setLastModifiedBy(" Turning sunshine ")
   ->setTitle(" Data EXCEL Export ")
   ->setSubject(" Data EXCEL Export ")
   ->setDescription(" Backup data ")
   ->setKeywords("excel")
  ->setCategory("result file");
 /* The following is an example of how to handle Excel The data in,   Take data horizontally, mainly because of this 1 Step, don't change anything else */
foreach($data as $k => $v){
  $num=$k+1;
  $objPHPExcel->setActiveSheetIndex(0)
 //Excel The first part of A Column, uid You find out the key value of the array, and so on 
 ->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 all the exported contents. The download address of phpExcel this site is https://www.ofstack.com/codes/194070. html

Readers who are interested in thinkPHP can check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "smarty Template Introduction Basic Tutorial" and "PHP Template Technology Summary".

I hope this article is helpful to the PHP programming based on ThinkPHP framework.