DataGridView exporting data to Excel and formatting changes in cells

In the software development process, often encounter some of the data from the DataGridView Export to Excel table, if you write more will find quite simple, we may as well write a write, stay as a backup, after all, sometimes CTRL + C and CTRL + is more convenient.

Ideas very simple, 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 ' Modify log: ' 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 ' add 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 begins with the first row, the first column (0,0) (index starts at 0, 0) Myexcel.cells (IntRow + 2, Intcolumn + 1) = DGV (Intcolumn, IntRow). Value.tostring Next Intcolumn Next IntRow return True ' add complete, 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~ is generally the case, but there are still some details to note

Cause: If 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 about it, you will understand that the format of numbers in Excel defaults to normal (the regular cell format does not contain any specific number format), so the number in front of the 0 is removed.

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

<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 a table header, when adding data:                ' Excel assigns values from the second row, the first column (2,1),                ' DataGridView is assigned from the first row, the first column (0,0) (index from 0, 0 start)                myexcel.cells (IntRow + 2, Intcolumn + 1) = "'" + DGV (Intcolumn, IntRow). Value.tostring            Next Intcolumn        next introw</span>

solution Rethinking: as described above, all the cells will be converted into text format, and not necessarily meet all the circumstances, so you need to find the data column, only in front of this column of content " ' " on the line.

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

re-understanding: If you were to design a database or write entities to consider these situations, and make appropriate changes, perhaps the following problems will not arise. (PS: will also encounter other new problems, hehe, these need to accumulate experience in the future)

DataGridView exporting data to Excel and formatting changes in cells