Programmatically adding a style assumes number format as “General”
Few days back, I’ve seen an interesting scenario; If you add a “Style” programmatically, without specifying the number format as anything, it is assumed to be “General”.
Something like below is fine ..
Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim str As String str = "SomeStyle" Try oXL = CreateObject("Excel.Application") oXL.Visible = True ' Get a new workbook. oWB = oXL.Workbooks.Add oXL.ActiveWorkbook.Styles.Add(str) ' Add a new style to the workbook 'Define the style formatings oXL.ActiveWorkbook.Styles(str).NumberFormat = "#,##0.0" With oXL.ActiveWorkbook.Styles(str).Font .Name = "Times New Roman" .Size = 15 .Bold = True .Italic = True .Strikethrough = False End With With oXL.ActiveWorkbook.Styles(str) .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignTop .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .IncludeBorder = False End With Catch ex As Exception MessageBox.Show(ex.Message.ToString()) End Try
But if I comment out the line “.NumberFormat=”#,##0.0”, then the number format for “SomeStyle” is assumed to be “General”, which is .. well .. kind of fine in most cases.
Now, imagine a scenario, where you have a lot of different number formatting in different cells, (e.g. 9.7489900 is displayed as 9.74), and then if you apply a style, which has a “General” format, it would reset the number format back .. which means, now you have 9.7489900! I’m sure you won’t like it .. if you specifically omitted NumberFormating.
The only workaround I see is .. use something like below:
Public Sub ApplyStyle(str As String) Select Case str Case "Style1" With oXL.Selection.Font .Name = "Times New Roman" .Color = Color.Blue .Strikethrough = False End With With oXL.Selection .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignTop End With Case "Style2" With oXL.Selection.Font .Name = "Times New Roman" .Color = Color.Red .Strikethrough = False End With With oXL.Selection .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignTop .NumberFormat = "General" End With End Select End Sub
In this scenario .. you don’t really apply a style to the Workbook, you just have the code which decides as to, what is to be done @ runtime.
Okay! Bye …