本文目录:
(一)背景
(二)数据库数据导入到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文件数据的步骤,如下:
////// 导出数据到EXCEL文件中 ////// /// 执行包的传入参数 /// 生成的Excel的文件 /// SSIS包名称 /// SSIS EXCEL模板名称 /// 生成的Excel的文件前缀 ///
{ // 数据包和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 = https://www.it610.com/article/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应用篇。 希望各位能够在本随笔中有所收获。一口气写下来,还真不容易,写文章确实挺锻炼人的。当然,本文中肯定还有很多不足之处,希望各位多多指教。 本文源代码附上VS2008项目: Jasen.SSISProject.rar[VS2008精简版SSIS应用源代码] 相关SSIS资料: 【数据库|效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】)...】 效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 效率最高的Excel数据导入续---SSIS Package包制作图解全过程
作者: JasenKin
出处: http://www.cnblogs.com/jasenkin/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利
推荐阅读
- Java|Java基础——数组
- 人工智能|干货!人体姿态估计与运动预测
- 推荐系统论文进阶|CTR预估 论文精读(十一)--Deep Interest Evolution Network(DIEN)
- java简介|Java是什么(Java能用来干什么?)
- Java|规范的打印日志
- Linux|109 个实用 shell 脚本
- 数据库|SQL行转列方式优化查询性能实践
- 程序员|【高级Java架构师系统学习】毕业一年萌新的Java大厂面经,最新整理
- Python专栏|数据分析的常规流程
- Spring注解驱动第十讲--@Autowired使用