1

I am currently needed to show data in a standard format in excel converted from a web application.

Number format needed to be displayed: 999.00

I use:

 CDbl(number).ToString("#,##0.00")

to display standard format in the web application. But when I convert it into excel, it displays: 999 (due to cell format = General)

Anything I can do to change the cell format to Number?

I tried add a spacebar in between to display the 2 decimal points:

CDbl(number).ToString("#,##0. 00")

It displays: 999. 00 (but it also changed to string instead of number and aligned to left which is not a good display)

Steve P.
  • 14,489
  • 8
  • 42
  • 72
Feliks
  • 11
  • 1
  • 1
  • 2

2 Answers2

2

It seems that you are in search of Range.NumberFormat Property. This snippet can help you out:

range.Cells(0,2).EntireColumn.NumberFormat = "@";

For more help check out this one.

Community
  • 1
  • 1
NeverHopeless
  • 11,077
  • 4
  • 35
  • 56
1

NeverHopeless is giving you information about how to use a macro in VBA to format the column in Excel. However, my suspicion is that you want excel users to be able to use a blank excel document to simply pull in new values directly into excel, or have a way to quickly open the formatted values in excel from the website. Unfortunately, as is evidenced by trying to load values from a csv file format, raw values do not contain the formatting information you need in excel.

One option would be to host an "export" button on the website to allow users to download a file from the website that is created on the fly based on the data values in the table and that has the formatting that excel needs. Be sure to use a file format that has the power to control the number format like xlsx, xls, xml, sylk, dif or something other than say csv which only has raw number values.

You can probably take advantage of the Excel API on the server side to help create an excel file if that's the format you want. Then you could control coloring, and all sorts of things besides just number format.

Using the Excel Interop library:

Dim style as Microsoft.Office.Interop.Excel.Style
style.NumberFormat = "#,##0.00"

With a reference to the range:

myRange.NumberFormat = "#,##0.00"

The current selection is also a range, so if you want it to work with the selection, just use:

Selection.NumberFormat = "#,##0.00"
Ted
  • 3,212
  • 25
  • 20
  • Thanks for your response. The excel user now can open the file in excel by clicking the "export" button in the website but the format is just not as same as in the website because of the excel autoformat. Is that possible that I change the excel option to disable the autoformat that changed "99.00" to "99"? – Feliks Jul 15 '13 at 03:50
  • How are you creating the excel file when they hit export? Are you using the Excel Interop libraries or are you using something else? Using the Interop it would be like: Dim style as Microsoft.Office.Interop.Excel.Style – Ted Jul 15 '13 at 13:28