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.bitsCN.com ')
-> SetLastModifiedBy ('http: // www.bitsCN.com ')
-> 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;