I’m working with Excel files that were exported from Google Sheets and I’m running into a problem with PHPExcel. When I use $spreadsheet->getHighestDataColumn() and $spreadsheet->getHighestDataRow() methods, they don’t give me the actual last cell with data.
Instead, these methods return the maximum range that Google Sheets had set up, which is typically around column Z and row 1000, even though most of these cells are completely empty. This happens because Google Sheets seems to pre-allocate or reserve these cells differently than regular Excel files.
The issue is that when I try to loop through the data, my script ends up processing thousands of empty rows and columns, which uses way too much memory and makes everything slow.
I need a better way to detect the real last row and column that actually contain data, so I can avoid wasting resources on empty cells. Right now I’m stuck having to manually check each row to see if all cells are empty, but this seems like there should be a more efficient solution.
Yeah, this happens all the time with Google Sheets exports. I’ve had better luck skipping getHighestRow() and getHighestColumn() altogether. Instead, I start from a reasonable max range and work backwards, checking if cells actually have values with getValue() or getCalculatedValue(). You can also grab the worksheet’s dimension property and parse it yourself for better boundaries. Another trick that’s worked for me: use toArray() and strip out null values - you’ll get just the populated range. The main thing is setting sensible upper limits when you iterate backwards instead of trusting Google Sheets’ inflated boundaries.
Indeed, dealing with Google Sheets exports can be quite challenging. I’ve faced similar issues, and what has helped me is to begin from a predetermined max range and check each cell’s value as I move backwards towards the initial rows or columns. Instead of relying on the default methods, I utilize getCellByColumnAndRow() while implementing checks for null values. I also keep track of the found boundaries to optimize the process and avoid unnecessary recalculations. A useful strategy is to stop when encountering three consecutive empty rows, which generally indicates the end of meaningful data. This tweak significantly reduced my processing time, making the task much more efficient.
i totally get it! have you tried calculateWorksheetDimension()? it really helps in finding the actual data limits. also maybe start from the last row/column and move back until you find some data. it’s way more efficient and lighter on memory.