如何知道Excel单元格的格式 [英] How to know the formatting of Excel Cell

问题描述

是否有可能弄清楚我知道有.NumberFormat的Excel单元格的格式，但它返回格式而不是类型...基本上我需要知道它是否是自定义的，那么它应该返回自定义，如果是货币，它应该返回货币或任何其他数据类型
请帮助我

Is it possible to figure out the Format of Excel cell i know there is .NumberFormat but it returns the formatting but not the type... basically i need to know if it is custom then it should return custom and if it is currency it should return Currency or any other datatype Please help me

推荐答案

Excel会将值存储在特殊格式下方式（大多数数据类型实际上是双精度），这使得在没有Excel的情况下检测单元格格式变得棘手。

Under the hood Excel stores values in a special way (most datatypes are actually doubles) and that makes it tricky to detect Cell Formats without the help of Excel.

因此，我建议您利用内置的Excel CELL 函数，而不要亲自查询数据类型：

Hence I recommend you leverage the inbuilt Excel CELL function rather that interrogating the datatypes yourself:

private void button1_Click(object sender, EventArgs e) 
{
    //C1 is a cell I use to evaluate the Format of Cells A1 thru to A7
    using (var rnEvaluate = xlApp.Range["C1:C1"].WithComCleanup())
    {
        for (int i = 1; i < 8; i++)
        {
            rnEvaluate.Resource.Value2 = "=CELL(\"format\",A" + i.ToString() + ")";
            string cellFormat = GetExcelCellFormat(rnEvaluate.Resource.Value2);
            System.Diagnostics.Debug.Write(cellFormat);
        }
    } 
}

private string GetExcelCellFormat(string cellFormat = "G") 
{
    switch (cellFormat.Substring(0, 1))
    {
        case "F" :
            return "Number";
            break;
        case "C":
            return "Currency";
            break;
        case "D":
            return "Date";
            break;
        default :
            return "General";
            break;
    } 
}

ps The .WithComCleanup（ ）是因为我正在使用 VSTO Contrib

ps The .WithComCleanup() is because I am using VSTO Contrib

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