Question about the IMEX parameter in the connection string

Problem description: The Excel data file we want to import contains a column named "winning price". The data in this column contains only numbers and only Chinese characters, there is also a mix of Chinese and numbers,

600

1882

1124

RMB 16.5/㎡

17.5 RMB/m3

Labor costs: 104 RMB/m2, and expansion of subcontracting fees: 13 RMB/m2

Labor costs: 104 RMB/m2, and expansion of subcontracting fees: 13 RMB/m2

Execute such data in the query Analyzer
Select winner, [winning price]
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "D: \ bidding file account ()).xls"; user id =; Password =; extended properties = "Excel 5.0" ')... [bidding file $]
Only the values of those numbers can be found, and other values with Chinese characters are null. View the cell properties in Excel and find that the original text format is normal, there is no exception; try to use sqlserver manager to export the table in Excel to the library, we found that the default conversion field type for this field is float, which is obviously not what we want. When Excel is used as a data source, how does the data reading program (such as oledb) determine its column attributes? The following text provides the answer:
The Excel driver reads a certain number of rows in the specified source (eight rows by default) to predict the Data Type of each column. If it is estimated that the column may contain the hybrid data type (especially when the text data is mixed), the driver will decide to use the dominant data type, return NULL values for units that contain other types of data. (If the number of data types is the same, the value type is used .)
The setting of most cell formats in an Excel worksheet does not affect the determination of this data type. You can modify this behavior of the Excel driver by specifying the import mode. To specify the import mode, add IMEX = 1 to the extended attribute value in the connection string of the Excel Connection Manager in the Properties window.

As mentioned in this section, you can smoothly read data by adjusting the connection string to the following two methods:
1. Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; HDR = yes; user id = admin; Password =; IMEX = 1;
Database = D: \ bidding document account ().xls ', [bidding file $])
2. Select [winning price] From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "D: \ bidding file account ()).xls"; user id =; Password =; extended properties = "Excel 5.0; IMEX = 1" ')... [bidding file $]

Sheetname = "sheet1 ";
Oledbconnection excelconn = new oledbconnection ("provider = Microsoft. jet. oledb.4.0; Data Source = "+ filepath +"; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '");
Excelconn. open ();
Oledbcommand cmd = new oledbcommand ("select * from [" + sheetname + "$]", excelconn );

Another guy suggested changing the Excel Macro. Although it was not practical (We could not allow all users to make such changes), it was a "thorough" approach, here is an excerpt:
The problem is not in sqlserver. To forcibly convert the ID card number field to text format in Excel, as shown below:
Add the following macros to an Excel file containing data:
Sub converts the content of the selected cell to the string format ()
Activecell. formular1c1 = "'" + activecell. Text
Dim icol, irow
For irow = 1 to selection. cells. Rows. Count
For icol = 1 to selection. cells. Columns. Count
Selection. cells (irow, icol). formular1c1 = "'" + selection. cells (irow, icol). Text
Next icol
Next irow
End sub

Add macro method:
1: open an Excel file
2: choose tools> macros> Visual Basic Editor"
3: Select "insert"> "module" from the menu and copy the above Code to it.
4. Close the Visual Basic Editor and choose Save.

Macro running method:
1: Go to "tool"-"macro"-"macro"
2: select the macro to be executed on the macro page.
3: click "execute ".

Select the ID card number column, execute the macro, and then import.

The following question is: What do I mean by extended attributes such as IMEX = 1? The following two explanations should be clear enough:
1. IMEX = 1: tells the driver to always read "intermixed" Data columns as text
2.HDR=Yes;To indicate that I have a header row in my sheets