How to export SQL data to excel using Microsoft.Interop.Office.Excel and C#

Follow below steps to Export SQL Data to Excel

Create new Console Application by clicking on File -> New -> Project -> Console Application menu of visual studio.

Name it as ExportProductsToExcel

If you just want to convert your data to CSV you can see Convert DataTable to CSV..

Reference to Microsoft.Office.Interop.Excel

Open solution explorer and right click on References select Add Reference.

From Add Reference dialogbox click on .NET, select Microsoft.Office.Interop.Excel and click Ok. Microsoft Office should be installed on your machine to use Microsoft.Office.Interop libarary. If you do not have already installed it click here to install Microsoft Office 2010 Primary Interop Assemblies.

Open Program.cs file of application and add below using statements.

using System.Reflection;
using System.Data.SqlClient;   
using System.Runtime.InteropServices;
using SQL = System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;

Read data from SQL Server

Create a connection using Visual Studio Server Explorer to Northwind.Products table. You may have more detail look on Access SQL Server database from console application

string conString = "your connection string";
StringBuilder query = new StringBuilder();
query.Append("SELECT Categories.CategoryName ");
query.Append(",[ProductID], [ProductName], [SupplierID] ");
query.Append(",[QuantityPerUnit], [UnitPrice], [UnitsInStock] ");
query.Append(",[UnitsOnOrder], [ReorderLevel], [Discontinued] ");
query.Append("FROM [northwind].[dbo].[Products] ");
query.Append("JOIN Categories ON Categories.CategoryID = Products.CategoryID ");
query.Append("ORDER BY Categories.CategoryName ");

SQL.DataTable dtProducts = new SQL.DataTable();
using (SqlConnection cn = new SqlConnection(conString))
{
    using (SqlDataAdapter da = new SqlDataAdapter(query.ToString(), cn))
    {
        da.Fill(dtProducts);
    }
}
    

Create Excel objects

Create excel objects of Application, Workbook, Worksheet which will handle interoperability and communication between COM and .NET code.

Add below code which creates required objects

Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
            
oXL = new Excel.Application();
oXL.Visible = true;

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

    

Create Category wise excel Worksheet

This tutorial will create sheets for each category and product will be displayed as per their category. We need to get unique categories from Products DataTable.

Add below code which get unique category names, creates a sheet for each category, name the category as mentioned in DataTable

try
{
    SQL.DataTable dtCategories = 
            dtProducts.DefaultView.ToTable(true, "CategoryName");

    foreach (SQL.DataRow category in dtCategories.Rows)
    {
        oSheet = (Excel._Worksheet)oXL.Worksheets.Add();
        oSheet.Name = category[0].ToString().Replace(" ", "").
            Replace("  ", "").Replace("/", "").
                Replace("\\", "").Replace("*", "");
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);  
}
finally
{   
    Marshal.ReleaseComObject(oWB);
}

    

For all program.cs code click her.

Add column names to excel sheet

Below code will read Column names from DataTable and add it to Excel sheet cells. It also makes column names as Bold and its alignment to Vertical.

It creates string array which holds column names and set it to excel cells. This uses Worksheet's get_Range method to set data, font and alignment of cells with in range.

string[] colNames = new string[dtProducts.Columns.Count];

int col = 0;

foreach (SQL.DataColumn dc in dtProducts.Columns)
    colNames[col++] = dc.ColumnName;

char lastColumn = (char)(65 + dtProducts.Columns.Count - 1);

oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment 
            = Excel.XlVAlign.xlVAlignCenter;

    

Add DataRows data to Excel

Below code will read the data from Products DataTable according to Category. It creates the DataRow array of one category and then process it to two dimensional string array.

Values of string array will be set to excel sheets cell using get_Range method.

If column value of ReorderLevel is less than UnitsOnOrder then the background color of corresponding excel row will be set as Red.

SQL.DataRow[] dr = 
        dtProducts.Select(string.Format("CategoryName='{0}'",
                                    category[0].ToString()));

string[,] rowData = 
        new string[dr.Count(), dtProducts.Columns.Count];

int rowCnt = 0;
int redRows = 2;
foreach (SQL.DataRow row in dr)
{                         
    for (col = 0; col < dtProducts.Columns.Count; col++)
    {
        rowData[rowCnt, col] = row[col].ToString();
    }

    if (int.Parse(row["ReorderLevel"].ToString()) 
            < int.Parse(row["UnitsOnOrder"].ToString()))
    {
        Range range = 
                oSheet.get_Range("A" + redRows.ToString(), "J"
                                        + redRows.ToString());
        range.Cells.Interior.Color = System.Drawing.Color.Red; 
    }
    redRows++;
    rowCnt++;
}
oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;

    

Save Product Excel sheet

Below code will save and open the Products excel sheet on C drive.

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

oWB.SaveAs("C:\Products.xlsx",
    AccessMode: Excel.XlSaveAsAccessMode.xlShared);
 

Source Code on Github