Excel Interop: Fastest way to change color of portions of text in a huge range of cells

Question

There some articles about the fastest way to write data using Excel interop assigning directly an Array of data to the value of the range. Like:

string[,] multidimensionalArrayData = new string[200, 3];
    // (...) Fill multidimensionalArrayData with your data
dataSheet.Range["A1:C200"].Value = multidimensionalArrayData;

There are also some articles about how to change the Font color of a specific portion of text, for example (VB this time):

With ActiveCell.Characters(Start:=3, Length:=3).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Color = "Red"
    .ThemeFont = xlThemeFontNone
End With

The question now is, what would be the fastest way to change the color of specific portions of text for thousands of cells? Currently, in my C# code, I have to do it cell by cell, with a horrible performance hit. Is there a way to fill an array of 'Characters' objects in C# and pass that array to a range in one go? Any other solutions?

Answer 1

Operations using Excel Interop is always slower, more memory consume, not recommended.

Below are some of the Open Source, yet faster way to do what you need and without the need of installation of Excel:

http://closedxml.codeplex.com/
http://epplus.codeplex.com/
http://code.google.com/p/excellibrary/
http://npoi.codeplex.com

Released by Microsoft: Open XML 2.0
Another faster way to do what you want.
Download: http://www.microsoft.com/en-us/download/details.aspx?id=5124
Introduction: http://blog.stuartwhiteford.com/?p=49

Answer 2

I am not sure that you will gain a large performance increase with this code (as hacking into individual cells is a time consuming operation). Characters is apparently a range property, so you don't have to specify a particular cell.

You can use the following to avoid looping

With Range("A1:A3").Characters(1, 2).Font
    .Name = "Arial"
    .Size = 6
End With

Great question.... I have had a number of use cases for formatting partial cells but it is a pain to do manually.