Why is this running so slow

[barski]

It works but seems slower than it should. ideas?

public static void ExportToExcel(DataTable dt)
{
	Excel.Application xlApp = new Excel.ApplicationClass();
	Excel.Workbook xlBook;
	Excel.Worksheet xlSheet;
	
	xlBook = xlApp.Workbooks.Add(Missing.Value);
	xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
	//Write headers
	for(int i=0; i<dt.Columns.Count; i++)
	{
		xlSheet.Cells[1,i+1] = dt.Columns[i].ColumnName.ToString();
	}

	//Write Data	
	for(int i=0; i<dt.Rows.Count; i++)
	{
		for(int k=0; k<dt.Columns.Count; k++)
		{
			xlSheet.Cells[2+i,k+1] = dt.Rows[i][k];
		}
	}
	xlApp.Visible = true;
	
}

 

[Mike_R]

The basic problem is that Automation is an Out-Of-Process situation. Excel is a Stand-Alone EXE Server and so the synching/marshalling involved is very slow, about 30-50x slower than running in-process. Sometimes as much as 80x slower. So VBA, while considered "slow" because it is not compiled to native, is actually 30-50x faster than Automation through VB6 or .Net. (The COM Interop adds more overhead as well for .Net.)

The key is to reduce the number of calls to the Excel Object Model. Each call has a ton of overhead and time-lag. So you want to push as much data as possible in each call. By looping through the cells and updating their values one-by-one, this is very slow. The key is to create a 2D Array(), filling it with the values you need, and then delivering the complete array in one shot.

I dont know C#, unfortunately, or I would adjust your code directly, but instead, Ill have to suffice by giving you a little sample/example in VB.Net, which I think youll be able to adopt/adjust easily:

    Sub ExportExample()
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRng As Excel.Range
        Dim TheValues(1, 1) As Object

        xlApp = New Excel.Application
        xlApp.Visible = True

        xlBook = xlApp.Workbooks.Add(Type.Missing)
        xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)

        TheValues(0, 0) = 1
        TheValues(0, 1) = 2
        TheValues(1, 0) = 3
        TheValues(1, 1) = 4

        xlRng = xlSheet.Range("A1:B2")  <-- Set xlRng = 2x2 Block of Cells
        xlRng.Value = TheValues         <-- Pass in Array in one shot!

        MessageBox.Show("Done!")        <-- Youll see the values in A1:B2 now.

        xlBook.Close(False)
        xlApp.Quit()

        xlRng = Nothing
        xlSheet = Nothing
        xlBook = Nothing
        xlApp = Nothing

        GC.Collect()
    End Sub

I hope this is clear. If not, please ask... and I have C# on order so hopefully Ill be better at this kind of question in the future...

 

[barski]

Thanks! I didnt know excel was "smart" enough to handle placing the array elements in the appropriate columns

 

[Mike_R]

Yeah, Excel uses 1-Based Arrays, so this is actually interesting. You are creating a 0-Based Array and passing it into Excel, which is converted to the 1-based arrays that Excel uses without any trouble.

However, when you return an Array from Excel to .Net, you actually will get a 1-based array! This is kinda cool/weird because 1-based arrays really cant be done in .Net as far as I know without some trickery...

To prove it, we can add a little bit of code and get:

    Sub ExportExample()
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRng As Excel.Range
        Dim TheValues(1, 1) As Object
        Dim Results(,) As Object

        xlApp = New Excel.Application
        xlApp.Visible = True

        xlBook = xlApp.Workbooks.Add(Type.Missing)
        xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)

        TheValues(0, 0) = 1
        TheValues(0, 1) = 2
        TheValues(1, 0) = 3
        TheValues(1, 1) = 4

        xlRng = xlSheet.Range("A1:B2")
        xlRng.Value = TheValues
        Results = DirectCast(xlRng.Value, Object(,))   <-- Results gets the Array.

        MessageBox.Show(Results.GetLowerBound(1).ToString)   Returns 1
        MessageBox.Show(Results.GetUpperBound(1).ToString)   Returns 2

        xlBook.Close(False)
        xlApp.Quit()

        xlRng = Nothing
        xlSheet = Nothing
        xlBook = Nothing
        xlApp = Nothing

        GC.Collect()
    End Sub

Again, apologizing for the VB.Net code, but note that .GetLowerBound is returning 1, not 0. Pretty cool.

Also, keep in mind that for Excel that the first Dimention is the RowIndex and the second dimention is the ColumnIndex. Most people consider this "backwards". You just have to get used to it...

 

[buddyB]

Thank you Mike R,
i had the same problem, Excelfile reading was very slow. How larger the excelfile how longer it took to read. Now it is very fast. I dont really understand the DirectCast, but it works.

BuddyB

 

[Mike_R]

Yes, Array manipulation is very fast, but controlling an out-of-process EXE Server is slow. COM Interop probably slows it a bit more as well.

So, as much as possible, move all data to-and-from Excel Worksheets in one large Array. Then manipulate the Array and then send it back in one shot.

I agree with you an the DirectCast() call here, you would not normally think its necessary. (Actually, if you have Option Strict Off then it is not necessary, but this is not a great practice).

Here is the code without DirectCast()

Dim xlSheet As Excel.Worksheet
xlSheet = xlBook.Worksheets.Item(1)

This looks simple enough. You are returning a Worksheet from the Worksheets Collection and passing it to xlSheet, which is declared As Excel.Worksheet. Perfect, right?

Well, Excel has a lot of legacy stuff floating around, and the Worksheets Collection actually includes things like old-school Macro 4 sheets. So Excel gave the Worksheets.Item() Property a return type of As Object. So, as far as the compiler is concerned, you are passing an Object returned by Worksheets.Item() to be held within xlSheet As Excel.Worksheet. This is a Narrowing Conversion.

With Option Strict Off this conversion occurs at run-time (and in this case would succeed). With Option Strict On, however, the compiler would flag this, and you would have to explicitly tell it which Object Type the returned Object actually is. So you must adjust your code with CType or DirectCast (DirectCast is better):

Dim xlSheet As Excel.Worksheet
xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)

You have now told the compiler exactly what Object this is. This looks a little silly here, I admit, but this is only because Worksheets.Item() is declared As Object which what a poor choice on Excels part, to be honest. Using Option Strict On is highly recommended, for it can pick up some subtle flaws that are far less "silly" than this example...

 

[bugmenot]

Mike,
I realize its been a long time since this thread was active but can you tell me how to dynamically get the bottom of the range I want to get into an array based on conditions?
For example Im going to get a spreadsheet with values in columns A, B, and C but dont know how many rows. For this line:

xlRng = xlSheet.Range("A1:B"+intX.ToString)

How do I get the value of intX so that X is the last row where column A isnt empty? (or find the first empty cell in column A and subtract 1)
Thanks,
Andy

 

[Mike_R]

Hey Andy,

Assuming that at least Cell A1 has some data in it (you might want to check first to make sure) you could use the Range.End() method. For example, using VB.NET, you could do something like this:

Dim rng As Excel.Range = xlSheet.Range("A1")
rng = rng.End(Excel.XlDirection.xlDown)

C# is the same, really:

Excel.Range rng = xlSheet.get_Range("A1", Type.Missing);
rng = rng.End(Excel.XlDirection.xlDown);

This only works though if the column has no empty cells within the column, otherwise it will stop at the first gap. If there are gaps, you might want to come up from the bottom:

Dim rng As Excel.Range = _
    CType(xlSheet.Cells(xlSheet.Rows.Count, 1), Excel.Range)
rng = rng.End(Excel.XlDirection.xlUp)

In C#:

Excel.Range rng = 
    (Excel.Range) xlSheet.Cells(xlSheet.Rows.Count, 1);
rng = rng.End(Excel.XlDirection.xlUp);

Hope this helps!
Mike

 

[Qete_ARG]

I know the thread is a little old, but I am just starting to discover the .NET automation of Office (Excel in my case) and this helps me a lot. All I did before was in VBA.
Just wanted to say thanks!!!
Miguel.

 

[Mike_R]

Sure, no problem, glad it helped!

,
Mike

 

[Beeble]

Hi Mike,

Some great information here. If youre still tracking this thread I have a question.
Im using VB to automate PowerPoint presentations and need to chart information. I do this by writing to the datasheets behind charts. I can do it a cell at a time but if theres a way to do it with a range this would help. However, I cant figure out what I need to do with the "DirectCast" syntax.
An example of what I use currently is:
oGraph.application.datasheet.range("0" & intRowNo + 1).value = saData(intBrandNo, 1)
By looping through this I can build the datasheet.
Any help would be appreciated.
John

 

[Mike_R]

Hey John,

Im sorry to say, but I do not have a clue about the PowerPoint object model. Using the PowerPoint object browser in VBA, I cannot even find the Application.DataSheet member. Odd...

The examples I see online seem to use the same "cell-by-cell" approach that you are using. (For example: here. However, there seems to be a mechanism for importing an Excel spreadsheet into a chart, so that might be the way to go, if you can figure it out.

Sorry I cant help more, but Im not a PowerPoint guy...

 

[Beeble]

Hi Mike,

Thanks for looking anyway. If I eventually crack it Ill post the answer here.
It took me a lot of searching to work out how to do it the way I am currently.
Once you get down to using MSGraph within PowerPoint there is no help from Intellisense.

Im still extremely glad I found your posts. They have been most enlightening.

John

 

[Mike_R]

Im glad it helped (at least a little).

You might want to try something like:

oGraph.Application.DataSheet.Range("A1:C3").Value

And see if you can get a 2D array of values or set a 2D array of values in one shot. It will either work or youll get an exception, but it couldnt hurt to try, right?