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

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

HakanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

HakanAuthor 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.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

HakanAuthor 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?
HakanAuthor Commented:
Numbers are not being formatted correctly in excel.
Éric MoreauSenior .Net ConsultantCommented:
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?
HakanAuthor 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

HakanAuthor 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.
HakanAuthor 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.
HakanAuthor 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 ConsultantCommented:
as I already said, you will need to format your columns. see https://github.com/closedxml/closedxml/wiki/Styles-NumberFormat
HakanAuthor 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

Éric MoreauSenior .Net ConsultantCommented:
is it possible that you have other code elsewhere changing the formatting? what's in FormatSheetAsTable?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HakanAuthor 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 ConsultantCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.