Great post and excellent

PHPonly — Wed, 10 Feb 2010 06:08:01 +0000

Great post and excellent share. Thank you!

Ya, I know. My only point

pr0gr4mm3r — Sun, 13 Dec 2009 21:08:46 +0000

Ya, I know. My only point was that it might be easier to save it in the CSV format instead.

I think the question was

Shaggy — Sun, 13 Dec 2009 19:29:16 +0000

I think the question was about reading Excel files. I hope my including write examples didn't confuse things.

I agree that doing a CSV will

Greg K — Sat, 12 Dec 2009 19:26:00 +0000

I agree that doing a CSV will be a LOT easier.

Assuming you get the reading of the file in place, you can use the following. The WHILE loop is where you would be looping over all the data, so that $aryRow is:

$aryRow[0] = Category
$aryRow[1] = Sub Cat
$aryRow[2] = Keyword

There are several ways to do this, this is just one generalized way without knowing what you will be doing with the data.

$aryCategory = array();
$arySubCategory = array();
$aryKeyword = array();

while ($aryRow = [[GET A RECORD]]) {
  $intCatID = array_search($aryRow[0],$aryCategory);
  if ($intCatID===false) {
    $intCatID = count($aryCategory);
    $aryCategory[$intCatID] = $aryRow[0];
  }
  $intSubID = array_search($aryRow[1],$arySubCat);
  if ($intSubID===false) {
    $intSubID = count($arySubCat);
    $arySubCat[$intSubID] = $aryRow[1];
  }
  $aryKeyword[] = array('CategoryID'=$intCatID,'SubCatID'=>$intSubID,'Keyword'=>$aryRow[2]);
}

Now, if you are going to need use that as the final step and from there lookup the Cat/SubCat based upon keyword, the change it to:

$aryCategory = array();
$arySubCategory = array();
$aryKeyword = array();
$aryKeyData = array();

while ($aryRow = [[GET A RECORD]]) {
  $intCatID = array_search($aryRow[0],$aryCategory);
  if ($intCatID===false) {
    $intCatID = count($aryCategory);
    $aryCategory[$intCatID] = $aryRow[0];
  }
  $intSubID = array_search($aryRow[1],$arySubCat);
  if ($intSubID===false) {
    $intSubID = count($arySubCat);
    $arySubCat[$intSubID] = $aryRow[1];
  }
  $aryKeyword[] = $aryRow[2];
  $aryKeyData[] = array('CategoryID'=$intCatID,'SubCatID'=>$intSubID);
}

....

$strKeyword = 'keyword to look up';
$intKeyID = array_search($strKeyword,$aryKeyword);
if ($intKeyID !== false) {
  echo "Keyword = ",$strKeyword,"<br />\n";
  echo "Category = ",$aryCatgory[$aryKeyData[$intKeyID]['CategoryID']],"<br />\n";
  echo "SubCategory = ",$arySubCat[$aryKeyData[$intKeyID]['SubCatID']],"<br />\n";
}
else {
  echo "Keyword not found.<br />\n";
}

-Greg

I think it would be better to

pr0gr4mm3r — Sat, 12 Dec 2009 16:39:31 +0000

I think it would be better to save the Excel file as CSV, and read it using the CSV Read Functions in PHP. While the Excel PHP class is a good option, I like to stick to native libraries as much as possible.

Are you asking how to read

Shaggy — Fri, 11 Dec 2009 21:01:19 +0000

Are you asking how to read from Excel files?

I've used PHPExcel ( a PHP class library ) with success:
http://www.codeplex.com/PHPExcel/

Read Example:
include 'PHPExcel/Reader/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load('spreadsheet.xlsx');
$objPHPExcel->setActiveSheetIndex(0);
$cellValue = $objPHPExcel->getActiveSheet()->getCell('DC14')->getValue();

Write Example:
include 'PHPExcel/Writer/Excel2007.php';
$oExcel = new PHPExcel();
$oExcel->setActiveSheetIndex(0);
$oExcel->getActiveSheet()->SetCellValue('A1', 'Hello');
$oExcel->getActiveSheet()->SetCellValue('B2', 'World!');

$oWrite = new PHPExcel_Writer_Excel2007($oExcel);
$oWrite->save('myfile.xlsx');

Cheers,
Shaggy.