Writing into cells with defined format

[vika8713]

There would be very nice to have an opportunity to manage cell format while writing into. Now every entry has a general format (at least in .xlsx files).

[adrilo]

Do you mean controlling the style for each cell separately instead of each row?

[madflow]

Do you mean controlling the style for each cell separately instead of each row?

@adrilo I believe that is what @vika8713 is asking for. This is basically covered in #124 I think. I have a use case for this, where I want to create a "heatmap": a carpet of values (with background color) in a spreadsheet.

In addition to #124 I could think of lightweight "Cell Objects" that contain the cell value, the value type, styling, cell comments, other metadata - that could be passed in an array to Writer::addRows().

[TonisOrmisson]

I would love to have the opportunity to be able to format each cell individually.
In terms of using spreadheet as an analysis tool it is very useful to be able to visually highlight cells to visually make it easier to identify important pieces in a matrix. I would have a lot of use for a tool that could do cell level formatting and would not run into memory problems over ridiculously small files (like phpExcel and others do).

[TonisOrmisson]

Since the Styling currently is rather limited I'll specify that I would really love to be able to add to current styling: background colour, borders, cell comments.
This would allow do create visually presentable tables.

BTW. I love this code. I have just gone through 4-5 different Excel readers/writers over past weeks that all seem to run into memory issues very quickly. This project seems exactly what I was looking for.

If only had also cell formatting it could be used also in analysis type of tasks instead of raw import/export :)

[madflow]

@adrilo Can we colaborate on how to proceed with the cell styling/formatting? My first instinct was to make the concept of cells a "first class citizen" and skip the "column styling" approach like documented here #209 (comment). However I do not want to take another shot at this and see if it fits in a PR.

Have you generated any ideas on how to proceed? Are there any "junior" tasks that can be adressed on the way to 3.0?

[adrilo]

Hey @madflow, thanks for following-up on this issue. Sorry it took so long to get back to you!

I was thinking of the same approach, where instead of only accepting scalars or DateTime, the addRow() can also accept a Cell object. The Cell object holds data related to the cell that will be written (value and style for now).
I agree with the "skipping column styling", as it can be accomplished by cell styling (not as convenient for this use case, but doable).

Now, how to get there is interesting. I think we should set up another branch where we can start developing 3.0 features. And in order to add support for cell styling, it can be split into 2 main tasks:

• writers should support Cell objects. A Cell object only holds a value (so no style)
• add support for Cell's custom style

What do you think?

[madflow]

Sounds good to me! How can contribution look like? As the Product Owner ;) maybe you could log some smaller issues?

[davidNieves]

Hi all! someone know the status of this feature?? this library only need this to be perfect!! Thanks

[adrilo]

Hey @madflow and @davidNieves !
I'm super busy these days and haven't had a chance to answer your comments... Just wanted to let you know that this is still on the roadmap but I'll get back to it as soon as I have more time. Sorry about that...
In the meantime, I think someone can do that :

1. Add a Cellobject
2. Add support for Cellobjects in the writers (a cell holding just a value, no styling)

And later, we can add support for styling. @madflow are these tasks small enough?

[madflow]

@adrilo Yes - small enough ;)

[naqk]

Spout_NAQK_Hack.zip
I only did some hack in code forcus on set BackgroundColor of cell different with row's color.
Changed code is here :
1 - Spout\Writer\XLSX\Internal\Worksheet.php function addNonEmptyRow
Detect cell value if is array object with "value" = true cell value; "style" = Style create with StyleBuilder to get correct "value" and "style" for getCellXML function

2 - Spout\Writer\Common\Internal\AbstractWorkbook.php
2.1 - function addRowToCurrentWorksheet
call registerCellStyle to register all cell style if needed before write row

2.2 - new function registerCellStyle
Register all cell style in row data and return styleHelper

To use :
With cell need format, change cell's value as array object with "value" and "style"
$newCellValue = array("value"=>$oldCellValue,"style"=>$newCellStyle);

[adrilo]

Thanks @naqk! I'm not a fan of the map syntax and would rather have proper objects, like @madflow did. Your approach and the approach taken in this PR is very similar though, so that's a good thing :)

[adrilo]

Fixed in #485