如何将数据库中的数据导入到EXCEL文件中,我们经常会碰到。本文将比较常用的几种方法,并且将详细讲解基于SSIS的用法。笔者认为,基于SSIS的方法,对于海量数据来说,应该是效率最好的一种方法。个人认为,这是一种值得推荐的方法,因此,本人决定将本人所知道的、以及自己总结的完整的写出来,一是提高一下自己的写作以及表达能力,二是让更多的读者能够在具体的应用中如何解决将海量数据导入到Excel中的效率问题。
(二)方法的比较
方案一:SSIS(SQL Server数据集成服务),追求效率,Package制作过程复杂一点(容易出错)。
方案二:采用COM.Excel组件。一般,对于操作能够基本满足,但对于数据量大时可能会慢点。下面的代码,本人稍微修改了下,如下所示:该方法主要是对单元格一个一个的循环写入,基本方法为 excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat)。当数据量大时,肯定效率还是有影响的。
方案三:采用Excel组件。一般,对于操作能够基本满足,但对于数据量大时可能会慢点。下面的代码,本人在原有基础上稍微修改了下,如下所示:
方法四:采用DataGrid,GridView自带的属性。
(三)SSIS的简介
SQL Server 2005 提供的一个集成化的商业智能开发平台,主要包括:
*SQL Server Analysis Services(SQL Server数据分析服务,简称SSAS)
*SQL Server Reporting Services(SQL Server报表服务,简称SSRS)
*SQL Server Integration Services(SQL Server数据集成服务,简称SSIS)
SQL Server 2005 Integration Services (SSIS) 提供一系列支持业务应用程序开发的内置任务、容器、转换和数据适配器。可以创建 SSIS 解决方案来使用 ETL 和商业智能解决复杂的业务问题,管理 SQL Server 数据库以及在 SQL Server 实例之间复制 SQL Server 对象。
(四)数据库中存储过程示例(SSIS应用过程中需要的,最好拿个本子把需要的内容记下)
在SQL SERVER 2005中,以SSISDataBase数据库作为应用,仅包括2张表City,Province.(主要是为了简单,便于讲解)
其中存储过程如下:
ALTER PROCEDURE [dbo].[ProvinceSelectedCityInfo]
(
@provinceId int=0
)
as
begin
select P.EName as 省份拼音,P.CName as 省份名,C.CName as 城市名 from City C left join Province P
on C.ProvinceId = P.ProvinceId
where C.ProvinceId =@provinceId and @provinceId is not null or @provinceId is null or @provinceId=0
end
其中,在这一步中我们必须要记住相关的内容,如上标识(红色);为什么这么做?主要是在制作SSIS包的时候很容易混淆,建议拿个本子把需要的内容写好。
这一步是最主要的过程,当然,也是很容易出错的一步。 笔者会另外详细介绍制作Package包的过程,本文将直接将生成的包放到VS项目中进行运用。
利用SQL Server 2005数据库自带的SQL Server Business Intelligence Development Studio(SQL Server商业智能开发平台),最终生成的项目如下图所示:
protected void btnSSISSearch_Click(object sender, EventArgs e)
{
//构造sql语句 作为参数传递给数据包
string sqlParams = Jasen.SSIS.Core.SsisToExcel.BuildSql("dbo.ProvinceSelectedCityInfo", "@provinceId", int.Parse(ddlProvice.SelectedValue));
Jasen.SSIS.Core.SsisToExcel ssis = new Jasen.SSIS.Core.SsisToExcel();
string rootPath = Request.PhysicalApplicationPath;
string copyFilePath;
//执行SSIS包的操作 生成EXCEL文件
bool result = ssis.ExportDataBySsis(rootPath, sqlParams, out copyFilePath, "Package.dtsx", "ProviceCityInfoExcel.xls", "ProviceCityInfo");
if (result == false){
if (System.IO.File.Exists(copyFilePath)) System.IO.File.Delete(copyFilePath);
}
else
{
ssis.DownloadFile(this, "ProviceCityInfoClientFile.xls", copyFilePath, true);
}
}
你肯定会说:“哥,你这个也太简单了吧?”。就是这么简单,不就是多写一个类给你调用就可以了吗。调用接口,这个你总会吧。不过你得了解各个参数才行。
首先,我们必须引用2个DLL,Microsoft.SQLServer.ManagedDTS.dll和Microsoft.SqlServer.DTSPipelineWrap.dll(系统自带的)。先看下我们生成Excel文件数据的步骤,如下:
/// <summary>
/// 导出数据到EXCEL文件中
/// </summary>
/// <param name="rootPath"></param>
/// <param name="sqlParams">执行包的传入参数</param>
/// <param name="copyFile">生成的Excel的文件</param>
/// <param name="packageName">SSIS包名称</param>
/// <param name="execlFileName">SSIS EXCEL模板名称</param>
/// <param name="createdExeclPreName">生成的Excel的文件前缀</param>
/// <returns></returns>
public bool ExportDataBySsis(string rootPath, string sqlParams, out string tempExcelName, string packageName, string execlFileName, string createdExeclPreName)
{
//数据包和EXCEL模板的存储路径
string path = rootPath + @"Excel导出\";
//强制生成目录
if (!System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path);
//返回生成的文件名
string copyFile = this.SaveAndCopyExcel(path, execlFileName, createdExeclPreName);
tempExcelName = copyFile;
//SSIS包路径
string ssisFileName = path + packageName;
//执行---把数据导入到Excel文件
return ExecuteSsisDataToFile(ssisFileName, tempExcelName, sqlParams);
}
代码注释如此清楚,想必也不需要再多做解释了吧,下面就是最最最重要的一步,需要看清楚了----->
private bool ExecuteSsisDataToFile(string ssisFileName, string tempExcelName, string sqlParams)
{
Application app = new Application();
Package package = new Package();
//加载SSIS包
package = app.LoadPackage(ssisFileName, null);
//获取 数据库连接字符串
package.Connections["AdoConnection"].ConnectionString = Jasen.SSIS.Common.SystemConst.ConnectionString;
//目标Excel属性
string excelDest = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", tempExcelName);
package.Connections["ExcelConnection"].ConnectionString = excelDest;
//给参数传值
Variables vars = package.Variables;
string str = vars["用户::SqlStr"].Value.ToString();
vars["用户::SqlStr"].Value = sqlParams;
//执行
DTSExecResult result = package.Execute();
if (result == DTSExecResult.Success){
return true;
}
else{
if (package.Errors.Count > 0){
//在log中写出错误列表
StringBuilder sb=new StringBuilder();
for (int i = 0; i < package.Errors.Count; i++){
sb.Append("Package error:" + package.Errors[i].Description +";");
}
throw new Exception(sb.ToString());
}
else{
throw new Exception("SSIS Unknow error");
}
return false;
}
}
上面标注为红色的就是最重要的几个步骤了,相对来说,就是(1)加载包,(2)设置包的数据库连接串,(3)设置Excel的连接串,(4)设置参数变量,(5)执行操作
其次是如何巧妙的将Excel模板复制,使模板可以重复利用(当然也要注意将生成的文件下载到客户端后,将服务器上生成的Excel临时文件删除,你也可以写自己的算法进行清理不必要的Excel临时文件),如下代码所示,方法将复制模板,然后返回生成的临时文件的路径,如果需要删除该文件,System.IO.File.Delete(filePath)就可以删除文件了: