GemBox Spreadsheet : Multiple font colors in one cell

Question

I'm exporting data using GemBox Spreadsheet and I need to format the text in a cell to have multiple colors.

This is possible in Excel like this (found on StackOverflow):

Dim fixedLength As Long
fixedLength = Len("Employee")
ActiveCell.FormulaR1C1 = "Employee Some Employee"
With ActiveCell.Characters(Start:=fixedLength + 2, Length:=Len(ActiveCell) - FixedLength - 1).Font
    .Color = vbRed
End With

However, all I can find in the GemBox class is to set the Style.Font.Color property and this affects the entire cell.
Example:

for (int i = 0; i < tempArray.GetUpperBound(0); i++)
{
    Color backColour = ColorTranslator.FromHtml(tempArray[i+1]);
    ws.Cells[row, col].Value += tempArray[i] + Environment.NewLine;
    ws.Cells[row, col].Style.Font.Color = backColour;
    i++;
}

Is this possible with GemBox?

GemBox Spreadsheet Professional 3.5 for .NET 4.0
v4.0.30319
v35.3.40.1000

score 0 · Accepted Answer · edited Nov 15 '19 at 13:47

I know this question is old, but I asked the developers via email a few years ago. They said this was not available.

If you email them directly, they usually respond within one business day. I think they are in the Czech Republic.

You can open a support ticket to them here.

There is also a feedback tab on that same page where you can suggest they add this as a feature and other devs can vote on features to add.

GemBox Dev Team · Answer 2 · 2019-11-15T13:35:04.760

The current version of GemBox.Spreadsheet (version 3.9) has an API support for this, see the Excel Inline Text Formatting example.

In short, what you need is to use GetCharacters method, for example like this:

int row = 0;
int col = 0;
var tempArray = new string[] {
    "First Value",
    "Red",
    "Second Value",
    "Green",
    "Third Value",
    "Blue"
};

ws.Cells[row, col].Value = string.Concat(
    tempArray.Select((tempItem, i) => i % 2 == 0 ? tempItem : Environment.NewLine));

int charStartIndex = 0;
for (int i = 0; i < tempArray.Length; i+=2)
{
    string value = tempArray[i];
    SpreadsheetColor color = ColorTranslator.FromHtml(tempArray[i + 1]);

    ws.Cells[row, col].GetCharacters(charStartIndex, value.Length).Font.Color = color;
    charStartIndex += value.Length + Environment.NewLine.Length;
}

This is the result:

GemBox Spreadsheet : Multiple font colors in one cell

2 Answers2