In this article we will show you the solution of read excel file in PHP and insert into database, there are various processes involved in reading an XLS file in PHP and entering its data into a database.

Installing an Excel file reader for PHP, such as PHPExcel, Spout, or PHPSpreadsheet, is the first step.

Installing these libraries is feasible thanks to Composer, a PHP dependency manager.

You can read the Excel file & extract the necessary data using the library when it has been installed.

We will now talk about the idea of reading an Excel file into a database using PHP.

Step By Step Guide On Read Excel File In PHP And Insert Into Database :-

The built-in functions & methods of the library can be used to read Excel files; these functions and methods normally need the creation of an object instance of a library's Excel reader class as well as the passage of the Excel file's path.

<?php
// include the PHPExcel library
require_once 'PHPExcel/Classes/PHPExcel.php';
// establish a database connection
$host = 'localhost';
$user = 'username';
$password = 'password';
$database = 'database_name';
$conn = mysqli_connect($host, $user, $password, $database);
// get the Excel file path
$file_path = 'path/to/file.xlsx';
// create a new PHPExcel object
$objPHPExcel = PHPExcel_IOFactory::load($file_path);
// get the active sheet
$worksheet = $objPHPExcel->getActiveSheet();
// get the highest row and column indices
$highest_row = $worksheet->getHighestRow();
$highest_column = $worksheet->getHighestColumn();
// iterate through the rows
for ($row = 1; $row <= $highest_row; $row++) {
  // iterate through the columns
  $data = array();
  for ($col = 'A'; $col <= $highest_column; $col++) {
    // get the value of the cell
    $cell_value = $worksheet->getCell($col . $row)->getValue();
    // add the value to the data array
    $data[] = $cell_value;
  }
  // insert the data into the database
  $query = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
  $stmt = mysqli_prepare($conn, $query);
  mysqli_stmt_bind_param($stmt, 'sss', $data[0], $data[1], $data[2]);
  mysqli_stmt_execute($stmt);
}
// close the database connection
mysqli_close($conn);
?>

1. You can see in this example how we wrote the PHP code to read an Excel file and enter it into a database.
2. With the require once command, we first add the PHPExcel library in this code.
3. Then, we use the mysqli connect function to connect to the MySQL database, providing the host, user name, password, & database name.
4. The next step is to enter the location of the Excel file you want to read and use the PHPExcel IOFactory::load method to generate a new PHPExcel object.
5. Then, using the getActiveSheet method and the getHighestRow & getHighestColumn methods, respectively, we obtain the active worksheet and ascertain the highest row and column indices.
6. We next traverse through the Excel file's rows and columns using nested for loops to extract each cell's value and add it to a data array.
7. The data is then finally inserted into the database using a prepared statement, with the values bound to the statement that use the mysqli stmt bind param function.

Conclusion :-

Thus, we were able to understand the concept of reading an Excel file in PHP & inserting it into a database.

We also discovered that this code reads an Excel file using the PHPExcel package, extracts its data, & inserts it into a MySQL database.

It explains how to connect to a database, read an Excel file, loop throughout its rows and columns, and use a prepared statement to insert the data into the database.

I hope this article on read excel file in PHP and insert into database helps you and the steps and method mentioned above are easy to follow and implement.