Use phpExcel to import and export Excel data (detailed analysis of all steps)

Use phpExcel to import and export Excel data (detailed analysis of all steps)_PHP tutorial

WBOY

Release： 2016-07-13 17:17:39

Original

956 people have browsed it

Many articles mention the use of 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 the usage method summarized by me from studying the usage routines of phpExcel. Let’s get straight to the point.

First of all, let me say that this routine of mine is used in the Thinkphp development framework. 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 The reference path of the core class of phpExcel is wrong. 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 the code The code is as follows:

< form method="post" action="php file" enctype="multipart/form-data">
file_stu" />

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

/ It is not an Excel file, upload it again' );
}

/*Set upload path*/
$savePath = SITE_PATH . '/public/upfile/Excel/';

/*Name the uploaded file based on time*/
$str = date ('Ymdhis');

$file_name = $str . "." . $file_type;

/*Whether the upload was successful*/
if (! copy ($tmp_file, $savePath . $file_name ))

{

$this->error ('Upload failed');
}

/*

*Process the uploaded Excel data to generate programming data. This function will be in the ExcelToArray class in the third step below

Note: This call executes the read function in the third step class, converts Excel into an array and returns it to $res, and then writes to the database

$res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );

Important code to solve the problem that Thinkphp M and D methods cannot be called

If you encounter M or D method failure in thinkphp, add the following code

//spl_autoload_register ( array ('Think', 'autoload' ) );

/*Write the generated array to the database*/
foreach ($res as $k => $v)

{

if ($k != 0)
{
$data ['uid'] = $v [0];
$data ['password'] = sha1 ('111111'); 1];

$data ['uname'] = $v [3];

$data ['institute'] = $v [4];

$result = M ( 'user' )->add ( $data );

if (! $result)

{

}

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() {

/*Import phpExcel core class Note: If your path is different from mine, you cannot copy it directly*/
include_once('./Excel/PHPExcel.php');
}

/**

* Read excel $filename path file name $encode return data encoding default is utf8

*Basically do not modify the following

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(); 🎜 >
}

}

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

(2) Excel export (much simpler than importing)

First, find out the data in the database to generate Excel, such as: $data= M('User')->findAll(); //Find the data

$name='Excelfile'; //The file name of the generated Excel file

$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() {

/*Import the core category of Phpexcel Note: Your path is not the same as me, you can't directly copy*/
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();

> ModifiedBy(" Turning sunshine")

->setTitle("Data EXCEL export")
->setDescription("Backup Data")
- & gt; setKeywords ("excel")
-& gt; setcategory ("result file");
/*below is to process data in Excel. */
foreach($data as $k => $v){

$num=$k+1;
$objPHPExcel->setActiveSheetIndex(0)

// Excel's column A, UID is the key value of you found the array. The following is a push

-& gt; setCellValue ('a'. $ Num, $ v ['uid']) — > }

$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');

}

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