[RESOLVED] datagridview to excel - numbers saved as text ( vb.net )

[iDio]

hi all,

i have a vb.net form that let's me save a datagridview as excel, but it saves the numbers as text.
should i format the columns with numbers in the vb code or there's any other way to save numbers as numbers?
once i'm using a templated excel, where the datagrid values go to an existing file, i thought that they would be formatted the way the cells were formatted..

this is part of the code i'm using.

Private Sub ButtonExport_Click(sender As Object, e As EventArgs) Handles ButtonExport.Click
        'Sub Export()
        'My.Application.DoEvents()
        Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
        Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "template.xlsx")

        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(xlPath)
        xlWorkSheet = xlWorkBook.Worksheets("data")

          For i = 0 To DataGridView1.RowCount - 1
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value

                Next
            Next
        Next

        Dim m As Integer
        Dim n As Integer

        For m = 0 To DataGridView2.RowCount - 1
            For n = 0 To DataGridView2.ColumnCount - 1
                For k As Integer = 1 To DataGridView2.Columns.Count
                    xlWorkSheet.Cells(m + 1, n + 6) = DataGridView2(n, m).Value

                Next
            Next
        Next
 
        xlWorkSheet.SaveAs("C:\Users\User\Desktop\" & TextBox3.Text & ".xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        Me.Close()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

[Shaggy Hiker]

I seem to remember this behavior as being standard, except that I thought Excel defaulted to General, or something like that. I'd be inclined to try to change the Excel columns datatype setting unless ALL of the columns are numbers, in which case you could try just converting the DGV cells to a double, decimal, or integer. If the cells have strings in one column, numbers in a different one, and so on, then changing the columns in Excel seems like the easier solution.

[TnTinMN]

The problem is most likely due to the DGV being unbound and the Column.ValueType property being left to its default of Nothing with the columns are created as type DataGridViewTextBoxColumn. So when you enter a value into a DataGridViewTextBoxCell, the value is left as String. You can force type conversion by setting the type before displaying the DGV.

dgv1.Columns(0).ValueType = GetType(Int32) ' for integers
dgv1.Columns(0).ValueType = GetType(Double) ' for floating point
dgv1.Columns(0).ValueType = GetType(Decimal) ' for currency

Or you could parse each string to the proper type before sending to Excel.

[iDio]

I seem to remember this behavior as being standard, except that I thought Excel defaulted to General, or something like that. I'd be inclined to try to change the Excel columns datatype setting unless ALL of the columns are numbers, in which case you could try just converting the DGV cells to a double, decimal, or integer. If the cells have strings in one column, numbers in a different one, and so on, then changing the columns in Excel seems like the easier solution.

but i don't want to make ANY change in the excel. it is formatted the way to make pie charts and stuff when the values are inserted. it is suppose to make things easier..

The problem is most likely due to the DGV being unbound and the Column.ValueType property being left to its default of Nothing with the columns are created as type DataGridViewTextBoxColumn. So when you enter a value into a DataGridViewTextBoxCell, the value is left as String. You can force type conversion by setting the type before displaying the DGV.

dgv1.Columns(0).ValueType = GetType(Int32) ' for integers
dgv1.Columns(0).ValueType = GetType(Double) ' for floating point
dgv1.Columns(0).ValueType = GetType(Decimal) ' for currency

Or you could parse each string to the proper type before sending to Excel.

i tried "dgv1.Columns(0).ValueType = GetType(Decimal) " before showing the dgv and before exporting to excel.. nothing happened. any ideas?

[iDio]

or is it possible to format my templated excel ? something like formatting column 3 to convert all values to numbers?

[TnTinMN]

I misstated about sending a String containing a valid numeric representation as automatically being entered into the worksheet as a text. In fact Excel will convert a String represent numeric to a mumeric unless it is prefixed witha single quote.

The only way I can reproduce your issue is if the cells are preformatted as Text within the Excel worksheet. What is the preset cell format in the destination Worksheet?

[iDio]

I misstated about sending a String containing a valid numeric representation as automatically being entered into the worksheet as a text. In fact Excel will convert a String represent numeric to a mumeric unless it is prefixed witha single quote.

The only way I can reproduce your issue is if the cells are preformatted as Text within the Excel worksheet. What is the preset cell format in the destination Worksheet?

the whole column is set as decimal number. all the values that go there are something like "0,0"
i've tried setting them as text before , but the problem is still there: the charts won't update.

[TnTinMN]

the whole column is set as decimal number. all the values that go there are something like "0,0"
i've tried setting them as text before , but the problem is still there: the charts won't update.

I think that perhaps your decimal mark (comma) may be the issue here assuming that you are sending a string to Excel.

As a test, try changing:

xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value

to:

xlWorkSheet.Cells(i + 1, j + 1) = CDbl(DataGridView1(j, i).Value)

[iDio]

xlWorkSheet.Cells(i + 1, j + 1) = CDbl(DataGridView1(j, i).Value)

refers to the whole datagridview, which has this format ColA- INTEGER, ColB- DATE, ColC- DECIMAL (0,0), ColD- OK/NOT OK

so i changed to

xlWorkSheet.Cells(i + 1 , 3) = CDbl(DataGridView2(2, i).Value)

Error:
conversion to 'DBNull' of type Double is not valid.

[TnTinMN]

Is the DGV bound to a DataTable? If so, show the code that fills the DataTable.
Try this to handle the DBNull issue.

Dim val as Object = DataGridView2(2, i).Value
If IsDbNull(val) Then 
   xlWorkSheet.Cells(i + 1 , 3) = Nothing
Else
   xlWorkSheet.Cells(i + 1 , 3) = CDbl(val)
End If

[iDio]

it works like this ! thank you !
yep i understand what you did, but what does the DBNull issue mean?

For i = 0 To DataGridView2.RowCount - 1
            For j = 0 To DataGridView2.ColumnCount - 1
                Dim val As Object = DataGridView2(2, i).Value
                If IsDBNull(val) Then
                    xlWorkSheet.Cells(i + 1, 3) = Nothing
                Else
                    xlWorkSheet.Cells(i + 1, 3) = CDbl(val)
                End If
            Next
        Next

[TnTinMN]

yep i understand what you did, but what does the DBNull issue mean?

The fact that the test succeeded indicates that the DGV cell value is probably stored as a String value. My previous suggestion to set the the Datagridview.Column.ValueType will only validate/convert the values to the set type if they are entered/edited in the DGV. It will not convert previously set String values.

DBNull is value used by the database methods to indicate that a value was not stored in a field. This is why I asked (and you have not answered) about the use of a DataTable. Are you using OLEDB to load a DataTable from Excel and using that as the DGV DataSource? If so, you are probably using "IMEX=1" in the connection string; this forces all the values to be read as strings. Empty cells would receive a DBNull value.

When Excel is interacted with via Interop, it ignores regional/culture based conventions and uses US based conventions. So if it is sent a string containing "12,34" it will interpret it as a string because of the "comma". If you sent it a string of "12.34" it would interpret that as a numeric value of 12.34 (US convention of "." as the decimal mark).

The CDbl method honors the computer's set culture and converts a string of "12,34" (assuming the culture use comma as the decimal mark) to a double value. When this double value is sent to Excel, it does not have to do any interpretation and properly sets the cell value as numeric.

So the rule here is send Excel numeric data types where the value is numeric.

Edit: Please be polite and mark this thread resolved and also your posting on SO and any other forum that you have posted this to.

[iDio]

Nice explanation ! And no, i didn't use DataTables. Thank you very much! Thread closed/