数字设置
如何设置数字和日期的显示格式
Microsoft Excel的一个非常强大的特性是它允许用户设置数字值和日期的显示格式。我们知道,数字数据可以用来表示不同的值,包括小数、货币、百分比、分数或会计值等。所有这些数值根据表示的信息类型以不同的格式显示。类似地,日期或时间可以以多种格式显示。 Aspose.Cells支持此功能,并允许开发人员为数字或日期设置任何显示格式。
如何在Microsoft Excel中设置显示格式
在Microsoft Excel中设置显示格式:
- 右键单击任何单元格。
- 选择设置单元格格式。将会出现一个对话框,用于设置任何类型值的显示格式。
在对话框的左侧,有许多值类别,如常规、数字、货币、会计、日期、时间、百分比等。Aspose.Cells支持所有这些显示格式。
Aspose.Cells提供了一个表示Microsoft Excel文件的类,Workbook。Workbook类包含一个Worksheets集合,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供一个Cells集合。Cells集合中的每个项都表示Cell类的对象。
Aspose.Cells为Cell类提供了GetStyle和SetStyle方法。这些方法用于获取和设置单元格的格式设置。Style类提供了一些有用的属性,用于处理数字和日期的显示格式。
如何使用内置数字格式
Aspose.Cells提供了一些内置数字格式来配置数字和日期的显示格式。这些内置数字格式可以通过Number对象的Style属性应用。所有内置数字格式都具有唯一的数值。开发人员可以将任何所需的数值分配给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 | @ |
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Obtaining the reference of first worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Adding the current system date to "A1" cell | |
worksheet.Cells["A1"].PutValue(DateTime.Now); | |
// Getting the Style of the A1 Cell | |
Style style = worksheet.Cells["A1"].GetStyle(); | |
// 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["A1"].SetStyle(style); | |
// Adding a numeric value to "A2" cell | |
worksheet.Cells["A2"].PutValue(20); | |
// Getting the Style of the A2 Cell | |
style = worksheet.Cells["A2"].GetStyle(); | |
// Setting the display format to number 9 to show value as percentage | |
style.Number = 9; | |
// Applying the style to the A2 cell | |
worksheet.Cells["A2"].SetStyle(style); | |
// Adding a numeric value to "A3" cell | |
worksheet.Cells["A3"].PutValue(2546); | |
// Getting the Style of the A3 Cell | |
style = worksheet.Cells["A3"].GetStyle(); | |
// Setting the display format to number 6 to show value as currency | |
style.Number = 6; | |
// Applying the style to the A3 cell | |
worksheet.Cells["A3"].SetStyle(style); | |
// Saving the Excel file | |
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003); |
如何使用自定义数字格式
要为设置显示格式定义自定义的格式字符串,请使用Style对象的Custom属性。这种方法不像使用预设格式那样快,但它更灵活。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Adding a new worksheet to the Excel object | |
int i = workbook.Worksheets.Add(); | |
// Obtaining the reference of the newly added worksheet by passing its sheet index | |
Worksheet worksheet = workbook.Worksheets[i]; | |
// Adding the current system date to "A1" cell | |
worksheet.Cells["A1"].PutValue(DateTime.Now); | |
// Getting the style of A1 cell | |
Style style = worksheet.Cells["A1"].GetStyle(); | |
// 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["A1"].SetStyle(style); | |
// Adding a numeric value to "A2" cell | |
worksheet.Cells["A2"].PutValue(20); | |
// Getting the style of A2 cell | |
style = worksheet.Cells["A2"].GetStyle(); | |
// Setting the custom display format to show value as percentage | |
style.Custom = "0.0%"; | |
// Applying the style to A2 cell | |
worksheet.Cells["A2"].SetStyle(style); | |
// Adding a numeric value to "A3" cell | |
worksheet.Cells["A3"].PutValue(2546); | |
// Getting the style of A3 cell | |
style = worksheet.Cells["A3"].GetStyle(); | |
// Setting the custom display format to show value as currency | |
style.Custom = "£#,##0;[Red]$-#,##0"; | |
// Applying the style to A3 cell | |
worksheet.Cells["A3"].SetStyle(style); | |
// Saving the Excel file | |
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003); |