C # key points of reading and writing excel tables through OleDb,

C # key points of reading and writing excel tables through OleDb,

1. connection string of OleDbConnection:

0 ffice 97-2003:

StrConnection = string. format ("Provider = Microsoft. jet. OLEDB.4.0; Data Source = {0}; Extended Properties = \ "Excel 8.0; HDR = {1}; IMEX = {2: D} \"; ", ExcelFilePath, HDR, IMEX );
// HDR = No indicates that ADO will not use the first line of your Excel file as the field name (in this case, the default field name is F1, F2 ..)
// IMEX = 0 write mode, 1 read mode, 2 connection read/write mode (low efficiency), "IMEX = 1;" always read the "mixed" data column as text

Office2007:

StrConnection = string. format ("Provider = Microsoft. ACE. OLEDB.12.0; Data Source = {0}; Extended Properties = \ "Excel 12.0 Xml; HDR = {1}; IMEX = {2: D} \"; ", ExcelFilePath, HDR, IMEX );

2. Create a connection and enable it:

Try
{
Connection = new OleDbConnection (strConnection );

/* Note: After a connection is established in C ++, the connection can be directly used. In C #, datatable and other operations can be connected, but the connection is closed at this time. //*/

Connection. Open ();

/* In particular, if the connection is closed without this sentence, an error will be prompted when the SQL statement is executed: the connection is closed and unavailable. I used a day to study it.

This seems to have no relevant information and instructions on MSDN and the Internet. I have been wondering that there are no errors in establishing a connection, datatable. no problem with fill (). Why is the SQL statement execution Connection closed and unavailable?

In most cases, a large amount of data is written back and exported to an EXCEL table, which is a SQL database. You can use SqlBulkCopy, but my database is SqlCe and does not support SqlBulkCopy.

Therefore, you can only execute the insert statement on your own thread.

It seems that in C ++, after a connection is established, no manual Open ,,,,,,,,,,,,,

//*/

}

Catch (OleDbException ex)
{
}

3. Execute the SQL statement:

Try
{
OleDbCommand comm = new OleDbCommand (sqlstr, Connection );

Result = comm. ExecuteNonQuery (); // return the number of affected data rows.

}
Catch (OleDbException e)
{
}

4. SQL statement writing in an excel table

String sqlstr = "select F1, F2 from [Sheet1 $]"; // note that the table names [] and $ cannot be missing.

5. Some data is lost or incorrect during reading.

Excel scans the first eight rows of data by default to automatically identify the field data type. Therefore, if the field type does not match or the field exceeds the length, the data will be lost.

1. When IMEX = 1 is connected, it is set to read mode to always process numbers and characters.

2. Modify the registry and increase the number of rows to 0-16. 0 indicates the entire table.

TypeGuessRows is set to 0. Note that if the table is large and 10000 rows are changed to 0, you need to consider performance and efficiency issues.
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Excel]
Windows 7 registry project
HKEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ Microsoft \ Jet \ 4.0 \ Engines \ Excel

3. Modify the Registry script

@ Echo off reg add HKLM \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Excel/v TypeGuessRows/t REG_DWORD/d 0/f
@ Echo off reg add "HKLM \ SOFTWARE \ Microsoft \ Office \ 12.0 \ Access Connectivity Engine \ Engines \ Excel"/v TypeGuessRows/t REG_DWORD/d 0/f
@ Echo off reg add "HKLM \ SOFTWARE \ Microsoft \ Office \ 14.0 \ Access Connectivity Engine \ Engines \ Excel"/v TypeGuessRows/t REG_DWORD/d 0/f