在实际的开发应用中,数据库导入导出是经常遇到的问题,尤其是数据库与Excel文件之间的导入导出,还存在数据类型不一致的问题。例如:数据库的数字超长时会在Excel里格式化成科学计数法的格式,或者记录内容是数字和字符的混合内容会丢失内容等等。将Access数据库的内容直接导入到Excel则可以避免这些问题。
下面例子就是实现这个功能,例子中的数据库使用《ASP.NET 2.0应用开发技术》一书中自带的数据库为例子。
另外,需要注意:Excel文件有诸多限制,在如果数据库记录内容很多,还要计算每次导出的数量和Sheet数目,另外,对Sheet名字相同的监测也省略了,需要的读者请根据情况自行添加上去。 结合存储过程的分页功能实现起来比较好。
C#
<%
@ Page Language
=
"
C#
"
%>
<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< script runat = " server " >
protected void Button1_Click( object sender, EventArgs e)
... {
string sql;
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|ASPNET20Book.mdb;Persist Security Info=True";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(connstr);
System.Data.OleDb.OleDbCommand cmd;
cn.Open();
//先得到记录数目:
sql = "select Count(*) From Paging";
cmd = new System.Data.OleDb.OleDbCommand(sql, cn);
int RecordCount = (int)cmd.ExecuteScalar();
// TODO:计算Sheet数目,进行记录分段,将不同的数据段导入到不同的Sheet(Sheet数目不知道有没有限制:()
// TODO:文件名,Sheet名字的存在检测略
//每个Sheet只能最多保存65536条记录。
sql = @"select top 65535 * into [Excel 8.0;database=" + Server.MapPath(".") + @"ASPNET20Book.xls].[Sheet1] from Paging";
cmd = new System.Data.OleDb.OleDbCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
cn.Dispose();
cn = null;
}
</ script >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head runat = " server " >
< title > 直接将Access数据库导入到Excel文件 </ title >
</ head >
< body >
< form id = " form1 " runat = " server " >
< asp:Button ID = " Button1 " runat = " server " OnClick = " Button1_Click " Text = " 到处数据 " />
</ form >
</ body >
</ html >
<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< script runat = " server " >
protected void Button1_Click( object sender, EventArgs e)
... {
string sql;
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|ASPNET20Book.mdb;Persist Security Info=True";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(connstr);
System.Data.OleDb.OleDbCommand cmd;
cn.Open();
//先得到记录数目:
sql = "select Count(*) From Paging";
cmd = new System.Data.OleDb.OleDbCommand(sql, cn);
int RecordCount = (int)cmd.ExecuteScalar();
// TODO:计算Sheet数目,进行记录分段,将不同的数据段导入到不同的Sheet(Sheet数目不知道有没有限制:()
// TODO:文件名,Sheet名字的存在检测略
//每个Sheet只能最多保存65536条记录。
sql = @"select top 65535 * into [Excel 8.0;database=" + Server.MapPath(".") + @"ASPNET20Book.xls].[Sheet1] from Paging";
cmd = new System.Data.OleDb.OleDbCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
cn.Dispose();
cn = null;
}
</ script >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head runat = " server " >
< title > 直接将Access数据库导入到Excel文件 </ title >
</ head >
< body >
< form id = " form1 " runat = " server " >
< asp:Button ID = " Button1 " runat = " server " OnClick = " Button1_Click " Text = " 到处数据 " />
</ form >
</ body >
</ html >