本文目录:
(一)背景
(二)数据库数据导入到Excel的方法比较
(三)SSIS的简介
(四)数据库中存储过程示例(SSIS应用需要)
(五)Excel模板的制作(这步这么简单,稍微介绍一下)
(六)SSIS操作过程(生成Package,用来调用)(下一篇随笔将详细讲解制作Package包的过程,图片太多,篇幅过长,因此本文将直接采用生成的Package包进行应用)
(七)C#中如何调用SSIS创建的Package和Excel模板(可以自己编写逻辑代码进行重复利用),用来生成Excel数据
(八)总结
(一)背景
如何将数据库中的数据导入到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)。当数据量大时,肯定效率还是有影响的。
public
string
DataExcels(System.Data.DataTable[] dts,
string
strTitle,
string
FilePath, Hashtable nameList,
string
[] titles)
{
COM.Excel.cExcelFile excel
=
new
COM.Excel.cExcelFile();
//
当文件大于10的时候 清空所有文件!!!
ClearFile(FilePath);
//
文件名
string
filename
=
strTitle
+
DateTime.Now.ToString(
"
yyyyMMddHHmmssff
"
)
+
"
.xls
"
;
//
生成相应的文件
excel.CreateFile(FilePath
+
filename);
//
设置margin
COM.Excel.cExcelFile.MarginTypes mt1
=
COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2
=
COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3
=
COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4
=
COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double
height
=
2.2
;
excel.SetMargin(
ref
mt1,
ref
height);
excel.SetMargin(
ref
mt2,
ref
height);
excel.SetMargin(
ref
mt3,
ref
height);
excel.SetMargin(
ref
mt4,
ref
height);
//
设置字体!!
COM.Excel.cExcelFile.FontFormatting ff
=
COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string
font
=
"
宋体
"
;
short
fontsize
=
14
;
excel.SetFont(
ref
font,
ref
fontsize,
ref
ff);
byte
b1
=
1
, b2
=
12
;
short
s3
=
12
;
excel.SetColumnWidth(
ref
b1,
ref
b2,
ref
s3);
string
header
=
"
页眉
"
;
string
footer
=
"
页脚
"
;
excel.SetHeader(
ref
header);
excel.SetFooter(
ref
footer);
COM.Excel.cExcelFile.ValueTypes vt
=
COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf
=
COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca
=
COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl
=
COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
//
报表标题
int
cellformat
=
1
;
int
rowIndex
=
1
;
//
起始行
int
colIndex
=
0
;
foreach
(System.Data.DataTable dt
in
dts)
{
colIndex
=
0
;
//
取得列标题
foreach
(DataColumn colhead
in
dt.Columns)
{
colIndex
++
;
string
name
=
colhead.ColumnName.Trim();
object
namestr
=
(
object
)name;
excel.WriteValue(
ref
vt,
ref
cf,
ref
ca,
ref
chl,
ref
rowIndex,
ref
colIndex,
ref
namestr,
ref
cellformat);
}
//
取得表格中的数据
foreach
(DataRow row
in
dt.Rows)
{
rowIndex
++
;
colIndex
=
0
;
foreach
(DataColumn col
in
dt.Columns)
{
colIndex
++
;
if
(col.DataType
==
System.Type.GetType(
"
System.DateTime
"
))
{
object
str
=
(
object
)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString(
"
yyyy-MM-dd
"
); ;
excel.WriteValue(
ref
vt,
ref
cf,
ref
ca,
ref
chl,
ref
rowIndex,
ref
colIndex,
ref
str,
ref
cellformat);
}
else
{
object
str
=
(
object
)row[col.ColumnName].ToString();
excel.WriteValue(
ref
vt,
ref
cf,
ref
ca,
ref
chl,
ref
rowIndex,
ref
colIndex,
ref
str,
ref
cellformat);
}
}
}
rowIndex
+=
3
;
}
int
ret
=
excel.CloseFile();
return
FilePath
+
filename;
}
方案三:采用Excel组件。一般,对于操作能够基本满足,但对于数据量大时可能会慢点。下面的代码,本人在原有基础上稍微修改了下,如下所示:
1
public
string
OutputExceles(
string
strTitle,
string
FilePath,
string
typeName, System.Data.DataTable[] dtList,
string
[] smallTitleList)
2
{
3
beforeTime
=
DateTime.Now;
4
Excel.Application excel;
5
Excel._Workbook xBk;
6
Excel._Worksheet xSt;
7
int
rowIndex
=
1
;
8
int
colIndex
=
1
;
9
excel
=
new
Excel.ApplicationClass();
10
xBk
=
excel.Workbooks.Add(
true
);
11
xSt
=
(Excel._Worksheet)xBk.ActiveSheet;
12
int
add
=
0
;
13
foreach
(System.Data.DataTable dt
in
dtList)
14
{
15
colIndex
=
1
;
16
//
取得整个报表的标题
17
excel.Cells[rowIndex ,
1
]
=
smallTitle[add];
18
add
++
;
19
///
/设置整个报表的标题格式
20
xSt.get_Range(excel.Cells[rowIndex,
1
], excel.Cells[rowIndex , dt.Columns.Count]).Font.Bold
=
true
;
21
xSt.get_Range(excel.Cells[rowIndex,
1
], excel.Cells[rowIndex , dt.Columns.Count]).Font.Size
=
22
;
22
///
/设置整个报表的标题为跨列居中
23
xSt.get_Range(excel.Cells[rowIndex ,
1
], excel.Cells[rowIndex , dt.Columns.Count]).Select();
24
xSt.get_Range(excel.Cells[rowIndex ,
1
], excel.Cells[rowIndex, dt.Columns.Count]).HorizontalAlignment
=
Excel.XlHAlign.xlHAlignCenterAcrossSelection;
25
rowIndex
++
;
26
foreach
(DataColumn col
in
dt.Columns)
27
{
28
excel.Cells[rowIndex, colIndex]
=
col.ColumnName;
29
//
设置标题格式为居中对齐
30
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold
=
true
;
31
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment
=
Excel.XlVAlign.xlVAlignCenter;
32
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Select();
33
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Interior.ColorIndex
=
titleColorindex;
34
colIndex
++
;
35
}
36
//
取得表格中的数据
37
foreach
(DataRow row
in
dt.Rows)
38
{
39
rowIndex
++
;
40
colIndex
=
1
;
41
foreach
(DataColumn col
in
dt.Columns)
42
{
43
if
(col.DataType
==
System.Type.GetType(
"
System.DateTime
"
))
44
{
45
if
(
!
string
.IsNullOrEmpty(row[col.ColumnName].ToString()))
46
{
47
excel.Cells[rowIndex, colIndex]
=
(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString(
"
yyyy-MM-dd
"
);
48
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment
=
Excel.XlVAlign.xlVAlignCenter; }
49
}
50
else
if
(col.DataType
==
System.Type.GetType(
"
System.String
"
))
51
{
52
excel.Cells[rowIndex, colIndex]
=
"
'
"
+
row[col.ColumnName].ToString();
53
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment
=
Excel.XlVAlign.xlVAlignCenter;r; }
54
else
55
{
56
excel.Cells[rowIndex, colIndex]
=
row[col.ColumnName].ToString();
57
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment
=
Excel.XlVAlign.xlVAlignCenter; }
58
colIndex
++
;
59
}
60
}
61
rowIndex
++
;
62
}
63
afterTime
=
DateTime.Now;
64
xSt.Name
=
strTitle;
65
string
filename
=
typeName
+
DateTime.Now.ToString(
"
yyyyMMdd
"
)
+
"
.xls
"
;
66
//
excel.Save(FilePath+filename);
67
excel.ActiveWorkbook.SaveCopyAs(FilePath
+
filename);
68
#region
结束Excel进程
69
xBk.Close(
null
,
null
,
null
);
70
excel.Workbooks.Close();
71
excel.Quit();
72
#endregion
73
return
filename;
74
}
方法四:采用DataGrid,GridView自带的属性。如下:
private
void
ExportExcelFromDataGrid(
string
filename, System.Web.UI.WebControls.GridView ToExcelGrid)
{
Response.ClearHeaders();
Response.Clear();
Response.Expires
=
0
;
Response.Buffer
=
true
;
Response.HeaderEncoding
=
System.Text.Encoding.UTF8;
//
Response.Charset = "utf-8";
Response.AppendHeader(
"
Content-Disposition
"
,
"
attachment;filename=
"
+
Server.UrlEncode(filename));
Response.ContentEncoding
=
System.Text.Encoding.Default;
//
设置输出流为简体中文
//
Response.ContentType = "application/ms-excel";
//
设置输出文件类型为excel文件。
Response.ContentType
=
"
Application/octet-stream
"
;
this
.EnableViewState
=
false
;
System.Globalization.CultureInfo myCItrad
=
new
System.Globalization.CultureInfo(
"
zh-CHS
"
,
true
);
System.IO.StringWriter oStringWriter
=
new
System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter
=
new
System.Web.UI.HtmlTextWriter(oStringWriter);
ToExcelGrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
(三)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包的时候很容易混淆,建议拿个本子把需要的内容写好。
(五)Excel模板的制作(这步这么简单,稍微介绍一下)
因为SSIS中列映射对应的是Excel的标题,与数据是一对一的关系。先不管这么多,看下我们的模板,如下图所示。我们应该能够发现,省份拼音、省份名、城市名,还有ProvinceCityInfoExcel.xls,Sheet1都被笔者标识了,当然
这一步与数据库中的存储过程取出的数据也是一对一的。(
名称一致,可以减少很多不必要的麻烦,不然的话,嘿嘿....自己去想,那不是哥的事)
等下,需要将创建的EXCEL模板放置到我们的项目文件目录中。
(详见第七步)
(六)SSIS操作过程(生成Package,用来调用)
这一步是最主要的过程,当然,也是很容易出错的一步。
笔者会另外详细介绍制作Package包的过程,本文将直接将生成的包放到VS项目中进行运用。
利用SQL Server 2005数据库自带的SQL Server Business Intelligence Development Studio(SQL Server商业智能开发平台),最终生成的项目如下图所示:
然后,
将在SSIS项目中生成的Package.dtsx包复制到自己的项目文件目录中。这就是我们马上进入的步骤了---->(步骤七)
(七)C#中调用SSIS创建的Package和Excel模板(可以自己编写逻辑代码进行重复利用),用来生成Excel数据
先看下我们的VS2008项目,如下图所示:
大家会发现,笔者
将(五)(六)步骤生成的模板和Package包放置在项目中的“Excel导出”目录下,当然这些文件随便你放在哪里,这是不用再废话的,哈哈。
另外,笔者简单的设计了如下很粗糙的界面,目的是根据省份来显示城市的相关信息(其实大家都是很熟悉这些的,很多项目都是有省-市-县数据库表的),添加一个导出按钮,点击的时候,我们可以
参考页面显示的内容和我们生成的客户端Excel中的内容是否一致。
现在我们的重头戏开始了,如下代码(点击将触发的代码内容):
1
protected
void
btnSSISSearch_Click(
object
sender, EventArgs e)
2
{
3
//
构造sql语句 作为参数传递给数据包
4
string
sqlParams
=
Jasen.SSIS.Core.SsisToExcel.BuildSql(
"
dbo.ProvinceSelectedCityInfo
"
,
"
@provinceId
"
,
int
.Parse(ddlProvice.SelectedValue));
5
Jasen.SSIS.Core.SsisToExcel ssis
=
new
Jasen.SSIS.Core.SsisToExcel();
6
string
rootPath
=
Request.PhysicalApplicationPath;
7
string
copyFilePath;
8
//
执行SSIS包的操作 生成EXCEL文件
9
bool
result
=
ssis.ExportDataBySsis(rootPath, sqlParams,
out
copyFilePath,
"
Package.dtsx
"
,
"
ProviceCityInfoExcel.xls
"
,
"
ProviceCityInfo
"
);
10
if
(result
==
false
){
11
if
(System.IO.File.Exists(copyFilePath)) System.IO.File.Delete(copyFilePath);
12
}
13
else
14
{
15
ssis.DownloadFile(
this
,
"
ProviceCityInfoClientFile.xls
"
, copyFilePath,
true
);
16
}
17
}
你肯定会说:“哥,你这个也太简单了吧?”。就是这么简单,不就是多写一个类给你调用就可以了吗。调用接口,这个你总会吧。不过你得了解各个参数才行。
首先,我们必须引用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);
}
代码注释如此清楚,想必也不需要再多做解释了吧,下面就是最最最重要的一步,需要看清楚了----->
1
private
bool
ExecuteSsisDataToFile(
string
ssisFileName,
string
tempExcelName,
string
sqlParams)
2
{
3
Application app
=
new
Application();
4
Package package
=
new
Package();
5
//
加载SSIS包
6
package
=
app.LoadPackage(ssisFileName,
null
);
7
//
获取 数据库连接字符串
8
package.Connections[
"
AdoConnection
"
].ConnectionString
=
Jasen.SSIS.Common.SystemConst.ConnectionString;
9
//
目标Excel属性
10
string
excelDest
=
string
.Format(
"
Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\
"
EXCEL
8.0
;HDR
=
YES\
"
;
"
, tempExcelName);
11
package.Connections[
"
ExcelConnection
"
].ConnectionString
=
excelDest;
12
//
给参数传值
13
Variables vars
=
package.Variables;
14
string
str
=
vars[
"
用户::SqlStr
"
].Value
.ToString();
15
vars[
"
用户::SqlStr
"
].Value
=
sqlParams;
16
//
执行
17
DTSExecResult result
=
package.Execute();
18
if
(result
==
DTSExecResult.Success){
19
return
true
;
20
}
21
else
{
22
if
(package.Errors.Count
>
0
){
23
//
在log中写出错误列表
24
StringBuilder sb
=
new
StringBuilder();
25
for
(
int
i
=
0
; i
<
package.Errors.Count; i
++
){
26
sb.Append(
"
Package error:
"
+
package.Errors[i].Description
+
"
;
"
);
27
}
28
throw
new
Exception(sb.ToString());
29
}
30
else
{
31
throw
new
Exception(
"
SSIS Unknow error
"
);
32
}
33
return
false
;
34
}
35
}
上面标注为红色的就是最重要的几个步骤了,相对来说,就是(1)加载包,(2)设置包的数据库连接串,(3)设置Excel的连接串,(4)设置参数变量,(5)执行操作
其次是如何巧妙的将Excel模板复制,使模板可以重复利用(当然也要注意将生成的文件下载到客户端后,将服务器上生成的Excel临时文件删除,你也可以写自己的算法进行清理不必要的Excel临时文件),如下代码所示,方法将复制模板,然后返回生成的临时文件的路径,如果需要删除该文件,System.IO.File.Delete(filePath)就可以删除文件了:
1
private
string
SaveAndCopyExcel(
string
sourcePath,
string
execlFileName,
string
createdExeclPreName)
2
{
3
string
copyFile
=
sourcePath
+
createdExeclPreName
+
DateTime.Now.ToString(
"
yyyyMMddHHMMss
"
)
+
"
.xls
"
;
4
if
(File.Exists(copyFile)) File.Delete(copyFile);
5
File.Copy(sourcePath
+
execlFileName, copyFile,
true
);
6
return
copyFile;
7
}
讲了这么多,来看下我们点击后生成的效果,
开始有点效果了,Excel终于可以下载到客户端了,我们保存该文件。我们是不是想核实一下,我们采用的SSIS方法来实现Excel数据导入是不是正确的,会不会生成错误的数据?
那我们看下下面的一张图,将它与上面的一张图比较一下,看下数据是不是一样的:
发现生成的数据是一模一样的。我们是将数据导入到服务器上的临时EXCEL文件中,将文件发送到客户端肯定是不会出错的,除了你RP太差以外。RP差,任何事情都可能发生,嘿嘿。
(八)总结
在上面的示例中,由于数据量不是太多,你还感觉不到该方法的优势(效率高)。但是当数据量很大的时候,你用其他方法还在那里慢慢地等待excel文件生成的时候,该方法早就已经将数据导入到Excel中,并且发送到客户端了。有时候时间相差几十秒也是有可能的。数据量越大,效果越明显.....
接下来笔者将在另外一篇随笔中详细讲解SSIS package包的制作过程。这篇主要是SSIS应用篇。
希望各位能够在本随笔中有所收获。一口气写下来,还真不容易,写文章确实挺锻炼人的。当然,本文中肯定还有很多不足之处,希望各位多多指教。