samedi 25 avril 2015

Php code to import excel file to database


I want to import Excel data to my database using PHPExcel. Below is my code.

When I upload the excel file and submit the form, I can echo out the table but no data is being stored in the database, Can anyone please help me with this.

<?php

# Database Connection
$dbc = mysqli_connect('localhost', 'root', 'ron143', 'tcc') OR die('Error: '.mysqli_connect_error());

/** Include path **/
#set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel/Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel.php';

/** PHPExcel_IOFactory */
include 'PHPExcel/Classes/PHPExcel/IOFactory.php';

# Upload the file to server
$file = $_FILES["file"]["name"];
$target_dir = "../../temp/";
$target_file = $target_dir . basename($file);
move_uploaded_file($_FILES["file"]["tmp_name"], $target_file);
$xls_url = "$target_dir$file";

$inputFileName = $xls_url;
$inputFileType = $_POST['file_type'];
$sheetname = '0'; /** input the worksheet number to read. 0 for first and 1 for second worksheet */

/**  Create a new Reader of the type defined in $inputFileType  **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

/**  Advise the Reader that we only want to load cell data and not its formating or any formula set on it **/
$objReader->setReadDataOnly(true);

/**  Load $inputFileName to a PHPExcel Object  **/
$objPHPExcel = $objReader->load($inputFileName);

//  Get worksheet dimensions
$objWorksheet = $objPHPExcel->getSheet($sheetname);
$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 2; $row <= $highestRow; ++$row) {
    echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {

    echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";

  }
  echo '</tr>' . "\n";
}
  echo '</tr>' . "\n";

#$q = "INSERT INTO products (product_name, product_code, category1, category1_id, category2, category2_id, stylename, grams, thickness, width, length, color_ground, color_border, material, backing, reed, weave, ply, pile, care, precaution, use, tag, image, link) VALUES(`".$val[0]."`, `".$val[1]."`, `".$val[2]."`, `".$val[3]."`, `".$val[4]."`, `".$val[5]."`, `".$val[6]."`, `".$val[7]."`, `".$val[8]."`, `".$val[9]."`, `".$val[10]."`, `".$val[11]."`, `".$val[12]."`, `".$val[13]."`, `".$val[14]."`, `".$val[15]."`, `".$val[16]."`, `".$val[17]."`, `".$val[18]."`, `".$val[19]."`, `".$val[20]."`, `".$val[21]."`, `".$val[22]."`, `".$val[23]."`, `".$val[24]."`)";
#$r = mysqli_query($dbc, $q);

if($q!='') $q.=',';
$q.='(\''.mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colproduct_name, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colproduct_code, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory1, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory1_id, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory2, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcategory2_id, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colstylename, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colgrams, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colthickness, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colwidth, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($collength, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcolor_ground, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcolor_border, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colmaterial, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colbacking, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colreed, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colweave, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colply, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colpile, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colcare, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colprecaution, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($coluse, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($coltag, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($colimage, $row)->getValue()).'\',\''.
        mysqli_real_escape_string($dbc, $objWorksheet->getCellByColumnAndRow($collink, $row)->getValue()).'\')';

if($q!='') {
    $q='INSERT INTO products (product_name, product_code, category1, category1_id, category2, category2_id, stylename, grams, thickness, width, length, color_ground, color_border, material, backing, reed, weave, ply, pile, care, precaution, use, tag, image, link) VALUES '.$q;
    $r=mysqli_query($dbc, $q);
    }


?>


Aucun commentaire:

Enregistrer un commentaire