خروجی به اکسل در چندین Sheet به کمک SSIS

خروجی  به چندین شیت در اکسل از یک جدول در SQL

همه ما در پروژه های داده محور با درخواست استخراج داده ها به صورت دسته بندی شده در Sheet  های جداگانه بر اساس یکی از ستونها روبه رو شده ایم در این مقاله قصد داریم تا به کمک SSIS  این کاررا انجام دهیم

این عملیات در خروجی گزارشات فروش و مالی می تواند بسیار کارآمد باشد

داده استفاده شده در این مقاله داده فروش  از بانک اطلاعاتی AdventureWorksDW2019  است

هدف : استخراج داده فروش این فروشگاه  در سال ۹۲ در یک فایل اکسل به نحوی که در هرشیت فقط داده های فروش یک ماه وجود داشته باشد است  .

مدت زمان لازم برای مطالعه : ۳۰ دقیقه

MultipleExcelSheets

قدم اول : ایجاد یک پروژه جدید در SSIS

قدم ۲: تعریف متغییر ها

در این عملیات ما با ۴ متغییر نیاز داریم

۱: تعریف مسیر نهایی FolderPath

۲: تعریف نام فایل اکسل FileName

۳:تعریف نام ستون ای که میخواهیم بر اساس آن عملیات  جدا سازی انجام شود ColumGroup

۴: نام جدولی که باید اطلاعات از آن خوانده شود

گام ۳ : ایجاد Connection  به بانک اطلاعاتی اگر از قبل Connection  فعال دارید آن را انتخاب نمایید در غیر اینصورت باید Connection  جدیدی را ایجاد نمود

در قسمت Connection Manager  راست کلیلک نمایید

گزینه New ADO.net Connection  را انتخاب نمایید

در پنجره باز شده نام سرور و بانک اطلاعاتی مورد نظر را انتخاب نمایید

گام ۵ : از منو SSIS Toolbox  یک Object  ، Script task   به پکیج اضافه میکنیم .و با دوبار کلیک روی آن شروع به تنظیمات آن می نماییم

در قسمت Read Only Variable  متغییر هایی که در مرحله ۲ تعریف کردیم را به پروژه اضافه میکنیم

(بر روی دکمه … کلیک نمایید)

و دکمه OK  را کلیک نمایید

در ادامه با کلیلک بر روی دکمه Edit Script   وارد فضای کد نویسی C#  میشویم

در اینجا کدی را که زیر را در آن کپی نمایید و طبق نام گذاری متغییر های پکیج خودتان کد آن را تغییر دهید

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

#endregion

namespace ST_2aa531f855c1439193aeafe73252f984
{
    


        
        public void Main()
        {
            // TODO: Add your code here

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
                string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
                string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                string ColumnNameForGrouping = Dts.Variables["User::ColumnNameForGrouping"].Value.ToString();
                ExcelFileName = ExcelFileName + "_" + datetime;


                OleDbConnection Excel_OLE_Con = new OleDbConnection();
                OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                //Construct ConnectionString for Excel
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                //drop Excel file if exists
                File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

                //USE ADO.NET Connection from SSIS Package to get data from table
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["ConnectionName"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Read distinct Group Values for each Excel Sheet
                string query = "Select distinct " + ColumnNameForGrouping + " from  " + TableName;

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                // myADONETConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                myADONETConnection.Close();

                //Loop through values for ColumnNameForGroup

                foreach (DataRow dt_row in dt.Rows)
                {
                    string ColumnValue = "";
                    object[] array = dt_row.ItemArray;
                    ColumnValue = array[0].ToString();


                    //Load Data into DataTable from SQL ServerTable
                    string queryString =
                     "SELECT * from " + TableName + " Where " + ColumnNameForGrouping + "='" + ColumnValue + "'";
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);


                    //Get Header Columns
                    string TableColumns = "";

                    // Get the Column List from Data Table so can create Excel Sheet with Header
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataColumn column in table.Columns)
                        {
                            TableColumns += column + "],[";
                        }
                    }

                    // Replace most right comma from Columnlist
                    TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
                    TableColumns = TableColumns.Remove(TableColumns.Length - 2);
                    //MessageBox.Show(TableColumns);


                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                    Excel_OLE_Cmd.CommandText = "Create table [" + ColumnValue + "] (" + TableColumns + ")";
                    Excel_OLE_Cmd.ExecuteNonQuery();


                    //Write Data to Excel Sheet from DataTable dynamically
                    foreach (DataTable table in ds.Tables)
                    {
                        String sqlCommandInsert = "";
                        String sqlCommandValue = "";
                        foreach (DataColumn dataColumn in table.Columns)
                        {
                            sqlCommandValue += dataColumn + "],[";
                        }

                        sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                        sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                        sqlCommandInsert = "INSERT into [" + ColumnValue + "] (" + sqlCommandValue + ") VALUES(";

                        int columnCount = table.Columns.Count;
                        foreach (DataRow row in table.Rows)
                        {
                            string columnvalues = "";
                            for (int i = 0; i < columnCount; i++)
                            {
                                int index = table.Rows.IndexOf(row);
                                columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                            }
                            columnvalues = columnvalues.TrimEnd(',');
                            var command = sqlCommandInsert + columnvalues + ")";
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();
                        }

                    }
                    Excel_OLE_Con.Close();
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch (Exception exception)
            {

                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
                    Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }

            }
        }
}

و در نهایت پس از ایجاد Build  با اجرا پروژه فایل اکسل خروجی در مسیر ای که برای آن تعریف کرده اید ایجاد میگردد

نکته مهم در خروجی به اکسل SQL این است که باید توجه داشته باشید حتما sql access database engine   را بر روی سرور نصب کرده باشید برای نصب آن می توانید از لینک زیر اقدام نمایید

https://www.microsoft.com/en-us/download/details.aspx?id=54920

امیدورام این مطلب برای شما مفید بوده باشد

نفیسه حجاری