PHP import and export excel files

To operate excel tables, you need to use the PHPExcel plug-in. Since I generally use the CI framework, I will introduce it with the CI framework.

First, introduce the relevant files of PHPExcel:

$this->load->library('PHPExcel/IOFactory');
$this->load->model('search_model');

Next, we will introduce how to export the excel file. First, create a new worksheet and initialize it:

//新建工作表
$objPHPExcel = new PHPExcel();
// 操作第一个工作表 
$objPHPExcel->setActiveSheetIndex(0); 
// 设置工作薄名称 
$objPHPExcel->getActiveSheet()->setTitle("集合"); 
// 设置默认字体和大小 
$objPHPExcel->getDefaultStyle()->getFont()->setName("宋体"); 
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10); 

Then set the header, of course, you can not set it, setCellValueByColumnAndRow( The first parameter of the $col, $row, $name) method is the column of the operation (starting at 0), the second parameter is the row of the operation (starting at 1), and the third parameter is the value you want to set. :

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, " ");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1, "IMSI");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1, "IMEI");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1, "设备号");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1, "号段");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1, "归属地");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1, "运营商");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 1, "时间");

After setting the header, start assigning values ​​line by line. The operation method is the same as above:

$row = 2;
$i = 0;
foreach($data as $item){
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $i+1);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $row, $item['QQ']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $row, $item['MSN']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $row, $item['DeviceID']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $row, $item['RTX']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $row, $item['Area']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $row, $item['Type']);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $row, $item['TimeStamp']);
	$row++;
	$i++;
}

Finally, output the file to the page for download:

$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');     
ob_end_clean();
header("Pragma: public");     
header("Expires: 0");    
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");     
header("Content-Type:application/force-download");     
header("Content-Type: application/vnd.ms-excel;charset=UTF-8");     
header("Content-Type:application/octet-stream");     
header("Content-Type:application/download");     
header("Content-Disposition:attachment;filename=result.xls"); //定义文件名   
header("Content-Transfer-Encoding:binary");     
$objWriter->save("php://output");

The import method is also similar, First upload the file to the background, get the path and then open excel:

$orig_name = explode('.',$upload_data['orig_name'])[0];//原始文件名
$extend=strrchr ($upload_data['file_name'],'.'); //获取文件类型
$readerType = ($extend == ".xlsx") ? "Excel2007" : "Excel5";
$path = $config['upload_path'].$upload_data['file_name']; //文件路径
$objPHPExcel = new IOFactory();
$objReader = $objPHPExcel::createReader($readerType)->load($path);
$sheet = $objReader->getSheet( 0 );

Then read the data in a loop and store it in the database:

$highestRow = $sheet->getHighestRow(); // 取得总行数 但是很多无数据的空白行也读取了，所以未采用此方法
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$colspan = range( 'A', $highestColumn );

$rowArray = $sheet->getRowDimensions();// 取得总行数 
$rowCount = count($rowArray);
//循环读取excel文件(因为第一行我一般都加了表头，所以总是从第二行开始读数据)
for ( $j = 2; $j <= $rowCount; $j++ ) {
	$array = array( );
	foreach ( $colspan as $value ) {
		$array[] = $objReader->getActiveSheet()->getCell( $value . $j )->getValue();
	}
	
	//插入表中
	$sql = "insert into im_blacklist(`name`,`MAC`,`IMSI`,`IMEI`,`groupid`) values('".$array[1]."','".$array[2]."',".$array[3].",".$array[4].",".$id.")";
	$this->db->query($sql);
}

However, when reading this method, it is related to the row where the excel cursor is located. It is best to open excel, move the cursor to the lower right corner of the table, and then close excel to perform the reading operation. Only then can all data be read correctly.

The above introduces the import and export of excel files in PHP, including the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.