PHP import and export excel instance_PHP tutorial

The PHP import and export excel function implemented here uses the open source PHPExcel. Please download the library file before performing the following operations. The official website: http://www.codeplex.com/PHPExcel. There are many case codes on the official website. , export pdf and so on. Here we mainly introduce the functions of PHP importing and exporting excel. The exported excel file is in office2007 format and is compatible with 2003.

php import excel
The data format of the imported excel file, the screenshot is as follows:
The following is the specific code to import the data of the excel file into the database:

Copy the code 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 path to the Excel file
$sheet=$objPHPExcel->getSheet(0);//Get the first worksheet
$highestRow=$sheet->getHighestRow();//Get the total number of rows
$highestColumn=$ sheet->getHighestColumn(); //Get the total number of columns
//Loop through the excel file, read one and insert one
for($j=2;$j<=$highestRow;$j++ ){//Read data starting from the first row
$str='';
for($k='A';$k<=$highestColumn;$k++){                                      Get data
//This method is simple, but there are drawbacks. Use '\' to merge into an array, and then divide \ into field values ​​and insert them into the database. Actual measurement in excel, if the value of a certain cell contains \import The data will be empty                                                                                                      🎜> }
//explode: function splits a string into an array.
$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);//What is performed here is the insert database operation
}
unlink($file_url); //Delete excel file
?>

php export excelThe following is a direct summary of part of the calling code for using PHP to export Excel.

Copy code 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'=> ;'123456',
'address'=>'Room 101, Lane 250, Gaolaozhuang in the Three Kingdoms Period'
),
1=>array(
'id'=>1002,
'username'=>'Guan Yu',
'password'=>'123456',
'address'=>'Three Kingdoms Flower and Fruit Mountain'
),
2=>array(
'id'=>1003,
'username'=>'Cao Cao',
'password'=>'123456',
'address'= >'No. 3, Lane 2055, Yan'an West Road'
),
3=>array(
'id'=>1004,
'username'=>'Liu Bei',
'password'=>'654321',
'address'=>'Room 3309, No. 188, Yuyuan Road'
)
);

$objPHPExcel=new PHPExcel();
$objPHPExcel->getProperties()->setCreator('http://www.jb51.net')
->setLastModifiedBy('http: //www.jb51.net ')
-& gt; settitle (' Office 2007 XLSX DOCUMENT ')
-& GT; Setsubject Scripting ('Document for Office 2007 ) )
                      ->setCellValue('D1','Address');

$i=2; objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i,$v['id'])
->setCellValue('B'.$i,$v[' username '])
-& gt; setcellValue (' c '. $ i, $ v [' password '])
-& gt; setCellvalue (' d '. $ i, $ v [' address']) ;
$i++;
}
$objPHPExcel->getActiveSheet()->setTitle('Grade 3 Class 2');
$objPHPExcel->setActiveSheetIndex(0);
$filename=urlencode('Student Information Statistics Table').'_'.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

truehttp: //www.bkjia.com/PHPjc/824867.html

TechArticleThe PHP import and export excel function implemented here uses the open source PHPExcel. Please download it before performing the following operations. Class library file, official website: http://www.codeplex.com/PHPExcel, official website...