Easy way to add an entire row at once?

Topics: Developer Forum
Jun 22, 2009 at 3:19 PM
Edited Jun 22, 2009 at 3:24 PM

I've been looking through the discussions, as well as through the source code, and--unless I'm missing something--it looks like PHPExcel operates on a cell-by-cell basis, at least insofar as it relates to adding data.

For my current project, I found that to be something of a limitation.  I added a "header" row (row 1), with data like name, title, start date, and the like--some 28 columns worth of data.  Later, though, I was asked to add a new column, right after Column P; this, of course, required me to completely re-letter the subsequent columns, both in the header row and in my database retrieval loop.

It got to where I've written a utility function, called addRowToSheet, that takes three parameters--the PHPExcel object, an array of row elements (each of which is an array), and the row number to add.  So, for example, I might have the following for my row elements:

 

$rowElements = array(
  array('contents' => 'Name', 'rotation' => '90', 'size' => '10'),
  array('contents' => 'Title', 'rotation' => '90', 'size' => '20'),
);

 

In the function, I loop through each row element, use the PHPExcel_Cell::stringFromColumnIndex() method to get the column number, and use the appropriate PHPExcel method to process the keys (e.g. if array_key_exists('contents', $element), call the setCellValue() method).  At present, I allow for adding the contents; setting the rotation, vertical, and horizontal alignment; setting the size; toggling bold styling; and setting the number formatting (at least for three possible formats, of my choosing).  I'm finding that this function keeps getting larger and larger, though...

Thing is, after having found PHPExcel_Cell::stringFromColumnIndex() this morning (my boss and I actually wrote our own function last week to do just this, but as a do/while), I can't help but think that there's got to be a simple way of adding a row's worth of data at a time.

Am I missing something obvious?  Or am I stuck with my utility function--which, frankly, has saved me a lot of time, and is something I'm fairly happy with.

Jun 22, 2009 at 5:26 PM

I don't know if I've understood you correctly, but can't this be done with a typical for loop?

The algorithm being:

for $i = 1 to sizeof($array_name){

setcellvalueexplicit('A' . $i, $array_name['index'] ...    //setcellvalueexplicit is faster than the regular setcellvalue (thanks to koyama for the pointer)

setcellvalueexplicit('B' . $i, $array_name['index'] ...

setcellvalueexplicit('C' . $i, $array_name['index'] ...

.

.

.

}

Just thought I'll mention this since you're looking for something simple. Please ignore this if you're not a beginner.

Jun 22, 2009 at 5:31 PM

karpar:

Yes, that would work fine--but here's the problem.  Let's say I have this:

setCellValueExplicit('A'.$i, $elements['index'];
...
setCellValueExplicit('B'.$i, $elements['index'];
...
setCellValueExplicit('C'.$i, $elements['index'];
...
setCellValueExplicit('D'.$i, $elements['index'];
...
setCellValueExplicit('E'.$i, $elements['index'];

Then, someone comes back and says, "Let's add a column right at the beginning of the list..."  I then have to back and change A to B, B to C, C to D, and so on in the code.

Adding a row as an entity in and of itself somewhat solves this problem, since I can dynamically determine the column position--and simply drop the new column data (cell) into the proper position of the array.

(Incidentally, I'm a PHPExcel beginner, but not so much at PHP itself. :) So, I can use all the PHPExcel pointers I can get my hands on.)

Jun 22, 2009 at 5:37 PM

Very true - my column structure doesn't change often and it's not been a problem for me, but I see it's an issue in your case (especially since you have 28 columns!). Perhaps Erik or Mark could help you out - sorry I wasn't of much help.

Jun 22, 2009 at 5:41 PM

No apologies necessary!  Indeed, 28 is a bit long, but it's the request of the "client," so c'est la vie.

Coordinator
Jun 22, 2009 at 10:21 PM
You could take advantage of the way the PHP Increment operator works with character values rather than just numerics
$column='A';
setCellValueExplicit($column++.$i, $elements['index'];
...
setCellValueExplicit($column++.$i, $elements['index'];
...
setCellValueExplicit($column++.$i, $elements['index'];
...
setCellValueExplicit($column++.$i, $elements['index'];
...
setCellValueExplicit($column++.$i, $elements['index'];
Jun 23, 2009 at 11:36 AM

Mark:

That works, although I suspect it would fail once $column is 'Z'.  Of course, using the built-in PHPExcel_Cell::stringFromColumnIndex() with a number gets around this somewhat.

Still, though, it feels...well, inelegant.  Doesn't the following look so much better?

addRowToSheet($objPHPExcel, $acctHeaderRow, $currentRow);

Coordinator
Jun 23, 2009 at 2:19 PM

>> That works, although I suspect it would fail once $column is 'Z'. 

:-)

Try it and see, you just might be surprised

 

Quoting from the manual:

PHP follows Perl's convention when dealing with arithmetic operations on character variables and not C's. For example, in Perl 'Z'+1 turns into 'AA', while in C 'Z'+1 turns into '[' ( ord('Z') == 90, ord('[') == 91 ). Note that character variables can be incremented but not decremented and even so only plain ASCII characters (a-z and A-Z) are supported.

Jun 23, 2009 at 2:36 PM

Definitely good to know, Mark.  Still, though, it's just...well, ugly.  I like being able to cobble an array of arrays and pass that to my function to add a row.  Somehow it just feels cleaner.  I've even had my boss suggest that I extend PHPExcel to add my own classes to support this functionality (although I admit to being a bit nervous at such a prospect).

Nevertheless, I appreciate your insight!  At the very least, it helps me to get a better grasp on character arithmetic.

So, Mark...feel like tackling my question (in another thread) about number formatting? ;-)

Coordinator
Jun 23, 2009 at 4:15 PM

I've added this suggestion as a new workitem

(and anyting implemented may well use the incrementor operation on column characters)

Jun 23, 2009 at 4:28 PM

Brilliant! I'm much obliged.