Exporting data to excel using Asp.net is simple and effective.

1. Use the stringwriter class to read the data in the able cyclically. This method is good, simple and not complex, and you do not need to add references.

First, you must obtain data from the database:

Datatable Ds;

DS = dbhelpersql. Query (strsql. tostring (), parameters );

Stringwriter SWR = new stringwriter ();
SWR. writeline ("operation time \ t operator ID \ t private IP \ t public IP \ t game name \ t game account \ t Order Number \ t order status \ t release Single IP ");

// Set the exported file name
Datetime dt = datetime. now;
String strfilename = This. tbxfristtime. Text. tostring () + "-" + this.txt gamename. Text. Trim () + "-" + this. radiobuttonlist1.selecteditem. Text. tostring ();
Strfilename = strfilename + ". xls ";
Foreach (datarow DR in DS. Rows)
{
SWR. writeline (Dr [0]. tostring () + "\ t" + Dr [1]. tostring () + "\ t" + Dr [2]. tostring () + "\ t" + Dr [3]. tostring () + "\ t" + Dr [4]. tostring () + "\ t" + Dr [5]. tostring () + "\ t" + Dr [6]. tostring () + "\ t" + Dr [7]. tostring () + "\ t" + Dr [8]. tostring () + "\ t ");
}
SWR. Close ();
Response. addheader ("content-disposition", "attachment; filename =" + httputility. urlencode (strfilename, encoding. utf8). tostring ());
Response. contenttype = "application/MS-excel ";
Response. contentencoding = encoding. default;
Response. Write (SWR );
Response. End ();

 

 

F 2: To use the Excel component, you must first add reference:

// Using Microsoft. Office. InterOP. Excel;
Using Excel = Microsoft. Office. InterOP. Excel;
Using system. IO;

The following code is used:

 

If (this. tbxfristtime. Text! = "")
{
String strorderstate = convert. tostring (this. radiobuttonlist1.selectedvalue );
If (strorderstate! = "")
{

Stringbuilder strstarttime = new stringbuilder ();
Strstarttime. append (this. tbxfristtime. Text. tostring ());
Strstarttime. append ("00:00:00 ");
Stringbuilder strenetime = new stringbuilder ();
Strenetime. append (this. tbxfristtime. Text. tostring ());
Strenetime. append ("23:59:59 ");
Datatable Ds;

Sqlparameter [] parameters = {
New sqlparameter ("@ starttime", sqldbtype. nvarchar, 30 ),
New sqlparameter ("@ endtime", sqldbtype. nvarchar, 30 ),
New sqlparameter ("@ orderstate", sqldbtype. nvarchar, 4 )};
Parameters [0]. value = strstarttime. tostring ();
Parameters [1]. value = strenetime. tostring ();
Parameters [2]. value = strorderstate;
Stringbuilder strsql = new stringbuilder ();
Strsql. append ("select ftime, foperatorid, fipaddr, fpubip, fgamename, faccno, forderno, forderstate from ");
Strsql. append ("dbrc2management... torderoperationlog where ");
Strsql. append ("findex in (select max (findex) from dbrc2management... torderoperationlog group by fpubip) and fpubip <>'' and ");
Strsql. append ("forderstate = @ orderstate and ftime between @ starttime and @ endtime order by ftime DESC ");
DS = dbhelpersql. Query (strsql. tostring (), parameters );

Microsoft. Office. InterOP. Excel. Application myexcel = new Microsoft. Office. InterOP. Excel. Application ();
Myexcel. application. workbooks. Add ("E: \ AA \ order.csv ");
Microsoft. Office. InterOP. Excel. worksheet myworksheet = (Microsoft. Office. InterOP. Excel. worksheet) myexcel. worksheets [1];
Microsoft. Office. InterOP. Excel. Range myrange = myexcel. get_range (myexcel. cells [1, 1], myexcel. cells [3, 3]);
Myexcel. Visible = true;
Myexcel. Caption = "Operation Time ";
Myworksheet. cells [1, 1] = "private IP ";
Myworksheet. cells [2, 1] = "Public IP ";
Excel. applicationclass mylxls = new excel. applicationclass ();
Mylxls. application. workbooks. Add (true );
Myexcel. Caption = "abnormal order table ";
Myworksheet. cells [1, 1] = "5173 abnormal order data ";
Myworksheet. cells [2, 1] = "execution time ";
Myworksheet. cells [2, 2] = "operator ID ";
Myworksheet. cells [2, 3] = "private IP Address ";
Myworksheet. cells [2, 4] = "Public IP ";
Myworksheet. cells [2, 5] = "game name ";
Myworksheet. cells [2, 6] = "game account ";
Myworksheet. cells [2, 7] = "Order Number ";
Myworksheet. cells [2, 8] = "order status ";
Myworksheet. get_range (myworksheet. cells [1, 1], myworksheet. cells [1, 8]). mergecells = true;

Int I = 0;
Foreach (datarow DT in DS. Rows)
{

Myworksheet. cells [3 + I, 1] = dt ["ftime"]. tostring (); // database field .. No LB above
Myworksheet. cells [3 + I, 2] = dt ["foperatorid"]. tostring ();
Myworksheet. cells [3 + I, 3] = dt ["fipaddr"]. tostring ();
Myworksheet. cells [3 + I, 4] = dt ["fpubip"]. tostring ();
Myworksheet. cells [3 + I, 5] = dt ["fgamename"]. tostring ();
Myworksheet. cells [3 + I, 6] = dt ["faccno"]. tostring ();
Myworksheet. cells [3 + I, 7] = dt ["forderno"]. tostring ();
Myworksheet. cells [3 + I, 8] = dt ["forderstate"]. tostring ();
I ++;
}
}
Else MessageBox. Show (this, "Please select the order status! ");
}
Else MessageBox. Show (this, "Enter the time range to query !!! ");
Response. addheader ("content-disposition", "attachment; filename =" + httputility. urlencode ("test.xls", encoding. utf8). tostring ());

Read data from repeater1.
For (INT I = 0; I <repeater1.items. Count; I ++)
{
Myworksheet. cells [3 + I, 1] = (this. repeater1.items [I]. findcontrol ("lbftime") as label). text;
Myworksheet. cells [3 + I, 2] = (this. repeater1.items [I]. findcontrol ("lbfoperatorid") as label). text;
Myworksheet. cells [3 + I, 3] = (this. repeater1.items [I]. findcontrol ("lbfipaddr") as label). text;
Myworksheet. cells [3 + I, 4] = (this. repeater1.items [I]. findcontrol ("lbfpubip") as label). text;
Myworksheet. cells [3 + I, 5] = (this. repeater1.items [I]. findcontrol ("lbfgamename") as label). text;
Myworksheet. cells [3 + I, 6] = (this. repeater1.items [I]. findcontrol ("lbfaccno") as label). text;
Myworksheet. cells [3 + I, 7] = (this. repeater1.items [I]. findcontrol ("lbforderno") as label). text;
Myworksheet. cells [3 + I, 8] = (this. repeater1.items [I]. findcontrol ("lbforderstate") as label). text;
}