Excel and PHP

They have: 426 posts

Joined: Feb 2005

SO I need to specify keywords in an excel spreadsheet and read this from PHP.

I am not sure how to structure my excel spreadsheet.

I have three columns,

1) Top level Category
2) Sub category
3) Keywords

So at present each row is repeating the "Top level Category" and "Sub Category" for each keyword.

For example:

Top level 1 | Subcat 1 | Keyword 1
Top level 1 | Subcat 1 | Keyword 2
Top level 1 | Subcat 1 | Keyword 3

I then get repeated data but I need to specify this so I know which category each keyword is from.

Also, How can I read this from PHP?

They have: 121 posts

Joined: Dec 2008

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.

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

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.

They have: 121 posts

Joined: Dec 2008

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

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

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

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

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

PHPonly's picture

They have: 13 posts

Joined: Apr 2009

Great post and excellent share. Thank you!

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.