CODEWITHSUNDEEP
vb.netperformanceexcelcomoffice-interop
dvcolgan
dvcolgan

Reputation: 7728

Fastest way to write cells to Excel with Office Interop?

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?

Upvotes: 16

Views: 32008

Answers (6)

Mathias
Mathias

Reputation: 15391

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;

Upvotes: 32

VenerableAgents
VenerableAgents

Reputation: 656

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.

Upvotes: 0

Tommy
Tommy

Reputation: 39807

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.

Upvotes: 10

Sunny Sharma
Sunny Sharma

Reputation: 4934

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 !!

Upvotes: 3

Josaph
Josaph

Reputation: 429

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.

Upvotes: 2

potatopeelings
potatopeelings

Reputation: 41075

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

Upvotes: 5

Related Questions