function reference documentation please [columnAndRow, setStyle]

Dec 3, 2010 at 4:26 AM

I am impressed with all the work on this project and all the functionality.

So, I'm very thankful for it.

However, I find it difficult to use because the object/class model is not typical of that used in most other projects I've worked on.  A lot of it is borrowed/inherited, I believe, from original Perl non-Excel2007 version.

I have tried to read thoroughly the documentation but think I have some suggestions that would make it easier for newcomers.

I would like to specifically find any of these combinations:

  • getStyleByColumnAndRow()->setStyleFromArray() or
  • getCellByColumnAndRow()->setStyleFromArray()or
  • getStyleByRange(fromcolumn=1,fromrow=1,tocolumn=100,torow=1000)->setStyleFromArray()

....but there's no way to confirm if such a thing exists without trial and error.  It's not in the documentation.

I have a dataset coming from a JSON source of arbitrary width using dynamic columns easily exceeding a count of 26.

I cannot statically assign specific data elements to static columns and refer to them by a letter.

I want to convert my data into an Excel-compatible spreadsheet and it would be very cumbersome to write/find/integrate code just to map the numeric column number to A-Z,  AA-AZ, BA-BZ,... ZA-ZZ, AAA-ZZZ,... in order to concatenate that with the row number and feed that to getCellValue/setCellValue/getStyle/setStyle/getHyperlink/setHyperlink/getComment/setComment.  I would like to see the "ByColumnAndRow" equivalents.

I would like to see, in the documentation, an alphabetized/itemized function reference and perhaps a picture of the object model

 

Please.

Any help appreciated.

Coordinator
Dec 4, 2010 at 11:11 AM

It might help you to know that PHP's ++ incrementor works on characters as well as numbers, and 'Z' will be incremented to 'AA' in the manner of Perl; so it is easy to use this feature in loops. e.g

$startColumn = 'A';
$endColumn = 'XFD';
$endColumn++;
for($column = $startColumn; $column != $endColumn; $colum++) {
   echo $column,' ';
}

You just need to be a bit careful when testing for the end condition, hence my incrementing the end column prior to the loop, then testing for !=

 

There is also full PHPDocumentor API documentation in the production downloads that details all classes and their methods, found in /Documentation/API/; and I am in the process of writing much more comprehensive user documentation... though I'm sure that you can appreciate writing this level of detail is a mammoth task.

Dec 10, 2010 at 1:29 AM
Edited Dec 10, 2010 at 2:43 AM

That does help.  It should be workable.

I will be pulling data from another array so, I'd probably just use that array's index as the iterator and let it control the "for" loop bounds.

The main drawback is that I'd still carry a temporary "character" variable that I'd remember to initialize before the loop and increment at the end within the loop.  Much easier than building an elaborate function to do it, though.

I'm very curious how localization (language, character set, encoding, collation sequence) would affect this style of character incrementation.  Would this break it?

I would prefer the reliability of having the "ByColumnAndRow" methods.

I do understand the effort required for good documentation.  So very few people are interested in it and so few are good at it.  The people qualified to do it usually would rather do code and their bosses would want them to code as well.  I will look for more of that documentation.  Didn't know it was out there and didn't find a link to it.  <<EDIT: I don't seem to have access to it. ???  http://phpexcel.codeplex.com/Documentation/API ???>><<EDIT2: DUNHH!!! I was supposed to download and look at the html documentation files extracted from within.  Functions/Methods I'm looking for still aren't there, though. :*( >>

Thanks. 

Coordinator
Dec 10, 2010 at 9:41 PM
complexed wrote:

I will be pulling data from another array so, I'd probably just use that array's index as the iterator and let it control the "for" loop bounds.

The main drawback is that I'd still carry a temporary "character" variable that I'd remember to initialize before the loop and increment at the end within the loop.  Much easier than building an elaborate function to do it, though.

You can always use the PHPExcel_Cell::stringFromColumnIndex($index) method (using the array's index) where 0 returns 'A', 1 returns'B', 25, returns'Z', 26 returns'AA', etc.

 

complexed wrote:

I'm very curious how localization (language, character set, encoding, collation sequence) would affect this style of character incrementation.  Would this break it?

 Character incrementing using the ++ operator only works with ASCII characters A-Z and a-z, and is case sensitive. It's a very useful quirk of PHP when working with Excel column names (pity the -- decrementor operator doesn't work with strings); and I use it a lot in my own code.

 

complexed wrote:

I would prefer the reliability of having the "ByColumnAndRow" methods.

The duplicateStyle() method might be of some use when you want to replicate a style across a whole series of cells. Set the style for a single cell (or create a style object manually). e.g.

$objPHPExcel->getActiveSheet()->duplicateStyle(getStyleByColumnAndRow($pColumn = 0, $pRow = 1), $pRange = 'L3:N2048');

or you can set individual style elements across a range:

$objPHPExcel->getActiveSheet()->getStyle('L3:N2048')->getNumberFormat()->setFormatCode('dd-mmm-yyyy hh:mm:ss');

complexed wrote:

I do understand the effort required for good documentation.  So very few people are interested in it and so few are good at it.  The people qualified to do it usually would rather do code and their bosses would want them to code as well.  I will look for more of that documentation.  Didn't know it was out there and didn't find a link to it.  <<EDIT: I don't seem to have access to it. ???  http://phpexcel.codeplex.com/Documentation/API ???>><<EDIT2: DUNHH!!! I was supposed to download and look at the html documentation files extracted from within.  Functions/Methods I'm looking for still aren't there, though. :*( >>

 Yes, the API documentation isn't here online on codeplex, but is included in the production downloads because it's built dynamically using PHPDocumentor as part of the release process.

The latest download also includes a couple of new sets of documents: the Functionality Cross-Reference.xls file, which I'll be building up slowly; and PHPExcel User Documentation – Reading Spreadsheet Files.doc with the corresponding set of Examples. It's my intention to building this into a comprehensive set of documented, worked examples covering most of the functionality of the library... but you're right - like most developers, I'd far rather be developing than documenting. Now that the 1.7.5 release is available, I'll try to devote some time to expanding this documentation before turning my focus to the code for the 1.7.6 release. I'll look at including a class diagram in that documentation for the next release; and one of these days I'll find the time to start a blog, including scripted examples of PHPExcel recipes.

Dec 11, 2010 at 12:21 AM

Gee.  That pretty much answers all my questions.

Thanks for your time, effort and mostly patience.

Keep up the excellent work.