SSIS控制流上的Excel的email发送

好几天没写东西了。 这两天试着把一个每天都得花点时间的常规性事情自动化了。我想这也是作为机器应该帮助我们去减少的事情。

例如: 每天我们都需要不停的去执行SQL 脚本生成数据,然后去把数据拷贝到Excel中,再去使用Excel的公式设计出来自己需要的报表,相当bored。然后我在想应该去它每天自动的发送给用户了。没必要每天浪费1个多小时的时间,我只需要知道它到底有没有准时到达用户的邮箱就行了。

画了个流程图：

 

                                         

  实际上前面的数据无所谓是数据库还是其它地方的数据了。你可以是xml数据或者是ftp下载下来的txt(csv)文本。最终发送给用户需要做以下几步:

1.       生成Excel.

不管怎么说生成Excel对大家来说是比较简单的事情了,但是我现在的Excel需要有个模板来把数据给放进去,不是简单的把一些数据写入excel中。.net2.0中有下面这几个库对于我们生成各种形式的Excel很有帮助:

 

首先我们需要有个模板：

 

在excel中的模板时后缀为xlt的文件。预先在这个模板中设置好筛选和title等等。随便你设置,你甚至设置好使用透视表和透视图去在别的sheet中去做数据分析。

使用.net我们如何实现模板的写入呢？

public void UseTemplate(string path, string templatePath, System.Data.DataTable dt)

        {

            try

            {

              //  this.myTemplateValues = myTemplateValues;

                this.dt = dt;

                //create new EXCEL application

                EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();

                //Yes file exists, so open the file

                workbook = EXL.Workbooks.Open(templatePath,

                    0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",

                    true, false, 0, true, false, false);

                //get the workbook sheets collection

                sheets = workbook.Sheets;

                //get the new sheet

                worksheet = (Worksheet)sheets.get_Item(1);

                //Change its name to that requested

                worksheet.Name = "ATemplate";

                //Fills the Excel Template File Selected With A 2D Test Array

              //  fillTemplate_WithTestValues();

                fillTemplate_WithDataTable();

                //Select all used cells within current worksheet

                SelectAllUsedCells();

 

                try

                {

                    workbook.Close(true, path, Type.Missing);

                    EXL.UserControl = false;

                    EXL.Quit();

                    EXL = null;

                    //kill the EXCEL process as a safety measure

                    killExcel();

                    // Show that processing is finished

                    ProgressEventArgs pe = new ProgressEventArgs(100);

                    OnProgressChange(pe);

                    MessageBox.Show("Finished adding test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);

 

                }

                catch (COMException)

                {

                    Console.WriteLine("User closed Excel manually, so we don't have to do that");

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error " + ex.Message);

            }

        }

上面这个写入模板的方法需要三个参数,最终的excel文件生成的路径,模板路径以及填充模板的数据,虽然我这里是datatable,你也可以使用其它的对象来填充excel。你应该注意到上面有个fillTemplate_WithDataTable(),它是用来把datatable填充到excel确定好的那个sheet。而它之前的代码是用来操作excel对象并选择好sheet以及把sheet得名字写好。它的代码如下:

private void fillTemplate_WithDataTable()

        {

 

 

          //Initilaise the correct Start Row/Column to match the Template

            int StartRow = 3;

            int StartCol = 1;

 

            position = 0;

         // Display the DataTable elements within the Output window, make sure its correct before

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                //loop  through this row,and put into EXCEL template

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                  //update position int progressbar

                    position = (100 / dt.Rows.Count) * i;

                    ProgressEventArgs pe = new ProgressEventArgs(position);

                    OnProgressChange(pe);

 

                    //put into EXCEL template

                    Range rng = (Range)worksheet.Cells[StartRow,StartCol++];

                    rng.Select();

                    rng.Value2 = dt.Rows[i][j].ToString();

                    rng.Rows.EntireRow.AutoFit();

                }

                //New row, so column needs to be reset

                StartCol = 1;

                StartRow++;

            }

        }

代码很简单,注意那个Range的使用。对了,我这里有个ProgressBar是测试这个东西时使用的一个winform上的组件。

Anyway,不是很理解的话没关系,我会把最终的代码共享给大家。

 

2.       发送email。

我想这个大家也是很熟悉,而且基本的都是知道使用各config文件来配置subject,收件人,stmp,附件的名字。但是我突然发现以前很多的sendmail的可配置的程序都无法完成我的要求,因为我需要每天的email名字都是yyyyMMddXX.xls.所以没办法只有自己再去敲点代码写入这些规则。因为我在发送email的时候遇到附件是乱码的问题,所以多说以下发送附件时的编码问题:

 

因为我发送的都是英文所以编码是iso-8859-1,但是如果你是中文的发送千万记得使用utf-8.而我的attachment出现乱码是因为我的content-transfer-Encoding:开始不是Base64而是quoted-printable或者是8bit。

顺便注明下:我没有使用微软的system.net.mail。但是我相信它确实很强大。给大家两个参考,:

http://www.codeproject.com/KB/IP/MailMergeLib.aspx

http://dotnetopenmail.sourceforge.net/

相当不错的两个email发送代码。

3.       工作流的控制：

其实上面这两个基本的东西实现了算是做了60%了,我们还需要错误的记录错误的及时通知等。

最重要的是需要一个控制流来告知这一步完成了我们需要进行下一步了。如果你也是用的是SQL Server建议你使用SSIS来管理。

 

它本身的控制流和数据流能帮助我们在存储过程执行完成以后执行生成excel的程序,excel生成后再发送email,最后OK了还能通知我们已经成功完成了任务。我比较喜欢用它。

 

我给个例子如上图所示,显示选择数据源,然后是执行sql或者是存储过程,再去这性格win32程序,exe,cmd等等。你可以去设定每个流执行后如果失败就发送email通知你。

本人也不是SSIS的专家,只是看了本wrox的一本介绍SSIS的书。

推荐给大家看看。