How can I merge cell styles?

Topics: Developer Forum
Jan 3, 2012 at 9:04 AM


Often we have a cell with some styles already set and we need to set additional style. For example, the text in the cell may be bold and have a number format specified. And later we want to add background color to that cell.

Currently if we set a style with background color it will completely overwrite current cell style (bold + number format) and the cell will have only background  color.

This results in creating many styles for all the possible cases, causing verbose duplicate code and making a programmer nervous :)

We can create many little styles for all the cases to use them as building bricks in creating complex style using array_merge_recursive() (see this post for details: ) but it's still far not a perfect solution.

Some libraries for working with Excel offer a feature to merge cell styles, so you can take 2 styles and merge them.

The pseudo code can look like this:

$style = PHPExcel_Style::mergeStyles($cell->getStyle(), $additionalStyle);

$worksheet->setStyle($style, $cell);

or even better:

$worksheet->mergeStyle($cellRange, $additionalStyle);

Jan 3, 2012 at 9:10 AM

what's the difference with an array_merge? ;) Someway or another the additional style attributes have to be merged with existing ones, and overwrite them. If you hide this complexity from the developer I think it would introduce unexpected behaviour, as well as a performance penalty.

Jan 3, 2012 at 10:00 AM
Edited Jan 3, 2012 at 10:06 AM

The difference with an array_merge_recursive() is that to use array_merge_recursive() you must have all the necessary styles as arrays.

What if I don't like to declare styles in array form?

What if I want to combine the styles dynamically? For example, I need to add background for a cell and I don't know what styles have been previously set for  that cell - how do I use array_merge_recursive() in this situation?

Also, how it will introduce unexpected behavior? Can you explain? It will introduce unexpected behavior only if you name the function inappropriately. But if you give the function the right name, its behavior will be as expected :)

As for performance penalty - we just need an appropriate name + comments (which will be shown in documentation also).

I will give you another example:

You have a worksheet with different data. Now you want to apply background color for the whole column (that has different data with different styles in its cells). How will you do it?

Jan 3, 2012 at 12:49 PM
Edited Jan 3, 2012 at 12:51 PM

I'll consider adding a toArray() method to the Style class.

If I added a mergeStyles() method, it would effectively use style arrays purely for efficiency.

$worksheet->mergeStyle($cellRange, $additionalStyle);

Would you expect this to "add" the $additionalStyle (e.g. set all cells in the defined $cellRange to bold, while retaining any existing style settings for those cells)? or to create a single "merged" style object/array from the styles of all the cells in the defined $cellRange (e.g. if A1 was bold, A2 italic, B1 was red and B2 was bordered, you'd want a returned style of bold, italic, red and bordered)? I'd assume the former, but can't be certain. If the former, then how should different number format masks be handled?

Jan 3, 2012 at 2:39 PM

Yes, I mean the former. We can also think about the best name for the function.

If some of the cells in the range already have some number format and we want to add additional style that has a number format specified, then the number format should be overwritten for those cells. I also asked other developers on such a behavior and they agree.

As for API for this feature I can show you how it is implemented in different .NET libraries (I worked with all of them).


In Aspose.Cells library such feature is implemented with the following function:

public void ApplyStyle(Style style, StyleFlag flag);

(see here for details: )

so you can write like this:

range.ApplyStyle(style, new StyleFlag { Borders = true });

and it means only borders will be merged.


In TMS FlexCel library (though I don't like their API and performance) it is implemented in a similar way:

public void SetCellFormat( int row1, int col1, int row2, int col2, TFlxFormat newFormat, TFlxApplyFormat applyNewFormat )

and  TFlxApplyFormat have bool properties to specify which style parts should be merged.


But the best API (imho) for controlling cell styles is when you control Excel through COM. This API is copied in the SpreadsheetGear library, so you can set additional styles like this:

range.Font.Bold = true; // voilà! only bold style is applied for a range of cells, keeping other styles untouched.

Jan 29, 2012 at 9:01 AM

Mark, is there any news regarding this topic?

Nov 29, 2013 at 9:24 AM