Read Excel file and save details to database using PHP-Repost

How can you read excel file using php and save that details to the database? My other post is describing Read Excel file and save details to database using PHP.But it is not properly working in new version of excel 2007..etc.My readers are asked to do this for other excel versions.So I’m going to show how to read any format of excel file like 97-2003(xls) or 2007 (xlsx) formats excel files. Also I’ll show how to create/write excel file using php.

In here I’m using different library to do this.That is the “PHPExcel” Library.

Step-1

Creating the Database Connection


Step-2

Including files and libraries

My Excel file is like this.
php_read_excel_file

	/** Include path **/
	set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
	/** PHPExcel_IOFactory */
	include 'PHPExcel/IOFactory.php';
        // Set the Excel file name and path
        $inputFileName = 'users.xlsx'; // this is 2007 new format.

Step-3

Call to the functions to read excel file

 	//  Read your Excel workbook
	try {
		$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
		$objReader = PHPExcel_IOFactory::createReader($inputFileType);
		$objPHPExcel = $objReader->load($inputFileName);
	} catch(Exception $e) {
		die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
	}
	//  Get worksheet dimensions
	$sheet = $objPHPExcel->getSheet(0); // get sheet 1 / active sheet
	$highestRow = $sheet->getHighestRow(); // max row count
	$highestColumn = $sheet->getHighestColumn(); // max column count

Step-4

Display sheet data

	//  Loop through each row of the worksheet in turn
	for ($row = 1; $row <= $highestRow; $row++){
			//  Read a row of data into an array
			$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE);
			print_r($rowData);
	}

Final result is like this.
php_read_excel_file_result

Step-5

Save details to the database

Here, I’m customizing the above loop to insert details to the database.
This is the table structure.
php_read_excel_file_table

	//  Get worksheet dimensions
	$sheet = $objPHPExcel->getSheet(0);
	$highestRow = $sheet->getHighestRow();
	$highestColumn = $sheet->getHighestColumn();
	$sql = ''; // query
	$rowData = '';
	$DataArr ='';
	$sqlCreate = '';
	$result_insert = '';
	$path_parts = pathinfo($inputFileName);
	$excelFileName = $path_parts['filename']; // since PHP 5.2.0
	// create table name
	$tableName = 'excel_reader_'.$excelFileName;
	 if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tableName."'"))!=1)  {
		// create the table
			$sqlCreate ="CREATE TABLE " . $tableName . " (`ID` int(11) NOT NULL AUTO_INCREMENT,`firstName` varchar(255) NOT NULL,`age` int(11),`gender` varchar(255) NOT NULL,`email` varchar(255) NOT NULL,PRIMARY KEY  (`ID`),UNIQUE KEY (`ID`));";
		$result_table = mysql_query($sqlCreate) or die(mysql_error());
	 }
	//  Loop through each row of the worksheet in turn
	for ($row = 1; $row <= $highestRow; $row++){
			//  Read a row of data into an array
			$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE);
			 $DataArr = $rowData[0];
			 if($row==1) {
				// get headings
			 } else {
				$sql  ="INSERT INTO ".$tableName."(ID,firstName,age,gender,email) VALUES('','".$DataArr[0]."','".$DataArr[1]."','".$DataArr[2]."','".$DataArr[3]."');";
	  			//echo $sql;
				$result_insert = mysql_query($sql) or die(mysql_error());
			 }
		//print_r($rowData);
	}
 	if($result_insert) echo 'Succssfully Inserted '.($highestRow-1).' records to the Database';

That’s only. All are done.

Additional Details

Set Sheet number

$sheet = $objPHPExcel->getSheet(0); //sheet 1

Get number of rows

$highestRow = $sheet->getHighestRow();

Get number of columns

$highestColumn = $sheet->getHighestColumn();

Get specific cell address value/text

$objPHPExcel->getActiveSheet()->getCell('A1')->getValue(); // cell A1

Get specific cell calculated value/formula value

// EX: A5 cell is number of users count
$objPHPExcel->getActiveSheet()->getCell('A5')->getCalculatedValue();

Note

-This is working for excel 97-2003 , 2007 and latest format of the excel files.
-If you have suggestions, idea, questions…etc please leave a comment below.
-I am suggesting check the library files and folders then you can get the more information than this.
-In the next post, I’ll show you how to write/create excel file and insert data to it using php.

Here is the full source code to download.
Download Read Excel file Full source Code (920 KB)