Excel Interop C# - How to read individual cell characters

Question

I would like to convert the Excel cell content to html (including font color, size, etc.) only with Microsoft.Office.Interop.Excel (Microsoft 365 64 bits / .NET 7.0) and no external libraries.

I manage to get all characters via range.Characters, but I'm not able to get individual character. I understand that from the documentation, it can't be iterated through, but it seems possible to get any sub-characters via Range.Characters[Object, Object] Property which perfectly works from VBA.

Does anyone have an idea what cause the issue? Why am I not able to write range.Characters[1, 1] to get the first character?

PS I already encountered a few methods (such as Add() instead of AddEx() for the ListRows which are highlighted via the intellisence, but I don't think it corresponds to a similar issue since we are facing a property but not a method).

Answer 1

I can't tell you why (perhaps someone with better knowledge of the dlls will know), but I can say that you can call the methods and properties of the Characters object. For example, if you're trying to access the character, the syntax rng.Characters[1, 1].Text will yield the string.

I presume that you're trying to access the Characters object to change a font or something on single character.

The sample code below gives some examples:

        var app = new XL.Application
        {
            Visible = true
        };
        var wb = app.Workbooks.Add();
        var ws = (XL.Worksheet)wb.Worksheets["Sheet1"];
        var rng = ws.Range["A1"];

        Console.WriteLine("Range value = abc");
        rng.Value = "abc";

        Console.WriteLine("Reading each character...");
        for (var i = 1; i <= rng.Characters.Count; i++)
        {
            var c = rng.Characters[i, 1].Text;
            Console.WriteLine($"  Character {i} = {c}");
        }

        Console.WriteLine("Setting second charater to x");
        rng.Characters[2, 1].Text = "x";
        Console.WriteLine($"Range value = {rng.Characters.Text}");

        Console.WriteLine("Making second charater bold");
        rng.Characters[2, 1].Font.Bold = true;

        Console.WriteLine("See Excel app for range value change.");
        Console.WriteLine("Press any key to close Excel app.");
        Console.ReadKey();
        wb.Close(false);
        app.Quit();