C # oledb reads Excel files to avoid columns with scientific notation

 

In Excel, when you enter more than 11 digits, it is automatically converted into a scientific counting method, for example, 123456789012345. After the input, it becomes 1.23457e14, in excle, you can change the format of this column to text. Or Add "'", that is, '123. Enter a pair of single quotes (') and a numerical value. Therefore, Excel is regarded as a text instead of a scientific notation.

 

 

 

Use oledb of C # To import EXCEL to the database and generate scientific notation to solve the problem.

 

It is found that the records read by oledb for long numbers in the Excel column are displayed in scientific notation. 450107630382345 to 4.5010763038 + 14.

 

The main problem is the connection string.

"Provider = Microsoft. Ace. oledb.12.0; Data Source =" + filename + "; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '";

 

What does excel IMEX and HDR in oledb mean?

Extended properties = "" Excel 8.0; HDR = yes; IMEX = 1 ""

A: HDR (header row) settings

If the value is yes, the first row of the worksheet in the Excel file is the column name.

If the value is no, the first row of the worksheet in the Excel file is the item, and there is no column name.

B: IMEX (Import Export mode) settings

There are three modes for IMEX, And the read/write operations caused by these two modes are also different:

0 is export Mode

1 is import Mode

2 is linked mode (full capabilities)

Here I want to declare the IMEX parameter, because the incorrect mode represents an incorrect read/write operation:

When IMEX = 0, the "Export mode" is enabled. The Excel files opened in this mode can only be used for "writing.

When IMEX = 1, it is in import mode. The Excel files opened in this mode can only be used for reading.

When IMEX = 2, the connection mode is used. The Excel files enabled in this mode can support both read and write functions.

The so-called "4.5010763038 + 14" is only used for display, but is actually "450107630382345 ". Therefore, when IMEX = 1 is used for reading, "4.5010763038 + 14" is obtained ".

When IMEX is set to 0, the value "450107630382345" is returned ".

 

We need to change the connection string

"Provider = Microsoft. Ace. oledb.12.0; Data Source =" + filename + "; extended properties = 'excel 8.0; HDR = yes; IMEX = 0 '";

The problem is solved.