Importing data to MySQL from Excel in a PHP Websit

Question

I am building a website in which i want to give the users a choice to upload their excel file which has all the data.

Website is built on PHP, Database used- MySQL.

When a user uploads the excel sheet, all the data has to be imported into my Database. Now i want to do it programatically using PHP. Can anyone help me out with this. The code should also be able to extract data from multiple tabs in the excel file.

Thank you.

Answer 1

You can try with any of the below libraries if you want Excel file itself need to be imported.

http://phpexcel.codeplex.com/

http://sourceforge.net/projects/phpexcelreader/

Note :

Importing from Excel files is harder than improting from CSV files. So I suggest you to provide an option for importing into MySQL from CSV. (Users can convert XLS to CSV using Excel)

Look at PHP function fgetcsv at:

http://ca.php.net/manual/en/function.fgetcsv.php

Eg.

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?>

Answer 2

you should use PHPExcel

http://phpexcel.codeplex.com/

you can use following examples

http://phpexcel.codeplex.com/wikipage?title=Examples

you can also have a look at this link

https://code.google.com/p/php-excel-reader/wiki/Documentation

Answer 3

First, try to avoid Excel format in favor of CSV. It is much faster and simpler.

Also, you can use PHPExcel library.