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:
- Export the data from
Datagridview
.
- 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 DataFormat
s 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.
With xlWorkSheet
.Range("A1", misValue).EntireRow.Font.Bold = True
DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
DataGridView1.SelectAll()
Dim str As String = TryCast(DataGridView1.GetClipboardContent().GetData(DataFormats.UnicodeText), String)
Clipboard.SetText(str, TextDataFormat.UnicodeText)
.Range("A1:" & ConvertToLetter(DataGridView1.ColumnCount) & DataGridView1.RowCount, misValue).Select()
.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
.
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
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.
Clipboard.SetText(AryToString(ToArray(dTable)))
xlWorkSheet.Range(ConvertToLetter(col + 1) & "1").EntireColumn.Select()
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