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 don't know C#, unfortunately, or I would adjust your code directly, but instead, I'll have to suffice by giving you a little sample/example in VB.Net, which I think you'll 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!")       ' <-- You'll 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 I'll be better at this kind of question in the future...

[barski]

Thanks! I didn't 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 can't 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. :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 don't 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 it's 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 Excel's 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 it's 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 I'm going to get a spreadsheet with values in columns A, B, and C but don't 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 isn't 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 you're still tracking this thread I have a question.

I'm 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 there's a way to do it with a range this would help. However, I can't 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,

 

I'm 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 can't help more, but I'm not a PowerPoint guy...

[Beeble]

Hi Mike,

 

Thanks for looking anyway. If I eventually crack it I'll 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.

 

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

 

John

[Mike_R]

I'm 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 you'll get an exception, but it couldn't hurt to try, right?

[noleafclover614]

Hi All,

 

I realize this thread is very old, but I recently came across it and it's ringing true for a problem I am having.

 

I am converting an old VBA program to .NET, and to make a long story short, I need to loop through cells and insert a blank row after each cell that doesn't match the one underneath it. Doing this by using ActiveCell.Offset is proving to be painfully slow, as the program takes about 50x longer to complete than the VBA counterpart.

 

While the array approach seems to be fine if you are doing some basic data manipulation, what would you suggest for my situation?

 

I can post code if necessary.

[dorramide7]

Ya it will be great if they both get active on this forum...

and share their exp. with gaming...

 

__________________

how to download wii games

downloading wii games