Php import and export excel instance _ PHP Tutorial

Php imports and exports an excel instance. The PHP import/export excel function implemented here uses open source PHPExcel. before performing the following operations, download the class library file. official website: www. codeplex. comPHPExcel, the PHP import and export excel function implemented on the official website uses open source PHPExcel. please download the class library file before performing the following operations. official website.

Php import excel
The data format of the imported excel file is as follows:
The following code imports the data in the excel file to the database:

The code is as follows:

Require_once 'classes/PHPExcel. php ';
Require_once 'classes/PHPExcel/IOFactory. php ';
Require_once 'classes/PHPExcel/Reader/excel5.php ';

$ ObjReader = PHPExcel_IOFactory: createReader ('excel5'); // use excel2007 for 2007 format
$ ObjPHPExcel = $ objReader-> load ($ file_url); // $ file_url is the Excel file path.
$ Sheet = $ objPHPExcel-> getSheet (0); // obtain the first worksheet
$ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns
// Read the excel file cyclically, read one, and insert one
For ($ j = 2; $ j <= $ highestRow; $ j ++) {// read data from the first row
$ Str = '';
For ($ k = 'a'; $ k <= $ highestColumn; $ k ++) {// read data from column
// This method is simple, but there is something wrong with it. combine it with '\' as an array, then split \ as the field value and insert it into the database. the actual measurement is in excel, if the value of a cell contains \ imported data, it is null.
$ Str. = $ objPHPExcel-> getActiveSheet ()-> getCell ("$ k $ j")-> getValue (). '\'; // read a cell
}
// Explode: The function separates strings into arrays.
$ Strs = explode ("\", $ str );
$ SQL = "INSERT '". TB_PREFIX. "business" ('username', 'password', 'Company', 'prov', 'address', 'btime', 'phone', 'Email ', 'name ') VALUES (
'{$ Strs [0]}',
'{$ Strs [1]}',
'{$ Strs [2]}',
'{$ Strs [3]}',
'{$ Strs [4]}',
'{$ Strs [5]}',
'{$ Strs [6]}',
'{$ Strs [7]}',
'{$ Strs [8]}') ";
$ Db-> query ($ SQL); // Insert a database.
}
Unlink ($ file_url); // delete an excel file
?>

Php excel export
The following is a summary of the call code for exporting Excel files using PHP.

The code is as follows:

Error_reporting (E_ALL );
Date_default_timezone_set ('Asia/Shanghai ');
Require_once './Classes/PHPExcel. php ';

$ Data = array (
0 => array (
'Id' => 1001,
'Username' => 'Zhang Fei ',
'Password' => '123 ',
'Address' => 'room 250, Lane 101, gaolozhuang, S3'
),
1 => array (
'Id' => 1002,
'Username' => 'Guan yu ',
'Password' => '123 ',
'Address' => 'Three Kingdoms'
),
2 => array (
'Id' => 1003,
'Username' => 'caocao ',
'Password' => '123 ',
'Address' => 'no. 3, Lane 2055, Yan'an West Road'
),
3 => array (
'Id' => 1004,
'Username' => 'Liu Bei ',
'Password' => '123 ',
'Address' => 'room 188, No. 3309 Yuanyuan road'
)
);

$ ObjPHPExcel = new PHPExcel ();
$ ObjPHPExcel-> getProperties ()-> setCreator ('http: // www.jb51.net ')
-> SetLastModifiedBy ('http: // www.jb51.net ')
-> SetTitle ('Office 2007 XLSX document ')
-> SetSubject ('Office 2007 XLSX document ')
-> SetDescription ('document for Office 2007 XLSX, generated using PHP classes .')
-> SetKeywords ('Office 2007 openxml php ')
-> SetCategory ('result file ');
$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('A1', 'id ')
-> SetCellValue ('b1 ', 'Username ')
-> SetCellValue ('C1', 'password ')
-> SetCellValue ('d1 ', 'address ');

$ I = 2;
Foreach ($ data as $ k => $ v ){
$ ObjPHPExcel-> setActiveSheetIndex (0)
-> SetCellValue ('A'. $ I, $ v ['id'])
-> SetCellValue ('B'. $ I, $ v ['username'])
-> SetCellValue ('C'. $ I, $ v ['password'])
-> SetCellValue ('D'. $ I, $ v ['address']);
$ I ++;
}
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ('class 2, grade 3 ');
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ Filename = urlencode ('student information Statistics'). '_'. date ('Y-m-dHis ');

/*
* Generate an xlsx file
Header ('content-Type: application/vnd. openxmlformats-officedocument.spreadsheetml.sheet ');
Header ('content-Disposition: attachment; filename = "'.w.filename.'.xlsx "');
Header ('cache-Control: max-age = 0 ');
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel2007 ');
*/

/*
* Generate an xls file
Header ('content-Type: application/vnd. ms-excel ');
Header ('content-Disposition: attachment; filename = "'.w.filename.'.xls "');
Header ('cache-Control: max-age = 0 ');
$ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
*/

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

...