Excel中的默认数字格式 [英] Default numeric format in Excel

问题描述

我有一个将报告导出到Excel的Web应用程序。它通过将报表的HTML发送到剪贴板，然后将其粘贴到Excel中来完成此操作。快速和肮脏。它来得相当好。数字被格式化为数字，日期日期等等。

但是报告的负数被格式化为使用圆括号而不是减号，并且当粘贴时到Excel，它变成了一个减号。您只需在记事本中键入（200），然后将其选中，复制并粘贴到Excel电子表格中的单元格中，即可看到此操作。它将通过-200.

我的用户希望它显示为（200）。而且我可以使用Javascript中的自动化手动格式化选定的单元格。但是这很慢。有没有办法让Excel来改变它的默认数字格式？如果你粘贴的HTML格式的数据在HTML中表格形式转换成Excel，那么你可以包含CSS样式来控制单元格内容的显示方式。

见（eg）： http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html

VBA示例：

 子格式（）
 
 Dim html As String， d作为新的数据对象
 
 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示例： Excel Web查询的CSS格式

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?

解决方案

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

这篇关于Excel中的默认数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！