Click here to Skip to main content
15,843,727 members
Articles / Desktop Programming / Win32

Bulk data Export to Excel from Datatable and Datagridview

Rate me:
Please Sign up or sign in to vote.
4.19/5 (7 votes)
14 Nov 2014CPOL3 min read 65.3K   2.8K   11   19
Huge data transfer to Excel in a fast and efficient manner


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.


It's copying the values cell by cell into Excel. The detailed explanation of the method can be found in my old article....

New Method

Image 1

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     

    '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     
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) 

'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 

Points of Interest

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


Pervious version of this article is export to Excel (for min. data)


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionCan this code be used to populate data from an object instead of a Dataset? Pin
Member 1147562315-Jul-19 7:55
Member 1147562315-Jul-19 7:55 
QuestionThanks Pin
Abdul Amin Khan17-Dec-17 0:30
Abdul Amin Khan17-Dec-17 0:30 
QuestionOut of Memory Pin
billsut19-May-17 8:05
billsut19-May-17 8:05 
AnswerRe: Out of Memory Pin
Senthil Sambandam27-Dec-18 0:44
professionalSenthil Sambandam27-Dec-18 0:44 
QuestionHello Senthil Pin
Member 1260278318-May-17 13:13
Member 1260278318-May-17 13:13 
QuestionThanks For this but just a question please... Pin
Boy Balantoy6-Oct-16 21:07
Boy Balantoy6-Oct-16 21:07 
AnswerRe: Thanks For this but just a question please... Pin
Boy Balantoy6-Oct-16 21:14
Boy Balantoy6-Oct-16 21:14 
GeneralRe: Thanks For this but just a question please... Pin
Senthil Sambandam3-Nov-16 2:42
professionalSenthil Sambandam3-Nov-16 2:42 
GeneralRe: Thanks For this but just a question please... Pin
Boy Balantoy5-Dec-16 0:04
Boy Balantoy5-Dec-16 0:04 
QuestionError Pin
bethanco4-Aug-16 8:28
bethanco4-Aug-16 8:28 
AnswerRe: Error Pin
Senthil Sambandam3-Nov-16 2:30
professionalSenthil Sambandam3-Nov-16 2:30 
AnswerExcel export Pin
AnnuBhai7-Sep-15 1:37
AnnuBhai7-Sep-15 1:37 
GeneralRe: Excel export Pin
Senthil Sambandam7-Sep-15 4:29
professionalSenthil Sambandam7-Sep-15 4:29 
QuestionHow Export to Excel from Datatable Columnname s Pin
gxnxy22-May-15 10:58
gxnxy22-May-15 10:58 
AnswerRe: How Export to Excel from Datatable Columnname s Pin
Senthil Sambandam10-Jun-15 0:09
professionalSenthil Sambandam10-Jun-15 0:09 
GeneralRe: How Export to Excel from Datatable Columnname s Pin
Member 1387063228-Nov-18 23:33
Member 1387063228-Nov-18 23:33 
GeneralRe: How Export to Excel from Datatable Columnname s Pin
Senthil Sambandam27-Dec-18 0:48
professionalSenthil Sambandam27-Dec-18 0:48 
GeneralMy vote of 5 Pin
Gun Gun Febrianza18-Dec-14 20:20
Gun Gun Febrianza18-Dec-14 20:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.