Question

ASP.NET 自定义数据导入excel，求源码方法

我需要把三行自定义数据导入分别excel
如下：
姓名
编号
电话
这样三行数据导入excel，哪位老大能帮帮忙哦？

Answer 1

USE [Report]
GO

/****** Object: Table [dbo].[BookInfo] Script Date: 11/29/2011 20:52:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BookInfo]') AND type in (N'U'))
DROP TABLE [dbo].[BookInfo]
GO

USE [Report]
GO

/****** Object: Table [dbo].[BookInfo] Script Date: 11/29/2011 20:52:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BookInfo](
[名字] [varchar](50) NULL,
[编号] [varchar](50) NULL,
[手机号码] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2个步骤

--开启导入exec程序
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--改你所要execl的地址 Sheet2$ 看你的数据在哪里
insert into BookInfo
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0', 'EXCEL 5.0;HDR=YES;IMEX=2;DATABASE=E:\BookInfo
.xls'
,'SELECT * FROM [Sheet2$]')

--关闭导入exec程序
--使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

追问

我不是要写数据库语句进行导出啊~
我是想把我自定义的datatable数据导入到excel进行导出下载功能啊！l

追答

//页面上一个按钮+gridview

//导出Exec
private void GridViewToExcel()
{
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); //这里是用日期做名称
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentType = "application/excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
this.GridView.AllowPaging = false; //GridView不启用分页
GridView.RenderControl(htmlWrite);
HttpContext.Current.Response.Write(stringWrite.ToString());
}
//导出Exec数据
protected void butExec_Click(object sender, EventArgs e)
{
GridViewToExcel();
}

追问

你的意思是把自定义的数据绑定在gridview，然后通过gridview输出到excel中？

追答

是 哇 搞到个gridview导出。

Answer 2

可以这样，希望对你有帮助：
HttpResponse hr = Page.Response;
hr.Clear();
hr.HeaderEncoding = Encoding.Default;
string fileName = "登入日志[" + DateTime.Now + "].xls";
hr.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
hr.ContentEncoding = Encoding.Default;
hr.ContentType = "application/ms-excel";

DataTable dt = DbProvider.Online().OnlineStatSearch(DepartmentID, ClassesID, BeginTime, EndTime, UserNo);
StringBuilder sb = new StringBuilder();
sb.Append("序号\t登入帐户\t登入时间\t上线持续时间\n");
int i = 1;
foreach (DataRow dr in dt.Rows)
{
sb.AppendFormat("{0}\t", i);
sb.AppendFormat("\'{0}\t", dr["UserNo"]);
sb.AppendFormat("{0}\t", dr["LoginTime"]);
sb.AppendFormat("{0}\n", OnlineTimeFormat(dr["OnLineTime"].ToString()));
i++;
}

hr.Write(sb.ToString());
hr.End();