C # comparison of OLEDB and COM modes for Excel operations

When performing read/write operations on Excel, there are two solutions provided by Microsoft: OLEDB and COM. OLEDB reads Excel at a very high speed compared to calling COM components, because OLEDB uses Excel as a database and Excel as a data source, obtain data directly using SQL statements. Another advantage is that you do not need to install Office Excel. However, this method can only read or write data. However, it is not supported for some complicated logic of Excel, such as cell merging and Cell Coloring. [Csharp] string connStr = ""; if (fileType = ". xls ") connStr =" Provider = Microsoft. jet. OLEDB.4.0; "+" Data Source = "+ fileName +"; "+"; Extended Properties = \ "Excel 8.0; HDR = YES; IMEX = 1 \""; else connStr = "Provider = Microsoft. ACE. OLEDB.12.0; "+" Data Source = "+ fileName +"; "+"; Extended Properties = \ "Excel 12.0; HDR = YES; IMEX = 1 \""; string strCom = "SELECT * FROM [Part $ A7: AE] where [Part No] =" + PartNO; OleDbConnection myConn = new OleDbConnection (strCon); OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom) System. data. dataTable partTable = new System. data. dataTable (); myCommand. fill (partTable); in this way, the content in Excel is placed in the DataTable. A common method is to read data to the able for caching and filter or process the data in the DataTable. Of course, if you want to write data to Excel, you need to use the insert statement to directly input data to Excel. Note that when the cell in Excel is empty, the value returned to the able is DBNull and Convert is used. toString () is converted to "", and if Convert. toInt32 () Conversion throws an exception instead of 0.2. to call COM components, you must first reference Microsoft. office. interop. excel. The data in Excel can be read flexibly and used in a wide range of ways. Basically, you can use VSTO to call the COM component when you open the Office Excel software by clicking the mouse, in addition, you can call the macro method in Excel. However, if the Web site is deployed on IIS, you also need to have Excel installed on the server, and sometimes you need to configure IIS permissions. The most important thing is that the data is very slow because it is read in cells. My personal suggestion is that, in the big data loop, for example, for (int I = 0; I <10000; I ++ ){......} Do not use COM objects in the logic here, because after all, COM objects are unmanaged code and there will be a conversion between managed objects and unmanaged objects during calls, this is like a door to go through, which is time-consuming and laborious. When writing data to an Excel worksheet, if there are 10000 rows and one row calls the Range row object of COM to assign values, it is quite slow. In this case, you can use an array to accumulate values into the array, and finally assign the array to all the areas to be assigned a value. In this case, you must note that the Range area must correspond to the array dimension. Otherwise, there may be some strange symbols. In actual development, if the data volume is large and multiple Excel files need to be compared, you can use them in combination to cache the sheet of an Excel file to the able, then compare it with another Excel file. If you need to write a large amount of data into the Excel file, you can use the two-dimensional array assignment method and then write it back to the Excel file.