Worksheet/Columns Names w/o complete load

Topics: Developer Forum
Apr 17, 2010 at 1:10 PM
Edited Apr 17, 2010 at 1:15 PM

I'm using PHPExcel @version 1.7.2 with PHP Version 5.2.9 on Windows NT 5.1 build 2600.

Is there a way for me to determine something like

   $objWorksheet->getHighestColumn();

without having to load the entire worksheet first?  I'm trying to serve up an HTML table spreadsheet in chunks due to the very large size.  I'd like to initially load say the first 100 rows, then have ajax lazy load additional rows as the user scrolls down.  I plan on using a custom read filter to only grab several rows at a time, but when I initially draw the table, I need to know what headers to draw (A-???).

For instance, if the highest column in the first 50 rows is E, but there's data in row G54, I wouldn't have written a table header pasts E when I first drew the table, so I'm hosed.

So, what's the best way to determine the highest populated column for a spreadsheet without loading the entire sheet in to memory?

Also, right now, I'm getting a list of spreadsheet names by loading the file with a read Filter that basically includes nothing (spreadsheet name = "somebogusname").  I'm able to use the worksheet iterator and loop through to get my names, but...

Is there a more ellogant way to get the spreadsheet list without having to evaluate every cell with a read filter?

Thanks in advance!

 

 

Developer
Apr 20, 2010 at 3:02 AM

So, what's the best way to determine the highest populated column for a spreadsheet without loading the entire sheet in to memory?

You are correct. It is currently necessary to load the whole sheet to know the highest column. I'm not sure what we can do about it.

Excel does in fact store for each sheet something called the dimension. Example: <dimension ref="A1:H1" />

We could perhaps provide a method to extract that piece of information?

 

Is there a more ellogant way to get the spreadsheet list without having to evaluate every cell with a read filter?

Excel does store a list of sheet names. Technically, it would be possible to extract that list without having to mess with cells.

 

It is possible to do something about the problems. The challenge lies in providing a clean solution.

Apr 22, 2010 at 12:26 PM

I got to thinking and realized that the custom read filter I created for pulling back say rows 101-150 already must evaluate every cell to determine whether to include it or not.  That being the case, I added another public variable to house the maximum column evaluated.  This way, when I initially load the web page and need to draw all the column headers (whether they're populated or not during the first 50 rows), I can check the readfilter's public $maxCol to see what the highest column was after it's loaded.  I'm limiting the load to a specific spreadsheet prior to loading it, otherwise I'd probably need a map or something to house highest columns per spreadsheet.

The only part that's lame is I have to create my html table headers on my own as opposed to looping via a column iterator which is built in.  Anyway, here's my custom read filter in case anyone's interested.


<?php
/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
/**
 * @ignore
 */
    define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
}

/** PHPExcel_Reader_IReadFilter */
require_once PHPEXCEL_ROOT . 'PHPExcel/Reader/IReadFilter.php';

class PHPExcel_Reader_CustomReadFilter implements PHPExcel_Reader_IReadFilter {

    public $wsName;
    public $startingRow;
    public $rowCount;
    public $maxCol;

    public function __construct($pWsName, $pStartingRow, $pRowCount = 50) {
        $this->wsName = $pWsName;
        $this->startingRow = $pStartingRow;
        $this->rowCount = $pRowCount;
    }

    public function readCell($column, $row, $worksheetName = '') {
        // Get max column regardless of filter.
        if ($this->wsName == $worksheetName &&
            ($this->maxCol == null ||
            str_pad($this->maxCol, 10, ' ', STR_PAD_LEFT) < str_pad($column, 10, ' ',STR_PAD_LEFT))) {
            $this->maxCol = $column;
        }
        if ($row >= $this->startingRow
            && $row < $this->startingRow + $this->rowCount
            && (isset($this->wsName) && $worksheetName == $this->wsName)
        ) {
            return true;
        } else {
            return false;
        }
    }
}