o dear unfortune, excel2html.php was to be coded, but a same file was duplicated now i am sending u complete.
--------------------
am sending u the code, make sure to create a database and a table in it to copy excel data, plz change the database name according to ur databade in file inputExcel.php.
there r 3 files in this regard.
here is coding of 3 files and u will run index.html to copy data.
note that only IE will do bcoz mozilla donot send file full path.
-------------
index.html
-------------
<html>
<head>
<title>Welcome to Excel2Html :: Login ::</title>
<script language="javascript">
function validate(){
if(form1.inFile.value == ""){
alert("Please insert filename!");
form1.inFile.focus();
return false;
}
return true;
}
</script>
</head>
<body>
<div align="center">
<br><h1>Input the Excel File values</h1><br><br>
<form name="form1" id="form1" method="post" action="inputExcel.php" onSubmit="return validate()">
<center>
<table width="45%" border="1" cellpadding="0" cellspacing="0" bordercolor="#A0D0FF">
<tr> <td>Input File</td><td colspan='2'><INPUT type="file" name='inFile'/></td></tr>
<tr> <td>Worksheet</td><td><INPUT name='worksheet'/></td><td> </td></tr>
<tr> <td>Start Cell</td><td><INPUT name='startCell'/></td><td> </td></tr>
<tr> <td>End Cell</td><td><INPUT name='endCell'/></td><td> </td></tr>
<tr> <td> </td><td> </td><td> </td></tr>
<tr> <td colspan="3" align="center"><INPUT type="submit" name="submit" value="Submit" /></td></tr>
</table>
</center>
</form>
</div>
</body>
</html>
---------------------------
inputExcel.php
--------------------------
<?php
require ("excel2html.php");
// require ("function.php");
//$pathInName = $_POST['inFile'];
$pathInName = isset($_REQUEST['inFile'])? $_REQUEST['inFile']:'';
echo $pathInName;
$worksheet = $_POST['worksheet'];
$startCell = $_POST['startCell'];
$endCell = $_POST['endCell'];
$pathInName = realpath($pathInName); //some browser store differently
echo $pathInName;
// Instantiate Excel
$E = new Excel2html;
$workbook = $E->myfile($pathInName);
$pathin = $E->mypath($pathInName);
// Default value for Worksheet
if (!$worksheet)$worksheet = 'Sheet1';
// Open the workbook
$E->XL($workbook, $pathin, $sheet);
// Default value for Starting Cell
if (!$startCell)$startCell = 'A1';
// Default value for End Cell
if (!$endCell)$endCell = 'd5';
$sheet = $worksheet;
$startStr = $E->getCellString($startCell);
$startNum = $E->getCellNum($startCell);
$endStr = $E->getCellString($endCell);
$endNum = $E->getCellNum($endCell);
// Read the content of range of cells and output to html file
$cellRange = $startCell . ":" . $endCell;
$content = $E->readrange($sheet, $cellRange);
echo "<html><head><title>Excel to HTML</title></head>";
//echo "<body style='background-color: #DEEEF3'>";
echo "<div align='center'><br>
<table border='1'";
if (is_array($content)) {
$count = sizeof($content);
for ($num = 0; $num < $count; $num++)
if (is_array($content[$num])) {
$countArray = sizeof($content[$num]);
if ($num == 0) {
echo "<tr bgcolor='#cococo' align='center'>";
for ($numArray = 0; $numArray < $countArray; $numArray++) {
/* echo "<td>";
echo strtoupper($startStr++); // COLUMNS
echo "</td>";*/
}
echo "</tr>";
}
echo "<tr>";
for ($numArray = 0; $numArray < $countArray; $numArray++) {
/*if ($num == 0) {
if ($numArray == 0)
print "<td bgcolor='#COCOCO'>" . $startNum++ . "</td>";
echo "<td bgcolor='blue'><font size='5' color='white'>";
echo $content[$num][$numArray]; echo "row1";
echo "</font></td>";
} else {
if ($numArray == 0)*/
$startNum++;
//echo "num = " . $num . "--numArray = " . $numArray;
//echo $content[$num][$numArray];
$a = $content[$num][$numArray]; echo $a; $numArray++;
$b = $content[$num][$numArray]; echo $b; $numArray++;
$c = $content[$num][$numArray]; echo $c;
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
mysql_query("INSERT INTO mytable(d_code, destination, rate) VALUES('$a', '$b', '$c')");
//}
}
echo "</tr>";
}
}
echo "</table></div><br><br>";
echo "Selected data has been copied to MySql <br /><br />PLEASE CLOSE THIS WINDOW";
/*
echo $content[0][0] . $content[0][1] . $content[0][2];
$a = $content[0][0];
$b = $content[0][1];
$c = $content[0][2];
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
mysql_query("INSERT INTO mytable
(name, extension, email) VALUES('$a', '$b', '$c')");
*/
echo "</body></html>";
// Close the file
$E->closexl();
unset ($E);
exit();
?>
-------------------------
excel2html.php
------------------------
<?php
class Excel2html {
/* variables */
var $pathin;
var $workbook;
var $sheet;
var $range;
var $celladdress;
/* Constructor */
function excel2html()
{
// Instantiate Excel
$this->ex = new COM("Excel.sheet") or Die ("Did not instantiate Excel");
return 1;
}
function XL($workbook, $pathin = "", $sheet = "Sheet1")
{
if ($workbook) {
// Load the workbook
$wkb = $this->ex->application->Workbooks->Open($pathin . $workbook) or Die ("Did not open $pathin $workbook");
} else {
// New workbook
$wkb = $this->ex->application->Workbooks->Add or Die ("Unable to add a workbook");
}
if ($sheet) {
// Activate the sheet
$sheets = $wkb->Worksheets($sheet) or Die ("Unable to activate $sheet");
} else {
// new sheet
$sheet = "Sheet1" ;
}
// Excel Won't prompt the user when replacing or closing workbooks
// Comment the line below if you want Excel to prompt
$this->ex->application->DisplayAlerts = "False";
return 1;
}
function readrange($sheet = "Sheet1", $range)
{
// Read all the cells in the range to $result and return it
unset ($result);
$range = trim($range);
// Determine start and end of range
$tokstart = strtok($range, ":");
$tokend = strtok(":");
if ($tokend == "") {
// Read one single cell
$sheets = $this->ex->Application->Worksheets($sheet);
$sheets->activate;
// Select the cell
$selcell = $sheets->Range($range);
$selcell->activate;
return $selcell->value;
}
// Read a range of cells
// determine column and row numbers
$sheets = $this->ex->Application->Worksheets($sheet);
$sheets->activate;
$rgstart = $sheets->range($tokstart);
$colstart = $rgstart->column;
$rowstart = $rgstart->row;
$rgend = $sheets->range($tokend);
$colend = $rgend->column;
$rowend = $rgend->row;
if ($colstart > $colend or $rowstart > $rowend) {
Print ("Notation Error! Cell Column/Row should be increasing.");
return;
}
// Now read each cell
if ($colstart == $colend) {
// Read Vertically
$j = 0;
For ($i = $rowstart; $i <= $rowend; $i++) {
$selcell = $sheets->cells($i, $colstart);
$selcell->activate;
$result[$j] = $selcell->value;
$j++;
}
} else {
// Read vertically
$k = 0;
For ($l = $rowstart; $l <= $rowend; $l++) {
// Read horizontally
$j = 0;
For ($i = $colstart; $i <= $colend; $i++) {
$selcell = $sheets->cells($rowstart, $i);
$selcell->activate;
$result[$j] = $selcell->value;
// echo "result:$result[$j]<br>";
$j++;
}
$resultArray[$k] = $result;
$rowstart++;
$k++;
}
}
return $resultArray;
}
// Returns the file name
function myfile($pathin)
{
$pathComponent = explode("\\", $pathin);
for($i = 0;$i < count($pathComponent)-1;$i++)
if (!$path)
$path = $pathComponent[$i];
else
$path = $path . "\\" . $pathComponent[$i];
return $workbook = $pathComponent[count($pathComponent)-1];
}
// Returns the file path
function mypath($pathin)
{
$pathComponent = explode("\\", $pathin);
for($i = 0;$i < count($pathComponent)-1;$i++)
if (!$path)
$path = $pathComponent[$i];
else
$path = $path . "\\" . $pathComponent[$i];
return $pathin = $path . "\\";
}
// Returns the Starting Cell String value
function getCellString($celladdress)
{
for($i = 1;$i <= strlen($celladdress);$i++) {
if (is_numeric(substr($celladdress, - ($i)))) {
$cellStr = substr($celladdress, 0, strlen($celladdress) - $i);
}
}
return $cellStr;
}
// Returns the starting cell Numeric value
function getCellNum($celladdress)
{
for($i = 1;$i <= strlen($celladdress);$i++) {
if (is_numeric(substr($celladdress, - ($i))))
$cellnum = substr($celladdress, - $i) ;
}
return $cellnum;
}
function closexl()
{
// Close active workbook without prompt from Excel
$this->ex->application->ActiveWorkbook->Close("False");
return 1;
}
}
/* end of Excel class */
?>
-------------------------------
take care
from Muhammad Farooq
fffsoft@hotmail.com
----------------------------
