15

I am writing a function to export data to Excel using the Office Interop in VB .NET. I am currently writing the cells directly using the Excel worksheet's Cells() method:

worksheet.Cells(rowIndex, colIndex) = data(rowIndex)(colIndex)

This is taking a long time for large amounts of data. Is there a faster way to write a lot of data to Excel at once? Would doing something with ranges be faster?

davidscolgan
  • 7,508
  • 9
  • 59
  • 78
  • This near-duplicate has extensive answers: http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects. – Govert Nov 10 '11 at 07:20

6 Answers6

31

You should avoid reading and writing cell by cell if you can. It is much faster to work with arrays, and read or write entire blocks at once. I wrote a post a while back on reading from worksheets using C#; basically, the same code works the other way around (see below), and will run much faster, especially with larger blocks of data.

  var sheet = (Worksheet)Application.ActiveSheet;
  var range = sheet.get_Range("A1", "B2");
  var data = new string[3,3];
  data[0, 0] = "A1";
  data[0, 1] = "B1";
  data[1, 0] = "A2";
  data[1, 1] = "B2";
  range.Value2 = data;
Mathias
  • 15,191
  • 9
  • 60
  • 92
  • 1
    I was unable to leave a comment on your blog, so let me write it here. You may create a 1-based array in C# using the CreateInstance method: var arr = (object[,] ) Array.CreateInstance(typeof(object), new int[] { 2, 3 }, new int[] { 1, 1 }) – IMil Dec 04 '13 at 06:09
  • The link to the blog article is dead. – puls200 Oct 31 '22 at 06:29
  • Fixed the link, thank you for the pointer @puls200 – Mathias Apr 07 '23 at 00:01
10

If you haven't already, make sure to set Application.ScreenUpdating = false before you start to output your data. This will make things go much faster. The set it back to True when you are done outputting your data. Having to redraw the screen on each cell change takes a good bit of time, bypassing this saves that.

As for using ranges, you still will need to target 1 (one) specific cell for a value, so I see no benefit here. I am not aware of doing this any faster than what you are doing in regards to actually outputting the data.

Gábor Imre
  • 5,899
  • 2
  • 35
  • 48
Tommy
  • 39,592
  • 10
  • 90
  • 121
5

Just to add to Tommy's answer.

  • You might also want to set the calculation to manual before you start writing.

Application.Calculation = xlCalculationManual

And set it back to automatic when you're done with your writing. (if there's a chance that the original mode could have been anything other than automatic, you will have to store that value before setting it to manual)

Application.Calculation = xlCalculationAutomatic

  • You could also use the CopyFromRecordset method of the Range object.

http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.copyfromrecordset(office.11).aspx

potatopeelings
  • 40,709
  • 7
  • 95
  • 119
3

The fastest way to write and read values from excel ranges is Range.get_Value and Range.set_Value.

The way is as below:

Range filledRange = Worksheet.get_Range("A1:Z678",Missing);
object[,] rngval = (object[,]) filledRange.get_Value (XlRangeValueDataType.xlRangeValueDefault);

Range Destination = Worksheet2.get_Range("A1:Z678",Missing);
destination.set_Value(Missing,rngval);

and yes, no iteration required. Performance is just voila!!

Hope it helps !!

eeerahul
  • 1,629
  • 4
  • 27
  • 38
Sunny Sharma
  • 4,688
  • 5
  • 35
  • 73
2

Honestly, the fastest way to write it is with comma delimiters. It's easier to write a line of fields using the Join(",").ToString method instead of trying to iterate through cells. Then save the file as ".csv". Using interop, open the file as a csv which will automatically do the cell update for you upon open.

Josaph
  • 429
  • 3
  • 7
  • 3
    If speed is what you're after then this is the way to go, however there are gotchas especially around strings and special characters – David B Heise Sep 10 '10 at 06:10
0

In case someone else comes along like me looking for a full solution using the method given by @Mathias (which seems to be the fastest for loading into Excel) with @IMil's suggestion on the Array.
Here you go:

'dt (DataTable) is the already populated DataTable
'myExcelWorksheet (Worksheet) is the worksheet we are populating
'rowNum (Integer) is the row we want to start from (usually 1)
Dim misValue As Object = System.Reflection.Missing.Value
Dim arr As Object = DataTableToArray(dt)
'Char 65 is the letter "A"
Dim RangeTopLeft As String = Convert.ToChar(65 + 0).ToString() + rowNum.ToString()
Dim RangeBottomRight As String = Convert.ToChar(65 + dt.Columns.Count - 1).ToString() + (rowNum + dt.Rows.Count - 1).ToString()
Dim Range As String = RangeTopLeft + ":" + RangeBottomRight
myExcelWorksheet.Range(Range, misValue).NumberFormat = "@" 'Include this line to format all cells as type "Text" (optional step)
'Assign to the worksheet
myExcelWorksheet.Range(Range, misValue).Value2 = arr

Then 

Function DataTableToArray(dt As DataTable) As Object
    Dim arr As Object = Array.CreateInstance(GetType(Object), New Integer() {dt.Rows.Count, dt.Columns.Count})
    For nRow As Integer = 0 To dt.Rows.Count - 1
        For nCol As Integer = 0 To dt.Columns.Count - 1
            arr(nRow, nCol) = dt.Rows(nRow).Item(nCol).ToString()
        Next
    Next
    Return arr
End Function

Limitations include only allowing 26 columns before it would need better code for coming up with the range value letters.

VenerableAgents
  • 645
  • 1
  • 8
  • 16