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

This article will help you how to export SQL data to excel. For this, we will be using Microsoft.Interop.Office.Excel namespace and C#. The classes and interfaces in Microsoft.Interop.Office.Excel gives support for interoperability between the COM object model of Microsoft Excel 2010 and managed applications that automate xls or xlsx files.

For this tutorial we have to add reference to Microsoft.Interop.Office.Excel for this Microsoft office has to be installed. So before starting make sure you have Microsoft Office installed on your machine. If you do not have already installed it click here to install Microsoft Office 2010 Primary Interop Assemblies.

This tutorial will read data from Products table of Northwind database and write it to excel. If you do not have Northwind database get it from here.

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))

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

    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)


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 = 

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; 
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;

    AccessMode: Excel.XlSaveAsAccessMode.xlShared);

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

Source code on Git hub Source Code on Github

Speak your mind
Please login to post your comment!

  • geeksarray user
    08/18/2016 09:13 AM Kishore

    Excellent. I had a jump start

  • geeksarray user
    06/26/2017 09:16 PM Murali

    Hi, I can you share the design view for this...!

  • geeksarray user
    04/05/2019 01:10 PM BradCollins

    I will bookmark your site and take the feeds additionally…You might need when you want to read the file from server and export to the client.

  • geeksarray user
    04/07/2019 09:41 PM Amitgrover07

    Can we create excel using predifined rows in c# code...for example a new file with headers gets created after every 10th row in database...please help.

Blog Search

If you like my content please feel free to buy me coffee. Buy Me A Coffee