excelinsert.php
<?php
ini_set('max_file_uploads', "100");
ini_set('post_max_size', '40M');
ini_set('upload_max_filesize', '40M');
error_reporting(0);
include('config.php');
if(isset($_FILES['excel'])){
$errors= array();
foreach($_FILES['excel']['tmp_name'] as $key => $tmp_name ){
$file_name = $key.$_FILES['excel']['name'][$key];
$file_size =$_FILES['excel']['size'][$key];
$file_tmp =$_FILES['excel']['tmp_name'][$key];
$file_type=$_FILES['excel']['type'][$key];
if($file_size > 100097152){
$errors[]='File size must be less than 8 MB';
}
$query=mysql_query("insert into tb_colombia_excel_file set colombia_excel_name='$file_name',status=1") or die(mysql_error());
$desired_dir="excel-file";
if(empty($errors)==true){
if(is_dir("$desired_dir/".$file_name)==false){
move_uploaded_file($file_tmp,"$desired_dir/".$file_name);
}else{
$new_dir="$desired_dir/".$file_name.time();
rename($file_tmp,$new_dir) ;
}
mysql_query($query);
}else{
print_r($errors);
}
}
if(empty($error)){
$msg= "Files Uploaded Successfully ! Click Extract Data Button to Save all data in one single Excel ";
}
} ?> //this code for choose the single or multiple excel file and insert in to data base
<?php
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');
include 'Classes/PHPExcel/IOFactory.php';
$inputFileType = 'Excel2007';
include('config.php');
if($_POST['Submit']=='Extract Data')
{
$my_excel="SELECT * FROM tb_colombia_excel_file WHERE status='1'";
$query=mysql_query($my_excel);
while( $my_excel_row=mysql_fetch_array($query))
{
$file=$my_excel_row['colombia_excel_name'];
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("./excel-file/$file");
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
echo '<table>' . "\n";
for ($row = 3; $row <=$highestRow; ++$row) {
echo '<tr>' . "\n";
for ($col = 0; $col <=$highestColumn ; ++$col) {
'<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";
}
echo '</table>' . "\n";
$importer_name = $objWorksheet->getCellByColumnAndRow(1,3)->getValue();
$exporter_name = $objWorksheet->getCellByColumnAndRow(5, 3)->getValue();
$prod_quantity_unit = $objWorksheet->getCellByColumnAndRow(1,6)->getValue();
$prod_fob_value = $objWorksheet->getCellByColumnAndRow(5,6)->getValue();
$prod_quantity = $objWorksheet->getCellByColumnAndRow(1,8)->getValue();
$prod_fob_unit_price = $objWorksheet->getCellByColumnAndRow(5,8)->getValue();
$prod_gross_weight = $objWorksheet->getCellByColumnAndRow(1,10)->getValue();
$prod_cif_value = $objWorksheet->getCellByColumnAndRow(5,10)->getValue();
$prod_net_weight = $objWorksheet->getCellByColumnAndRow(1,12)->getValue();
$prod_cif_unit_price = $objWorksheet->getCellByColumnAndRow(5,12)->getValue();
$prod_fare_usd = $objWorksheet->getCellByColumnAndRow(5,14)->getValue();
$be_no = $objWorksheet->getCellByColumnAndRow(1,17)->getValue();
$shipping_date = $objWorksheet->getCellByColumnAndRow(5,17)->getValue();
$hs_code = $objWorksheet->getCellByColumnAndRow(1,19)->getValue();
$transport_company = $objWorksheet->getCellByColumnAndRow(5,19)->getValue();
$customs = $objWorksheet->getCellByColumnAndRow(1,21)->getValue();
$transport_mode = $objWorksheet->getCellByColumnAndRow(5,21)->getValue();
$c_o = $objWorksheet->getCellByColumnAndRow(1,23)->getValue();
$country_of_trade = $objWorksheet->getCellByColumnAndRow(5,23)->getValue();
$product_description = $objWorksheet->getCellByColumnAndRow(1,26)->getValue();
$insertquery="INSERT INTO tb_colombia_data(importer_name,exporter_name,product_quantity_unit,product_quantity,product_gross_weight,product_net_weight,product_fob_value,product_fob_unit_price,
product_cif_value,product_cif_unit_price,product_fare_USD,shipping_be_no,shipping_hs_code,shipping_customs,
shipping_country_of_origin,shipping_date,shipping_transport_company,shipping_transport_mode,shipping_country_of_trade,product_description) VALUES
('$importer_name','$exporter_name','$prod_quantity_unit','$prod_quantity','$prod_gross_weight','$prod_net_weight',
'$prod_fob_value','$prod_fob_unit_price','$prod_cif_value','$prod_cif_unit_price','$prod_fare_usd','$be_no','$hs_code','$customs','$c_o','$shipping_date','$transport_company','$transport_mode','$country_of_trade','$product_description')";
mysql_query($insertquery)or die('Errorrtyryhiii: '.mysql_error());
?>
<script>parent.parent.window.location.href="data_colombia.php";</script>
$insertexcel="UPDATE tb_colombia_excel_file SET status=0 WHERE colombia_excel_name='".$my_excel_row['colombia_excel_name']."' ";
mysql_query($insertexcel);
}
}
?>
//AND NOW THIS IS HTML CODE
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Trade Info | Excel to Predefined Excel</title>
<style>
#heading
{
height:150px;width:100%; text-align:justify; background-color:#066; color:#FFF;font-family:Verdana, Geneva, sans-serif, Gadget, sans-serif; font-size:16px; padding:9px 10px 10px 10px;
}
#main
{
height:auto;width:100%;background-color:#FFF;
}
#export
{ color:#33C;margin-top:-5px;}
</style>
</head>
<body>
<div id="main">
<div id="heading">
<p>Please follow these steps:</p>
<p>1.Upload Excel Files of Colombia , you can upload multiple files together.</p>
<p>2.After you see the success message click "Extract Data" Button.</p>
<p>3.Go to Export Page to Download All data in one Single Predefined Excel Format</p>
</div>
<div align="left" style="width:auto;"><a href="index.php"><img src="image/home.png" /></a></div>
<div align="right" style="width:auto">
<a href="colombia_export.php"><img src="image/Download_Icon.png" height="20px" width="20px" />Export Page</a>
</div>
<form name="" method="post" action="" enctype="multipart/form-data">
<p><label style="margin-left:200px; color:#FF0000;">Upload Excel File:</label><input type="file" name="excel[]" id="excel" style="margin-left:70px;" required value="" multiple="multiple" /><input type="submit" name="submit" style="margin-left:0px;;" value="Upload" required /></p>
</form>
<p style="color:#F00; margin-removed200px;"><?php echo $msg?> </p>
<form name="hii" method="POST" action="">
<p>
<label style="margin-left:200px; color:#FF0000;">Extract Data:
<input type="submit" name="Submit" style="margin-left:105px; margin-top:-20px;" value="Extract Data" />
</p>
</form>
</div>
</body>
</html>