Microsoft.Office.Interop.Excel really slow
You are updating individual cells. That's going to be very slow. If you think about it, each time you update a cell, an RPC call will be marshalled to the Excel process.
It will be much faster if you assign your two dimensional array of values to an Excel Range of the same dimensions in a single statement (one cross-process call) instead of your current 1200 x 800 = 960,000 cross-process calls.
Something like:
// Get dimensions of the 2-d array
int rowCount = indexMatrix.GetLength(0);
int columnCount = indexMatrix.GetLength(1);
// Get an Excel Range of the same dimensions
Excel.Range range = (Excel.Range) xlWorkSheet.Cells[1,1];
range = range.get_Resize(rowCount, columnCount);
// Assign the 2-d array to the Excel Range
range.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, indexMatrix);
Actually, to be pedantic, there are three cross-process calls in the above code (.Cells, .get_Resize and .set_Value), and there are two calls per iteration in your code (.Cells get and an implicit .set_Value) for a total of 1200 x 800 x 2 = 1,920,000.
Noterange.get_Resize
and range.set_Value
were needed for an old version of the Excel interop library I was using when this post was first authored. These days you can use range.Resize
and range.Value
as noted in the comment by @The1nk.
C# Excel Interop Slow when looping through cells
Excel and C# run in different environments completely. C# runs in the .NET framework using managed memory while Excel is a native C++ application and runs in unmanaged memory. Translating data between these two (a process called "marshaling") is extremely expensive in terms of performance.
Tweaking your code isn't going to help. For loops, string construction, etc. are all blazingly fast compared to the marshaling process. The only way you are going to get significantly better performance is to reduce the number of trips that have to cross the interprocess boundary. Extracting data cell by cell is never going to get you the performance you want.
Here are a couple options:
Write a sub or function in VBA that does everything you want, then call that sub or function via interop. Walkthrough.
Use interop to save the worksheet to a temporary file in CSV format, then open the file using C#. You will need to loop through and parse the file to get it into a useful data structure, but this loop will go much faster.
Use interop to save a range of cells to the clipboard, then use C# to read the clipboard directly.
Slow Performance When Reading Excel With Microsoft.office.Interop.Excel
You want to do it in one operation:
object[,] objectArray = cSheet.get_Range("A1:C4").Value2;
dataRange.Value2 = objectArray;
To get the UsedRange address for "A1:C4", try:
Microsoft.Office.Interop.Excel.Range range = cSheet.UsedRange;
string address = range.get_Address();
string[] cells = address.Split(new char[] {':'});
string beginCell = cells[0].Replace("$", "");
string endCell = cells[1].Replace("$", "");
Interop Excel is slow
This answer is only about the second part of your question.
Your are using lots of ranges there which is not as intended and indeed very slow.
First read the complete range and then iterate over the result like so:
var xx[,] = (MySheet.Cells["A1", "XX100"] as Excel.Range).Value2;
for (int i=0;i<xx.getLength(0);i++)
{
for (int j=0;j<xx.getLength(1);j++)
{
Console.WriteLine(xx[i,j].toString());
}
}
This will be much faster!
Microsoft Office Interop Excel ExportAsFixedFormat - very slow, images not loaded
I am running the code in ASP.NET server with IIS.
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.
I'd suggest using the Open XML SDK instead, see Welcome to the Open XML SDK 2.5 for Office. Also you may consider using third-party components designed for the server-side execution.
Related Topics
Should You Obfuscate a Commercial .Net Application
Best Way to Split String into Lines
Mock Httpcontext for Unit Testing a .Net Core MVC Controller
Should I Use Appdomain.Currentdomain.Basedirectory or System.Environment.Currentdirectory
Extension Methods Syntax VS Query Syntax
How to Programmatically Generate an X509 Certificate Using Only C#
What's a Good Threadsafe Singleton Generic Template Pattern in C#
String.Replace() VS. Stringbuilder.Replace()
Microsoft.Office.Interop.Excel Really Slow
Suppress Properties with Null Value on ASP.NET Web API
Binding Property to Control in Winforms
How to Execute an X86 Assembly Sequence from Within C#
Send Http Post Message in ASP.NET Core Using Httpclient Postasjsonasync
How to Get the Network Interface and Its Right Ipv4 Address
.Net, Event Every Minute (On the Minute). Is a Timer the Best Option