Create Excel Workbook via Visual Studio desktop application Button Click and fill sql data

Question

Visual Studio 2012 for Windows Desktop Microsoft SQL Server 2008 Excel 2007

I have created a desktop application for data entry into a SQL database using databinding. I now need to create a report to extract this information from the SQL database from the client side of the application in the form of an excel workbook.

My question is: How can I export all columns from a SQL view into an excel workbook from my desktop application?

I have the ground work below, but not sure how to put the view data into the worksheet.

private void button_Click(object sender, EventArgs e)
{
    try
    {
        Excel.Application oXL = new Excel.Application();
        oXL.Visible = true;

        Excel.Workbook oWB = oXL.Workbooks.Add();
        Excel.Worksheet oSheet = oWB.ActiveSheet;
        oSheet.Cells[1, 1] = "Hello World!";

    }
    catch (Exception ex)
    {
    MessageBox.Show("Error");
    }
}

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
You have the basics right, I recommend you to get the data of your query and input it cell by cell in your workbook, also you may want to disable screen updates in Excel. — Rafael
@ChipHappens, please put your updated code as an answer to your own question so it's easy to locate to future visitors. — Rafael
@Rafael It said i could not do it for another couple of hours (8 from the time i asked the question) because I don't have enough clout on this forum — ChipHappens

Unknown Unknown · Accepted Answer · 2018-05-14T16:13:49

Solution by OP.

Issue solved with below code. Might not be the prettiest, but it got me through the day.

using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Server;

private void button_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = dataSet1.Properties.Settings.Default.ConnectionString.ToString();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM dbo.Table";

        SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "dbo.Table");
        DataTable dt = ds.Tables["dbo.Table"];

        Excel.Application oXL = new Excel.Application();
        Excel.Workbook oWB = oXL.Workbooks.Add();
        Excel.Worksheet oSheet = oWB.ActiveSheet;

        //Puts column headers starting in Row 5
        int col = 0, row = 5;
        foreach (DataColumn dc in dt.Columns)
        {
            oXL.Cells[row, ++col] = dc.ColumnName.ToString();
        }

        //Fills data starting in row 6
        foreach (DataRow dr in dt.Rows)
        {
            row++;
            col = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                oXL.Cells[row, ++col] = dr[dc.ColumnName];
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error");
    }
}

Create Excel Workbook via Visual Studio desktop application Button Click and fill sql data

1 Answers