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....
I going to explain the new method in two different ways. That is:
- Export the data from
- Export the data from
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
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:
Disable, the copying feature is disabled.
EnableAlwaysIncludeHeaderText, the copied text will include values of selected cells as well as captions of rows and columns that contain selected cells.
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.
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.
.Range("A1", misValue).EntireRow.Font.Bold = True
DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
Dim str As String = TryCast(DataGridView1.GetClipboardContent().GetData(DataFormats.UnicodeText), String)
.Range("A1:" & ConvertToLetter(DataGridView1.ColumnCount) & DataGridView1.RowCount, misValue).Select()
2. Export the data from DataTable
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
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
Public Function AryToString(ByVal ary As String()) As String
Return String.Join(vbNewLine, ToArray(dTable))
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.
xlWorkSheet.Range(ConvertToLetter(col + 1) & "1").EntireColumn.Select()
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)