Read Excel file

Topics: Developer Forum, User Forum
Feb 26, 2009 at 2:35 PM

Hey,

I want to replace some data when an excel is read.
The numbers 1 to 10 is a field should be replaced by images. is this possible?



/** Include path **/
ini_set('include_path', ini_get('include_path').':/srv/www/htdocs/intranet/frsbe/life/includes/Classes/');

#include 'PHPExcel.php';
include 'PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getSheetByName($sheet);

$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
}

echo '</tr>' . "\n";
}
echo '</table>' . "\n";

 

}

Developer
Feb 26, 2009 at 2:48 PM
Do you mean this?
  1. In an Excel template file you type numbers 1-10 in some fields.
  2. You need to generate an Excel file where numbers are replaced by images.
If yes, you could load the Excel file and loop through all cells. Then let the script insert the appropriate image when it finds a number 1-10.

Feb 26, 2009 at 3:11 PM
1 2 3
BRUCE SPRINGSTEEN FRANZ FERDINAND STAN VAN SAMANG
WORKING ON A (2CD) TONIGHT TAKE IT FROM ME
BRUCE SPRINGSTEEN FRANZ FERDINAND STAN VAN SAMANG
WORKING ON A DREAM TONIGHT (2cd) TAKE IT FROM ME
BRUCE SPRINGSTEEN FRANZ FERDINAND STAN VAN SAMANG
WORKING ON A (2CD) TONIGHT TAKE IT FROM ME


This is what the imput is .... an excel sheet with a chart.
I want to display this on a web site using phpexcel. I wonder if i can replace the 1 -2 - 3 in my phpcode

something like

if(is_number($field){
echo "<img src\"./images/chart".$field.".png\"> 
}

Developer
Feb 26, 2009 at 3:26 PM
You can type some strings e.g. _IMAGE1_, _IMAGE2_, etc in your Excel sheet, and then replace those strings (fields) with HTML tags after generating the table. Example:

$html = '<table><tr><td>_IMAGE1_</td></tr>...</table>';

str_replace('_IMAGE1_', '<img src="image1.png">', $html);

Once you get this working you can make it more sophisticated by using replacements via regular expressions to automate for n images.

Feb 27, 2009 at 7:19 AM

hmmm was not wat i was looking for

but this works ;-)

echo "<table class=$type>";
for ($row = 1; $row <= $highestRow; ++$row) {
echo "<tr>";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$value= $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
if(is_numeric($value)){
if(in_array($value,$charts)){
echo "<td div align=\"center\"><img src=\"./images/".$value."-".$type.".png\"></td>";
}
else{
echo "<td div align=\"center\">" . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . "</td>";
}
}
else{
echo "<td div align=\"center\">" . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . "</td>";
}
#if(is_numeric( $objWorksheet->getCellByColumnAndRow($col, $row)->getValue())
#{
# echo "<td div align=\"center\">" . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . "i jaaaaaaaaa</td>";
#}
#else{
#echo "<td div align=\"center\">" . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . "</td>";
#}
}
echo "</tr>";
}
echo "</table>";