Examples of ASP. net c # datatable and Excel interoperability

A few days ago, a friend company needs to use Excel to import and export the content in the database and excel through the Web! Asked me, I have done it before. Many similar solutions on the Internet use controls like DataGrid or gridview to directly import to excel, which is convenient, but not very practical, sometimes the exported Excel file is not in the format we need! I remember hearing from Dr. Yan Zhidong about the automated operations under ASP. NET. But none of them were found at the moment.Code. Here I will post my example here to help you and your friends who need it later!

The page running effect is as follows:

The function of "generating datatable" is to create a datatablle in the internal directory and then display it using repeater.

  Protected   Void Btnloaddt_click ( Object Sender, eventargs E)
{
Binddata (loaddatatable ());
}
// Create a able
Private Datatable loaddatatable ()
{
Datatable dt =   New Datatable ();
Datacolumn DC =   New Datacolumn ( " Username " );
DT. Columns. Add (DC );
DC =   New Datacolumn ( " Usersex " );
DT. Columns. Add (DC );

Datarow Dr = DT. newrow ();
Dr [ 0 ] =   " Fengyan " ;
Dr [ 1 ] =   " Male " ;
DT. Rows. Add (DR );

Dr = DT. newrow ();
Dr [ 0 ] =   " Efly " ;
Dr [ 1 ] =   " Male " ;
DT. Rows. Add (DR );

Dr = DT. newrow ();
Dr [ 0 ] =   " Chu Xuan " ;
Dr [ 1 ] =   " Male " ;
DT. Rows. Add (DR );

Return DT;
}

Click datatablle to export the datatable content to the specified Excel file. The related code is as follows:

Protected   Void Btnexportexcel_click ( Object Sender, eventargs E)
{
// Get the datatable to import the Excel file.
Datatable dt = Loaddatatable ();
// Add the column name! (This step is used to import the Excel file into the memory table to automatically create a column name .)
Datarow Dr = DT. newrow ();
Dr [ 0 ] =   " Username " ;
Dr [ 1 ] =   " Usersex " ;
DT. Rows. insertat (DR, 0 );

// Instantiate an Excel assistant tool class
Excelhelper ex =   New Excelhelper ();
// Import all! (Starting from the first column of the First row)
Ex. datatabletoexcel (DT, 1 , 1 );
// Export the path saved in Excel!
Ex. outputfilepath = Txtexcelpath. text;
Ex. outputexcelfile ();
}

After running, open the generated Excel file as follows:

The content has been exported to excel. Now add a line of content to it
 
If you want to read the Excel file to the able, you can use ADO. net
The code for importing a datatable event in Excel is as follows:

  Protected   Void Btnexceltodatatable_click ( Object Sender, eventargs E)
{
String Strconn =   " Provider = Microsoft. Jet. oledb.4.0; Data Source = "   + Txtfromexcel. Text +   " ; Extended properties = Excel 8.0 " ;
// Link Excel
Oledbconnection cnnxls =   New Oledbconnection (strconn );
// Read Table sheet1 in Excel
Oledbdataadapter ODA =   New Oledbdataadapter ( " Select * from [sheet1 $] " , Cnnxls );
Dataset DS =   New Dataset ();
// Load the table content in Excel to the memory table!
ODA. Fill (DS );
Datatable dt = DS. Tables [ 0 ];
Binddata (DT );
}

The following page is displayed after running:

We can see that the data is read back from Excel and automatically bound to repeater. The added data row is displayed correctly!
You can bind a column name correctly because a column name is added to the top of the Excel worksheet during export. Therefore, you can bind a column name to it!
In reality, it is more about the interoperability between databases and excel! This article describes datatable, which is easy to convert from datatable to database!
After the data is imported from Excel to datatable, we can add or update the data back to the database by writing a small amount of code! This is also possible!

Download the sample code/files/eflylab/excelhelper.rar