PHP Code to subscribe email address

How to read excel sheet data in php and insert to oracle/mysql database?

If you want to read excel sheet using php programming then you will have to add a excel reader liberary. This liberary has few functions that read cells of excel sheet and display on screen
I will show you how to upload excel file and read it and save excel sheeet data to mysql.
First create database table with few columns then write php code mysql query that will map with excel sheet data and put data into mysql database.
Here is code to upload excel sheet

index.php

<div style=”padding-left:200px;”>
<form name=”import” method=”post” action=”processExcel.php” enctype=”multipart/form-data”>
<input type=”file” name=”file” /><br />
<input type=”submit” name=”submit” value=”Submit” class=”btn btn-lg btn-primary btn-block btn-signin” id=”h-fill-animation-start” style=”height: 50px;width:100px” />
</form>
</div>

This is form that accepts excel sheet file and upload on server

processExcel.php

include ‘excel_reader.php’;     // include the class
$excel = new PhpExcelReader;
if(isset($_POST[“submit”]))
{
if(isset($_FILES[‘file’][‘name’])  && $_FILES[‘file’][‘name’] !=”) {
$array_file=array();
$max_file_size = 10000000000000000; //100 kb

$array_errors=array();
$new_file_name = ”;
$file_size =$_FILES[‘file’][‘size’];
$file_loc =$_FILES[‘file’][‘tmp_name’];
$file_type=$_FILES[‘file’][‘type’];
$new_file_name = $_FILES[‘file’][‘name’];
$userfile_extn = pathinfo($_FILES[‘file’][‘name’],PATHINFO_EXTENSION);
$new_file_name = str_replace(‘ ‘, ‘_’, $new_file_name);
$new_size = $file_size/1024;  
if ($_FILES[‘file’][‘size’] > $max_file_size) {
$size_msg = $new_file_name.” is not uploaded due to large size!.”;
array_push($array_errors, $size_msg);
continue; // Skip large files
}
$final_file_name=$new_file_name;
if(move_uploaded_file($file_loc,$final_file_name))
{
$file_path = $final_file_name;
$excel->read($file_path);
$nr_sheets = count($excel->sheets);       // gets the number of sheets
$excel_data = ”;              // to store the the html tables with data of each sheet
//for($i=0; $i<$nr_sheets; $i++) {
$excel_data .=  sheetData($excel->sheets[0]) .'<br/>’;  

header(“Location: index.php”);
//}
}

}else{
$_SESSION[‘message’] = “<div class=’alert alert-danger fade in’>No file have been Selected</div>”;
header(“Location: index.php”);
}

}

In above code i have writtten code that accepts excel file and process it. and red line code is sheetData function that is called after uploading.
 Starting lines in this code are to include excel read liberary  and also i have created object for that liberary
Please download the excel reader liberary from following link and include in code
https://sourceforge.net/projects/phpexcelreader/

function sheetData($sheet) {

$conn=”(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.82.131)(PORT = 1528)))(CONNECT_DATA=(SID=PROD)))”;
$conn = oci_connect(‘WEBERP’,’weberp123.’, $conn);
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e[‘message’], ENT_QUOTES), E_USER_ERROR);
}
$date = $sheet[‘cells’][2][3];
$source = $sheet[‘cells’][2][5];
$org_id = $sheet[‘cells’][2][13];
$_SESSION[‘date’] = $date;
$_SESSION[‘source’] = $source;
$_SESSION[‘org_id’] = $org_id;

$sel_query = “SELECT * from CUST_RECEIPTS WHERE DEPOSIT_DATE='”.$date.”‘ AND BATCH_SOURCE_NAME ='”.$source.”‘ AND ORG_ID='”.$org_id.”‘”;

    $result1 = oci_parse($conn, $sel_query);
    oci_execute($result1);
    $tmpcount = oci_fetch($result1);
     if ($tmpcount==1) {
$del_query = “DELETE FROM weberp.CUST_RECEIPTS WHERE DEPOSIT_DATE='”.$date.”‘ AND BATCH_SOURCE_NAME ='”.$source.”‘ AND ORG_ID='”.$org_id.”‘”;
$result2 = oci_parse($conn, $del_query);
oci_execute($result2);


  while($x <= $sheet[‘numRows’]) {
    $y = 1;
$sql = “INSERT INTO tabl (col1,col2, col3, col4) VALUES(“;

$countCols = 1;
    while($y <= $sheet[‘numCols’]) {
      $cell = isset($sheet[‘cells’][$x][$y]) ? $sheet[‘cells’][$x][$y] : ”;
$sql .= “‘”.$cell.”‘”;
if($countCols<$sheet[‘numCols’]){
$sql .= “,”;
}

$countCols++;
      $y++;
    }
$sql .= “)”;

$stid = oci_parse($conn, $sql);

$bool = oci_execute($stid);
if($bool){
$_SESSION[‘message’] = “<div class=’alert alert-success fade in’>Excel Sheet have been uploaded</div>”;
}
else{
$_SESSION[‘message’] = “<div class=’alert alert-danger fade in’>There is some error while inserting data</div>”;
}

    $x++;
  }
  

  return $re;     // ends and returns the html table
}


In above function excel sheet data is read cell by cell and a $sql query is generated. at the end of loop mysql execute statement is run.

Leave a Reply

Your email address will not be published. Required fields are marked *