在Excel中,数字字符串用不同格式表示,可代表不同数据意义。例如在财务报表里需要用特定的数字字符串格式来反映金额信息、货币币种、数据精确程度、增减趋势等等。下面分享如何通过C#编程来设置Excel表格中的数字字符串格式。
使用工具:Spire.XLS for .NET
C#代码示例
using Spire.Xls; using System; namespace SetDataformat_XLS { class Program { static void Main(string[] args) { //初始化Workbook对象 Workbook workbook = new Workbook(); //获取第一个工作表 Worksheet sheet = workbook.Worksheets[0]; //写入文本到B1,C1 sheet.Range["B1"].Text = "数字格式"; sheet.Range["C1"].Text = "应用效果"; sheet.Range["B1"].Style.Font.IsBold = true; sheet.Range["C1"].Style.Font.IsBold = true; //在B3中写入文本(即数字格式),在C3中写数字并应用数字格式 sheet.Range["B3"].Text = "0"; sheet.Range["C3"].NumberValue = 12345678; sheet.Range["C3"].NumberFormat = "0"; //重复上面的步骤写入更多格式及对应效果 sheet.Range["B4"].Text = "0.00"; sheet.Range["C4"].NumberValue = 12345678; sheet.Range["C4"].NumberFormat = "0.00"; sheet.Range["B5"].Text = "#,##0.00"; sheet.Range["C5"].NumberValue = 12345678; sheet.Range["C5"].NumberFormat = "#,##0.00"; sheet.Range["B6"].Text = "¥#,##0.00"; sheet.Range["C6"].NumberValue = 12345678; sheet.Range["C6"].NumberFormat = "¥#,##0.00"; sheet.Range["B7"].Text = "0;[Red]-0"; sheet.Range["C7"].NumberValue = -12345678; sheet.Range["C7"].NumberFormat = "0;[Red]-0"; sheet.Range["B8"].Text = "0.00;[Blue]-0.00"; sheet.Range["C8"].NumberValue = -12345678; sheet.Range["C8"].NumberFormat = "0.00;[Blue]-0.00"; sheet.Range["B9"].Text = "#,##0;[Red]-#,##0"; sheet.Range["C9"].NumberValue = -12345678; sheet.Range["C9"].NumberFormat = "#,##0;[Red]-#,##0"; sheet.Range["B10"].Text = "#,##0.00;[Green]-#,##0.000"; sheet.Range["C10"].NumberValue = -12345678; sheet.Range["C10"].NumberFormat = "#,##0.00;[Green]-#,##0.00"; sheet.Range["B11"].Text = "0.00E+00"; sheet.Range["C11"].NumberValue = 12345678; sheet.Range["C11"].NumberFormat = "0.00E+00"; sheet.Range["B12"].Text = "0.00%"; sheet.Range["C12"].NumberValue = 12345678; sheet.Range["C12"].NumberFormat = "0.00%"; sheet.Range["B13"].Text = "(###)###-###"; sheet.Range["C13"].NumberValue = 128346841; sheet.Range["C13"].NumberFormat = "(###)###-###"; sheet.Range["B14"].Text = "###-###"; sheet.Range["C14"].NumberValue = 2019328; sheet.Range["C14"].NumberFormat = "###-#-##"; //设置列宽 sheet.Columns[1].ColumnWidth = 20; sheet.Columns[2].ColumnWidth = 20; //保存文档 workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010); System.Diagnostics.Process.Start("output.xlsx"); } } }
设置效果:
(本文完)
转载请注明出处!