0

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.stack.imgur.com/0kIxo.png
Code Instinct
  • 1
  • 2
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) 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](http://bobby-tables.com/). **NEVER** put arbitrary data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Jul 15 '19 at 03:54

1 Answers1

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.

dodzb
  • 379
  • 2
  • 5