Setting font color

[Jeff Loughlin]

I need to create a spreadsheet that contains colored text. My spreadsheet has 10 columns and a few thousand rows, and each cell is color coded based on the data (each cell is colored with one of 10 unique colors).

When I try to open the resulting .xls file in Excel, I get an error saying that it has exceeded the maximum number of fonts. The spreadsheet loads (ignoring the error), but only the first 506 cells are colored, the rest are the default (black) color.

Here is a super-simplified example that replicates the problem:

workbook wb;
worksheet *sh = wb.sheet("SHEET1");
for (int row = 0; row < 1024; row++)
{
    for (int col = 0; col < 10; col++)
    {
        cell_t *cell = sh->label(row, col, "Blah");
        cell->fontcolor(col + 10);
    }
}
wb.Dump("test.xls");

If you try to load the resulting file in Excel (I'm running 2010), you will see the problem I'm talking about.

My guess is that xlslib is creating a new format each time I call fontcolor(), instead of reusing the existing ones. I looked at the example code included with the project, and it seems to do pretty much what I've done here, so that wasn't much help.

I think what I need to do is have 10 different custom formats, one for each color I want to use, and then apply the correct format to each cell, but I'm unsure of how to do that. Do you have any examples showing how to create custom formats and then apply them to a cell?

Jeff Loughlin

[David Hoerl]

Its been a while since I played with this sort of thing. I recall that what you do is create 10 custom extformats - objects of type "xf_t". You can then set the appropriate one for each of your cells using SetXF(xf_t* pxfval);

See if that does the trick.

[Jeff Loughlin]

I got it working using extended formats as suggested. In case others come here in the future with the same question, here is a snippet of code that will set text colors on cells to one of ten different colors:

workbook wb;
worksheet *sh = wb.sheet("SHEET1");

xf_t *formats[10];

for (int color = 0; color < 10; color++)
{
    font_t *font = wb.font("Courier");
    font->SetColor(color);
    xf_t *customFormat = wb.xformat(font);
    formats[color] = customFormat;
}

for (int row = 0; row < 1024; row++)
{
    for (int col = 0; col < 10; col++)
    {
        cell_t *cell = sh->label(row, col, "Blah");
        cell->SetXF(formats[col]);
    }
}

Thanks again for your help David.