Value2的底层类型有哪些？C#开发Excel插件遇类型转换问题

Great question! I’ve wrestled with this exact behavior while building Excel add-ins with C# (VS 2017 + Excel 2016), so let’s break down all the underlying types you might encounter with Range.Value2—and why your string cast failed for "1234".

First, the key point: Value2 returns object as the top-level type, but the actual underlying type depends entirely on what’s in the cell(s). Here are all the common (and not-so-common) types you’ll run into:

• double: This is the most frequent type for numeric values. Excel stores all numbers (integers like 1234, decimals, dates, and times) as doubles. Dates are represented as the number of days since January 1, 1900, and times as fractional parts of a day. This is why your cast to string failed for "1234"—the cell’s value is stored as a double, not a string, so direct casting throws an error.
• string: Returned for plain text (like "abcdef"), numeric values prefixed with an apostrophe (e.g., '1234, which forces Excel to treat it as text), or formula results that output text.
• bool: When a cell contains a boolean value (TRUE/FALSE, either manually entered or returned by a formula like =A1>B1), Value2 will give you a bool type directly.
• object[,]: If you’re accessing a multi-cell range (e.g., Range("A1:C3")), Value2 returns a 2D array. Each element in the array corresponds to a cell, and will be one of the other types listed here (double, string, bool, DBNull, or error code).
• DBNull: Empty cells return DBNull.Value, not null. This is a common gotcha—checking if (rangeValue == null) won’t work; you need to use DBNull.Value.Equals(rangeValue) instead.
• Integer error codes: When a cell shows an Excel error value (like #DIV/0!, #N/A, #VALUE!), Value2 returns an integer representing that error. For example, #DIV/0! maps to -2146826281, and #N/A maps to -2146826246. Casting these directly to string or numeric types will fail, so you’ll need to handle them separately.

Practical Tips for Safe Conversion #

To avoid casting errors, always check the underlying type before converting. Here’s a quick example of robust handling:

var cellValue = Application.get_Range("A1").Value2;
string targetValue;

if (DBNull.Value.Equals(cellValue))
{
    targetValue = string.Empty;
}
else if (cellValue is double doubleVal)
{
    // Handle numbers or dates
    if (DateTime.TryFromOADate(doubleVal, out DateTime dateResult))
    {
        targetValue = dateResult.ToString("yyyy-MM-dd HH:mm:ss");
    }
    else
    {
        targetValue = doubleVal.ToString();
    }
}
else if (cellValue is bool boolVal)
{
    targetValue = boolVal.ToString();
}
else if (cellValue is string strVal)
{
    targetValue = strVal;
}
else if (cellValue is int errorCode)
{
    // Convert Excel error code to readable text
    targetValue = Application.WorksheetFunction.Text(errorCode, "@");
}
else
{
    // Fallback for any rare edge cases
    targetValue = cellValue?.ToString() ?? string.Empty;
}

内容的提问来源于stack exchange，提问作者Snowflake