Phpexcel upload excel file to database

Question

| Posted on May 12, 2021 |

Table of Contents Show

https://github.com/PHPOffice/PHPExcel
Not the answer you're looking for? Browse other questions tagged php excel import or ask your own question.
How do you upload data from Excel to MySQL using PHP?
How do I export data from Excel to PHPExcel?
How do I insert Excel data into MySQL database?
How do I import a XLSX file into MySQL?

here is an example of how you can Import Excel to MySQL using PHP. Import Excel to MySQL database is a very important method. Sometimes you have a very big list of items in an excel file and at sometimes you need to import all that list into your database so you can see that list on your website. You may also like PHPExcel import excel to mysql in Codeigniter and How to Convert Excel Sheet into PHP Array using PHPExcel.

Download PHPExcel library.

Steps For PHPExcel import Excel to mysql in PHP

Step 1. Install PHPExcel Library

Unzip or extract the downloaded PHPExcel library files and copy Class directory inside files any folder and call library file.

require_once 'phpexcel/PHPExcel/IOFactory.php';

Step 2. Create HTML Form

Here we create the HTML form which contains the one file input element and one button.

<form method="post" action="" enctype="multipart/form-data" class="form-horizontal">
    <div class="row">
        <div class="col-sm-12">
            <div class="row">
                <label class="col-sm-3 label-on-left" style="margin-top: -16px;">Upload Excel</label>          
                <div class="col-md-6">
                    <input name="result_file"  required=""  type="file">
                </div>
            </div>
        </div>
    </div>
    
    <div class="row" >
        <div class="col-sm-3" style="width: 31%;margin-top: 15px;"> 
            <div class="pull-right hidden-print">
                <button type="submit" name="upload_excel" class="btn btn-primary"> Upload Excel</button>
            </div>
        </div>
    </div>   
</form>

Step 3. Press Upload Excel

In this step you are press upload Excel button after save file one specific folder and read file. PHPExcel_IOFactory read excel file and create array after store one by one record in mysql with check duplicate record.

<?php
    require_once 'phpexcel/PHPExcel/IOFactory.php';
    if(isset($_POST['upload_excel']))
    {
        $file_info = $_FILES["result_file"]["name"];
        $file_directory = "uploads/excel_mail/";
        $new_file_name = date("dmY").".". $file_info["extension"];
        move_uploaded_file($_FILES["result_file"]["tmp_name"], $file_directory . $new_file_name);
        $file_type	= PHPExcel_IOFactory::identify($file_directory . $new_file_name);
        $objReader	= PHPExcel_IOFactory::createReader($file_type);
        $objPHPExcel = $objReader->load($file_directory . $new_file_name);
        $sheet_data	= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

        foreach ($sheet_data as $row)
        {
            if(!empty($row['C']))
            {
                $checkemail = mysqli_query($conn,'SELECT * FROM `wo_emaillist` WHERE email = "'.$row['C'].'" ');
                if(mysqli_num_rows($checkemail) == '0')
                {
                    mysqli_query($conn,'INSERT INTO `wo_emaillist` (firstname,gender,email) VALUES ("'.$row['A'].'","'.$row['B'].'","'.$row['C'].'") ');
                }
            }
        }
        $updatemsg = "File Successfully Imported!";
        $updatemsgtype = 1;
    }
?>

Post Navigation ← Previous PostNext Post →

I tried to search for some plugins to import Excel file into MySQL database, one of them is http://code.google.com/p/php-excel-reader/

The tool is so powerful that it displays the entire excel content into html.

However, I think I just need to read the Excel file and extract the contents, for example, into an array, and then write a SQL statement for entering into the database.

Would there be any good codes and packages? Thanks!

JasonMArcher

13.5k22 gold badges55 silver badges51 bronze badges

asked Jul 12, 2012 at 8:33

3

This is best plugin with proper documentation and examples

https://github.com/PHPOffice/PHPExcel

Plus point: you can ask for help in its discussion forum and you will get response within a day from the author itself, really impressive.

answered Jul 12, 2012 at 8:37

diEchodiEcho

52.6k41 gold badges172 silver badges241 bronze badges

4

Sometimes I need to import large xlsx files into database, so I use spreadsheet-reader as it can read file per-row. It is very memory-efficient way to import.

<?php
    // If you need to parse XLS files, include php-excel-reader
    require('php-excel-reader/excel_reader2.php');

    require('SpreadsheetReader.php');

    $Reader = new SpreadsheetReader('example.xlsx');
    // insert every row just after reading it
    foreach ($Reader as $row)
    {
        $db->insert($row);
    }
?>

https://github.com/nuovo/spreadsheet-reader

answered Jan 6, 2015 at 12:25

shukshin.ivanshukshin.ivan

10.7k3 gold badges50 silver badges67 bronze badges

0

If you can convert .xls to .csv before processing, you can use the query below to import the csv to the database:

load data local infile 'FILE.CSV' into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\n' (FIELD1,FIELD2,FIELD3)

Jon Taylor

7,8605 gold badges29 silver badges54 bronze badges

answered Jul 12, 2012 at 9:03

user1259132user1259132

3202 gold badges3 silver badges11 bronze badges

1

If you save the excel file as a CSV file then you can import it into a mysql database using tools such as PHPMyAdmin

Im not sure if this would help in your situation, but a csv file either manually or programatically would be a lot easier to parse into a database than an excel file I would have thought.

EDIT: I would however suggest looking at the other answers rather than mine since @diEcho answer seems more appropriate.

answered Jul 12, 2012 at 8:36

Jon TaylorJon Taylor

7,8605 gold badges29 silver badges54 bronze badges

I wrote an inherited class:

<?php
class ExcelReader extends Spreadsheet_Excel_Reader {	
	
 function GetInArray($sheet=0) {

  $result = array();

   for($row=1; $row<=$this->rowcount($sheet); $row++) {
    for($col=1;$col<=$this->colcount($sheet);$col++) {
     if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) {
      $val = $this->val($row,$col,$sheet);
      $result[$row][$col] = $val;
     }
    }
   }
  return $result;
 }

}
?>

So I can do this:

<?php

 $data = new ExcelReader("any_excel_file.xls");
 print_r($data->GetInArray());

?>

answered Feb 23, 2015 at 13:42

Not the answer you're looking for? Browse other questions tagged php excel import or ask your own question.

How do you upload data from Excel to MySQL using PHP?

PHP - import excel file into mysql database tutorial.

1.Download Package..

2.Create db_config.php file..

3.Create index.php file..

4.Create excelUpload.php..

5.Create Upload Folder..

Step 1: Download Package..

Step 2: Create db_config.php file..

db_config.php..

How do I export data from Excel to PHPExcel?

Get the data that you want from limesurvey, and use setCellValue() to store those data values in the cells where you want to store it. The Quadratic. php example file in /Tests might help as a starting point: it takes data from an input form and sets it to cells in an Excel workbook. Show activity on this post.