DataGridView Export Data to Excel and cell formatting changes

In the process of software development, it is often encountered that some data information is exported from DataGridView to Excel tables. If you write a lot of it will find quite simple, we'd better write a write, to stay as a backup, after all, sometimes CTRL + C and CTRL + is more convenient.

Ideas very easy. Write a module, and then call:

The module code is as follows:

<span style= "Font-family:times New roman;font-size:18px;"  > ' ********************************************** ' file name: Leadtoexcel ' namespace: UI ' function: Export data from DataGridView to Excel table ' Author: Shangui ' build Date: 2014/8/23 10:55:54 ' version number: V1.0.0.0 ' changelog: ' Copyright note: ' ***********************************************imp Orts Microsoft.Office.Interop ' Add a reference to Office module Leadtoexcel Function leadtoexcel (ByVal DGV as DataGridView) As Boolean ' export as Excel function ' Establish Excel connection Dim Myexcel as New excel.application () myExcel.Application.Workb Ooks.        Add (true) Myexcel.visible = True ' defines the loop variable, row, column variable Dim intRow As Integer Dim intcolumn As Integer Dim Inttitle as Integer ' joins the table header for inttitle = 0 to DGV. ColumnCount-1 myexcel.cells (1, inttitle + 1) = DGV. Columns (Inttitle). HeaderText Next Inttitle ' adds the data in the control to the table for IntRow = 0 to DGV by looping. RowCount-1 for intcolumn = 0 to DGV. ColumnCount-1 'Because the first row is a table header, when you add data: ' Excel assigns values from the second row, the first column (2,1). ' DataGridView from the first row, the first column (0. 0) Start Assignment (index from 0, 0 starts) myexcel.cells (IntRow + 2, Intcolumn + 1) = DGV (Intcolumn, IntRow). Value.tostring Next Intcolumn Next IntRow return True ' join completed, return true End functionend module& Lt;/span>

Click button to invoke

<span style= "Font-family:times New roman;font-size:18px;" >    Private Sub btnleadexcel_click (sender as Object, e as EventArgs) Handles Btnleadexcel.click        Call Leadtoexcel.leadtoexcel (DataGridView1)    End sub</span>

O (∩_∩) o~ This is generally the case, but there are still some details that need to be noted

problem Reason: Assuming that you have numbers displayed in DataGridView, and the numbers start with 0, 0 disappears after exporting Excel. (for example, a card number is 001, the export is displayed as 1)

If you think carefully, you will be clear. The format of numbers in Excel is default (regular cell formatting does not include any particular number format), so 0 of the number in front of the numbers is removed.

Workaround: in the Leadtoexcel module, when assigning a value, precede the data with " ' " to become a text format, such as the following:

<span style= "Font-family:times New roman;font-size:18px;" >        ' Add the data in the control through the loop to the table for        intRow = 0 to DGV. RowCount-1 for            intcolumn = 0 to DGV. ColumnCount-1                ' Because the first row is the table header. So when adding data:                ' Excel from the second row, the first column (2. 1) Start assigning values.                ' DataGridView from the first line. The first column (0. 0) Start Assignment (index from 0, 0 starts)                myexcel.cells (IntRow + 2, Intcolumn + 1) = "'" + DGV (Intcolumn, IntRow). Value.tostring            Next Intcolumn        next introw</span>

The solution to think again: according to the above method, all the cells will be converted into text format, and not necessarily meet all the circumstances, so, need to find the data column. Just put " ' " in front of this column.

extrapolate: date. Time, currency format conversion can be solved according to this idea.

re-understanding: Assuming that the original design of the database or write the entity can take into account these circumstances, and make appropriate changes, perhaps the latter problems will not arise. (PS: Will encounter other new problems, hehe, these all need to accumulate experience in the future)

DataGridView Export Data to Excel and cell formatting changes