Excel and PHP
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?
Shaggy posted this at 21:01 — 11th December 2009.
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 posted this at 16:39 — 12th December 2009.
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.
Shaggy posted this at 19:29 — 13th December 2009.
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 posted this at 21:08 — 13th December 2009.
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 posted this at 19:26 — 12th December 2009.
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 posted this at 06:08 — 10th February 2010.
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.