C# 设置Excel中的数字字符串格式

在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");
        }
    }
}

设置效果：

（本文完）

 转载请注明出处！