Click here to Skip to main content
14,302,396 members

Bulk data Export to Excel from Datatable and Datagridview

Rate this:
4.19 (7 votes)
Please Sign up or sign in to vote.
4.19 (7 votes)
14 Nov 2014CPOL
Huge data transfer to Excel in a fast and efficient manner

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

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

License

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

Share

About the Author

Senthil Sambandam
Technical Lead
India India
No Biography provided

Comments and Discussions

 
QuestionCan this code be used to populate data from an object instead of a Dataset? Pin
Member 1147562315-Jul-19 6:55
memberMember 1147562315-Jul-19 6:55 
In my case, the source of data is not a dataset but an object that I need to use to populate the excel. One case if that I iterate through each object property to create a dataset but this will be time consuming and will defeat the purpose. Is there an alternative in which an object of a class can be used directly to populate the excel workbook?\

Thanks,
Gaurav
QuestionThanks Pin
Abdul Amin Khan16-Dec-17 23:30
memberAbdul Amin Khan16-Dec-17 23:30 
QuestionOut of Memory Pin
billsut19-May-17 7:05
memberbillsut19-May-17 7:05 
AnswerRe: Out of Memory Pin
Senthil Sambandam26-Dec-18 23:44
professionalSenthil Sambandam26-Dec-18 23:44 
QuestionHello Senthil Pin
Member 1260278318-May-17 12:13
memberMember 1260278318-May-17 12:13 
QuestionThanks For this but just a question please... Pin
Boy Balantoy6-Oct-16 20:07
memberBoy Balantoy6-Oct-16 20:07 
AnswerRe: Thanks For this but just a question please... Pin
Boy Balantoy6-Oct-16 20:14
memberBoy Balantoy6-Oct-16 20:14 
GeneralRe: Thanks For this but just a question please... Pin
Senthil Sambandam3-Nov-16 1:42
professionalSenthil Sambandam3-Nov-16 1:42 
GeneralRe: Thanks For this but just a question please... Pin
Boy Balantoy4-Dec-16 23:04
memberBoy Balantoy4-Dec-16 23:04 
QuestionError Pin
bethanco4-Aug-16 7:28
memberbethanco4-Aug-16 7:28 
AnswerRe: Error Pin
Senthil Sambandam3-Nov-16 1:30
professionalSenthil Sambandam3-Nov-16 1:30 
AnswerExcel export Pin
AnnuBhai7-Sep-15 0:37
memberAnnuBhai7-Sep-15 0:37 
GeneralRe: Excel export Pin
Senthil Sambandam7-Sep-15 3:29
professionalSenthil Sambandam7-Sep-15 3:29 
QuestionHow Export to Excel from Datatable Columnname s Pin
gxnxy22-May-15 9:58
membergxnxy22-May-15 9:58 
AnswerRe: How Export to Excel from Datatable Columnname s Pin
Senthil Sambandam9-Jun-15 23:09
professionalSenthil Sambandam9-Jun-15 23:09 
GeneralRe: How Export to Excel from Datatable Columnname s Pin
Member 1387063228-Nov-18 22:33
memberMember 1387063228-Nov-18 22:33 
GeneralRe: How Export to Excel from Datatable Columnname s Pin
Senthil Sambandam26-Dec-18 23:48
professionalSenthil Sambandam26-Dec-18 23:48 
GeneralMy vote of 5 Pin
Gun Gun Febrianza18-Dec-14 19:20
member Gun Gun Febrianza18-Dec-14 19: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.

Article
Posted 14 Nov 2014

Stats

47.1K views
2.4K downloads
9 bookmarked