使用php读取excel存入数据库

使用php读取excel的数据

header("content-type:text/html;charset=utf-8"); 
	include  "excelUtil/Classes/PHPExcel.php";
	
	$inputFileName = "excle文件";
	date_default_timezone_set("PRC");
	
	// 读取excel文件
	try {
		$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);
		$objPHPExcel = $objReader->load($inputFileName);
	} catch(Exception $e) {
		die("加载文件发生错误");
	}

	// 确定要读取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧
	$sheet = $objPHPExcel->getSheet(0);
	$highestRow = $sheet->getHighestRow();
	$highestColumn = $sheet->getHighestColumn();

	//不想读取表头就设置$row的初始值为2,从第二行开始读
	// 获取一行的数据
	for ($row = 2; $row <= $highestRow; $row++){
		// Read a row of data  an array
		$rowData = $sheet->rangeToArray(\A\.$row . \:\ . $highestColumn . $row, NULL, false, FALSE);
		//这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
	var_($rowData);
	echo "<br/><br/><br/><br/><br/>";
	}

将excel的数据写入数据库

header("content-type:text/html;charset=utf-8"); 
	include  "excelUtil/Classes/PHPExcel.php";
	// Check prerequisites
	
	
	$con = mysql_connect("localhost","root","root");
	if (!$con)
	{
		die(\Could not connect: \ . mysql_error());
	}

	mysql__db("bc", $con);
	
	
	$inputFileName = "excelDemo.xlsx";
	date_default_timezone_set("PRC");
	
	// 读取excel文件
	try {
		$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);
		$objPHPExcel = $objReader->load($inputFileName);
	} catch(Exception $e) {
		die("加载文件发生错误");
	}

	// 确定要读取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧
	$sheet = $objPHPExcel->getSheet(0);
	$highestRow = $sheet->getHighestRow();
	$highestColumn = $sheet->getHighestColumn();

	$sql = "  bc_demo(name,age) values";
	// 获取一行的数据
	for ($row = 2; $row <= $highestRow; $row++){
		//获取一行的数据
		$rowData = $sheet->rangeToArray(\A\.$row . \:\ . $highestColumn . $row, NULL, false, FALSE);
		//最后拼接的sql语句
		$sql = $sql."(";
		//拼接sql
		foreach($rowData[0] as $val){
			if($val == null){
				break;
			}
			$sql = $sql."\".$val."\,";
		}
		//去除最后多余的,号
		$sql = substr($sql,0,strlen($sql)-1);
		$sql = $sql."),";
	}
	//去除最后多余的,号
	$sql = substr($sql,0,strlen($sql)-1);
	echo $sql;
	mysql_query($sql);

	mysql_close($con);

打赏

感谢支持与鼓励～