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