how can i get excel nested functions

Topics: Developer Forum
May 28, 2012 at 7:07 AM

i want to read excel data,it uses excel nested functions and pattern.

i want to read the data same as the excel looks like.

eg. data as 1201 but it displays 1,201

May 28, 2012 at 5:01 PM

is there a question hidden somewhere here?

Coordinator
May 28, 2012 at 9:25 PM

Can you explain exactly what you mean?

 

Jun 11, 2012 at 9:10 AM

i mean if an excel contains many columns,some column use ms excel function,how can i get the value,and how can i get formatted value

Coordinator
Jun 11, 2012 at 1:34 PM

For any cell:

  • getValue() will return the unformatted value, or the formula if the cell is a formula cell
  • getCalculatedValue() will return the unformatted value, calculating the result of the formula if the cell is a formula cell
  • getFormattedValue() will return the formatted value, calculating the result of the formula and formatting that if the cell is a formula cell

Or you can specify whether to calculate values and/or format values in the worksheet's rangeToArray and toArray methods

Jun 12, 2012 at 3:46 AM


$code = $sheets -> getStyle('E9') -> getNumberFormat() -> getFormatCode(); //return ##,0 $value = $PHPExcel -> getActiveSheet() -> getCellByColumnAndRow('4',9) -> getValue(); //return 1527 $value = $PHPExcel -> getActiveSheet() -> getCellByColumnAndRow('4',9) -> getFormattedValue(); //return 1527 $format_value = $sheets -> getStyle('E9') -> getNumberFormat() -> toFormattedString($value,$code);//return 1527

But I see in excel is 1,527

Coordinator
Jun 12, 2012 at 9:06 AM
Edited Jun 12, 2012 at 9:10 AM

First question:

Is 1527 a string in the first place?

Second question

Do you have a locale setup on your server? what do you get returned by a standard PHP call to localeconv()?

If the localeconv array has an empty value for 'thousands_sep', PHPExcel will use the value for 'mon_thousands_sep' instead. If this also empty, then you won't get any thousands separator displayed unless you define it yourself using

PHPExcel_Shared_String::setThousandsSeparator(',');

Third question

Does the number_format() function exist in your version of PHP? This is dependent on PHP version and operating system. If it doesn't exist, PHPExcel uses its own version of number_format

Jun 14, 2012 at 10:05 AM

May I do not explain exactly。

What i see,what i get。

Ido not know what type is it 1527 in excel。

It format code is #,##0.

$code = $sheets -> getStyle('E9') -> getNumberFormat() -> getFormatCode();   //return #,##0

$value = $PHPExcel -> getActiveSheet() -> getCellByColumnAndRow('4',9) -> getValue();  //return 1527

$value = $PHPExcel -> getActiveSheet() -> getCellByColumnAndRow('4',9) -> getFormattedValue();  //return 1527

 $format_value = $sheets -> getStyle('E9') -> getNumberFormat() -> toFormattedString($value,$code);
I get value by above code,is there any question?

php version 5.3.5。

I use toFormattedString function,$format,the final value is 0.The input is #,##0.
	

 

Coordinator
Jun 14, 2012 at 3:42 PM

Write and execute a simple PHP script that contains the following:

<?php
$locale = localeconv();
var_dump($locale);

if (function_exists('number_format')) {
   echo 'number_format() exists';
} else {
   echo 'number_format() is not defined';
}

The output should look something like:

array
  'decimal_point' => <small>string</small> '.' (length=1)
  'thousands_sep' => <small>string</small> '' (length=0)
  'int_curr_symbol' => <small>string</small> '' (length=0)
  'currency_symbol' => <small>string</small> '' (length=0)
  'mon_decimal_point' => <small>string</small> '' (length=0)
  'mon_thousands_sep' => <small>string</small> '' (length=0)
  'positive_sign' => <small>string</small> '' (length=0)
  'negative_sign' => <small>string</small> '' (length=0)
  'int_frac_digits' => <small>int</small> 127
  'frac_digits' => <small>int</small> 127
  'p_cs_precedes' => <small>int</small> 127
  'p_sep_by_space' => <small>int</small> 127
  'n_cs_precedes' => <small>int</small> 127
  'n_sep_by_space' => <small>int</small> 127
  'p_sign_posn' => <small>int</small> 127
  'n_sign_posn' => <small>int</small> 127
  'grouping' => 
    array
      empty
  'mon_grouping' => 
    array
      empty

number_format() exists

If the entries for 'thousands_sep' and for 'mon_thousands_sep' are both empty strings, as in the above, then formatted numbers will never show the , thousands separator unless you explicitly set it using

PHPExcel_Shared_String::setThousandsSeparator(',');