Shared formula returns empty cell ?

Topics: Developer Forum, User Forum
Jun 6, 2012 at 12:26 PM

Hello,

 

I'm using PHPExcel to extract data from big Excel file (to store it in the database). But in some cases the data is calculated using formulae shared on a range of cells. Here is an extract of the Open XML data of my file :

<c r="EA6" s="42" t="str">
  <f t="shared" ref="EA6:ER6" si="0">IF(SUM(EA16:EA22)=0,"",SUM(EA16:EA22))</f>
  <v/>
</c>
<c r="EB6" s="42" t="str">
  <f t="shared" si="0"/>
  <v/>
</c>
<c r="EC6" s="57">
  <f t="shared" si="0"/>
  <v>2.88889</v>
</c>
<c r="ED6" s="57">
  <f t="shared" si="0"/>
  <v>2.88889</v>
</c>
<c r="EE6" s="57">
  <f t="shared" si="0"/>
  <v>2.88889</v>
</c>
<c r="EF6" s="57">
  <f t="shared" si="0"/>
  <v>2.88889</v>
</c>

As you can see, the calculated values are not null, but when I read the file, it returns me an empty string.

For the record, here is my "setup" of PHPExcel, using a ReaderFilter, and custom cache method :

//Set the cache for PHPExcel
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '128MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);

//Declare a reader for an "xlsx" file
$objReader = new PHPExcel_Reader_Excel2007();

//Read data without format only
$objReader->setReadDataOnly(true);
//Implement settings of PHPExcel Reader and loads the file
$startRow=1;
$chunkSize = 1;
$chunkFilter = new App_MyReaderFilter();
$objReader->setReadFilter($chunkFilter);
$chunkFilter->setRows($startRow,$chunkSize);
$objReader->setLoadSheetsOnly(array($sheetName));
$excel = $objReader->load($file);

Any idea why it's doing that ?

 

Thanks

Coordinator
Jun 6, 2012 at 3:49 PM

The problem is likely to be the read filter ignoring the cell containing the original definition of the shared formula, so it's not available to other cells included by the read filter that use it

Coordinator
Jun 6, 2012 at 3:50 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jun 7, 2012 at 9:45 AM

You're right, I removed the ReaderFilter and it worked.

Thanks !