How can PHP be used to read data from an Excel spreadsheet and write it to a MySQL database?

To read data from an Excel spreadsheet and write it to a MySQL database using PHP, you can use the PHPExcel library to read the Excel file and then use MySQLi to connect to the database and insert the data. 

// Include PHPExcel library
require_once 'PHPExcel/Classes/PHPExcel.php';

// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Load Excel file
$excel = PHPExcel_IOFactory::load('example.xlsx');

// Get the first sheet
$sheet = $excel->getSheet(0);

// Get highest row and column
$highestRow = $sheet->getHighestDataRow();
$highestColumn = $sheet->getHighestDataColumn();

// Loop through rows and insert data into database
for ($row = 1; $row <= $highestRow; $row++) {
    $data = array();
    for ($col = 'A'; $col <= $highestColumn; $col++) {
        $data[] = $sheet->getCell($col . $row)->getValue();
    }
    $mysqli->query("INSERT INTO table_name (column1, column2, column3) VALUES ('" . $data[0] . "', '" . $data[1] . "', '" . $data[2] . "')");
}

// Close MySQL connection
$mysqli->close();

Keywords

PHP Excel spreadsheet MySQL database reading data writing data

Related Questions