C # Several key points for reading and writing Excel forms through OLE DB

1,oledbconnection Connection string:

0ffice 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 (this is the default field name: F1,F2). )
imex= 0 Write mode, 1 read mode, 2 connection to read/write mode (Low efficiency), "imex=1;" always reads 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, establish the connection and open:

Try
{
Connection = new OleDbConnection (strconnection);

/ * Special Note: After a connection is established in C + +, the connection can be used directly. In C #, connections can use operations such as a DataTable, but the connection is now off. //*/

Connection. Open ();

/* Specifically, if the connection is closed without this sentence, the Execute SQL statement will prompt the error: Connection shutdown is not available. It took me a day to study it.

It seems that MSDN and the Internet do not have the relevant information and instructions, I have been strange, the establishment of the connection is not any error, Datatable.fill () is not a problem, why the execution of SQL statement connection is closed is not available?

Most of the time a large amount of data write-back export to Excel table, will be SQL database, can use SqlBulkCopy, but my database is SqlCe, does not support SqlBulkCopy

Therefore, the INSERT statement can only be executed on its own thread.

Seemingly in C + +, after establishing a connection, never manually go to open,,,,,,,,,,,,,

//*/

}

catch (OleDbException ex)
{
}

3, execute the SQL statement:

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

result = Comm. ExecuteNonQuery (); //Returns the number of data rows affected.

}
catch (OleDbException E)
{
}

4,excel Table SQL statement Writing

String sqlstr= "Select F1,f2 from [sheet1$]";//Note that the table name [] and $ are not limited.

5, when reading, some data is lost, or error.

Excel scans the first 8 rows of data by default to automatically identify the field data type, so if the field type does not match or the field is super-long, it will be lost.

When the 1,imex=1 connection is set to read mode, the numbers and characters are always processed as characters.

2, modify the registry, change the number of rows to a larger 0-16;0 represents the entire table.

TypeGuessRows is set to 0, note that if the table is larger 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]
Registry entries for Windows 7
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

C # Several key points for reading and writing Excel forms through OLE DB