Import ms SQL database to excel in Asp.net

I am using a program to import an Excel file into the SQL database. It is the button1 button that starts to import. I export the Excel file to the third Colum:

Private void button#click (Object sender, system. eventargs E)
{

String mystring = "provider = Microsoft. Jet. oledb.4.0; Data Source = 'd:/exporttoexcel/EXCEL/test.xls '; extended properties = Excel 8.0 ";
Oledbconnection cnnxls = new oledbconnection (mystring );
Oledbdataadapter myda = new oledbdataadapter ("select * from [sheet1 $]", cnnxls );
Dataset myds = new dataset ();
Myda. Fill (myds );

If (myds. Tables [0]. Rows. Count> 0)
{
String strsql = "";
String cnnstring = "provider = sqloledb; database = testnews; server = (local); uid = sa; Pwd = ";
Oledbconnection conn = new oledbconnection (cnnstring );
Conn. open ();
Oledbcommand mycmd = NULL;

For (INT I = 0; I <myds. Tables [0]. Rows. Count; I ++)
{
Strsql = "insert into news (title, body) values ('";
Strsql + = myds. Tables [0]. Rows [I]. itemarray [1]. tostring () + "','";
Strsql + = myds. Tables [0]. Rows [I]. itemarray [2]. tostring () + "')";

Try
{
Mycmd = new oledbcommand (strsql, Conn );
Mycmd. executenonquery ();
Label8.text = "<script language = JavaScript> alert ('data imported successfully. '); </SCRIPT> ";
}
Catch
{
Label8.text = "<script language = JavaScript> alert ('data import failed. '); </SCRIPT> ";
}
}
Conn. Close ();
}

}
}

Let's take a look at this batch import from Excel to the database.
Private dataset getcollection ()
{
Dataset DS = new dataset ();
String strcon, strcmm;
// Strcon = "provider = Microsoft. Jet. oledb.4.0; Data Source = D: // IRMS // TMP // irsbd.xls; extended properties = Excel 8.0 ;";
Strcon = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + server. mappath ("tmp // irsbd.xls") + "; extended properties = Excel 8.0 ;";
Strcmm = "select distinct * from [sheet1 $]";
Oledbconnection olecnn = new oledbconnection (strcon );
Oledbcommand olecmm = new oledbcommand (strcmm, olecnn );
Oledbdataadapter oleda = new oledbdataadapter (olecmm );
Oleda. Fill (DS, "irsbd ");

Return Ds;

}

Private void putdata (Dataset DS)
{
String strcon = application ["strcon"]. tostring ();
String strsql = "select top 1 * From irsbd ";
Dataset myds = new dataset ();
Sqldataadapter da = new sqldataadapter (strsql, strcon );
Da. Fill (myds, "irsbd ");
For (INT I = 0; I <Ds. Tables [0]. Rows. Count; I ++)
If (Ds. Tables [0]. Rows [I] ["sbdno"]. tostring (). Trim ()! = "")
{
Datarow DR = myds. Tables [0]. newrow ();
Datarow DR1 = Ds. Tables [0]. Rows [I];
Dr ["sbdno"] = DR1 ["sbdno"];
Dr ["sbdnm"] = DR1 ["sbdnm"];
Dr ["sbdpd"] = DR1 ["sbdno"];
Dr ["sbdit"] = DR1 ["sbdit"];
Dr ["sbddt"] = DR1 ["sbddt"];
Dr ["sbdco"] = tbco. Text. Trim ();

Dr ["sbdel"] = datetime. today;
Dr ["sbdcs"] = 0;
Dr ["sbdas"] = 0;
Dr ["sbdps"] = 0;
Dr ["sbdcs1"] = 0;
Dr ["sbdcs2"] = 0;
Dr ["sbdcs3"] = 0;
Dr ["sbdcs4"] = 0;
Dr ["sbdas1"] = 0;
Dr ["sbdas2"] = 0;
Dr ["sbdas3"] = 0;
Dr ["sbdas4"] = 0;

Myds. Tables [0]. Rows. Add (DR );
}

Sqlcommandbuilder sqlcb = new sqlcommandbuilder (DA );

Da. Update (myds, "irsbd ");
Myds. acceptchanges ();
}

 

Asp.net page output to excel
[Introduction]
Recently, when developing an ISO file management system, I have encountered the need to output aspx directly to excel. Now I will share my experience with you. In fact, it is easy to use ASP. NET to output documents of the Word, Excel, txt, htm, and other types of specified content. It can be completed in three steps.

1. Define document type and character encoding

Response. Clear ();

Response. Buffer = true;

Response. charset = "UTF-8 ";

// The following line is very important. The attachment parameter indicates downloading as an attachment. You can change it to online.

// Filename=fileflow.xls specifies the name of the output file. Note that the extension is consistent with the specified file type. It can be. Doc. xls. txt. htm.

Response. appendheader ("content-disposition", "attachment?filename=fileflow.xls ");

Response. contentencoding = system. Text. encoding. getencoding ("UTF-8 ");

// Response. contenttype the specified file type can be application/MS-Excel application/MS-Word application/MS-TXT application/MS-HTML or other browsers can directly support documents

Response. contenttype = "application/MS-excel ";

This. enableviewstate = false;

2. Define an input stream

System. Io. stringwriter ostringwriter = new system. Io. stringwriter ();

System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );

3. Bind the target data to the input stream output

This. rendercontrol (ohtmltextwriter );

// This indicates that the current page is output. You can also bind the DataGrid or other controls that support the obj. rendercontrol () attribute.

Response. Write (ostringwriter. tostring ());

Response. End ();