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.

Write SQL query to fetch Products data to DataTable. Add below code which connects to SQL server and fetch records Products table.


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

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. It 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<SQL.DataRow>(), 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);
    
        

Display Products in Microsoft Excel

Download source code.

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by Kishore at 8/18/2016 9:13:00 AM
Excellent. I had a jump start
post comment on dotnetmentors.com