Default numeric format in Excel

Question

I have a web app that exports reports to Excel. It does this by sending the HTML of the report table to the clipboard, and then pasting it into Excel. Quick and dirty. And it comes through fairly well. Numbers are formatted as numbers, dates as dates, and so on.

But the reports have negative numbers formatted to use parentheses rather than a minus sign, and when this is pasted into Excel, it changes to a minus sign. You can see this in action simply by typing (200) into Notepad, and then selecting it, copying it, and pasting it into a cell in an Excel spreadsheet. It will come through as -200.

My users would like to have it display as (200). And I can use automation from Javascript to manually format selected cells. But that's slow. Is there any way to get Excel to change its default numeric format?

Answer 1

If you're pasting HTML-formatted data in the form of a table into Excel, then you can include css styles to control how the cell contents are displayed.

See (e.g.) : http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html

VBA example:

Sub Formatting()

    Dim html As String, d As New DataObject

    html = "<table><tr><td>(200)</td></tr>" & _
           "<tr><td style='mso-number-format:\@'>(200)</td></tr></table>"

    d.SetText html
    d.PutInClipboard

End Sub

HTML example: CSS Formatting for Excel Web Query