0

Use-case:

Export data from different apps using batch EXPORT APIs to merge into a master excel file and use it for reporting purpose...

Implementation Details

I'm using Podio export API to get data from an application. Application name is Kall8-number-text (as an example).. here is the code snippet

Code Snippet

$batch_id = PodioItem::export(11804702,"xlsx",array("filters" => array( "kall8-number-text" => "510-592-5916") ));

PodioBatch::get( $batch_id );
$file = PodioFile::get($file_id);

// Download the file. This might take a while...
$file_content = $file->get_raw();

// Store the file on local disk
$path_to_file= "downloads/".$name;
file_put_contents($path_to_file, $file_content);

Problem Description:

I'm trying to read downloaded file using phpexcel library but getting error "You tried to set a sheet active by the out of bounds index: 0. The actual number of sheets is 0"

This error shows that file has NO sheet but it is not true. File has data/sheet and it shows upon opening that file.

One interesting fact, if I open the same excel file (manually by double click) and SAVE without making any change, then same code works fine. In my end to end process, I cannot add a manual step to open file every-time to proceed further...

For your information, I thought this is a PHPExcel bug and contacted Mark Backer (coordinator PHPOffice Suit) and he replied with following remarks which seems true.

"My guess would be non-standard namespacing in the file that's generated, which loading and saving in MS Excel fixes"

File Reading Code

$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load('callsheet.xlsx');
$objPHPExcel->setActiveSheetIndex(0);
$dataArray = $objPHPExcel->getActiveSheet()->toArray(null, true,true,true);
var_dump($dataArray);

Error Trace

Fatal error: Uncaught exception 'PHPExcel_Exception' with message 'You tried to set a sheet active by the out of bounds index: 0. The actual number of sheets is 0.' in E:\xampp\htdocs\podioexcel\Classes\PHPExcel.php:688 Stack trace: #0 E:\xampp\htdocs\podioexcel\test.php(18): PHPExcel- setActiveSheetIndex(0) #1 {main} thrown in E:\xampp\htdocs\podioexcel\Classes\PHPExcel.php on line 688

Can you help me to address this issue? This is holding up my project completely.

File Path: https://drive.google.com/file/d/0B79S561prrEBUDY1NEhXQ1JySWM/view

Original question at Stackoverflow: Error While loading excel sheet Using phpexcel

Ejaz

Community
  • 1
  • 1
Ejaz
  • 1
  • When you open & save again in Excel, it should presumably be using the same Excel2007 format? If so, I'd try and open both versions in a HEX editor, and see what the differences are - maybe they are minor enough, so that you could apply the same to all files via a little (command line) script or something. – CBroe Nov 24 '16 at 18:28
  • My process is automated..like I'm supposed to download 4 files (from different apps) on click of a button and merge them into ONE to generate dashboard report..I've used excel 2007 and other versions and it shows same error on every version. Can you please elaborate bit more what should I do to identify or fix the issue?? To me this looks an API issue which is missing some metadata or namespacing while generating file. Looking forward to your kind reply.. – Ejaz Nov 25 '16 at 03:00
  • There is an interesting finding..EVERY file exported from Podio has this issue. Export any file (using Excel Export option or via Export Batch API), you face the same issue... – Ejaz Nov 25 '16 at 05:25
  • @CBroe: Can you please help me fixing this issue? OR do we have anyone from Podio to look into this issue and respond?? – Ejaz Nov 29 '16 at 04:41
  • Sorry, can’t help you there. I’d suggest you try Podio support ... – CBroe Nov 29 '16 at 08:07
  • If you are trying to merge and generate a report isn't it easier just to consume the data straight from the API instead of exporting and dealing with several excel files? – Brian Stengaard - Podio Nov 29 '16 at 20:57
  • @BrianStengaard-Podio: Thanks for the reply. I'm trying to export data using filters becoz my data is more than APIs limit. Response data can vary between 2000 to 5000 records per app and I'm exporting 4 to 5 apps data at the same time..I did evaluated APIs solutions but due limitation of APIs response, I decided to move to Excel export via Export APIs. Considering situation, what do you suggest here? I've explained you response data after applying filter. BTW, what is the issue with Export file generated via API? Why code is not able to read? Do you have some suggestions here too? – Ejaz Dec 04 '16 at 12:55
  • I was able to reproduce your error by using PHPExcel, but I suspect this is a bug in that library. When I tried another library ([openpyxl](https://pypi.python.org/pypi/openpyxl) for Python), I was able to read the file just fine. Example using openpyxl: https://gist.github.com/infinitewarp/357b0add9b1e5f89f8ac9a88dd4a60da – Brad Smith Dec 05 '16 at 19:18
  • Yes, my recommendation would be to move away from PHP in general. ;) More specifically for working with excel sheets see if you can find another excel library. Overall though I would recommend that you move to an API solution, where you iterate through the items by app_item_id. If you are running into API rate limits, reach out to support and they can help raise the limit. – Brian Stengaard - Podio Dec 06 '16 at 20:38

0 Answers0