PHP Import Export Excel Instance _php tutorial

Here implementation of the PHP import and export Excel function is open source Phpexcel, do the following before you download the class library file, the official website: http://www.codeplex.com/PHPExcel, the website case code a lot, export PDF and so on have , here is the main introduction of PHP Import and export Excel features, export Excel file is office2007 format, and compatible with 2003.

PHP Import into Excel
The data format of the imported Excel file is as follows:
The following is the specific code that imports the data for the Excel file into the database:
Copy the Code code 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 format
$objPHPExcel = $objReader->load ($file _url);//$file _url the path to the Excel file
$sheet = $objPHPExcel->getsheet (0);//Get First sheet
$highestRow = $sheet->gethighestrow ();//Get Total rows
$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained
Loop through the Excel file, read a line, insert a
for ($j =2; $j <= $highestRow; $j + +) {//Read data starting from the first line
$str = ";
for ($k = ' a '; $k <= $highestColumn; $k + +) {//Read data from column A
This method is simple, but inappropriate, with ' \ \ ' merged into the array, then split \ \ for the field value inserted into the database, measured in Excel, if the value of a cell contains \ \ Import the data will be empty
$str. = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue (). ' \ \ ';//Read cells
}
Explode: function splits a string into arrays.
$strs =explode ("\ \", $STR);
$sql = "INSERT into". 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);//The Insert database operation is performed here
}
Unlink ($file _url); Delete an Excel file
?>

PHP Export Excel
The following directly released my summary of the use of PHP to export the part of Excel calling code.
Copy the Code code 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 ' = ' 123456 ',
' Address ', ' Three Kingdoms ' Gao 250 lane, Room 101 '
),
1=>array (
' ID ' =>1002,
' Username ' = ' Guan Yu ',
' Password ' = ' 123456 ',
' Address ' = ' Three Kingdoms when Huaguoshan '
),
2=>array (
' ID ' =>1003,
' Username ' = ' Cao ',
' Password ' = ' 123456 ',
' Address ' = ' 2055 Lane 3rd, West Yanan Road '
),
3=>array (
' ID ' =>1004,
' username ' = ' Liu Bei ',
' Password ' = ' 654321 ',
' Address ', ' 188th Yuyuan Road, Room 3309 '
)
);

$objPHPExcel =new phpexcel ();
$objPHPExcel->getproperties ()->setcreator (' http://www.jb51.net ')
->setlastmodifiedby (' http://www.jb51.net ')
->settitle (' Office of XLSX Document ')
->setsubject (' Office of XLSX Document ')
->setdescription (' Document for Office ' XLSX, generated using PHP classes. ')
->setkeywords (' Office 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 (' Third Grade 2 class ');
$objPHPExcel->setactivesheetindex (0);
$filename =urlencode (' Student Information tab '). ' _ '. Date (' Y-m-dhis ');

/*
* Generate xlsx file
Header (' Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ');
Header (' Content-disposition:attachment;filename= '. $filename. '. Xlsx "');
Header (' cache-control:max-age=0 ');
$objWriter =phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel2007 ');
*/

/*
* Generate XLS file
Header (' Content-type:application/vnd.ms-excel ');
Header (' Content-disposition:attachment;filename= '. $filename. '. XLS "');
Header (' cache-control:max-age=0 ');
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');
*/

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

http://www.bkjia.com/PHPjc/824867.html www.bkjia.com true http://www.bkjia.com/PHPjc/824867.html techarticle here the implementation of the PHP import and export Excel function is open source Phpexcel, do the following before you download the class library file, the official website: http://www.codeplex.com/PHPExcel, website ...

