asp.net导出数据到EXCEL简单有效

一：用StringWriter类循环把DATATABLE里面的数据读出来。这方法好，简单也不复杂，不用添加引用。

首先得从数据库取到数据：

DataTable ds;

ds = DbHelperSQL.Query(strsql.ToString(), parameters);

StringWriter swr = new StringWriter();

swr.WriteLine("操作时间\t操作员ID\t私网IP\t公网IP\t游戏名称\t游戏帐号\t订单号\t订单状态\t发布单IP");

//设置导出的文件名

DateTime dt = DateTime.Now;

string strFileName = this.tbxFristTime.Text.ToString() + "-" + this.txtGameName.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二：利用EXCEL组件，首先得添加引用：

//using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;

using System.IO;

下面是代码：

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 = "操作时间";

myWorkSheet.Cells[1, 1] = "私网IP";

myWorkSheet.Cells[2, 1] = "公网IP";

Excel.ApplicationClass Mylxls = new Excel.ApplicationClass();

Mylxls.Application.Workbooks.Add(true);

myexcel.Caption = "异常订单表";

myWorkSheet.Cells[1, 1] = "5173异常订单数据";

myWorkSheet.Cells[2, 1] = "执行时间";

myWorkSheet.Cells[2, 2] = "操作员ID";

myWorkSheet.Cells[2, 3] = "私网IP";

myWorkSheet.Cells[2, 4] = "公网IP";

myWorkSheet.Cells[2, 5] = "游戏名称";

myWorkSheet.Cells[2, 6] = "游戏帐号";

myWorkSheet.Cells[2, 7] = "订单号";

myWorkSheet.Cells[2, 8] = "订单状态";

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(); //数据库字段。。没有前面的lb

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, "请选择订单状态！");

}

else MessageBox.Show(this, "请输入要查询的时间段！！！");

Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("Test.xls", Encoding.UTF8).ToString());

从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;

}