public class InputArguments

{

    public string ConnectStrig  { get; set; }

    public string SqlCommand    { get; set; }

    public string FileName      { get; set; }

}

public class OutputResult

{

    public string Error             { get; set; }

    public DataSet dataSet          { get; set; }

    public byte[] byteExcel         { get; set; }

}

/*  11.12.2012. Activity DbToXlsx.

 

    1.  Activity DbToXlsx reads the content of Orders table in MSSQL database, converts 

        then, on the basis of Dataset, creats an Excel document, 

        and writes it to a disc as an xlsx file.

 

    2. The input parameter is InputArguments class with fields:  

        public string ConnectStrig  { get; set; }

        public string SqlCommand    { get; set; }

        public string FileName      { get; set; }

 

    3. The output argument is OutputResult with fields:

        public string Error         { get; set; }

        public DataSet dataSet      { get; set; }

        public byte[] byteExcel     { get; set; }

 

    4. The customer sends the data of InputArguments, receives from Activity Excel the data stored

        in the object of OutputResult class.

        Writes byte[] array to a local disc, reads and displays the data in Microsoft Excel

*/



using System;

using System.Collections.Generic;

using System.Text;

using System.Activities;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Windows.Markup;

using System.ComponentModel;

using System.Reflection;

using Microsoft.Office.Tools.Excel;

using EXL = Microsoft.Office.Interop.Excel;

using System.Windows.Forms;

using ClassLibrary;



namespace ActivityLibrary

{

    public sealed class DbToXlsx : AsyncCodeActivity<OutputResult>

    {       

        // Input Object

        [RequiredArgument]

        [DefaultValue(null)]

        public InArgument<InputArguments> inputArguments { get; set; }



        // Temporary variable for input arguments

        private InputArguments inputObject      = null;

        private string connectStr               = null; 

        private string cmdSQL                   = null;

        private string fileName                 = null;

        private string activityDirectory        = null;



        //Output Object

        private OutputResult OutRes             = null;



        // Temporary use variable

        private DataSet dataSet                 = null;

        private SqlConnection connect           = null;

        private SqlCommand command              = null;

        private SqlDataAdapter adapter          = null;

        private SqlDataReader reader            = null;



        // Temporary Exsel variaable

        private EXL.Application xlsx            = null;

        private EXL.Workbook  workBook          = null;

        private EXL.XmlMap ImportMap            = null;

        private EXL.Range rangel                = null;



        Object missing                          = (Object)Type.Missing;



       protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)

        {        

            try

            {

                // Input Object

                inputObject = inputArguments.Get(context);

                // Connection string

                connectStr  = inputObject.ConnectStrig;

                // Command string

                cmdSQL      = inputObject.SqlCommand;

                // Name File for write docx

                fileName    = inputObject.FileName;



                // If connect string is empty

                if (String.IsNullOrEmpty(connectStr)) throw new ArgumentNullException("Value", "Connection String is Empty");



                // If command string is empty

                if (String.IsNullOrEmpty(cmdSQL)) throw new ArgumentNullException("Value", "Command String is Empty");



                // If nameFile string is empty

                if (String.IsNullOrEmpty(fileName)) throw new ArgumentNullException("Value", "File Name String is Empty");



                connect = new SqlConnection(connectStr);

                connect.Open();

                command = new SqlCommand(cmdSQL, connect);

                adapter = new SqlDataAdapter(command);

                context.UserState = adapter;

                return adapter.SelectCommand.BeginExecuteReader(callback, state);

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

                return null;

            }

        }



       protected override OutputResult EndExecute(AsyncCodeActivityContext context, IAsyncResult result)

       {         

           try

           {

               SqlDataAdapter adapter = (SqlDataAdapter)context.UserState;

               

               //Create Output Object

               OutRes = new OutputResult();

               

               // End Execute Reader

               reader = adapter.SelectCommand.EndExecuteReader(result);

               reader.Close();

               

               // Create DataSet

               dataSet = new DataSet();

               adapter.Fill(dataSet);

               

               // Count Rows and Columns

               int countRows = dataSet.Tables[0].Rows.Count;

               int countField = dataSet.Tables[0].Columns.Count;



               // Create xlsx Application

              xlsx = new EXL.Application();



               // Add workbook

               workBook = xlsx.Workbooks.Add();



               // Add a new XML map to the collection.

               ImportMap = xlsx.ActiveWorkbook.XmlMaps.Add(dataSet.GetXmlSchema(), "NewDataSet");



               // If error creating maps

               if (ImportMap == null) { throw new System.InvalidOperationException("Create Map is Error"); }



               // Definition range

               rangel = xlsx.ActiveSheet.Range["A1",missing];



               // Import xml data

               xlsx.ActiveWorkbook.XmlImportXml(dataSet.GetXml(), out ImportMap,true,rangel);

               xlsx.Visible = false;



               // Disable tips and warnings

               xlsx.DisplayAlerts = false;

 

               // Path for write               

               activityDirectory = AppDomain.CurrentDomain.BaseDirectory;



               // Write data

               workBook.SaveAs(activityDirectory + fileName, EXL.XlFileFormat.xlWorkbookDefault, 

                   missing, missing, missing, missing, EXL.XlSaveAsAccessMode.xlExclusive, 

                   missing, missing, missing, missing, missing);



               // Close and Ouit

               workBook.Close(missing, missing, missing);

               xlsx.Quit();



               // Create output data

               OutRes.byteExcel = File.ReadAllBytes(activityDirectory + fileName);

               OutRes.dataSet = this.dataSet;

           }

           catch (Exception ex)

           {

               Console.WriteLine(ex.Message);

               this.OutRes.Error = ex.ToString();

           }

           finally

           {

               releaseObject(ImportMap);

               releaseObject(rangel);

               releaseObject(workBook);

               releaseObject(xlsx);



               GC.GetTotalMemory(false);

               GC.Collect();

               GC.WaitForPendingFinalizers();

               GC.Collect();

               GC.GetTotalMemory(true); 

               this.connect.Close();

           }

           return OutRes;

       }



       // Cleaning tempory Exsel data

       private void releaseObject(object obj)

       {

           try

           {

               System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

               obj = null;

           }

           catch (Exception ex)

           {

               obj = null;

               Console.WriteLine(ex.Message);

               this.OutRes.Error = ex.ToString();

           }

           finally

           {

               GC.Collect();

           }

       }

    }

}

/* 11.12.2012 Client for checking Activity DbToXlsx */

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using EXL = Microsoft.Office.Interop.Excel;

using ClassLibrary;

using System.IO;



namespace ClientForDbToXlsx

{

    public partial class Form1 : Form

    {

        private ServiceReference.ServiceClient client   = null;

        private ClassLibrary.InputArguments input       = null;

        private ClassLibrary.OutputResult output        = null;



        // Temporary Excel variable

        private EXL.Application xlsx                    = null;

        private EXL.Workbook workBook                   = null;

        private string activityDirectory                = null;



        Object missing = (Object)Type.Missing;

        public Form1()

        {

            InitializeComponent();

            client = new ServiceReference.ServiceClient();

            input = new InputArguments();

            output = new OutputResult();

            textBox2.Text = @"Data Source=PROGR\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True; Asynchronous Processing=true";

            textBox3.Text = @"SELECT Orders.OrderID, Orders.CustomerID, Orders.ShipAddress FROM Orders";

            textBox1.Text = "DbToXlsx.xlsx";

        }



        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                // Input string for connect

                input.ConnectStrig = textBox2.Text;

                // Input SQL command

                input.SqlCommand = textBox3.Text;

                // Input name File for write

                input.FileName = textBox1.Text;



                // Send and receive data

                output = client.GetData(input);



                // Write Excel File to Disk

                File.WriteAllBytes(activityDirectory + input.FileName, output.byteExcel);



                // Creating Excel document

                xlsx = new EXL.Application();



                // Add workbook

                activityDirectory = AppDomain.CurrentDomain.BaseDirectory;

                workBook = xlsx.Workbooks.Add(activityDirectory + input.FileName);

                xlsx.Visible = true;

                xlsx.DisplayAlerts = false;

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.ToString());

            }

        }

    }

}