Excel export file is not readable through code -- I tried both, Export via Podio App and Export via Batch API
I'm trying to programmatically read an Excel File exported from Podio but it is not working. I'm 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"
$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 shows 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. 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. I used different readers available in PHPExcel but every reading is showing same error.
"My guess would be non-standard namespacing in the file that's generated, which loading and saving in MS Excel fixes"..
I initially thought that this issue is limited to Batch Export API only but when did it manually from Excel Export feature, I faced the same issue. I is showing same error against any APP..Every app export has this issue...
Can anyone has faced this issue before? any solution? Please guide me what I'm doing wrong here OR is it a Podio Export Bug which should be fixed on priority.
Is there any other API (other than PHPExcel) available which should be used to read exported file without error?
Similar issue is posted on StakeOverflow forum: http://stackoverflow.com/questions/40792556/file-downloaded-via-podio-export-api-is-not-readable-through-code
I have been struggling with PHPExcel as well. All other xlsx files I tried worked fine, except the Podio ones. Podio exports are either the wrong markup, or the compression used is not standard.
Anyway, if you're on linux you could try installing gnumeric. It has a command line conversion utility:
sudo apt-get install gnumeric --no-install-recommends
Or similar for other linux flavours. In your php script, after your xlsx download is finished:
shell_exec('ssconvert exported.xlsx converted.csv');
I used this to generate a csv-file to import into Google sheets with =importdata(). Works fine.
Please sign in to leave a comment.