Read Spreadsheet and insert in MySql Database

Topics: Developer Forum, Project Management Forum, User Forum
Aug 23, 2012 at 3:28 PM
Edited Aug 23, 2012 at 3:29 PM

I am working on a very important project and I am wondering how can I take data from a excel spreadsheet and import it into an MySql Database? With that said, I am very new to PHP and I could use a few pointers. Here is my code that reads my spreadsheets

 

 

function phpexcelreader() {
		require_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); 
    $highestColumn      = $worksheet->getHighestColumn(); 
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo "<br>The worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' row.';
    echo '<br>Data: <table border="1"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {
        echo '<tr>';
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
          }
           echo '</tr>';
        }
      echo '</table>';
    
}

 

 

Thanks in advance! God Bless!

Coordinator
Aug 25, 2012 at 8:24 AM

Within the for row loop, instead of echoing each cell value, build it into an SQL insert statement, and then at the end of the col loop, where you currently echo '</tr>', execute the insert

Aug 28, 2012 at 6:03 PM

Thanks Mark!