Color text parts in Excel >255 characters

Question

I have written a plugin for a third-party sw that extracts revised texts to an Excel sheet and then colors the changed parts in Excel. This works as long as each text segment (=cell content) does not exceed 255 characters. Alas this can and does occasionally happen.

In order to identify the changed parts in Excel, I surround them with <del> resp. <add> tags for deleted and added text. Then I color these parts (and remove the surrounding tags) like this:

while (((string)cell1.Text).Contains("<del>"))
{
    try
    {
        var pos = ((string) cell1.Text).IndexOf("<del>") + 1;
        var pos2 = ((string) cell1.Text).IndexOf("</del>") + 1;
        var txt = cell1.Characters[pos, (pos2-pos) + 9].Text;

        txt = txt.Replace("<del>", "").Replace("</del>", "");
        cell1.Characters[pos, (pos2-pos) + 9].Text = txt;
        cell1.Characters[pos, txt.Length-3].Font.Color = -16776961;
    }
    catch
    {
            break;
    }
}

I am using Interop because I find it much easier to work with, and also because I could not find any decent example on how to do this with OpenXML. However I am aware that Excel has its limits when it comes to cell text, so I am open to suggestions.

Is there a way to color single words in a cell containing >255 characters using Interop?

If everything fails, I will probably have to create a Word document with a table, do the formatting there and then copy/paste over to Excel (yukk). Please help me avoid this ugliness.

P.S: Yes, the revision summary needs to be Excel-based.

Answer 1

OK, I solved it with OpenXML now. If a cell contains text to be colored, I create a run of text up to that position, a second, colored run containing the affected text, and a third back-to-default run containing the rest.

var xlsx = SpreadsheetDocument.Open(xlsPath, true);
var contents = xlsx.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
foreach (SharedStringItem si in contents.SharedStringTable.Elements<SharedStringItem>())
{
    if (si.Text != null)
    {
        XlHelper.ColorCellText(si, "del", new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = "FFFF0000" });
        XlHelper.ColorCellText(si, "add", new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = "0000BF00" });
    }
}

And my XlHelper.ColorCellText method:

public static void ColorCellText(SharedStringItem si, string TagName, DocumentFormat.OpenXml.Spreadsheet.Color col)
{
    var newText = si.InnerText;
    var startTag = string.Format("<{0}>", TagName);
    var endTag = string.Format("</{0}>", TagName);
    if (newText.Contains(startTag))
    {
        si.Text.Remove();
        var lastpos = 0;
        while (newText.Contains(startTag))
        {
            try
            {
                var pos1 = newText.IndexOf(startTag);
                var pos2 = newText.IndexOf(endTag);
                var txtLen = pos2 - pos1 - 5;
                var it = string.Concat(newText.Substring(0, pos1), newText.Substring(pos1 + 5, txtLen),
                    newText.Substring(pos2 + 6));

                var run = new Run();
                var txt = new Text
                {
                    Text = it.Substring(0, pos1),
                    Space = SpaceProcessingModeValues.Preserve
                };
                run.Append(txt);
                si.Append(run);

                run = new Run();
                txt = new Text
                {
                    Text = it.Substring(pos1, txtLen),
                    Space = SpaceProcessingModeValues.Preserve
                };

                var rp = new RunProperties();

                rp.Append(col.CloneNode(true));
                run.RunProperties = rp;
                run.Append(txt.CloneNode(true));
                si.Append(run.CloneNode(true));

                newText = newText.Substring(pos2 + 6);
            }
            catch(Exception ex)
            {
                using (var sw = new StreamWriter(logFile, true))
                {
                    sw.WriteLine("Error: {0}\r\n{1}", ex.Message, newText);
                }
                break;
            }
        }
        if (newText.Length>=0)
        {
            var lastrun = new Run();
            var lasttxt = new Text
            {
                Text = newText,
                Space = SpaceProcessingModeValues.Preserve
            };
            lastrun.Append(lasttxt);
            si.Append(lastrun);
        }
    }
}

The Space = SpaceProcessingModeValues.Preserve part is crucial here, als else it will glue all three parts together and dismiss the spaces between.

I think I'll look into this EPPlus, as its "In-cell Richtext" feature sounds rather promising.