Php Import and Export excel instance _ php instance

Use PHPExcelphp to import and export excel files. It supports the office2007 format and is 2003 compatible. For more information, see the example! The PHP import/export excel function implemented here uses open source PHPExcel. before performing the following operations, download the class library file. 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.php.net ')
-> SetLastModifiedBy ('HTTP: // www.php.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;