格式化Excel单元格(货币) [英] Formatting Excel cells (currency)

问题描述

我为Excel开发了一个加载项，因此您可以将MySQL数据库中的一些数字插入特定的单元格中.现在，我尝试将这些单元格格式化为货币，这有两个问题. 1.在带格式的单元格上使用公式时，例如，总和显示如下: "353,2574€".我该怎么做才能以适当的方式显示它? 2.有些单元格为空，但也必须使用货币格式.当使用与求和公式相同的格式并输入内容时，仅显示数字.没有€"，什么都没有.那是什么? 我指定了一个Excel.Range并用它来设置范围的格式

I developed an Add-In for Excel so you can insert some numbers from a MySQL database into specific cells. Now I tried to format these cells to currency and I have two problems with that. 1. When using a formula on formatted cells, the sum for example is displayed like that: "353,2574€". What do I have to do to display it in an appropriate way? 2. Some cells are empty but have to be formatted in currency as well. When using the same format I used for the sum formula and type something in, there's only the number displayed. No "€", nothing. What is that? I specified a Excel.Range and used this to format the range

sum.NumberFormat = "#.## €";

但是我也尝试过

sum.NumberFormat = "0,00 €";
sum.NumberFormat = "#.##0,00 €";

有人知道吗?

推荐答案

这对我有用.我有一个excel测试应用程序，将货币格式格式化为2个小数位，以逗号作为千位分隔符.下面是在Excel文件上写入数据的控制台应用程序.

This one works for me. I have excel test app that formats the currency into 2 decimal places with comma as thousand separator. Below is the Console Application that writes data on Excel File.

确保您已引用Microsoft.Office.Interop.Excel dll

Make sure you have referenced Microsoft.Office.Interop.Excel dll

using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var bankAccounts = new List<Account> {
                new Account { ID = 345678, Balance = 541.27},
                new Account {ID = 1230221,Balance = -1237.44},
                new Account {ID = 346777,Balance = 3532574},
                new Account {ID = 235788,Balance = 1500.033333}
};
            DisplayInExcel(bankAccounts);
        }
        static void DisplayInExcel(IEnumerable<Account> accounts)
        {
            var excelApp = new Excel.Application { Visible = true };
            excelApp.Workbooks.Add();
            Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
            workSheet.Cells[1, "A"] = "ID Number";
            workSheet.Cells[1, "B"] = "Current Balance";
            var row = 1;
            foreach (var acct in accounts)
            {
                row++;
                workSheet.Cells[row, "A"] = acct.ID;
                workSheet.Cells[row, "B"] = acct.Balance;

            }
            workSheet.Range["B2", "B" + row].NumberFormat = "#,###.00 €";
            workSheet.Columns[1].AutoFit();
            workSheet.Columns[2].AutoFit();
        }
    }
    public class Account
    {
        public int ID { get; set; }
        public double Balance { get; set; }
    }
}

输出

这篇关于格式化Excel单元格(货币)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！