When using ADO. Net to export an Excel file, setting IMEX = 1 still cannot export all data.

Today, I encountered a problem using ADO. when exporting an Excel file, IMEX = 1 has been set in the connection string, and some data cannot be exported. After research, you need to configure typeguessrows in the registry. By default, driverProgramThe first eight rows of data are scanned to sample the data type. If it is set to 0, all the rows in the column will be scanned. If the first eight rows of sampled data are numerical, setting IMEX = 1 does not convert the default data type of the remaining rows to text. It retains the value type, that is, the entire column is treated as a number. Therefore, to obtain complete data, you need to modify the Registry Settings typeguessrows to 0. Location of typeguessrows in the registry:

[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ Excel]

Windows 7 and other 64 systems:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ wow6432node \ Microsoft \ jet \ 4.0 \ engines \ Excel]

Possible IMEX settings are:

0 export Mode
1 import Mode
2 linked mode (full update capabilities)

Setting typeguessrows to 0 will lead to performance degradation. The IMEX = 1 data content can also be abnormal, for example, long numbers may be displayed in scientific notation format. It is better to process all cells before exporting, and add 'before the cell content '.

 

From: http://dotnet.aspx.cc/file/ADO.NET-Excel-IMEX=1.aspx