More efficient way to write data tables in Excel?

Question

In my WPF application, I have a huge data table ( System.Data.DataTable ) that I need to write on a sheet in an excel document. The hard part of the function:

  for (; i < dt.Rows.Count; i++) { for (int colNum = 0; colNum < dt.Columns.Count; colNum++) newSheet.Cells[i + rowNumber, colNum + 1] = dt.Rows[i][colNum].ToString(); applyRowBorderStyle(newSheet, i + rowNumber, dt.Columns.Count); } 

dt is DataTable, neewSheet is the excel sheet I am writing, and applyRowBorderStyle (..) adds borders to all cells in the row. It works very slowly when the data table is large, it can take 10 minutes or even more. Is there any way to do this faster?

---

Edit: the program analyzes a lot of data and makes a lot of sheets, and I can not get the user to do anything. I should use only Microsoft Excel. This sheet table always contains 42 columns, but the number of rows varies depending on how much data was received by the program, ~ 500 rows. "applyRowBorderStyle" will make the code run a little faster, but does not meet the requirements. I really hope there is another way to make it work faster.

Answer 1

found the answer! here the iv'e function wrote, and the link I used: http://www.codeproject.com/Articles/21519/Fast-Exporting-from-DataSet-to-Excel

 private void FastDtToExcel(System.Data.DataTable dt, Worksheet sheet, int firstRow, int firstCol, int lastRow, int lastCol) { Range top = sheet.Cells[firstRow, firstCol]; Range bottom = sheet.Cells[lastRow, lastCol]; Range all = (Range)sheet.get_Range(top, bottom); string[,] arrayDT = new string[dt.Rows.Count, dt.Columns.Count]; for (int i = 0; i < dt.Rows.Count; i++) for (int j = 0; j < dt.Columns.Count; j++) arrayDT[i, j] = dt.Rows[i][j].ToString(); all.Value2 = arrayDT; } 

takes less than a second, which is awesome :)

Answer 2

I have always found the most efficient efficient way to get datatable to excel to convert data to adodb.recordset.
The important part is using the excels CopyFromRecordSet Method
objWorksheet.Range ("A1"). CopyFromRecordset (ConvertToRecordset (dt))

Just checked a couple of comparisons and the results are below.

50k entries

Datatable to excel = 1 minute 6 seconds
Datatable to RS to Excel = 2 seconds

250 thousand records

Datatable to excel = 5 minutes 29 seconds
Datatable to RS to Excel = 10 seconds

The following is obviously written in vb.net, so you will need to convert the code to C # for your application, but hope this helps.

 Public Class Form1 Private dt As New DataTable Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load dt.Columns.Add("header1") dt.Columns.Add("header2") dt.Columns.Add("header3") dt.Columns.Add("header4") For i = 0 To 250000 dt.Rows.Add({i, i, i, i}) Next End Sub Private Sub DataTableConvBtn_Click(sender As System.Object, e As System.EventArgs) Handles DataTableConvBtn.Click Dim starttime = Now.ToString Dim objExcel = CreateObject("Excel.Application") objExcel.Visible = True Dim objWorkbook = objExcel.Workbooks.Add() Dim objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Range("A1").CopyFromRecordset(ConvertToRecordset(dt)) Dim endtime = Now.ToString MsgBox(starttime & vbCrLf & endtime) End Sub Public Shared Function ConvertToRecordset(ByVal inTable As DataTable) As ADODB.Recordset Dim result As ADODB.Recordset = New ADODB.Recordset() result.CursorLocation = ADODB.CursorLocationEnum.adUseClient Dim resultFields As ADODB.Fields = result.Fields Dim inColumns As System.Data.DataColumnCollection = inTable.Columns For Each inColumn As DataColumn In inColumns resultFields.Append(inColumn.ColumnName, TranslateType(inColumn.DataType), inColumn.MaxLength, ADODB.FieldAttributeEnum.adFldIsNullable, Nothing) Next result.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic) For Each dr As DataRow In inTable.Rows result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value) For columnIndex As Integer = 0 To inColumns.Count - 1 resultFields(columnIndex).Value = dr(columnIndex) Next Next Return result End Function Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum Select Case columnType.UnderlyingSystemType.ToString() Case "System.Boolean" Return ADODB.DataTypeEnum.adBoolean Case "System.Byte" Return ADODB.DataTypeEnum.adUnsignedTinyInt Case "System.Char" Return ADODB.DataTypeEnum.adChar Case "System.DateTime" Return ADODB.DataTypeEnum.adDate Case "System.Decimal" Return ADODB.DataTypeEnum.adCurrency Case "System.Double" Return ADODB.DataTypeEnum.adDouble Case "System.Int16" Return ADODB.DataTypeEnum.adSmallInt Case "System.Int32" Return ADODB.DataTypeEnum.adInteger Case "System.Int64" Return ADODB.DataTypeEnum.adBigInt Case "System.SByte" Return ADODB.DataTypeEnum.adTinyInt Case "System.Single" Return ADODB.DataTypeEnum.adSingle Case "System.UInt16" Return ADODB.DataTypeEnum.adUnsignedSmallInt Case "System.UInt32" Return ADODB.DataTypeEnum.adUnsignedInt Case "System.UInt64" Return ADODB.DataTypeEnum.adUnsignedBigInt End Select Return ADODB.DataTypeEnum.adVarChar End Function Private Sub DtToExcelBtn_Click(sender As System.Object, e As System.EventArgs) Handles DtToExcelBtn.Click Dim starttime = Now.ToString Dim objExcel = CreateObject("Excel.Application") Dim objWorkbook = objExcel.Workbooks.Add() Dim objWorksheet = objWorkbook.Worksheets(1) Dim i = 1 Dim rownumber = 1 objExcel.Visible = True Do While (i < dt.Rows.Count) Dim colNum As Integer = 0 Do While (colNum < dt.Columns.Count) objWorksheet.Cells((i + rownumber), (colNum + 1)) = dt.Rows(i)(colNum).ToString colNum = (colNum + 1) Loop i = (i + 1) Loop Dim endtime = Now.ToString MsgBox(starttime & vbCrLf & endtime) End Sub End Class