问题描述
是否有可能弄清楚我知道有.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 datatypePlease 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( )
是因为我正在使用
ps The .WithComCleanup()
is because I am using VSTO Contrib
这篇关于如何知道Excel单元格的格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!