Geek Answers Handbook

Excel Interop: the fastest way to change the color of parts of text in a huge range of cells

There are some articles about the fastest way to record data using Excel internetworking, which directly binds the data array to a range value. How:

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

There are also articles on how to change the font color of a certain part of the 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

Now the question is: what would be the fastest way to change the color of certain parts of the text for thousands of cells? Currently, in my C # code, I have to do this cell by cell, with terrible success. Is there a way to populate an array of Symbols objects in C # and pass that array to a range at a time? Any other solutions?

+4
source share
3 answers

After months of struggling with different libraries for writing Excel, I must say that I finally returned to Excel Interop, believe it or not.

I tried ClosedXML, EPPlus and SpreadsheetLite with all the problems in learning each library and adapting my code. After a long turnaround, errors that sometimes create Excel that cannot be opened, sometimes have terrible performance problems or memory failures, I decided to try Excel Interop again and was surprised to see that it was faster and no errors for my script.

If you want to try the libraries in the mjb answer, do it, but I recommend that you write your Interop code as best as possible, it may surprise you how quickly in the end if you do it right.

The best option for my case:

Use Excel Interop. Write all the Excel data at once using a 2-dimensional array, then format the rich text of those cells that need it in the second pass.

+2
source

Operations using Excel Interop are always slower; more memory is consumed rather than recommended.

Below are some of the Open Source, but faster ways to do what you need, and without having to install Excel:

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


Microsoft Released: Open XML 2.0
Another 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

+5
source

I'm not sure that you will get a big performance boost with this code (since breaking into individual cells takes a lot of time). Symbols are a property of the range, so you do not need to specify a specific cell.

You can use the following to avoid a loop

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

Great question .... I had several use cases for formatting partial cells, but it hurts to do it manually.

+2
source

More articles:


All Articles