By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,965 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,965 IT Pros & Developers. It's quick & easy.

howto stop exponent to float conversion in phpExcelreader

P: 1
Hello it developers,

I'm using PHPExcelReader which I downloaded from sourceforge
(http://sourceforge.net/projects/phpexcelreader) To read excel xls files in php.

In the file reader.php
( http://scripts.ringsworld.com/database-tools/xls2mysql/inc/Excel/reader.php.html ) Somewhere there happens a exponent to float conversion so that strings as 1E3 2E9 are converted to their floating point values. I'm trying to put this functionality off in the source, but don't know exactly howto do this because I'm not very familar with the code. Well actually I've tried the following which didn't worked maybe someone has the experience time or encountered the same problems and could help me with the issue



<?
var $numberFormats = array(
0x1 => "%1.0f", // "0"
0x2 => "%1.2f", // "0.00",
0x3 => "%1.0f", //"#,##0",
0x4 => "%1.2f", //"#,##0.00",
0x5 => "%1.0f", /*"$#,##0;($#,##0)",*/
0x6 => '$%1.0f', /*"$#,##0;($#,##0)",*/
0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x9 => '%1.0f%%', // "0%"
0xa => '%1.2f%%', // "0.00%"
0xb => '%1.2f', // 0.00E00",
0x25 => '%1.0f', // "#,##0;(#,##0)",
0x26 => '%1.0f', //"#,##0;(#,##0)",
0x27 => '%1.2f', //"#,##0.00;(#,##0.00)",
0x28 => '%1.2f', //"#,##0.00;(#,##0.00)",
0x29 => '%1.0f', //"#,##0;(#,##0)",
0x2a => '$%1.0f', //"$#,##0;($#,##0)",
0x2b => '%1.2f', //"#,##0.00;(#,##0.00)",
0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)",
0x30 => '%1.0f'); //"##0.0E0";
?>


changed to a shorter array this doesn't have an effect on numberFormats
<?
var $numberFormats = array(
0x1 => "%1.0f", // "0"
0x2 => "%1.2f"); //"##0.0E0";
?>

changed var $exp in funtion create numbers
<?

function createNumber($spos)
{
$rknumhigh = $this->_GetInt4d($this->data, $spos + 10);
$rknumlow = $this->_GetInt4d($this->data, $spos + 6);
//for ($i=0; $i<8; $i++) { echo ord($this->data[$i+$spos+6]) . " "; } echo "<br>";
$sign = ($rknumhigh & 0x80000000) >> 31;
$exp = ($rknumhigh & 0x7ff00000) >> 20;
$mantissa = (0x100000 | ($rknumhigh & 0x000fffff));
$mantissalow1 = ($rknumlow & 0x80000000) >> 31;
$mantissalow2 = ($rknumlow & 0x7fffffff);
$value = $mantissa / pow( 2 , (20- ($exp - 1023)));
if ($mantissalow1 != 0) $value += 1 / pow (2 , (21 - ($exp - 1023)));
$value += $mantissalow2 / pow (2 , (52 - ($exp - 1023)));
//echo "Sign = $sign, Exp = $exp, mantissahighx = $mantissa, mantissalow1 = $mantissalow1, mantissalow2 = $mantissalow2<br>\n";
if ($sign) {$value = -1 * $value;}
return $value;
}

?>


I tried setting
<?
$exp = ($rknumhigh & 0x7ff00000) >> 20;

?>

to
<?
$exp = '';
?>

this didn't do anything to the exponent to float conversion

I also tried to stop assignments of the function which happens within to case breaks

first case:
<?
case SPREADSHEET_EXCEL_READER_TYPE_NUMBER:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
if ($this->isDate($spos)) {
list($string, $raw) = $this->createDate($tmp['double']);
// $this->addcell(DateRecord($r, 1));
}else{
//$raw = $tmp[''];
if (isset($this->_columnsFormat[$column + 1])){
$this->curformat = $this->_columnsFormat[$column + 1];
}
//here i try to get rid of createNumber
//$raw = $this->createNumber($spos);
$string = sprintf($this->curformat, $raw * $this->multiplier);
?>


second case:
<?
case SPREADSHEET_EXCEL_READER_TYPE_FORMULA2:
$row = ord($this->data[$spos]) | ord($this->data[$spos+1])<<8;
$column = ord($this->data[$spos+2]) | ord($this->data[$spos+3])<<8;
if ((ord($this->data[$spos+6])==0) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//String formula. Result follows in a STRING record
//echo "FORMULA $row $column Formula with a string<br>\n";
} elseif ((ord($this->data[$spos+6])==1) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Boolean formula. Result is in +2; 0=false,1=true
} elseif ((ord($this->data[$spos+6])==2) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Error formula. Error code is in +2;
} elseif ((ord($this->data[$spos+6])==3) && (ord($this->data[$spos+12])==255) && (ord($this->data[$spos+13])==255)) {
//Formula result is a null string.
} else {
// result is a number, so first 14 bytes are just like a _NUMBER record
$tmp = unpack("ddouble", substr($this->data, $spos + 6, 8)); // It machine machine dependent
if ($this->isDate($spos)) {
list($string, $raw) = $this->createDate($tmp['double']);
// $this->addcell(DateRecord($r, 1));
}else{
//$raw = $tmp[''];
if (isset($this->_columnsFormat[$column + 1])){
$this->curformat = $this->_columnsFormat[$column + 1];
}
//and here to
//$raw = $this->createNumber($spos);
$string = sprintf($this->curformat, $raw * $this->multiplier);

// $this->addcell(NumberRecord($r));
}
$this->addcell($row, $column, $string, $raw);
//echo "Number $row $column $string\n";
}
break;
?>

unfortunatelly my efforts don't have any significant effect
Oct 16 '07 #1
Share this Question
Share on Google+
1 Reply


hawkenterprises
P: 10
I used the same thing for a project and I say that thing is a beast to deal with, if possible I would just use csv format instead. It's more platform independent then the phpExcelReader and isn't a pain to code.
Oct 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.