IDC资讯中心
在实际的开发应用中，数据库导入导出是经常遇到的问题，尤其是数据库与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只能最多保存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>
vb.net
protected sub button1_click(byval sender as object, byval e as eventargs)
dim sql as string
dim connstr as string = “provider=microsoft.jet.oledb.4.0;data source=|datadirectory|aspnet20book.mdb;persist security info=true“
dim cn as system.data.oledb.oledbconnection = new system.data.oledb.oledbconnection(connstr)
dim cmd as system.data.oledb.oledbcommand
cn.open
sql = “select count(*) from paging“
cmd = new system.data.oledb.oledbcommand(sql, cn)
dim recordcount as integer = ctype(cmd.executescalar, integer)
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 = nothing
end sub
dim sql as string
dim connstr as string = “provider=microsoft.jet.oledb.4.0;data source=|datadirectory|aspnet20book.mdb;persist security info=true“
dim cn as system.data.oledb.oledbconnection = new system.data.oledb.oledbconnection(connstr)
dim cmd as system.data.oledb.oledbcommand
cn.open
sql = “select count(*) from paging“
cmd = new system.data.oledb.oledbcommand(sql, cn)
dim recordcount as integer = ctype(cmd.executescalar, integer)
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 = nothing
end sub
