数字设置

如何设置数字和日期的显示格式

Microsoft Excel的一个非常强大的特性是它允许用户设置数字值和日期的显示格式。我们知道,数字数据可以用来表示不同的值,包括小数、货币、百分比、分数或会计值等。所有这些数值根据表示的信息类型以不同的格式显示。类似地,日期或时间可以以多种格式显示。 Aspose.Cells for Python via .NET支持此功能,允许开发人员为数字或日期设置任何显示格式。

如何在Microsoft Excel中设置显示格式

在Microsoft Excel中设置显示格式:

  1. 右键单击任何单元格。
  2. 选择设置单元格格式。将会出现一个对话框,用于设置任何类型值的显示格式。

在对话框左侧,有许多类别的值,如常规数字货币会计日期时间百分比等。Aspose.Cells for Python via .NET支持所有这些显示格式。

Aspose.Cells for Python via .NET提供了Workbook类,代表一个Microsoft Excel文件。Workbook类包含一个worksheets集合,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供一个cells集合,集合中的每个项目代表一个Cell类的对象。

Aspose.Cells for Python via .NET为get_styleset_style方法提供支持,用于Cell类。这些方法用于获取和设置单元格的格式。Style类提供一些有用的属性,用于处理数字和日期的显示格式。

如何使用内置数字格式

Aspose.Cells for Python via .NET提供一些内置的数字格式,用于配置数字和日期的显示格式。这些内置数字格式可以通过使用Number属性应用于Style对象。所有内置数字格式都赋予了唯一的数字值。开发人员可以将任何期望的数字值赋予Number属性,从而应用显示格式。这种方法速度快。Aspose.Cells支持的内置数字格式如下所列。

数值 类型 格式字符串
0 General General
1 Decimal 0
2 Decimal 0.00
3 Decimal #,##0
4 Decimal #,##0.00
5 Currency $#,##0;$-#,##0
6 Currency $#,##0;[Red]$-#,##0
7 Currency $#,##0.00;$-#,##0.00
8 Currency $#,##0.00;[Red]$-#,##0.00
9 Percentage 0%
10 Percentage 0.00%
11 Scientific 0.00E+00
12 Fraction # ?/?
13 Fraction # /
14 Date m/d/yyyy
15 Date d-mmm-yy
16 Date d-mmm
17 Date mmm-yy
18 Time h:mm AM/PM
19 Time h:mm:ss AM/PM
20 Time h:mm
21 Time h:mm:ss
22 Time m/d/yy h:mm
37 Currency #,##0;-#,##0
38 Currency #,##0;[Red]-#,##0
39 Currency #,##0.00;-#,##0.00
40 Currency #,##0.00;[Red]-#,##0.00
41 Accounting _ * #,##0_ ;_ * “_ ;_ @_
42 Accounting _ $* #,##0_ ;_ $* “_ ;_ @_
43 Accounting _ * #,##0.00_ ;_ * “??_ ;_ @_
44 Accounting _ $* #,##0.00_ ;_ $* “??_ ;_ @_
45 Time mm:ss
46 Time h :mm:ss
47 Time mm:ss.0
48 Scientific ##0.0E+00
49 Text @
from aspose.cells import SaveFormat, Workbook
from datetime import datetime
from os import os, path
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
# Create directory if it is not already present.
IsExists = path.isdir(dataDir)
if notIsExists:
os.makedirs(dataDir)
# Instantiating a Workbook object
workbook = Workbook()
# Obtaining the reference of first worksheet
worksheet = workbook.worksheets[0]
# Adding the current system date to "A1" cell
worksheet.cells.get("A1").put_value(datetime.now())
# Getting the Style of the A1 Cell
style = worksheet.cells.get("A1").get_style()
# Setting the display format to number 15 to show date as "d-mmm-yy"
style.number = 15
# Applying the style to the A1 cell
worksheet.cells.get("A1").set_style(style)
# Adding a numeric value to "A2" cell
worksheet.cells.get("A2").put_value(20)
# Getting the Style of the A2 Cell
style = worksheet.cells.get("A2").get_style()
# Setting the display format to number 9 to show value as percentage
style.number = 9
# Applying the style to the A2 cell
worksheet.cells.get("A2").set_style(style)
# Adding a numeric value to "A3" cell
worksheet.cells.get("A3").put_value(2546)
# Getting the Style of the A3 Cell
style = worksheet.cells.get("A3").get_style()
# Setting the display format to number 6 to show value as currency
style.number = 6
# Applying the style to the A3 cell
worksheet.cells.get("A3").set_style(style)
# Saving the Excel file
workbook.save(dataDir + "book1.out.xls", SaveFormat.EXCEL_97_TO_2003)

如何使用自定义数字格式

要为设置显示格式定义自定义的格式字符串,请使用Style对象的custom属性。这种方法不像使用预设格式那样快,但它更灵活。

from aspose.cells import SaveFormat, Workbook
from datetime import datetime
from os import os, path
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
# Create directory if it is not already present.
IsExists = path.isdir(dataDir)
if notIsExists:
os.makedirs(dataDir)
# Instantiating a Workbook object
workbook = Workbook()
# Adding a new worksheet to the Excel object
i = workbook.worksheets.add()
# Obtaining the reference of the newly added worksheet by passing its sheet index
worksheet = workbook.worksheets[i]
# Adding the current system date to "A1" cell
worksheet.cells.get("A1").put_value(datetime.now())
# Getting the style of A1 cell
style = worksheet.cells.get("A1").get_style()
# Setting the custom display format to show date as "d-mmm-yy"
style.custom = "d-mmm-yy"
# Applying the style to A1 cell
worksheet.cells.get("A1").set_style(style)
# Adding a numeric value to "A2" cell
worksheet.cells.get("A2").put_value(20)
# Getting the style of A2 cell
style = worksheet.cells.get("A2").get_style()
# Setting the custom display format to show value as percentage
style.custom = "0.0%"
# Applying the style to A2 cell
worksheet.cells.get("A2").set_style(style)
# Adding a numeric value to "A3" cell
worksheet.cells.get("A3").put_value(2546)
# Getting the style of A3 cell
style = worksheet.cells.get("A3").get_style()
# Setting the custom display format to show value as currency
style.custom = "£#,##0;[Red]$-#,##0"
# Applying the style to A3 cell
worksheet.cells.get("A3").set_style(style)
# Saving the Excel file
workbook.save(dataDir + "book1.out.xls", SaveFormat.EXCEL_97_TO_2003)

高级主题