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);
}
?>
