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:

 Excel = Microsoft.Office.Interop.Excel;

Declare common private variables

 Excel.Application excelApplication =    saveFilePath =   openFilePath = .Empty;

Common public attributes

    {  { activeSheetIndex =   {  { excelWorkbook =    (excelWorkbook == )   (excelWorkbook.Worksheets == )  

Input the Excel file path to open an Excel file

  OpenExcelApplication( (excelApplication !=  (.IsNullOrEmpty(path))   Exception( (!  Exception(path +                     excelApplication = == 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, Missing.Value, Missing.Value, Missing.Value) = excelWorkbook.Worksheets[activeSheetIndex] =    Exception(.Format(

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

   = = = =  (excelApplication != =  

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]  (activeWorksheet == = activeWorksheet.Activate();

Excel.Range excelRange = activeWorksheet.get_Range(, );

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)

ExcelRange GetCurrentRegion (= constructor. Row =. Column = Public attribute Row {; Column {;

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

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 Excel. Workbook). Parent =

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]  Excel.Range) ==  ? .Empty : (excelRange.Cells[row, col]  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:

 ( row = ; row <= excelRange.Rows.Count; row++ ( col = ; col <= excelRange.Columns.Count; col++= (excelRange.Cells[row, col]  Excel.Range) ==  ? .Empty : (excelRange.Cells[row, col] 

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.

[,] dataValueRange =  = ( row = ; row <= rowCount; row++ ( col = ; col <= columnCount; col++= dataValueRange[row, col] ==  ? 

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.