Numbers stored as text warning vhen exporting with ClosedXml in vb.net

Hakan
Hakan used Ask the Experts™
on
Hello,

I have a warning like below. Each excel cells has an small green sign at their top left corner.
When clicking on it it shows Numbers stored as text.


problem1.jpgproblem2.jpg
I didn't want to get this warning and small green signs and keep all of them as text how can i do it ?

My code is also like below.

        Public Shared Function WriteCheckSheetDgvToExcel(ByVal objSheet As Excel.IXLWorksheet, ByVal dgv As DataGridView) As Boolean
            Try
                For colHeader As Integer = 1 To dgv.ColumnCount Step 1
                    objSheet.Cell(1, colHeader).Value = dgv.Columns(colHeader - 1).HeaderText
                Next
                For col As Integer = 0 To dgv.ColumnCount - 1 Step 1
                    For row = 0 To dgv.Rows.Count - 1
                        objSheet.Cell(row + 2, col + 1).SetValue(Convert.ToString(dgv.Rows(row).Cells(col).Value)).SetDataType(Excel.XLDataType.Text)
                        If dgv.Rows(row).Cells(col).GetType = GetType(DataGridViewComboBoxCell) Then
                            Dim cboCell As New DataGridViewComboBoxCell
                            cboCell = CType(dgv.Rows(row).Cells(col), DataGridViewComboBoxCell)
                            If cboCell.Items.Count > 1 Then
                                objSheet.Cell(row + 2, col + 1).DataValidation.List(ComboboxItemsToList(cboCell), True)
                            End If
                        End If
                    Next
                Next
                FormatSheetAsTable(objSheet)
                Return True
            Catch ex As Exception
                Return False
                Tools.OutputLog.Print(Tools.OutputLog.Type.ERR, ex.Message)
            End Try
        End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Hakan

Why do you appear to be converting the values you are putting in the cells to text?
objSheet.Cell(row + 2, col + 1).SetValue(Convert.ToString(dgv.Rows(row).Cells(col).Value)).SetDataType(Excel.XLDataType.Text)

Open in new window


You even seem to be setting the cell data type to Text.

What happens if you use this?
objSheet.Cell(row + 2, col + 1).Value = dgv.Rows(row).Cells(col).Value

Open in new window

Author

Commented:
Your suggestion like on the right,

But i'd like to get format same as my DataGridView.

Picture1.png
NorieAnalyst Assistant

Commented:
Hakan

I would stick with my suggestion, it does appear to be resulting in 'real' numbers in the cells, but add another line to obtain the formatting you require.
objSheet.Cell(row + 2, col + 1).Value = dgv.Rows(row).Cells(col).Value

objSheet.Cell(row + 2, col + 1).Style.NumberFormat.Format = "0.000"

Open in new window


PS You could probably apply the formatting to the entire range in one go, as you would in Excel, but give the above a try.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
Norie,

Result is the same :(. I'm writing them as String to DataGridView but i dont understand why it recognize it as numbers at excel.
NorieAnalyst Assistant

Commented:
Hakan

What do you mean the 'result' is the same?

Are you still getting the green warning triangles?

Are the numbers not being formatted correctly in Excel?

Author

Commented:
Numbers are not being formatted correctly in excel.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
If you export your data as string, you will get exactly the formatting you want from your .Net application.

If you export your data as raw values (numbers, dates, ...), Excel will format them. What you need to do is to set the format of the column. How do you create your Excel sheet?
NorieAnalyst Assistant

Commented:
Hakan

Can you, after the Excel file has been created, check what the formatting for the cells is set to?

Author

Commented:
Éric,

All of DataGridViewColumns are as Textbox or Combobox.

I'm creating with below code;

        Public Shared Sub WriteCheckoutSheet()
            Dim folderpath As String = Tools.StripTools.SelectFolderDirectory

            If folderpath <> String.Empty Then

                Dim tc As TabControl = GUI.tabCheckLocal
                For Each page As TabPage In tc.TabPages
                    If page IsNot GUI.tabCheckDashboard Then
                        Dim nameSheetXL As String = Base.ModelNameWithoutExt & "_Check_" & RemoveWhitespace(page.Text) & "_" & Date.Now.ToString("ddMMyy_HHmm") & ".xlsx"
                        Dim filepath As String = Path.Combine(folderpath, nameSheetXL)
                        Dim xlBook As New Excel.XLWorkbook

                        Dim DGVs As New List(Of DataGridView)
                        GetDataGridViews(page, DGVs)
                        For Each dgv As DataGridView In DGVs
                            Utility.ExcelMethods.WriteCheckSheetDgvToExcel(xlBook.Worksheets.Add(dgv.Parent.Text), dgv)
                        Next
                        xlBook.SaveAs(filepath)
                    End If
                Next

            End If


        End Sub

Open in new window

Author

Commented:
Norie,

It shows for all of them "General"
NorieAnalyst Assistant

Commented:
Hakan

Are the values in the cells actually numeric?

You can test that using the Excel formula ISNUMBER?

From your earlier post it looks to me as if they are numeric as they are aligned to the right.

PS I was using https://github.com/ClosedXML/ClosedXML/wiki/Styles-NumberFormat as reference for applying formatting.

Author

Commented:
Norin,

I took a screenshot from both of them.

Picture2.pngproblem2.jpg
NorieAnalyst Assistant

Commented:
In this https://www.experts-exchange.com/questions/29148900/Numbers-stored-as-text-warning-vhen-exporting-with-ClosedXml-in-vb-net.html#a42882988 the screenshot on the bottom right it appears, to me anyway, that the values in the cells are numeric.

There are no green warning triangles and they are right-justified.

Author

Commented:
Yes they are numeric as i posted their screenshot, but with that type i lost precision after comma on some values.
NorieAnalyst Assistant

Commented:
Hakan

It might appear that you have lost precision but that could be down to formatting.

What happens if you apply an appropriate format, e.g. 0.0000, manually in Excel?
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
as I already said, you will need to format your columns. see https://github.com/closedxml/closedxml/wiki/Styles-NumberFormat

Author

Commented:
Eric,

I'm using below code and i took the screenshot of the result

        Public Shared Function WriteCheckSheetDgvToExcel(ByVal objSheet As Excel.IXLWorksheet, ByVal dgv As DataGridView) As Boolean
            Try
                For colHeader As Integer = 1 To dgv.ColumnCount Step 1
                    If dgv.Columns(colHeader - 1).Visible = True Then
                        objSheet.Cell(1, colHeader).Value = dgv.Columns(colHeader - 1).HeaderText
                    End If

                Next
                For col As Integer = 0 To dgv.ColumnCount - 1 Step 1
                    For row = 0 To dgv.Rows.Count - 1
                        objSheet.Cell(row + 2, col + 1).Value = dgv.Rows(row).Cells(col).Value
                        objSheet.Cell(row + 2, col + 1).Style.NumberFormat.Format = "0.000"
                        'objSheet.Cell(row + 2, col + 1).SetValue(Convert.ToString(dgv.Rows(row).Cells(col).Value)).SetDataType(Excel.XLDataType.Text)
                        If dgv.Rows(row).Cells(col).GetType = GetType(DataGridViewComboBoxCell) Then
                            Dim cboCell As New DataGridViewComboBoxCell
                            cboCell = CType(dgv.Rows(row).Cells(col), DataGridViewComboBoxCell)
                            If cboCell.Items.Count > 1 Then
                                objSheet.Cell(row + 2, col + 1).DataValidation.List(ComboboxItemsToList(cboCell), True)
                            End If
                        End If
                    Next
                Next
                FormatSheetAsTable(objSheet)
                Return True
            Catch ex As Exception
                Return False
                Tools.OutputLog.Print(Tools.OutputLog.Type.ERR, ex.Message)
            End Try
        End Function

Open in new window


problem3.jpg
NorieAnalyst Assistant
Commented:
Eric

I've already posted code that should format the cells as they are populated.
objSheet.Cell(row + 2, col + 1).Style.NumberFormat.Format = "0.000"

Open in new window


If it's entire columns that need formatting then perhaps this will work.
objSheet.Columns(col+1).Style.NumberFormat.Format = "0.000"

Open in new window

Senior .Net Consultant
Top Expert 2016
Commented:
is it possible that you have other code elsewhere changing the formatting? what's in FormatSheetAsTable?

Author

Commented:
Hi Eric and Norie,

Yes i clear all formats when im formatting as table.

Thanks for your support.

I have one small issue don't know have you got experience for that but is there way to apply F3 format in excel i tried but didnt succeed it. Like in here : https://docs.microsoft.com/en-us/dotnet/api/system.double.tostring?view=netframework-4.8
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
as shown in https://github.com/closedxml/closedxml/wiki/Styles-NumberFormat, there is no way to set the F3 format directly. You need to use a format that is supported by ClosedXML
NorieAnalyst Assistant

Commented:
Hakan

Is F3 formatting something that could be applied in Excel, perhaps using a custom format?

If it is then you should be able to apply it here using the same custom format string.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial