Php Import and Export excel instances

Use PHPExcelphp to import and export excel files. It supports the office2007 format and is 2003 compatible. For more information, see the example!

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:, there are many cases on the official website, exporting pdf files or anything. Here we mainly introduce the PHP excel Import and Export function. The exported excel file is in office2007 format and is compatible with 2003.

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. merge it with '\' As an array, split \ as the field value and insert it into the database. The 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;