phpExcel is used to realize the import and export of Excel data
- 2020-11-25 07:11:08
- OfStack
Many articles have mentioned the import and export of Excel data using phpExcel. Most of them are similar or reprinted, and there will be some problems. The following is the usage method summarized by the use routine of phpExcel studied by me.
First first say 1, I this period of routine is to use on Thinkphp development framework, if in other frameworks are used the same method, a lot of people are not likely to achieve Excel import and export right, problem basically is the core of phpExcel class reference path error, whether be right if there is a problem we must refer to correct for testing.
(1) Import Excel
1. Upload files on the front page of html:
<form method="post" action="php file " enctype="multipart/form-data">
<h3> The import Excel Table: </h3><input type="file" name="file_stu" />
<input type="submit" value=" The import " />
</form>
Second, file processing 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];
/* To see if .xls File, check if it's not excel file */
if (strtolower ( $file_type ) != "xls")
{
$this->error ( ' not Excel File, reupload ' );
}
/* Set upload path */
$savePath = SITE_PATH . '/public/upfile/Excel/';
/* File uploads are named by time */
$str = date ( 'Ymdhis' );
$file_name = $str . "." . $file_type;
/* Whether the upload is successful */
if (! copy ( $tmp_file, $savePath . $file_name ))
{
$this->error ( ' Upload failed ' );
}
/*
* To upload Excel Data is processed to generate programming data , This function is going to be number one down here 3 The step ExcelToArray In the class
Note: The call executes a number here 3 In the step class read The function, Excel Converts to an array and returns to $res, Then do the database write
*/
$res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );
/*
Important code To solve Thinkphp M , D Problem where the method cannot be called
If the thinkphp Encountered in the M , D Add the following when the method fails 1 Other code
*/
//spl_autoload_register ( array ('Think', 'autoload' ) );
/* A database write 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 ( ' Import database failed ' );
}
}
}
}
Class ExcelToArrary, which refers to phpExcel and processes Excel data
class ExcelToArrary extends Service{
public function __construct() {
/* The import phpExcel Core classes Pay attention to Your path does not follow mine 1 The sample cannot be copied directly */
include_once('./Excel/PHPExcel.php');
}
/**
* read excel $filename Path file name $encode Returns the encoding of the data The default is utf8
* Do not change any of 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();
}
}
return $excelData;
}
}
Fourth, that's all for the import, and the phpExcel package is attached at the end.
(2) Export of Excel (much easier than import)
First, find out the data in the database to generate Excel, such as:
$data= M('User')-
>
findAll(); // Find out the data
$name='Excelfile'; // Generated Excel file name
$res=service('ExcelToArrary')-
>
push($data,$name);
The second, ExcelToArrary class, is used to reference phpExcel and process data
class ExcelToArrary extends Service{
public function __construct() {
/* The import phpExcel Core classes Pay attention to Your path does not follow mine 1 The sample cannot 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 is a 1 Some Settings What author Titles and things like that */
$objPHPExcel->getProperties()->setCreator(" Turning sunlight ")
->setLastModifiedBy(" Turning sunlight ")
->setTitle(" data EXCEL export ")
->setSubject(" data EXCEL export ")
->setDescription(" The backup data ")
->setKeywords("excel")
->setCategory("result file");
/* Here is the treatment Excel The data in, Take the data horizontally, mainly here 1 Step. Basically nothing else */
foreach($data as $k => $v){
$num=$k+1;
$objPHPExcel->setActiveSheetIndex(0)
//Excel The first A The column, uid Is the key value that you look up in 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, that's all for the export, with the phpExcel package attached at the end.