question

JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 asked JorgeEnciso-6991 answered

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

I have a data grid:
128871-image.png


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.

![128864-image.png][2]


dotnet-visual-basicoffice-scripts-excel-dev
image.png (109.8 KiB)
image.png (62.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 answered

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 :)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered

Hi @JorgeEnciso-6991 ,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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.

129079-s2.png

Full source - look at Export code and form code



s2.png (27.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 answered XingyuZhao-MSFT commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

There is no C# in the code sample. The only thing that is C# is SpreadSheetLight's code samples.

For docs they show VB


0 Votes 0 ·
s1.png (14.1 KiB)

Hi @JorgeEnciso-6991 ,

some times I can have 2 (00123P) or four 0000123P)

What's the data type of the first column in your DataGridView?
I find that if the first column is type of string , it can be exported to excel correctly.
A simple test :
129996-1.png


0 Votes 0 ·
1.png (13.2 KiB)
JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 answered karenpayneoregon commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HOW can i send only the columns in my DATAGRIDVIEW?

You write SQL to read data which specifies only the columns needed or get all columns and removed unwanted columns via remove method.


0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Here is an example for getting practical columns from an Excel WorkSheet.

Code to get DataTable

Form code where all code for column formatting is in the project.

 Public Class LimitedForm
     Private Sub LimitedForm_Shown(sender As Object, e As EventArgs) Handles Me.Shown
         DataGridView1.DataSource = ExcelOperations3.Read()
         For Each dataGridViewColumn As DataGridViewColumn In DataGridView1.Columns
             dataGridViewColumn.HeaderText = dataGridViewColumn.HeaderText.SplitCamelCase()
         Next
         DataGridView1.ExpandColumns()
     End Sub
    
 End Class

129275-limitedform.png

129331-countrysheet.png




limitedform.png (38.5 KiB)
countrysheet.png (42.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 answered karenpayneoregon commented

Karen
I add this formatting and the excel open but no data is delivered.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you have a GitHub account consider creating a repo for the project and post the link here. Don't use Onedrive, Google Drive etc.

0 Votes 0 ·
JorgeEnciso-6991 avatar image
0 Votes"
JorgeEnciso-6991 answered XingyuZhao-MSFT commented

XingyuZhao-MSFT,
The first column is a string.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JorgeEnciso-6991 ,
Could you share your code on github? It will help us make a test.

0 Votes 0 ·