Use ADO. Net to access Excel Data (use ADO. Net to retrieve and modify records in an Excel Workbook)

we know that the most direct and powerful way to operate Excel is to reference the Excel DLL and use the packaged EXCEL object system to search for and update Excel Data, or some other actions. However, if you simply process some set data, there is a way to avoid complicated Excel objects, various strange variables and function parameters, that is, ADO. net. The following is a Microsoft Knowledge Base Article . It describes in detail how to add, delete, modify, and query Excel data through the ole db connection, it supports almost all versions of Excel (I only used Excel 2003 and Excel 2007 ). Post the original thing will not do, there are detailed examples:
http://support.microsoft.com/default.aspx? SCID = KB; en-US; 316934

Note that if you encounter an exception "operation must use an updateable query" during creation, update, or deletion, it may be caused by the following reasons:
1. RunProgramYou do not have the permission to modify the target Excel file. In this case, you can easily search for it online.
2. "IMEX = 1" is set in the connection string, which is rarely mentioned. And read the official Microsoft Description: "to work around this problem for read-only data, enableImport ModeBy using the setting "IMEX = 1" in the extended Properties Section of the connection string. This enforcesImportmixedtypes = textRegistry Setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft knowlebase:

194124 Http://support.microsoft.com/kb/194124/EN-US) PRB: Excel values returned as null using Dao openrecordset "when IMEX is set to 1, Excel is viewed as a read-only data source! Therefore, remember to remove the IMEX = 1 option when creating, updating, or deleting an Excel file! See the original article:
Http://support.microsoft.com/kb/257819