C # (com component) read and write operations in Excel

In projects, we often involve read and write operations on Excel, and there are also many Excel operations on the market. The following methods are commonly used:

　1. COM component operation Excel read/write

　　2. Perform Excel read/write operations in Ado.net Mode

3. Open-source third-party component NPOI

4. Open XML reading and writing Excel

The three methods have their own advantages and disadvantages ~, Today, we are going to talk about the Excel reading and writing operations on com components.

　　

During this time, you are ready to use these three methods to experience the pleasure of reading and writing Excel. You must use the Com component to operate Excel, the first thing we need to do is to introduce the corresponding Office dll, which can also be found in the installation directory of the Office by default.

Add it because all Com component operations need to add the dll for operation. Note that if the following exception occurs during the development process

　　

In this case, you must open the DLL attribute you just referenced andEmbed Interop TypesChangeFalse,In this way, the compilation will not fail when the ExcelApplication class is created.

See http://blogs.msdn.com/ B /mshneer/archive/2009/12/07/interop-type-xxx-cannot-be-embedded-use-the-applicable-interface-instead.aspx for specific reasons

 

Go to the topic. Simply put, we only need to know four objects for the Excel Document Reading and Writing operations,Application,Workbook,Worksheet,Range

1. Application Object: an Excel instance, not just an Excel file, but an overall Excel program.

2. WorkBook is an Excel file in the substantive sense. You can save it.

3. Worksheet is a traditional type of workbook.

4. Range is the most used. You can think of it as a region block, for example, the common expression "A2: B5.

　　For more detailed concepts, refer to Microsoft's official documentation http://msdn.microsoft.com/zh-cn/library/wss56bz7 (v = vs.80). aspx

We can encapsulate a common ExcelUtil class to help us operate Excel through Com components.

First, we can read what we need from an Excel sheet. We can shorten the reference name of the control first, for example:

using Excel = Microsoft.Office.Interop.Excel;

Declare common private variables

private Excel.Application excelApplication = null;private Excel.Workbooks excelWorkbooks;private Excel.Workbook excelWorkbook;private Excel.Worksheet excelWorksheet;private Excel.Range excelRange;private int activeSheetIndex;private string saveFilePath = string.Empty;private string openFilePath = string.Empty;

Common public attributes

/// <Summary> /// obtain or set the currently valid Sheet Index. /// </summary> public int ActiveSheetIndex {get {return activeSheetIndex ;} set {activeSheetIndex = value ;}/// <summary> /// get or set /// </summary> public Excel. workbook {get {return excelWorkbook;} set {excelWorkbook = value ;}} /// <summary> /// obtain and set the number of Worksheet contained in the current Excel file. /// </summary> public int WorksheetsCount {get {if (excelWorkbook = null) return 0; if (excelWorkbook. worksheets = null) return 0; return excelWorkbook. worksheets. count ;}}

Input the Excel file path to open an Excel file

Public bool OpenExcelApplication (string path) {if (excelApplication! = Null) CloseExcelApplication (); if (string. IsNullOrEmpty (path) throw new Exception ("select a file! "); If (! File. Exists (path) throw new Exception (path + "the File does not exist! "); Else {try {// click the referenced third-party component, and set Embed Interop Types to False in the attribute, ActiveSheet. usedRange. rows. count excelApplication = new Excel. applicationClass (); excelWorkbooks = excelApplication. workbooks; excelWorkbook = excelWorkbooks. open (path, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Missing. value, Mis Sing. value, Missing. value, Missing. value) as Excel. workbook; excelWorksheet = excelWorkbook. worksheets [activeSheetIndex] as Excel. worksheet; excelApplication. visible = false; return true;} catch (Exception ex) {CloseExcelApplication (); throw new Exception (string. the Excel program is not installed in the Format ("(1) program. (2) or. NetFramework \ n not supported by Excel installation: {0} ", ex. Message ));}}}

After each Excel operation, you must release resources for the objects used in Excel as follows:

/// <Summary> /// close the Excel program /// </summary> public void CloseExcelApplication () {try {Save (); excelWorksheet = null; excelWorkbook = null; excelWorkbooks = null; excelRange = null; if (excelApplication! = Null) {excelApplication. workbooks. close (); excelApplication. quit (); excelApplication = null ;}} catch (Exception ex) {throw new Exception (ex. message);} finally {GC. collect (); GC. waitForPendingFinalizers ();}}

When reading a Sheet, we usually first need to get the corresponding current Worksheet object, and then get the corresponding Range object from this Worksheet object, such as from A1: C7.

The main code is as follows: Get the valid ActiveIndex, corresponding worksheet object

Excel. worksheet activeWorksheet = excelWorkbook. worksheets [activeIndex] as Excel. worksheet; if (activeWorksheet = null) activeWorksheet = new Excel. worksheet (); // It must be activated each time the latest activity sheet is obtained.ActiveWorksheet. Activate ();

// Obtain the Range object of the data you want to obtain. Excel. Range excelRange = activeWorksheet. get_Range ("A1", "C3 ");

Some people may want to know which columns and columns have data in the region boundary. In fact, here it comes with two methods to obtain the Range of the current data blocks (from A1 by default start)

/* Assume that the upper left corner of the table is in cell a1, and note that there are no empty rows or columns in the table, then: sheets ['sheet1']. range ['a1']. currentRegion. rows. count sheets ['sheet1']. range ['a1']. currentRegion. columns. count returns the number of rows in the table, and Columns returns the number of Columns in the table */public ExcelRange GetCurrentRegion (string startRange, Excel. worksheet activeWorksheet) {return new ExcelRange () {Row = activeWorksheet. range [startRange. toUpper ()]. currentRegion. rows. count, Column = activeWorksheet. range [startRange. toUpper ()]. currentRegion. columns. count };} public class ExcelRange {# region constructor public ExcelRange () {this. row = 0; this. column = 0 ;}# endregion # region public attribute public int Row {get; set ;}public int Column {get; set ;}# endregion}

The startRange object here takes the Cell coordinates in the upper left corner as the starting position by default, and obtains the coordinates of the last Cell in the lower right corner, for example, C3

Another method

/* You can use the SpecialCells function in VBA to return a Range object, which represents all cells that match the specified type and value. The available constants are as follows: comment cells in any format xlCellTypeAllValidation cells with valid conditions xlCellTypeBlanks empty cells xlCellTypeComments contains comment cells xlCellTypeConstants contains constant cells comment contains formula cells xlCellTypeLastCell used last cell of the region //// /×××× // This constant is most useful in determining the range! XlCellTypeSameFormatConditions cells with the same format xlCellTypeSameValidation cells with the same Valid Conditions xlCellTypeVisible all visible cells. For example, we can use this function to determine the last cell containing data: ExcelApp. workBooks. activeSheet. cells. specialCells (xlCellTypeLastCell, EmptyParam ). activate; lastRow: = ExcelApp. activeCell. row; lastCol: = ExcelApp. activeCell. column; */public ExcelRange GetCurrentRegion (Excel. worksheet activeSheet ){ActiveSheet. Cells. SpecialCells (Excel. XlCellType. xlCellTypeLastCell, Missing. Value). Activate (); // The xlCellTypeLastCell here is the key to get the last coordinate with the cursor.Excel. application excelApp = (activeSheet. parent as Excel. workbook ). parent as Excel. application ). application; return new ExcelRange () {Row = excelApp. activeCell. row, Column = excelApp. activeCell. row };}

We do not recommend that you use UsedRange. row and UsedRange. the Column attribute is because they contain the range of the elements you have used, that is to say, if you have written things in D3 and then erased the blank space, the UsedRange attribute calculates the original range, which is often not what we want

Next, the most important step after we get the Range element is to operate on it to get the data. This operation is to get the value of a cell.

// Row and column are the rows and columns of the Value cell respectively. The default value is from 1.

string cellValue = (excelRange.Cells[row, col] as Excel.Range) == null ? string.Empty : (excelRange.Cells[row, col] as Excel.Range).Value; 

If we want to obtain all the elements in the current Range, we need to loop the corresponding rows and, as shown below:

string cellValue;for (int row = 1; row <= excelRange.Rows.Count; row++){    for (int col = 1; col <= excelRange.Columns.Count; col++)    {        cellValue = (excelRange.Cells[row, col] as Excel.Range) == null ? string.Empty : (excelRange.Cells[row, col] as Excel.Range).Value;    }}

Here, efficiency is a major drawback. According to many methods on the Internet using COM components, the efficiency of using COM components for read/write operations is very low,In fact, it is not low at all. This is mainly because the Excel Range object is operated again and again, and the resource consumption is very high when getting its Value object.Later, I found a solution on the Internet to clear the entire case in one sentence ~ I found the answer http://www.cnblogs.com/maweifeng/archive/2005/06/28/182483.html in a very early blog by Jack Ma.

In fact, ourRange objects can have good interoperability with arrays, in fact, each time we only need to assign the element of the Range area to a two-dimensional array and then read or write the two-dimensional array cyclically.The speed improvement is a general feeling ~

I monitored it with Stopwatch. It took 94880ms to write data of 10 columns in 5000 rows to read the element in the Range mode, which is equivalent to 1 minute and 30 seconds, it takes only four seconds to switch to a two-dimensional array.

Therefore, we recommend that you use COM to read and write Excel files using arrays.

object[,] dataValueRange = new object[rowCount, columnCount];dataValueRange = excelRange.Value;for (int row = 1; row <= rowCount; row++){    for (int col = 1; col <= columnCount; col++)    {        cellValue = dataValueRange[row, col] == null ? string.Empty : dataValueRange[row, col].ToString();    }}

The above steps are the same for writing data to Excel.

But you can write the elements to be written to the two-dimensional array first, and then assign the entire Value to the Value of the corresponding Range.

This avoids efficiency issues.

The COM component Excel operation method is summarized here first ~ Continue to supplement next time ~ Hope to be useful to friends who need it ~

Note: The technology sharing copyright in this article is owned by myself. If it is found to be used for illegal commercial purposes without permission, legal investigations will be conducted.