How format a string column in Datagridview to be esport to excel?

Question

I have a data grid:

this is the code:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim rowsTotal, colsTotal As Short
Dim I, j, iC As Short
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim xlApp As New Excel.Application

    Try  
        Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add  
        Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)  
        xlApp.Visible = True  
        rowsTotal = DataGridView1.RowCount - 1  
        colsTotal = DataGridView1.Columns.Count - 1  

        With excelWorksheet  
            .Cells.Select()  
            .Cells.Delete()  
            For iC = 0 To colsTotal  
                .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText  
            Next  


            For I = 0 To rowsTotal - 1  
                For j = 0 To colsTotal  
                    .Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value  
                Next j  
            Next I  

            .Rows("1:1").Font.FontStyle = "Bold"  
            .Rows("1:1").Font.Size = 10  
            .Cells.Columns.AutoFit()  
            .Cells.Select()  
            .Cells.EntireColumn.AutoFit()  
            .Cells(1, 1).Select()  
        End With  

    Catch ex As Exception  
        MsgBox("Export to Excel Error: " & ex.Message)  
    Finally  
        'RELEASE ALLOACTED RESOURCES  
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default  
        xlApp = Nothing  

    End Try  

End Sub  

now the problem is I get the first column without the zeros at the left, I need this zeros because is part of the item number.

]2

Answer 1

ok, i read a couple articles and examples in the forum and come with this modification in my code:

For I = 0 To rowsTotal - 1
For j = 0 To colsTotal
.Cells.Columns.AutoFit().ToString()
.Cells.NumberFormat = "@"
.Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value.ToString()
Next j
Next I

it works perfectly, I'm assume if you explicitly give a format for a type of field the diferent type is take it.
anyway, thank you so much for the answers and guidance, im prety sure i will come back with more questions :)

Answer 2

Hi @Jorge Enciso ,
Take a look at the following code.

excelWorksheet.Columns(1).NumberFormat = "000000"  

More details you can see - How to enter the zero in front of numbers in excel?
Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

My recommendation is to not use Excel automation but instead use SpreadSheetLight (free on NuGet) and to read the DataGridView data into a DataTable where each column data is set as a string using this extension method.

I noticed you don't save the file while mine does, no big deal.

Only real downside to SpreadSheetLight is their docs are C# but with my demo you will be fine. The demo shown below has enough to get you going although you may need to tweak the code some what e.g. I setup columns in the DataGridView with proper names e.g. NumberColumn and DescriptionColumn which when exporting to Excel strips Column from each name so in Excel we have acceptable name but you might want to change that. Also note I've done styling and renamed the default sheet name.

Full source - look at Export code and form code

Answer 4

XingyuZhao-MSFT,
Thank you for your answer,
Your answer works, however is NOT a fix number of left zeros and this fix 5 zeros (or 6 or X) and some times I can have 2 (00123P) or four 0000123P) what i really need is send the text i have in mu column and not be reformatted when i send the data to Excel, now if I copy the from my Datagridview the data (select all and CTRL-C) and paste in Excel with SPECIAL PASTE TYPE, the copy works with the first column, How I can Replicate this??

Karen,
Thank you for your answer,
This is part of the more extensive program and I'm not sure I want and know how mix with C#, but Im going to check this nuget package for sure.

Answer 5

Now let me ask another question.

If you see my DATAGRIDVIEW I have only 7 columns, but my excel show ALL the columns in the table,

HOW can i send only the columns in my DATAGRIDVIEW?

Thank you