PHP Import Export Excel Instance _php instance

Here the implementation of the PHP Import export Excel function is to use open source Phpexcel, do the following operation please download the class library file, the official website: http://www.codeplex.com/PHPExcel, website case code a lot, export PDF what all have , the main introduction of PHP Import and export Excel features, export Excel file is office2007 format, compatible with 2003.

PHP Import Excel
The data format for the imported Excel file, screenshots are as follows:
The following is the specific code that imports the data from the Excel file into the database:

Copy Code code as follows:

<?php


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 path to the Excel file


$sheet = $objPHPExcel->getsheet (0);//Get First worksheet


$highestRow = $sheet->gethighestrow ()///Total number of rows obtained


$highestColumn = $sheet->gethighestcolumn (); Total number of columns obtained


Iterate through the Excel file, read one, insert a


for ($j =2; $j <= $highestRow; $j + +) {//Read data from the first line


$str = ';


for ($k = ' a '; $k <= $highestColumn; $k + +) {//Read data from column A


This method is simple, but improper, with ' \ \ ' combined with an array, and then split \ for field values inserted into the database, measured in Excel, if the value of a cell contains \ \ Imported data will be empty


$str. = $objPHPExcel->getactivesheet ()->getcell ("$k $j")->getvalue (). ' \//Read cells


}


Explode: function splits strings 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 Excel files


?>

PHP Export Excel
Let me directly below the summary of the use of PHP to export the part of the call code Excel.

Copy Code code as follows:

<?php


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 Gao 250 Lane 101 Room '
),
1=>array (
' ID ' =>1002,
' username ' => ' Guan Yu ',
' Password ' => ' 123456 ',
' Address ' => ' The Three Kingdoms '
),
2=>array (
' ID ' =>1003,
' username ' => ' Caocao ',
' Password ' => ' 123456 ',
' Address ' => ' Yanan West Road 2055 Lane 3rd '
),
3=>array (
' ID ' =>1004,
' username ' => ' Liu Bei ',
' Password ' => ' 654321 ',
' Address ' => ' Yu Yuan Road, Room 3309, no. 188th
)
);

$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");


$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