using Microsoft.Office.Interop.Excel;

using System;

using System.Data.SqlClient;

using System.Net.Mail;

using System.Net;

using System.Runtime.InteropServices;

​

namespace MyCode

{

    static class Program

    {

        public static string[] headers = { "Item #", "CustomerID", "CustomerNumber", "ProductID","CustomerProductID","Title","LastName",

                                           "FirstName","TelephoneMobile","EmailAddress","AddressFlat","AddressHouseName1",

                                           "AddressPremiseNumber","AddressStreetName1","AddressStreetName2","Address Town",

                                           "Address City","Address County","Postcode","PaymentFrequency"

        };

        static void Main(string[] args)

        {

            SqlConnection SQL_Connection;

            string connectionString = "Data Source=production.database.windows.net;Initial Catalog=prod;User ID=admin;Password=Password1!";

            string sql = "select * from dbo.Customers dboc " +

            "left join dbo.CustomerProducts dbocp on dboc.CustomerId = dbocp.CustomerId " +

            "left join dbo.Products dbop on dbocp.ProductID = dbop.ProductID " +

            "left join dbo.PaymentSchedules dbops on dbocp.CustomerProductId = dbops.CustomerProductId " +

                "where dbop.ProductID is not null " +

                "Order by dboc.CustomerNumber";

​

            SQL_Connection = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand(sql, SQL_Connection);

            SqlDataReader dataReader;

            SQL_Connection.Open();

            dataReader = command.ExecuteReader();

​

            Application xlApp = new Application();

            if (xlApp == null)

            {

                Console.WriteLine("Excel is not properly installed!!");

                return;

            }

            try

            {

                Console.WriteLine("Excel file being created.");

                CreateExcelDoc(xlApp, out Workbook xlWorkBook, out Worksheet xlWorkSheet, out object misValue);

                CreateHeaders(xlWorkSheet);

                int counter2 = 2;

​

                // Do a few more bits in here ... add more values depending on data 

                // this might not work if we need to add more stuff

​

                int[] ReadValues = { 0, 1, 36, 31, 4, 2, 3, 7, 8, 11, 12, 13, 14, 15, 16, 17, 18, 20,57 };

                int[] WriteValues = { 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,20 };

​

                Console.Write("Processing");

                while (dataReader.Read())

                {

                    xlWorkSheet.Cells[counter2, 1] = counter2 - 1;

                    for (int i = 0; i < ReadValues.Length; i++)

                    {

                        // For DOB only at the moment ...

                        if (ReadValues[i] == 6 && ReadValues[i].ToString().Length > 10)

                        {

                            xlWorkSheet.Cells[counter2, WriteValues[i]] = dataReader.GetValue(ReadValues[i]).ToString().Substring(0, 10);

                        }

                        else

                        {

                            xlWorkSheet.Cells[counter2, WriteValues[i]] = dataReader.GetValue(ReadValues[i]).ToString();

                        }

                    }

                    Console.Write(".");

                    counter2++;

                }

                SQL_Connection.Close();

                SaveAndCloseExcel(xlApp, xlWorkBook, xlWorkSheet, misValue);

            }

            catch (Exception ex)

            {

                Console.WriteLine("Excel file not created. Error: {ex}");

                Console.ReadLine();

            }

        }

        public static void CreateExcelDoc(Application xlApp, out Workbook xlWorkBook, out Worksheet xlWorkSheet, out object misValue)

        {

            misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        }

        public static void CreateHeaders(Worksheet xlWorkSheet)

        {

            int counter = 1;

            foreach (string item in headers)

            {

                xlWorkSheet.Cells[1, counter] = item;

                xlWorkSheet.Cells[2, counter] = "N/A";

                counter++;

            }

        }

        public static void SaveAndCloseExcel(Application xlApp, Workbook xlWorkBook, Worksheet xlWorkSheet, object misValue)

        {

            string path = @"d:\temp\reports\";

            System.IO.Directory.CreateDirectory(path);

            // watch the d:\foldername\filename.* part pf the save, for other file formats it would be d:\\foldername\filename.*

            xlWorkBook.SaveAs(@"d:\temp\CustomerOnboardingReport" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx", XlFileFormat.xlOpenXMLWorkbook, misValue,

                        misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            xlWorkBook.Close(true, misValue, misValue);

            xlApp.Quit();

            Marshal.ReleaseComObject(xlWorkSheet);

            Marshal.ReleaseComObject(xlWorkBook);

            Marshal.ReleaseComObject(xlApp);

            Console.WriteLine("Done.");

            Console.WriteLine("Excel file created.");

        }

    }

}