Parsing Excel data from specific row and columns to MySQL using PHP

Question

currently im doing my homework project to parse specific data from excel to MySQL using PHP. The project is about uploading an excel file and then the excel data supposed to be in MySQL after excel file uploaded.

The problem that im facing is, my homework project requirement is only parse specific row and column amongst the data exists in the excel file. I have already try using codes that i found, but it doesn't work. It parse everything in the excel files to MySQL table. Not the specific Row and Columns data that i expected.

I'm still looking for a solution for this. Spent almost 2 weeks for this but still i'm facing a dead end. My deadline project in 1 week. So, i would be really thankful if someone could help me and get me a solution of this.

I have tried on this PHP script. But, all the data in excel were parsed to MySQL table and the data messed the row and column in MySQL table. I want only the specific part (row and column) of excel files to be inserted to MySQL table that i've crated. i provided screenshot and the part that i want to be parsed to MySQL table highlighted with red color.

if(!empty($_FILES['excelfile']['name'])){
  // Get File extension eg. 'xlsx' to check file is excel sheet
  $pathinfo = pathinfo($_FILES['excelfile']['name']);

  // check file has extension xlsx, xls and also check
  // file is not empty
  if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls')
      && $_FILES['excelfile']['size'] > 0 ){

          $file = $_FILES['excelfile']['tmp_name'];
          $reader = ReaderFactory::create(Type::XLSX);

          $reader->open($file);
          $count = 0;

          foreach ($reader->getSheetIterator() as $sheet){

              foreach($sheet->getRowIterator () as $row)
              {
                  if ($count > 0){
                      $name = $row[2];
                      $job_schedule = $row[3];
                      $overtime = $row[4];
                      $notes = $row[5];
                      $start_working = $row[6];    
                      $finished_working = $row[7];

                      $qry = "INSERT INTO `timesheet` (`name`,`job_schedule`, `overtime`,`notes`, `start_working`,`finished_working`) VALUES ('$name','$job_schedule','$overtime','$notes','$start_working','$finished_working')";
                      $res = mysqli_query($con,$qry);
                  }
                  $count++;
              }

          }

          if($res){
              echo "Success";
          }
          else{
              echo "failed";
          }
          $reader->close();
      }
      else{
          echo "Excel format is not supported";
      }

```[Here is the SCREENSHOT LINK]


[1]: https://i.sstatic.net/0kIxo.png

WARNING: When using mysqli you should be using parameterized queries and bind_param to add any data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put arbitrary data of any kind directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Jul 15, 2019 at 3:54

dodzb · Accepted Answer · 2019-07-17 02:19:50Z

0

You can follow this link:

Exporting data from php to excel

I recommend use PHPExcel php class, just plug and play, I've done it myself. It's works better than trying to figure everthing by yourself.

answered Jul 17, 2019 at 2:19

dodzb

3792 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parsing Excel data from specific row and columns to MySQL using PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related