Question

asp源码导入、导出EXCEL数据

求这样的ASP程序，我是用来研究如何实现导入EXCEL数据到access的。并且如何能实现把部分字段生成excel表格。

Answer 1

下面这个函数你可以放在类中，随时拿来用， 这是一个讲OleDbDataReader导出EXCEL的函数， 三个参数，第一个是页面，默认是本页this， 第二个是OleDbDataReader，第三个是导出的文件名

public void CreateExcel(System.Web.UI.Page Page, OleDbDataReader dr, string FileName)
{
HttpResponse resp;

resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0;

for (i = 0; i < dr.FieldCount - 1; i++)
{
colHeaders += dr.GetName(i).ToString() + "\t";
}
colHeaders += dr.GetName(i).ToString() + "\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
while (dr.Read())
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
for (i = 0; i < dr.FieldCount - 1; i++)
{
ls_item += dr[i].ToString() + "\t";
}

ls_item += dr[i].ToString() + "\n";
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = "";

}

//写缓冲区中的数据到HTTP头文件中
resp.End();

}

Answer 2

<%
'下面连接到student.xls表sheet1$
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & Server.MapPath("student.xls")

SQL1="select * from [sheet1$]"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL1, conn, 3, 3

'下面连接到whole.mdb表table
curDir = Server.MapPath("../data/whole.mdb")
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & curDir
Set rs1 = Server.CreateObject("ADODB.Recordset")
Set rs1.ActiveConnection = conn1
rs1.Source = "select * from table"
rs1.CursorType = 3 ' adOpenKeyset
rs1.LockType = 3 'adLockOptimistic
rs1.Open
Do While Not rs.Eof
rs1.AddNew
rs1(0)=j
for i=0 to rs.Fields.Count-1
rs1(i)=Trim(rs(i))
Next
rs1.Update
rs.MoveNext
j=j+1
Loop

rs.Close
rs1.Close
conn.Close
conn1.Close
Set rs=nothing
Set conn=nothing
%>