Moving data from SQL Server to Excel using C#

Question

I'm trying to move data from SQL Server 2012 to Excel 2010 via a C# form using WinForms. The data I have contains a column called "Appeal" for which I would like to create separate worksheets inside an excel workbook. I would like to label the tabs with the name of the "Appeal". The part I can't seem to get is actually putting the data into the related tabs that I just created and labeled. Can anyone with more experience help me out? Note the table and values are a simplified version of the actual table.

CREATE TABLE [dbo].[Appeals](
    [Appeal] [nchar](10) NULL,
    [Member_ID] [varchar](10) NULL,
    [Amount] [money] NULL,
    [DateGiven] [date] NULL
) ON [PRIMARY]


insert into Appeals values ('6Y','101',50,'2-15-2016')
insert into Appeals values ('6Y','209',100,'2-14-2016')
insert into Appeals values ('6Y','218',200,'2-12-2016')
insert into Appeals values ('7G','102',300,'1-15-2016')
insert into Appeals values ('7G','209',20,'2-21-2016')
insert into Appeals values ('WR','108',50,'1-22-2016')
insert into Appeals values ('WR','198',100,'1-29-2016')
insert into Appeals values ('WR','303',500,'1-31-2016')
insert into Appeals values ('WR','312',150,'7-19-2016')


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 System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 


       private void button7_Click(object sender, EventArgs e)
        {
            SqlConnection cnn;
            string connectionString = null;

            object misValue = System.Reflection.Missing.Value;

            connectionString = "data source=D7010-H14NBZ1\\SQLEXPRESS;initial catalog=iTest;user id=TestUser;password=testPW;";
            StringBuilder query = new StringBuilder();

            cnn = new SqlConnection(connectionString);
            cnn.Open();
            query.Append("SELECT        Appeal, Member_ID, ");
            query.Append("Amount, DateGiven ");
            query.Append("FROM  dbo.Appeals ");
            query.Append("WHERE        (Appeal IN (N'6Y', N'7G', N'WR')) ");
            query.Append("ORDER BY Appeal DESC");
            SqlDataAdapter dscmd = new SqlDataAdapter(query.ToString(), cnn);
            DataTable dt = new DataTable();
            dscmd.Fill(dt);

            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;

            oXL = new Excel.Application();
            oXL.Visible = true;

            oWB = (Excel._Workbook)(oXL.Workbooks.Add(misValue));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            try
            {
                DataTable dtAppCode =
                        dt.DefaultView.ToTable(true, "Appeal");

                foreach (DataRow appcode in dtAppCode.Rows)
                {
                    oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
                    oSheet.Name = appcode[0].ToString().Replace(" ", "").
                        Replace("  ", "").Replace("/", "").
                            Replace("\\", "").Replace("*", ""); ;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                GC.Collect();
            }

            oXL.Visible = true;
            oXL.UserControl = true;

            oWB.SaveAs(@"H:\Appeals2.xlsx",
                AccessMode: Excel.XlSaveAsAccessMode.xlShared);

        }
      }
    }

Answer 1

This is know as exporting data from SQL Database to Excel using C#. It's really easy to implement and understand.

CodePlex provides an API known as ClosedXML. You can find it here : https://closedxml.codeplex.com/

ClosedXML allows to create different workbooks and worksheets to export SQL Data.

Using ClosedXML DLL you can export data very easily.

protected void button7_Click(object sender, EventArgs e)
    {
    string connectionstring;

    connectionstring = """data source=D7010-H14NBZ1\\SQLEXPRESS;initial catalog=iTest;user id=TestUser;password=testPW;";

    string query = "SELECT Appeal, Member_ID, Amount, DateGiven FROM  dbo.Appeals WHERE Appeal IN (N'6Y', N'7G', N'WR') ORDER BY Appeal DESC";

    SqlConnection connection = new SqlConnection(connectionstring);
        connection.Open();

        SqlCommand command = new SqlCommand(query, connection);

        DataTable data = new DataTable();

        using (SqlDataAdapter a = new SqlDataAdapter(command))
        {
            a.Fill(data);
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(data, "Excel Export");

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=Excel Export.xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }
}

This will help you for sure :)

Answer 2

    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 System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel; 

    namespace WindowsFormsApplication8
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                SqlConnection cnn;
                string connectionString = null;
                string data = null;
                int i = 0;
                int j = 0;
                int h = 1;
                int Appeal_ID = 20;
                object misValue = System.Reflection.Missing.Value;

                label1.Text = "Processing......";

                connectionString = "data source=D7010-H14NBZ1\\SQLEXPRESS;initial catalog=itest;user id=xxxx;password=xxxx;";
                StringBuilder query = new StringBuilder();

                cnn = new SqlConnection(connectionString);
                cnn.Open();

                Excel.Application oXL;
                Excel._Workbook oWB;
                Excel._Worksheet oSheet;

                oXL = new Excel.Application();
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(misValue));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;

                for (h = 48; h >= 1; h--)
                {
                    query.Append("SELECT     TOP (100) PERCENT dbo.FDN_GivingAppeal_Main.Appeal_ID, dbo.FDN_GivingAppeal_Main.ID, dbo.FDN_GivingAppeal_Main.Appeal,                 dbo.vBoCsContact.MemberType, ");
                    query.Append("dbo.vBoCsContact.Title, dbo.vBoCsContact.Prefix, dbo.vBoCsContact.FirstName, dbo.vBoCsContact.MiddleName AS MI, dbo.vBoCsContact.LastName, ");
                    query.Append("dbo.vBoCsContact.Suffix, dbo.vBoCsContact.Designation, dbo.vBoCsContact.Informal, dbo.vBoCsContact.Email, dbo.vBoCsContact.Company, ");
                    query.Append("dbo.vBoCsAddress.Address1, dbo.vBoCsAddress.Address2, dbo.vBoCsAddress.City, dbo.vBoCsAddress.StateProvince, dbo.vBoCsAddress.Zip, ");
                    query.Append("dbo.vBoCsAddress.Country, dbo.vBoCsAddress.Phone, dbo.FDN_GivingAppeal_Main.Amount AS YrTotal_LastGivingYr,               dbo.FDN_GivingAppeal_Main.LastTransDate, ");
                    query.Append("dbo.FDN_GivingAppeal_Main.LastTransAmt, dbo.FDN_GivingAppeal_Main.LargestGiving, dbo.FDN_GivingAppeal_Main.LifetimeTotal, ");
                    query.Append("dbo.FDN_GivingAppeal_Main.FiscalYear AS LastGivingYr, dbo.FDN_GivingAppeal_Main.CapitalCampaign, dbo.FDN_GivingAppeal_Main.P2GScore, ");
                    query.Append("dbo.FDN_GivingAppeal_Main.InnerCircle ");
                    query.Append("FROM         dbo.FDN_GivingAppeal_Main INNER JOIN ");
                    query.Append("dbo.FDN_AppealCode_Sort ON dbo.FDN_GivingAppeal_Main.Appeal_ID = dbo.FDN_AppealCode_Sort.ID AND  ");
                    query.Append("dbo.FDN_GivingAppeal_Main.Appeal = dbo.FDN_AppealCode_Sort.Appeal_code INNER JOIN  ");
                    query.Append("dbo.vBoCsContact ON dbo.FDN_GivingAppeal_Main.ID = dbo.vBoCsContact.ID INNER JOIN ");
                    query.Append("dbo.vBoCsAddress ON dbo.vBoCsContact.ID = dbo.vBoCsAddress.ID ");
                    query.Append("WHERE    (dbo.vBoCsAddress.PreferredMail = 1) AND (dbo.vBoCsAddress.BadAddress <> 'BAD') AND  (dbo.FDN_GivingAppeal_Main.Appeal_ID = " +              Appeal_ID + ") AND   Appeal_Sort in(" + h + ") ");
                    query.Append("ORDER BY dbo.FDN_AppealCode_Sort.Appeal_Sort desc, dbo.vBoCsContact.LastName, dbo.vBoCsContact.FirstName; ");
                    SqlDataAdapter dscmd = new SqlDataAdapter(query.ToString(), cnn);
                    DataTable dt = new DataTable();
                    dscmd.Fill(dt);
                    query.Clear();

                    try
                    {

                        oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
                        oSheet.Name = dt.Rows[0]["Appeal"].ToString().Replace(" ", "").
                            Replace("  ", "").Replace("/", "").
                                Replace("\\", "").Replace("*", "");


                        for (i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            for (j = 0; j <= dt.Columns.Count - 1; j++)
                            {
                                data = dt.Rows[i].ItemArray[j].ToString();
                                oSheet.Cells[i + 1, j + 1] = data;

                            }
                        }

                    }

                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        GC.Collect();
                    }
                }


                oWB.SaveAs(@"H:\4862appeals1.xlsx",
                   AccessMode: Excel.XlSaveAsAccessMode.xlShared);

                oWB.Close(true, misValue, misValue);
                oXL.Quit();

                label1.Text = "Click button to start";     
            }
        }
    }

Answer 3

With the EPPlus NuGet package, it's very easy.

public class TestObject
{
    public int Col1 { get; set; }
    public int Col2 { get; set; }
    public string Col3 { get; set; }
    public DateTime Col4 { get; set; }
}

[TestMethod]
public void LoadFromCollection_MemberList_Test()
{
    //https://stackoverflow.com/questions/32587834/epplus-loadfromcollection-text-converted-to-number/32590626#32590626

    var TestObjectList = new List<TestObject>();
    for (var i = 0; i < 10; i++)
        TestObjectList.Add(new TestObject {Col1 = i, Col2 = i*10, Col3 = (i*10) + "E4"});

    //Create a test file
    var fi = new FileInfo(@"c:\temp\LoadFromCollection_MemberList_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        //Do NOT include Col1
        var mi = typeof (TestObject)
            .GetProperties()
            .Where(pi => pi.Name != "Col1")
            .Select(pi => (MemberInfo)pi)
            .ToArray();

        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromCollection(
            TestObjectList
            , true
            , TableStyles.Dark1
            , BindingFlags.Public| BindingFlags.Instance
            , mi);

        pck.Save();
    }
}

Notice that Col1 is NOT in the output: