Import SQL Server database using Excel)

 

 

// Filename: absolute path of the Excel file

Private dataset readexcel2 (string filename)
{
If (filename. Trim (). Length = 0)
{
Return NULL;
}

If (! System. Io. file. exists (filename ))
{
Return NULL;
}


String strconn = "provider = Microsoft. Jet. oledb.4.0 ;"
+ "Data Source =" + filename + ";"
+ "Extended properties = Excel 8.0 ;";

Oledbconnection objconn = new oledbconnection (strconn );

// Archivecategorys is the name of the sheet in the Excel file

// Here, we can refer to execel.exe in the project,

// Use the Excel. Application, Excel. Workbook, Excel. worksheet, and Excel. Range pairs to obtain the desired information.

String strsql = "select ID, PID, name, code, memo, locked, deleted from archivecategorys order by ID ";

Oledbcommand objcmd = new oledbcommand (strsql, objconn );
Oledbdataadapter adapter = new oledbdataadapter (objcmd );

Try
{
Objconn. open ();

Adapter. Fill (DS );
Return Ds;
}
Catch (exception exc)
{
MessageBox. Show (EXC. tostring ());
}
Finally
{
Objconn. Dispose ();
}

Return Ds;

}

// Traverse all tables in dataset and all rows in the table:

Foreach (datatable table in DS. Tables)
{
Foreach (datarow row in table. Rows)
{

}
}

// The dataset data is stored in the database or is not written in other ways.

// Refer to an article on csdn and forget the address. It is in English.

// Haha, this is the first article I posted online. please correct me.