-
Jul 28th, 2022, 02:21 PM
#1
Thread Starter
Hyperactive Member
Excel Interop Change NumerFormat on Range
I am currently using Excel Interop from 2007 to integrate an application export into an Excel document.
I am taking string data from a DataGridView bound by a DataTable and putting them into and Excel document for graphing. Everything works great, until the default decimals change to a comma (,) instead of a period (.).
Example:
1.24 graphs fine
1,24 graphs nothing
The problem comes from the text being read as text and not numbers. I have found a work around for this, which is to use the Interop to specify the Number Format of the cell, as listed below:
Code:
Try
For ri = RowStart + 2 To MEx + RowStart + 1
For ci = 5 To MEy + 4
Dim rng5 As Microsoft.Office.Interop.Excel.Range
rng5 = xlWorkSheet.Cells(ri, ci)
rng5.Select()
Try
rng5.NumberFormat = "General"
rng5.Value = rng5.Value
rng5.NumberFormat = "0.00"
rng5.Value = rng5.Value
Catch ex As Exception
End Try
ReleaseCOM(rng5)
Next
Next
Catch exe4 As Exception
End Try
Yes, this works, but it is extremely slow, as it has to loop through each cell.
I tried to combine this together and do an entire range:
Code:
Try
Dim rng5 As Microsoft.Office.Interop.Excel.Range
Dim rng6 As Microsoft.Office.Interop.Excel.Range
Dim rng7 As Microsoft.Office.Interop.Excel.Range
rng6 = xlWorkSheet.Cells(RowStart + 2, 5)
rng7 = xlWorkSheet.Cells(MEx + RowStart + 1, MEy + 4)
Dim r6 As String = rng6.Address
Dim r7 As String = rng7.Address
r6 = Replace(r6, "$", "")
r7 = Replace(r7, "$", "")
rng5 = xlWorkSheet.Range(r6 & ":" & r7)
rng5.Select()
Try
rng5.NumberFormat = "General"
rng5.Value = rng5.Value
rng5.NumberFormat = "0.00"
rng5.Value = rng5.Value
Catch ex As Exception
End Try
ReleaseCOM(rng5)
ReleaseCOM(rng6)
ReleaseCOM(rng7)
Catch ex As Exception
End Try
However, this does not work. No errors are thrown, everything goes through, but the Number Format does not change.
What am I doing wrong? Is there a better way to do this? Any suggestions?
Thanks!
~Frab
-
Jul 28th, 2022, 03:20 PM
#2
Re: Excel Interop Change NumerFormat on Range
you might want to try NumberFormatLocal
see this
Code:
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
Try
Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
xlWb = xlApp.Workbooks.Open("E:\TestFolder\ExcelTest\vbexcel.xlsx")
Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)
With xlSt
'do stuff here with Sheet
'xlSt.Range("A4").Interior.Color = Color.BlueViolet
xlSt.Range("B1", "B222").NumberFormatLocal = "#.##0.00"
End With
xlWb.Save()
xlApp.Quit()
xlApp = Nothing
Catch g As Exception
MessageBox.Show(g.ToString)
End Try
End Sub
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 28th, 2022, 04:19 PM
#3
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by ChrisE
you might want to try NumberFormatLocal
see this
Just tried it, same issue. The numbers are still formatted as text instead of numbers.
Code:
Try
Dim rng5 As Microsoft.Office.Interop.Excel.Range
Dim rng6 As Microsoft.Office.Interop.Excel.Range
Dim rng7 As Microsoft.Office.Interop.Excel.Range
rng6 = xlWorkSheet.Cells(RowStart + 2, 5)
rng7 = xlWorkSheet.Cells(MEx + RowStart + 1, MEy + 4)
Dim r6 As String = rng6.Address
Dim r7 As String = rng7.Address
r6 = Replace(r6, "$", "")
r7 = Replace(r7, "$", "")
rng5 = xlWorkSheet.Range(r6, r7)
rng5.Select()
Try
rng5.NumberFormatLocal = "#.##0.00"
Catch ex As Exception
End Try
ReleaseCOM(rng5)
ReleaseCOM(rng6)
ReleaseCOM(rng7)
Catch ex As Exception
End Try
-
Jul 29th, 2022, 02:15 AM
#4
Re: Excel Interop Change NumerFormat on Range
you will have to correct your Datatable first then
you say here...
I am taking string data from a DataGridView bound by a DataTable and putting them into and Excel document for graphing. Everything works great, until the default decimals change to a comma (,) instead of a period (.).
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 29th, 2022, 07:15 AM
#5
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by ChrisE
you will have to correct your Datatable first then
you say here...
Please elaborate, because I do not understand.
For US conversions, everything is fine, inside the software and inside Excel. For DE conversions (, decimals), everything on the program side does with it is suppose to, Excel doesnt. This is even the case when I have all the regional settings changed to reflect commas as the decimal point in both the windows OS and office suite.
-
Jul 29th, 2022, 08:53 AM
#6
Re: Excel Interop Change NumerFormat on Range
Have you tried setting options?
Code:
' XLapp = Excel.Application
'default US
'With XLapp
' .DecimalSeparator = "."
' .ThousandsSeparator = ","
' .UseSystemSeparators = True
'End With
'Other format
'With XLapp
' .DecimalSeparator = ","
' .ThousandsSeparator = "."
' .UseSystemSeparators = False
'End With
-
Jul 29th, 2022, 09:29 AM
#7
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by dbasnett
Have you tried setting options?
Code:
' XLapp = Excel.Application
'default US
'With XLapp
' .DecimalSeparator = "."
' .ThousandsSeparator = ","
' .UseSystemSeparators = True
'End With
'Other format
'With XLapp
' .DecimalSeparator = ","
' .ThousandsSeparator = "."
' .UseSystemSeparators = False
'End With
I have. No change. Excel is still reading the data as text and not numbers.
-
Jul 29th, 2022, 09:36 AM
#8
Re: Excel Interop Change NumerFormat on Range
Originally Posted by Frabulator
Please elaborate, because I do not understand.
For US conversions, everything is fine, inside the software and inside Excel. For DE conversions (, decimals), everything on the program side does with it is suppose to, Excel doesnt. This is even the case when I have all the regional settings changed to reflect commas as the decimal point in both the windows OS and office suite.
you load Data to a DataTable then to a DGV, where does this Data come from ?
a CSV ?
a TXT ?
....
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 29th, 2022, 09:40 AM
#9
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by ChrisE
you load Data to a DataTable then to a DGV, where does this Data come from ?
a CSV ?
a TXT ?
....
It is pasted into the application by the user, or loaded from a csv.
-
Jul 29th, 2022, 09:48 AM
#10
Re: Excel Interop Change NumerFormat on Range
with a CSV you can use a schema.ini
Code:
[yourCSVFile.csv]
ColNameHeader=True
Format=Delimited(;)
DecimalSymbol=.
CurrencyDecimalSymbol=.
CharacterSet=ANSI
Col1=ProductCode Text Widht 30
Col2=Batch Integer
Col3=Counted Integer
Col5=myMoney Decimal
take a look at this thread
https://www.vbforums.com/showthread....nversion-Issue
Last edited by ChrisE; Jul 29th, 2022 at 09:51 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 29th, 2022, 07:38 PM
#11
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by ChrisE
with a CSV you can use a schema.ini
Code:
[yourCSVFile.csv]
ColNameHeader=True
Format=Delimited(;)
DecimalSymbol=.
CurrencyDecimalSymbol=.
CharacterSet=ANSI
Col1=ProductCode Text Widht 30
Col2=Batch Integer
Col3=Counted Integer
Col5=myMoney Decimal
take a look at this thread
https://www.vbforums.com/showthread....nversion-Issue
I dont think that would solve my issue. Once the data is loaded in, then it is automatically converted over into a data table of string variables inside my software. I need them to be strings so I can maniuplate the data. Not all will start off as numbers. The program does what it does with the data and then plops it out into Excel as all numbers.
I could possibly make it so the program exports a CSV, and then have Excel load the CSV, but that seems redundant.
It would be much easier if there were just a setting inside Excel that would allow me to convert a range of data to number formatting. I can do that manually (just by clicking on the exclamation mark and saying 'Convert all to Numbers') but I cant find a way to do this in code.
-
Jul 30th, 2022, 01:25 AM
#12
Re: Excel Interop Change NumerFormat on Range
I dont think that would solve my issue. Once the data is loaded in, then it is automatically converted over into a data table of string variables inside my software.
well you know where the problem is then
EDIT:
last try of mine ist the use of...EntireColumn
Code:
'....
xlSt.Range("A1").Value = "ID"
xlSt.Range("A:A").EntireColumn.NumberFormat = "0.00"
'........
but the problem is really your Datatable
Last edited by ChrisE; Jul 30th, 2022 at 01:38 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 30th, 2022, 10:55 AM
#13
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
Originally Posted by ChrisE
well you know where the problem is then
But if that is the problem, then why does it only affect the numbers that have a comma as the decimal? In the same logic, wouldn't 1.24 be read as text just as 1,24 is? Would it also not read 1,245.6 as text as well? I just dont understand how that could be different?
EDIT:
last try of mine ist the use of...EntireColumn
Code:
'....
xlSt.Range("A1").Value = "ID"
xlSt.Range("A:A").EntireColumn.NumberFormat = "0.00"
'........
I tried that, but the formatting is a little off using an interop setup. I will try and tweak it, as converting a column at a time is much quicker than a cell.
-
Jul 30th, 2022, 11:10 AM
#14
Re: Excel Interop Change NumerFormat on Range
If values are stored as their original types (Integer, Single, Date, etc), then they are passed directly into the target application that way, and the target application just needs to format them for display (ie: show a String version of the value).
If values are stored as Strings then the target application needs to decode those strings to convert them back to the original type (and this is the part that goes wrong, due to a wide variety of assumptions made in the code that converts them). After the conversion has happened, the target application can format them for display (as it did for values passed directly as their original type).
If you pass a DataTable containing the original data types, the values will be passed correctly, as no conversion is necessary. In terms of formatting things for display, you can do that in your app (via the various properties/methods of the DataGridView), and in the target app (such as setting NumberFormat in Excel).
-
Jul 30th, 2022, 09:31 PM
#15
Thread Starter
Hyperactive Member
Re: Excel Interop Change NumerFormat on Range
I have tried to take all the values from the datatable, converted the values to doubles, and then transposed them into another new datatable. The result was the same. Worked fine for period decimals, still 'text' as comma decimals when put into Excel.
I tried to special paste the values in, thinking that I could paste them in as 'values', but I had the same result.
I found that directly converting the numbers to doubles in Excel, instead of converting the text into numbers with the format tools, is much quicker.
Code:
Try
Dim rng5 As Microsoft.Office.Interop.Excel.Range
For ri = RowStart + 2 To MEx + RowStart + 1
For ci = 5 To MEy + 4
rng5 = xlWorkSheet.Cells(ri, ci)
rng5.Select()
rng5.Value = CDbl(rng5.Value)
ReleaseCOM(rng5)
Next
Next
Catch exe4 As Exception
End Try
I have not tried to export the data as CSV and load the CSV into excel. I suppose that will be the next step.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|