Bulk data Export to Excel from Datatable and Datagridview

• Download sample - 54.8 KB

Introduction

Exporting data from a .NET application to Excel is a very common requirement. A simple search on the Web results in several examples that show us the method to copy data and put it into the Excel cells. However, there is a payload problem. If there is bulk/huge data, then the current method is practically useless.

Background

It's copying the values cell by cell into Excel. The detailed explanation of the method can be found in my old article....
http://www.codeproject.com/Articles/19840/Export-to-Excel-using-VB-Net

New Method

I going to explain the new method in two different ways. That is:

1. Export the data from Datagridview.
2. Export the data from DataTable.

Yes, this is widely required by most developers. In purpose, I have taken these two as examples.

In brief: I am using Clipboard to copy the data from the source and pasting the copied values into the range of the Excels. It improves the exporting process and performance of the application. I.... think.... you guys are getting some idea!!! Am I right?
Ok guys, from the detailed explanation, you will understand more... here we go....

1. Export the data from Datagridview

The DataGridView allows you to copy what is selected in the grid to the clipboard using its GetClipboardContent method. The cell's contents are copied to the clipboard in different formats: as tab and comma-delimited text and as an HTML-formatted table (use DataFormats class in SetData method of Clipboard). Depending on the grid's ClipboardCopyMode property, the copied text may or may not include the row and column headers text:

• If ClipboardCopyMode is Disable, the copying feature is disabled.
• If ClipboardCopyMode is EnableAlwaysIncludeHeaderText, the copied text will include values of selected cells as well as captions of rows and columns that contain selected cells.
• If ClipboardCopyMode is EnableWithAutoHeaderText, the copied text will include the values of selected cells. The row and column headers will be included in the copied text only if at least one header is selected.
• If ClipboardCopyMode is EnableWithoutHeaderText, the copied text will include values of the selected cells without row and column headers.

I needed to provide a means of copying the entire grid's contents irrespective of what was selected. The code is quite simple. Below I am copying all the data in the datagridview including column name (header) and Unicode content, and pasting the copied data into the Excel from the beginning. Later, I clear the copy data from the clipboard memory. This method is simple and fast in data exporting from datagridview to Excel.

'Data transfer from grid to Excel.  
With xlWorkSheet     
    .Range("A1", misValue).EntireRow.Font.Bold = True     
    'Set Clipboard Copy Mode     
    DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText     
    DataGridView1.SelectAll()

    'Get the content from Grid for Clipboard     
    Dim str As String = TryCast(DataGridView1.GetClipboardContent().GetData(DataFormats.UnicodeText), String)
    
    'Set the content to Clipboard     
    Clipboard.SetText(str, TextDataFormat.UnicodeText)

    'Identify and select the range of cells in Excel to paste the clipboard data.     
    .Range("A1:" & ConvertToLetter(DataGridView1.ColumnCount) & DataGridView1.RowCount, misValue).Select()

    'Paste the clipboard data     
    .Paste()     
    Clipboard.Clear() 
End With

2. Export the data from DataTable

The Datatable is not facilitated with clipboard functionality. But there are some trick ideas which help us to achieve our goal. The trick ideas are convert datatable columns into array, convert the array into a single string divided by Newline and copy the converted string into clipboard as text and paste it into the particular cell. Sounds like converting.. converting... converting... Yes, it is so simple that we are not going to use FOR..Next loop at all. Check the conversion method, the "ToArray" method used to convert the datatable column to array and the "AryToString" method used to convert array into single string.

'Method convert datacolumn to array  
Public Function ToArray(ByVal dr As DataTable) As String()     
    Dim ary() As String = Array.ConvertAll(Of DataRow,String)(dr.Select(), AddressOf DataRowToString)   
Return ary End Function

Public Function DataRowToString(ByVal dr As DataRow) As String     
    Return dr(strCommonColumnName).ToString() 
End Function

'Method convert Array to string 
Public  Function AryToString(ByVal ary As String()) As String     
    Return String.Join(vbNewLine, ToArray(dTable)) 
End Function

These methods help to create a single string from a datatable column data. The new string will be copied to clipboard and pasted into a particular selected range of cell in Excel. The following code provides you with more information about bulk data transfer from database to Excel fast.

'Set the content from datatable (which is converted as array and again converted as string) 
Clipboard.SetText(AryToString(ToArray(dTable))) 

'Identifiy and select the range of cells in Excel to paste the clipboard data. 
xlWorkSheet.Range(ConvertToLetter(col + 1) & "1").EntireColumn.Select() 

'Paste the clipboard data 
xlWorkSheet.Paste() 
Clipboard.Clear()

Points of Interest

• No row wise looping
• Using Clipboard mechanism
• Fast data transfer
• Improved application performance

History

Pervious version of this article is export to Excel (for min. data)
http://www.codeproject.com/Articles/19840/Export-to-Excel-using-VB-Net