想通过这篇文章描述一下从sqlserver导入数据到excel和从excel导出数据到sqlserver的几种方法。
一、通过SQL语句直接导入到数据库
数据的导入与导出是每个系统基本都具有的功能,在开始的时候,自己想的是把用户的信息从数据库中直接导入到sqlserver数据库,于是,沿着这个思路也就有了第一种方法:
首先要执行语句:
exec sp_configure 'show advanced options',1 --1打开 0关闭
reconfigure
go
exec sp_configure 'ad hoc distributed queries',1 --1打开 0关闭
reconfigure
go
如果不执行上述语句,会报错,自己可以试下!(在sql2005的外围配置管理器里也可以设置)
然后执行如下语句(excel为excel97-03)便可以查询出excel中的数据,我查的是excel工作薄中的teacherInfo工作表
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\test.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[TeacherInfo$]
显示结果如下:
说明:1、提供程序版本:必须使用 Jet 4.0 提供程序;Jet 3.51 提供程序不支持 Jet ISAM 驱动程序
2、Excel 版本:对于 Excel 95 工作簿(Excel 版本 7.0),应指定 Excel 5.0;对于 Excel 97、Excel 2000 或 Excel 2002 (XP) 工作簿(Excel 版本 8.0、9.0 和 10.0),应指定 Excel 8.0 版本。
二、其实我原来是这样想的,后来通过和艳梅师姐讨论,说这是不可以的。对信息的导入不能通过直接对数据库进行操作,因为如果系统交付用户使用后,用户是不会操作数据库的,所以应该在系统的后台通过代码来实现用户信息的导入。
系统采用了三层架构,代码实现如下:
1、界面层,导入按钮:
protected void btnBatch_Click(object sender, EventArgs e)
{
m_BatchAddTeacher BatchAddTeacher=new m_BatchAddTeacher();
if (file.PostedFile.FileName=="")
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择要导入的文件!');</script>");
return;
}
DataTable dt = BatchAddTeacher.GetDataTable(file.PostedFile.FileName); //B层的批量导入的方法,获取数据。
try
{
if (BatchAddTeacher.WriteToDB("TeacherInfo",dt))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入成功!');</script>");
}
}
catch (Exception)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入失败,请确认是否修改年级名称为对应的序号!');</script>");
}
}
2、B层的主要方法
/// <summary>返回通过这个excel导入的dataTable
///
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable GetDataTable(string fileName)
{
return IBatchAddTeacher.GetDataTable(fileName);
}
/// <summary>写入数据库
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public bool WriteToDB(string tableName, DataTable dt)
{
return IBatchAddTeacher.WriteToDB(tableName, dt);
}
3、在D层采用了抽象工厂加反射,所以直接给出DAL层的接口实现:
OleDbConnection oledbConn = new OleDbConnection();
/// <summary>将excel中的数据加载到datatable
///
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public System.Data.DataTable GetDataTable(string fileName)
{
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;" + "data source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
oledbConn = new OleDbConnection(strConn);
DataSet ds = new DataSet();
OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from[Sheet1$]", GetCon());
oledbda.Fill(ds, fileName);
//获取数据集中的第一个表
return ds.Tables[0];
}
/// <summary>
/// 连接Excel表的方法
/// </summary>
/// <returns></returns>
public OleDbConnection GetCon()
{
if (oledbConn.State == ConnectionState.Closed)
{
oledbConn.Open();
}
return oledbConn;
}
/// <summary>
/// 插入数据到数据库
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public bool WriteToDB(string tableName, DataTable dt)
{
return new SqlHelper().BatchInsertData(tableName, dt);
}
下面是SQLHelper中的对应的方法:
说明:列标题:默认情况下,系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况,则必须将该设置关闭,否则,第一行数据将会“消失”,而被用作字段名称。这可通过向连接字符串的扩展属性添加可选的HDR=设置来完成。默认情况下(无需指定)是HDR=Yes。如果没有列标题,则需要指定HDR=No;提供程序将字段命名为 F1、F2 等等
2、关于IMEX的值,可以参考:http://support.microsoft.com/kb/194124
http://www.cnblogs.com/xingyukun/archive/2007/04/30/733461.html
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="tableName">数据表名称</param>
/// <param name="dt">要插入的数据</param>
/// <returns></returns>
public bool BatchInsertData(string tableName, DataTable dt)
{
//数据批量导入sqlserver,创建实例
System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(strCon);
//目标数据库表名
sqlbulk.DestinationTableName = tableName;
//数据集字段索引与数据库字段索引映射
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// sqlbulk.ColumnMappings.Add(i, i);
//}
//导入
sqlbulk.WriteToServer(dt);
sqlbulk.Close();
return true;
}
这样就实现excel的完美导入!
三、sqlserver导出到excel
可以直接通过DTS(data transformation service)导出和导入excel数据到sqlserver。因为都是图形化的界面,在此不再赘述了。
四、在系统里面有一个需求就是要把数据,导出到excel,现在给出从dataTable把数据导出到excel的实现:
/// <summary>把dataTable中的数据导出到excel
///
/// </summary>
/// <param name="table"></param>
private void TableToExcel(DataTable table)
{
StringBuilder sbText = new StringBuilder();
try
{
sbText.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\">");
sbText.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
//导出列名
sbText.AppendLine("<tr style=\"font-weight:bold;white-space:nowrap;\">");
for (int i = 0; i < table.Columns.Count; i++)
{
sbText.AppendFormat("<td>{0}</td>", table.Columns[i].ColumnName);
}
sbText.AppendLine("</tr>");
//导出数据
foreach (DataRow row in table.Rows)
{
sbText.Append("<tr>");
for (int i = 0; i < table.Columns.Count; i++)
{
if (i==0)
{
//这个看情况吧,如果是以0开头的话,就加上这句话。
sbText.AppendFormat("<td>{0}</td>", "'"+row[i].ToString());
}
else
{
sbText.AppendFormat("<td>{0}</td>", row[i]);
}
}
sbText.AppendLine("</tr>");
}
sbText.AppendLine("</table>");
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
//设置输出流编码格式
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//设置输出文件类型为excel文件。
Response.ContentType = "application/ms-excel";
EnableViewState = false;
}
catch (Exception ex)
{
//错误处理代码
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('错误信息:!"+ex.Message.ToString()+"');</script>");
}
Response.Write(sbText.ToString());
Response.End();
}
说明:对于有的字符串是0开头的话,excel会自动把前面的0去掉,于是在导出的时候加了一个方法,就是在前面加一个单引号。(我的表第一列就是这种情况)。
到此,关于sqlserver与excel的几种导入与导出关系都介绍完毕了,其实还有好东西要了解和掌握的,如果想深刻地理解,最好还是参考MSDN的帮助。
猛击此处:http://support.microsoft.com/kb/257819