Detailed description of Data Reading methods in Excel interoperability

This section describes how to read data from Excel using C # And the main interoperability assembly, and points out some details about how to use these methods. The main interoperability Assembly uses Microsoft. Office. Interop. Excel, version 12.0.0.0.

1. Range. Value2 attributes

MSDN: Returns or sets the cell value. Read/write Variant.

Use this attribute to read data within a certain range.

1.1 Return Value

The returned value returns a copy of the Range. Value2 attribute, instead of an internal object reference.
Object [,] value = (object [,]) range. Value2;
Object v11 = value [1, 1]; // v11 = "aaa"
V11 = 10; // v11 = 10
Object v11new = (object [,]) range. Value2). GetValue (1, 1); // v11new = "aaa"
1.2 range type

For a single cell, the returned type is cell. For example, the number 1.4 in Excel returns the double type.

For multiple cells, the returned object is of the Two-dimensional array (object [,]) type. Note that the lower threshold (subscript) of these arrays starts from 1.

Excel. Range range1 = sheet. get_Range ("A1", "A1 ");
Object value1 = range1.Value2; // value1 = "aaa"
Object [,] value11 = (object [,]) range1.Value2; // triggers InvalidCastException

Excel. Range range2 = sheet. get_Range ("A1", "E3 ");
Object value2 = range2.Value2; // value2 is object [1... 3, 1... 5]
Object [,] value22 = (object [,]) range2.Value2; // value22 and value2 are the same
1.3 cell type

Excel supports two Data Types: Numbers and Strings (see Data Types Used By Excel ).

1.3.1 blank cells
Empty cells return null.

1.3.2 numeric Cell
The number cell returns the double type.

Bool isDouble = value [1, 3] is double; // isDouble = true

It indicates that the number cell of the date and time still returns double instead of DateTime. For example, double value 2000.0 indicates January 1, June 22, 1905. To convert double to DateTime, you must call the DateTime. FromOADate static method.

DateTime dt = DateTime. FromOADate (double) value [4, 1]); // (double) value [4, 1] = 1.0

String s = dt. ToString (); // s = "1899-12-31 0:00:00"

1.3.3 text Cells
The text cell returns a string.

2. Range. Text attributes

MSDN: Returns or sets the text for the specified object. Read-only String.

2.1 return type

For a single cell, the returned text constant is always displayed in Excel. For example, if the value of 2000.0 in a numeric cell is June 22, 1905, Range. Text returns "June 22, 1905 ". In addition, empty strings are returned for blank cells.

For multiple cells, DBNull. Value is always returned.

3. Range. Value Attribute (C # Not Supported)

4. Summary

You can use the Range. Value2 and Range. Text attributes to obtain data in Excel, but we should avoid the Range. Text attribute as much as possible. Because Range. Text cannot read the values of multiple cells, and it does not support "strong type" like Range. Value2 ".