How to apply background color to the Excel cell in OpenTBS?

Question

I cannot figure out how to apply background color to the cell in OpenTBS.

I've tried Word's syntax but it did not work.

[row.cell.val][row.cell.bg;att=w:shd#w:fill]

Code in PHP (simplified)

$rs = [
    // ... 
    'cell' => [
        'val' => 5,
        'bg'  => 'efefef',
    ],
// ...
];

$TBS->MergeBlock('row', $rs);

I looked inside XML body of excel document, but could not understand what tags and attributes define the color of the cell.

Can anyone help me?

score 1 · Answer 1 · answered Mar 19 '18 at 10:06

Excel XML's cell background colour is stored in cell style, in the <Styles> section and referred by each cell's ss:StyleID.

Example:

<!-- "s62" is the style id" -->
<Style ss:ID="s62">
   <!-- Interior = Background -->
   <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
</Style>

...

<!-- Using "s62" style which says yellow background -->
<Cell ss:StyleID="s62"> 
   <Data ss:Type="String">foobar</Data>
</Cell>

If you have a fixed list of background colour, you can manually put them all in <Styles>, map colour to style id in php, and merge the style id to each cell.

If you don't have a fixed list of colour, or if the spreadsheet's formatting is complicated, you may have to create and merge the style list first.

score 1 · Accepted Answer · answered Mar 26 '18 at 07:25

It is quite difficult to change the background color of a cell in an XLSX workbook using OpenTBS (or other tool) because this information is stored a single style definition in a extra sub-file xl/styles.xml and in a complicated way.

So when modifying the corresponding style you will also change the color of all other cells having this style. There is no other way in an XLSX to store the background color of a cell. (this is different is DOCX where you can store the information in the cell property).

So my suggestion is to to use an Excel conditional formatting for this purpose.

How to apply background color to the Excel cell in OpenTBS?

2 Answers2