note 51780 deleted from ref.com by cmb
| From: | cmb@php.net | Date: | Sun, 30 Jun 2019 07:53:46 +0000 |
| Subject: | note 51780 deleted from ref.com by cmb | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-211423@lists.php.net to get a copy of this message | ||
Note Submitter: Jack dot CHLiang at gmail dot com
----
If you want to search an Excel file and don't connect with ODBC, you can try the function I
provide. It will search a keyword in the Excel find and return its sheet name, text, field and the
row which found the keyword.
<?php
// The example of print out the result
$result = array();
searchEXL("C:/test.xls", "test", $result);
foreach($result as $sheet => $rs){
echo "Found at $sheet";
echo "<table width=\"100%\" border=\"1\"><tr>";
for($i = 0; $i < count($rs["FIELD"]); $i++)
echo "<th>" . $rs["FIELD"][$i] . "</th>";
echo "</tr>";
for($i = 0; $i < count($rs["TEXT"]); $i++) {
echo "<tr>";
for($j = 0; $j < count($rs["FIELD"]); $j++)
echo "<td>" . $rs["ROW"][$i][$j] . "</td>";
echo "</tr>";
}
echo "</table>";
}
/**
* @param $file string The excel file path
* @param $keyword string The keyword
* @param $result array The search result
*/
function searchEXL($file, $keyword, &$result) {
$exlObj = new COM("Excel.Application") or Die ("Did not connect");
$exlObj->Workbooks->Open($file);
$exlBook = $exlObj->ActiveWorkBook;
$exlSheets = $exlBook->Sheets;
for($i = 1; $i <= $exlSheets->Count; $i++) {
$exlSheet = $exlBook->WorkSheets($i);
$sheetName = $exlSheet->Name;
if($exlRange = $exlSheet->Cells->Find($keyword)) {
$col = 1;
while($fields = $exlSheet->Cells(1, $col)) {
if($fields->Text == "")
break;
$result[$sheetName]["FIELD"][] = $fields->Text;
$col++;
}
$firstAddress = $exlRange->Address;
$finding = 1;
$result[$sheetName]["TEXT"][] = $exlRange->Text;
for($j = 1; $j <= count($result[$sheetName]["FIELD"]); $j++) {
$cell = $exlSheet->Cells($exlRange->Row ,$j);
$result[$sheetName]["ROW"][$finding - 1][$j - 1] = $cell->Text;
}
while($exlRange = $exlRange->Cells->Find($keyword)) {
if($exlRange->Address == $firstAddress)
break;
$finding++;
$result[$sheetName]["TEXT"][] = $exlRange->Text;
for($j = 1; $j <= count($result[$sheetName]["FIELD"]); $j++) {
$cell = $exlSheet->Cells($exlRange->Row ,$j);
$result[$sheetName]["ROW"][$finding - 1][$j - 1] = $cell->Text;
}
}
}
}
$exlBook->Close(false);
unset($exlSheets);
$exlObj->Workbooks->Close();
unset($exlBook);
$exlObj->Quit;
unset($exlObj);
}
?>
For more information, please visit my blog site (written in Chinese)
http://www.microsmile.idv.tw/blog/index.php?p=77