C#/Excel Code Slow; Suggestions?

[JonOfAllTrades]

Good evening, everyone. I have a simple C# program that copies a lot of data
into Excel, with some minimal formatting, and it runs VERY slowly. Once I
have the data (in DataGridView controls), I can write CSV files in seconds,
but the Excel part takes hours. This is the main code, sorry it's not very
readable in this forum:

int r, c;

....

r = 0;
foreach (DataGridViewRow row in grid.Rows)
{
for (c = 0; c < numColumns; /*iterated at bottom of block*/)
{
object value = row.Cells[c].Value;

if (value != null)
{
file.Write(Program.CleanForCSV(value.ToString()));
worksheet.Cells[r+3, c+1] = value; // Skip ahead to row #3, to allow
space for headers
}

if (++c < numColumns) file.Write(','); // We don't want a comma after
the last column
}
if (++r < numRows) file.WriteLine();
}

The odd iteration is the simplest way I could think of to efficiently avoid
trailing commas in CSV.
I've done some simple profiling at the procedure level, and there's no
single part of the export process that takes much longer than the rest. Can
I copy data by row or even copy the content of the whole DataGridView? It
seems like that should be possible, but I haven't seen where. Any ideas?
Thanks!

 

[Tim]

Since you're communicating between processes, a cell-by-cell transfer of a
lot of data is going to be slow, since each call has significant overhead
and you're making one call per cell value. Try building an array and
filling the sheet in one step.

Tim

 

[gimme_this_gimme_that]

I absolutely agree with Tim. Also, look for a DoEvents method so you
don't hold up the operating system.

 

[JonOfAllTrades]

Since you're communicating between processes, a cell-by-cell transfer of a
lot of data is going to be slow, since each call has significant overhead
and you're making one call per cell value. Try building an array and
filling the sheet in one step.

Tim

I suppose I took that to be a bit self-evident. What I need to know
is, how can I move data from a DataGridView to Excel en bloc? The data is
populated in C#, there's no bound data source I can access. I've gone over
the DataGridView and DataGridViewRow classes, and can't find a property or
method along the lines of "object[] Values". On the receiving side, in
Excel, the simple task of selecting a single row is maddeningly slippery.
"worksheet.Cells"? "worksheet.Range[0,0,numColumns,numRows]"?
"worksheet.Rows[n]"? None of the above, that would be too easy!
All the examples I can find on Google have used a bound data source, or
other workarounds like CSV or XML. Surely this isn't so difficult? It ought
to be as simple as "worksheet.Range[0,0,100,100].Values = grid.Values"!

 

[Tim Williams]

I'm not familiar with c# or .NET in general, but there must be a way to
access the values used to populate your datagrid.
Google groups had some promising stuff.

In VBA you can populate an entrire range from an array like this:

'************
Sub Tester()
Dim x, y
Dim arr(1 To 10, 1 To 10) as Integer


For x = 1 To 10
For y = 1 To 10
arr(x, y) = ((x - 1) * 10) + y
Next y
Next x

ActiveSheet.Range("A1").Resize(10, 10).Value = arr

End Sub
'************

ActiveSheet.Range("A1").Resize(10, 10).Value = arr
is equivalent to
ActiveSheet.Cells(1,1).Resize(10, 10).Value = arr

or

With ActiveSheet
.Range(.Cells(1,1), .Cells(10,10)).Value = arr
End With

a single row is referred to as

ActiveSheet.Rows(1)



Tim

Since you're communicating between processes, a cell-by-cell transfer of
a
lot of data is going to be slow, since each call has significant overhead
and you're making one call per cell value. Try building an array and
filling the sheet in one step.

Tim

I suppose I took that to be a bit self-evident. What I need to know
is, how can I move data from a DataGridView to Excel en bloc? The data is
populated in C#, there's no bound data source I can access. I've gone
over
the DataGridView and DataGridViewRow classes, and can't find a property or
method along the lines of "object[] Values". On the receiving side, in
Excel, the simple task of selecting a single row is maddeningly slippery.
"worksheet.Cells"? "worksheet.Range[0,0,numColumns,numRows]"?
"worksheet.Rows[n]"? None of the above, that would be too easy!
All the examples I can find on Google have used a bound data source,
or
other workarounds like CSV or XML. Surely this isn't so difficult? It
ought
to be as simple as "worksheet.Range[0,0,100,100].Values = grid.Values"!

 

[JonOfAllTrades]

I'm not familiar with c# or .NET in general, but there must be a way to
access the values used to populate your datagrid.
Google groups had some promising stuff.

In VBA you can populate an entrire range from an array like this:

'************
Sub Tester()
Dim x, y
Dim arr(1 To 10, 1 To 10) as Integer


For x = 1 To 10
For y = 1 To 10
arr(x, y) = ((x - 1) * 10) + y
Next y
Next x

ActiveSheet.Range("A1").Resize(10, 10).Value = arr

End Sub
'************

ActiveSheet.Range("A1").Resize(10, 10).Value = arr
is equivalent to
ActiveSheet.Cells(1,1).Resize(10, 10).Value = arr

or

With ActiveSheet
.Range(.Cells(1,1), .Cells(10,10)).Value = arr
End With

a single row is referred to as

ActiveSheet.Rows(1)

Tim

Now THAT would be convenient! Doing this in C# is not nearly that
straightforward. There's no Resize() function in C#, and the Rows() function
doesn't work that way. There's a get_Range(Cell1, Cell2) function, but it
doesn't take Cell objects as parameters! I guess COM doesn't allow for
method overloading, though since everything's passed as objects Excel should
be able to interrogate the parameters and interpret them correctly as strings
or Cells. I got this much to work:

string topLeft = "A1";
string bottomRight = char.ConvertFromUtf32(65 + numColumns - 1) +
numRows.ToString(); // 65 is ASCII for 'A', didncha know?
Range range = worksheet.get_Range(topLeft, bottomRight);

Later I found the get_Resize() function; looks like this is a known
workaround, KB 824004.
Building the array is much simpler, but my code is still not right to
actually push the data into Excel. KB 302096 uses
range.set_Value(Missing.Value, array), where array is an array of longs or
strings. The MS example uses [row][column], but I think that's wrong, other
sources indicate it's [column][row]. Trying to use set_Value, I keep getting
error "Specified array was not of the expected type."
I've tried object arrays and string arrays. I've tried specifying
RangeValueDataType, and I've tried using Type.Missing or
System.Reflection.Missing.Value. I've tried flipping the axis of my array.
I think I've tried every combination of these settings.
Clearly I'm missing something. Any ideas? If it can be done in
VB.NET, it can be done in C#, I'm sure.

 

[Tim Williams]

Sorry, .NET is new to me: I had assumed that when automating Excel you could
use syntax similar to VB[A] but it appears that's not the case.

Tim

I'm not familiar with c# or .NET in general, but there must be a way to
access the values used to populate your datagrid.
Google groups had some promising stuff.

In VBA you can populate an entrire range from an array like this:

'************
Sub Tester()
Dim x, y
Dim arr(1 To 10, 1 To 10) as Integer


For x = 1 To 10
For y = 1 To 10
arr(x, y) = ((x - 1) * 10) + y
Next y
Next x

ActiveSheet.Range("A1").Resize(10, 10).Value = arr

End Sub
'************

ActiveSheet.Range("A1").Resize(10, 10).Value = arr
is equivalent to
ActiveSheet.Cells(1,1).Resize(10, 10).Value = arr

or

With ActiveSheet
.Range(.Cells(1,1), .Cells(10,10)).Value = arr
End With

a single row is referred to as

ActiveSheet.Rows(1)

Tim

Now THAT would be convenient! Doing this in C# is not nearly that
straightforward. There's no Resize() function in C#, and the Rows()
function
doesn't work that way. There's a get_Range(Cell1, Cell2) function, but it
doesn't take Cell objects as parameters! I guess COM doesn't allow for
method overloading, though since everything's passed as objects Excel
should
be able to interrogate the parameters and interpret them correctly as
strings
or Cells. I got this much to work:

string topLeft = "A1";
string bottomRight = char.ConvertFromUtf32(65 + numColumns - 1) +
numRows.ToString(); // 65 is ASCII for 'A', didncha know?
Range range = worksheet.get_Range(topLeft, bottomRight);

Later I found the get_Resize() function; looks like this is a known
workaround, KB 824004.
Building the array is much simpler, but my code is still not right to
actually push the data into Excel. KB 302096 uses
range.set_Value(Missing.Value, array), where array is an array of longs or
strings. The MS example uses [row][column], but I think that's wrong,
other
sources indicate it's [column][row]. Trying to use set_Value, I keep
getting
error "Specified array was not of the expected type."
I've tried object arrays and string arrays. I've tried specifying
RangeValueDataType, and I've tried using Type.Missing or
System.Reflection.Missing.Value. I've tried flipping the axis of my
array.
I think I've tried every combination of these settings.
Clearly I'm missing something. Any ideas? If it can be done in
VB.NET, it can be done in C#, I'm sure.

 

[gimme_this_gimme_that]

You don't need resize.

Try something more like this.

Set objwb = objXL.Workbooks.Add
Set objws = ExcelAddSheet("ActiveDirectory",DARK_BLUE)
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
data = Array("fasdfa","asdfasdf","asdfasdafd")
objws.Range(objws.Cells(1,1),objws.Cells(1,3)).Value = data

Also, an Excel statement such as this one inserts a two dimensional
array into a Worksheet
where arr is an array with 5 rows and three columns:

objws.Range("A1:C5").Value =
Application.Transpose(Application.Transpose(arr))

 

[Ayman Absi]

I have the following code in VB.net with very good performance I think
you can convert it easly to C#:

Private Sub exportToExcel()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object

Dim data(1)() 'two dim array (Dynamic)
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Create an instance of Excel and add a workbook
xlApp = CreateObject("Excel.Application")
xlWb = xlApp.Workbooks.Add
xlWs = xlWb.Worksheets("Sheet1")

' Copy field names to the first row of the worksheet
fldCount = DataGridView1.ColumnCount
ReDim data(0)(fldCount)
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = DataGridView1.Columns(iCol -
1).HeaderText
Next

' Copy data to the rest rows of the worksheet
recCount = DataGridView1.Rows.Count
For iRow = 2 To recCount
For iCol = 1 To fldCount
data(0)(iCol - 1) = DataGridView1.Rows(iRow -
1).Cells(iCol - 1).Value
Next
xlWs.Range(xlWs.Cells(iRow, 1), xlWs.Cells(iRow, iCol -
1)).Value = data(0)
Next

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit()
xlApp.Selection.CurrentRegion.Rows.AutoFit()

' Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Release Excel references
xlWs = Nothing
xlWb = Nothing
xlApp = Nothing

End Sub