Skale
asked on
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.
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.
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.
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 FunctionASKER
Your suggestion like on the right,
But i'd like to get format same as my DataGridView.
But i'd like to get format same as my DataGridView.
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.
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.
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"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.
ASKER
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.
Result is the same :(. I'm writing them as String to DataGridView but i dont understand why it recognize it as numbers at excel.
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?
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?
ASKER
Numbers are not being formatted correctly in excel.
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?
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?
Hakan
Can you, after the Excel file has been created, check what the formatting for the cells is set to?
Can you, after the Excel file has been created, check what the formatting for the cells is set to?
ASKER
Éric,
All of DataGridViewColumns are as Textbox or Combobox.
I'm creating with below code;
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 SubASKER
Norie,
It shows for all of them "General"
It shows for all of them "General"
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.
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.
ASKER
In this https://www.experts-exchange.com/questions/29148900/Numbers-stored-as-text-warning-vhen-exporting-with-ClosedXml-in-vb-net.html?anchorAnswerId=42882988#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.
There are no green warning triangles and they are right-justified.
ASKER
Yes they are numeric as i posted their screenshot, but with that type i lost precision after comma on some values.
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Eric,
I'm using below code and i took the screenshot of the result
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 FunctionSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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.
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.
Why do you appear to be converting the values you are putting in the cells to text?
Open in new window
You even seem to be setting the cell data type to Text.
What happens if you use this?
Open in new window