Exporting the data to a Templated Excel sheet using Excel Application object

Tweet

This post is a continuation of a series. Please look at the end of this post for more information.

In many scenarios, we required to export the GridView data to a template Excel sheet. For example, I have an Excel sheet which has three sheets,

Sheet1: Defining set of column headers. The data will be populated from the GridView (or from database) by code
Sheet2: A Pivot table defined as per the first sheet for some analysis
Sheet3: A line Chart based on the pivot table.

In this scenario, we have to populate the data to the first sheet (Sheet1) in predefined format. It means, the columns will be already defined in the template and the code must export to the predefined columns. Once the data populated, the user can just refresh the second and third (Pivot and Chart) sheets to get the analyzed data.

This post explains about how to implement exporting the data to a predefined excel template sheet from the code.

I use a Generic class for doing this implementation, so it can be used for other projects without doing any change. But, this class will work only with IList collection. So the data must be in a List collection and given to the generic class as input for exporting to excel. The data is in any other format such as DataTable must be converted to IList before using this code.

In this example, I have taken the following requirement for implementation.

1. A page contains a button for exporting to Excel
2. There will be a Template Excel sheet in the application path which has two sheets. One is used for populating data and another one is for defining a pivot table, will be refreshed manually after the data populated.
3. When clicking a button, the application should export all the data to the excel sheet and save the file.

For the implementation of this requirement, I have a project with three class file and an aspx file. The class files are

1. ExcelAppExportor.cs – a Generic Class for exporting the data to excel sheet.
   Basically this class does the following
   
   • Defining a Generic class - ExcelAppExportor, which get IList collection as input.
   • Declare an excel application object using ApplicationClass and related Workbook, Sheets, Worksheet.
   • If the data needs to be exported in a new Excel file, then create a new Workbook and Sheet.
   • If the Header text needs to exported, export it.
   • Export the Data.
   • Close the workbook by saving it.
2. ExportColumn.cs – An entity class used for defining the list of columns for export. This class will be used in ExcelAppExportor class.
3. Order.cs – An entity class for holding actual data will be exported.
4. Default.aspx – Contains a button, used for triggering export functionality.

This code uses ApplicationClass class in Microsoft.Office.Interop.Excel namespace. So to implement this code, we must add reference to Microsoft.Office.Interop.Excel assembly in the project.

I use Northwind database for getting the data to the excel sheet. So to use the working code, make sure you have Northwind database in the SQL Server and change the connection string. Below is the code for each class.

Code for ExcelAppExportor class (ExcelAppExportor.cs)

using System;
using Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

namespace ImageToolTip
{
    /// <summary>
    /// Class to Export the Data to Excel sheet. Input must be a IList colection
    /// </summary>
    /// <typeparam name="T">Entity</typeparam>
    public class ExcelAppExportor<T>
    {
        /// <summary>
        /// Constructor
        /// </summary>
        public ExcelAppExportor()
        {
            TemplateFileName = string.Empty;
            IsExportIncludesHeader = true;
            ExportSheetName = "Export";
        }
        /// <summary>
        /// Holds the Data
        /// </summary>
        private IList<T> ListData;

        /// <summary>
        /// Holds the Export Columns
        /// </summary>
        private IList<ExportColumn> ExportColumns;

        /// <summary>
        /// Add the export column
        /// </summary>
        /// <param name="ExportColumn">ExportColumn</param>
        public void AddExportColumn(ExportColumn ExportColumn)
        {
            if (ExportColumns == null) ExportColumns = new List<ExportColumn>();
            ExportColumns.Add(ExportColumn);
        }
        /// <summary>
        /// List contains the list of entity object which are export to excel
        /// </summary>
        public IList<T> BindDataList
        {
            get { return ListData; }
            set { ListData = value; }
        }
        /// <summary>
        /// File Name of the Export output file
        /// </summary>
        public string ExportFileName { get; set; }
        
        /// <summary>
        /// Template File Name - Using Template file to Export
        /// </summary>
        public string TemplateFileName { get; set; }

        /// <summary>
        /// Sheet name to Export the data
        /// </summary>
        public string ExportSheetName { get; set; }

        /// <summary>
        /// Is the header data needs to be exported
        /// </summary>
        public bool IsExportIncludesHeader { get; set; }

        public void Export()
        {
            try
            {
                #region Filling Export Columns
                // Check the columns to export is mentioned, if not
                if (ExportColumns == null)
                {
                    // Create an entity object. If the list count == 0 ??? - needs to be handled from client
                    T tEntity = ListData[0];

                    // Export the columns to export from the property name
                    ExportColumns = new List<ExportColumn>();
                    foreach (System.Reflection.PropertyInfo propertyInfo in tEntity.GetType().GetProperties())
                        ExportColumns.Add(new ExportColumn(propertyInfo.Name, propertyInfo.Name));
                }
                #endregion

                // Create excel application
                Application ExcelApp = new ApplicationClass();
                Workbook workbook;
                Sheets sheets;
                Worksheet worksheet;

                // Is Export needs to be exported to a Template file
                if (TemplateFileName != string.Empty)
                {
                    #region Load the Template file
                    // Load the work book
                    workbook = ExcelApp.Workbooks.Open(TemplateFileName, 0, false, 5, "", "", false,
                        XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                    sheets = workbook.Sheets;
                    worksheet = (Worksheet)sheets.get_Item(1); // To avoid unassigned variable error

                    bool IsWorkSheetFound = false;

                    //Check is there any worksheet with the name provided. If yes, clear all data inside to fill new data
                    for (int intSheetIndex = 1; intSheetIndex <= sheets.Count; intSheetIndex++)
                    {
                        worksheet = (Worksheet)sheets.get_Item(intSheetIndex);
                        if (worksheet.Name.ToString().Equals(ExportSheetName))
                        {
                            IsWorkSheetFound = true;
                            break;
                        }
                    }

                    // If No work sheet found, add it at the last
                    if (!IsWorkSheetFound)
                    {
                        worksheet = (Worksheet)workbook.Sheets.Add(
                            Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
                            Type.Missing, Type.Missing);
                        worksheet.Name = ExportSheetName;
                    }
                    #endregion
                }
                else
                {
                    #region Crate the Template File
                    // Adding new work book
                    workbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                    sheets = workbook.Sheets;

                    worksheet = (Worksheet)sheets.get_Item(1);

                    worksheet.Name = ExportSheetName;
                    #endregion
                }
                int intCol = 0;

                #region Populating the Header
                // If the header needs to exported. In templated files, normally we will have the headings
                if (IsExportIncludesHeader == true)
                {
                    // Exporting Header
                    foreach (ExportColumn exportColumn in ExportColumns)
                    {
                        Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex - 1)];
                        range.Select();
                        range.Value2 = exportColumn.HeaderText.ToString();
                        range.Columns.EntireColumn.AutoFit();
                        range.Font.Bold = true;
                    }
                }
                #endregion

                // Exporting Data 
                foreach (T tEntity in BindDataList)
                {
                    intCol = 0;
                    foreach (ExportColumn exportColumn in ExportColumns)
                    {
                        Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                        range.Select();
                        range.Value2 = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();
                        range.Columns.EntireColumn.AutoFit();
                    }
                }
  intCol = 0;
                foreach (ExportColumn exportColumn in ExportColumns)
                {
                    Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
                    if (exportColumn.ValueFormat != string.Empty)
                        range.Columns.EntireColumn.NumberFormat = exportColumn.ValueFormat;
                }
                try
                {
                    // Save the workbook with saving option
                    workbook.Close(true, ExportFileName, Type.Missing);
                    ExcelApp.UserControl = false;
                    ExcelApp.Quit();
                    ExcelApp = null;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                        p.Kill();
                }
            }
        }
    }

Code for ExportColumn class (ExportColumn.cs)

/// <summary>
    /// Holds the details of te columns to be exported to Excel from IList
    /// </summary>
    public class ExportColumn
    {
        /// <summary>
        /// Entity object(class) property name, mandatory
        /// </summary>
        public string ColumnName { get; set; }

        /// <summary>
        /// What needs to be shows in the Excel sheet for that col, mandatory
        /// </summary>
        public string HeaderText { get; set; }

        /// <summary>
        /// The format of the value. To get the format string, from the excel sheet -> Column -> Format Cells -> Number -> Custom -> Take the Type
        /// </summary>
        public string ValueFormat { get; set; }

        /// <summary>
        /// From which row the data needs to be exported, for template file it will be useful
        /// </summary>
        public int StartRowIndex { get; set; }

        /// <summary>
        /// On which column the data needs to be exports, So it can be any column. Useful for Templated file
        /// Needs to assign the Index of the column starting by 1
        /// A - 1, B - 2, C - 3 etc., Counting will include the hidden column
        /// </summary>
        public int ExcelColumnIndex { get; set; }

        public ExportColumn()
        {
            StartRowIndex = 1;
            this.ValueFormat = string.Empty;
            this.StartRowIndex = 1;
            this.ExcelColumnIndex = 0;
        }
        public ExportColumn(string ColumnName, string HeaderText)
        {
            this.ColumnName = ColumnName;
            this.HeaderText = HeaderText;
            this.ValueFormat = string.Empty;
            this.StartRowIndex = 1;
            this.ExcelColumnIndex = 0;
        }
        public ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex)
        {
            this.ColumnName = ColumnName;
            this.HeaderText = HeaderText;
            this.ValueFormat = string.Empty;
            this.StartRowIndex = StartRowIndex;
            this.ExcelColumnIndex = ExcelColumnIndex;
        }
        public ExportColumn(string ColumnName, string HeaderText, string ValueFormat)
        {
            this.ColumnName = ColumnName;
            this.HeaderText = HeaderText;
            this.ValueFormat = ValueFormat;
            this.StartRowIndex = 1;
            this.ExcelColumnIndex = 0;
        }
        public ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex)
        {
            this.ColumnName = ColumnName;
            this.HeaderText = HeaderText;
            this.ValueFormat = ValueFormat;
            this.StartRowIndex = StartRowIndex;
            this.ExcelColumnIndex = ExcelColumnIndex;
        }
    }
}

Code for Order class (Order.cs)

public class Order
{
    public string OrderID { get; set; }
    public string CustomerID { get; set; }
    public string CustomerName { get; set; }
    public DateTime OrderDate { get; set; }
    public double UnitPrice { get; set; }
    public int Quantity { get; set; }
    public double Discount { get; set; }
    public double TotalAmount { get; set; }
}

Default.aspx script

<form id="form1" runat="server">
<div>
    <asp:Button ID="btnNormalExport" runat="server" Text="Normal Export" OnClick="btnNormalExport_Click" />
    <asp:Button ID="btnTemplateExport" runat="server" Text="Export To Template File" OnClick="btnTemplateExport_Click" />
</div>
</form>

Default.aspx.cs codebehind.

/// <summary>
/// Template file is used
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnTemplateExport_Click(object sender, EventArgs e)
{
    try
    {
        ExcelAppExportor<Order> exportor = new ExcelAppExportor<Order>();
        exportor.BindDataList = OrderList();
        exportor.AddExportColumn(new ExportColumn("OrderID", "Order ID", 2, 1));
        exportor.AddExportColumn(new ExportColumn("CustomerID", "Customer ID", 2, 2));
        exportor.AddExportColumn(new ExportColumn("CustomerName", "Customer Name", 2, 3));
        exportor.AddExportColumn(new ExportColumn("OrderDate", "Order Date", "dd/mmm/yyyy", 2, 4));
        exportor.AddExportColumn(new ExportColumn("UnitPrice", "Unit Price", "[$$-409]#,##0.00_);([$$-409]#,##0.00)", 2, 5));
        exportor.AddExportColumn(new ExportColumn("Quantity", "Quantity", "##0", 2, 6));
        exportor.AddExportColumn(new ExportColumn("Discount", "Discount", "[$$-409]#,##0.00_);([$$-409]#,##0.00)", 2, 7));
        exportor.AddExportColumn(new ExportColumn("TotalAmount", "Total Amount", "[$$-409]#,##0.00_);([$$-409]#,##0.00)", 2, 8));

        string strExportFileName = Path.Combine(@"C:\Temp", Guid.NewGuid().ToString("N")) + ".xls";
        string strTemplateFileName = HttpContext.Current.Request.PhysicalApplicationPath;
        if (!strTemplateFileName.EndsWith("\\"))
            strTemplateFileName = strTemplateFileName + "\\";

        strTemplateFileName = strTemplateFileName + "Template.xlsx";

        exportor.ExportFileName = strExportFileName;
        exportor.TemplateFileName = strTemplateFileName;
        exportor.IsExportIncludesHeader = false;
        exportor.ExportSheetName = "Orders";
        exportor.Export();

        byte[] ExcelStream = File.ReadAllBytes(strExportFileName);

        Context.Response.ClearContent();
        Context.Response.ContentType = "application/ms-excel";
        Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "Product List"));
        Context.Response.Charset = "";
        Context.Response.BinaryWrite(ExcelStream);
        Context.Response.End();
    }
    catch (Exception ex)
    {
    }
}
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private IList<Order> OrderList()
{
    using (SqlConnection connection =
        new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
    {

        SqlCommand command = new SqlCommand(
                "Select Top 20 Orders.OrderID, Orders.CustomerID, Suppliers.CompanyName, Orders.OrderDate, " +
                "OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount, " +
                "((OrderDetails.Quantity * OrderDetails.UnitPrice) - OrderDetails.Discount) TotalAmount " +
                "From Orders Join [Order Details] OrderDetails On OrderDetails.OrderID = Orders.OrderID " +
                "Join Products ON Products.ProductID = OrderDetails.ProductID " +
                "JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
                "JOIN Categories ON Products.CategoryID = Categories.CategoryID ", connection);

        connection.Open();
        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

        IList<Order> orderList = new List<Order>();
        while (dr.Read())
        {
            Order order = new Order();
            order.OrderID = dr["OrderID"].ToString();
            order.CustomerID = dr["CustomerID"].ToString();
            order.CustomerName = dr["CompanyName"].ToString();
            order.OrderDate = Convert.ToDateTime(dr["OrderDate"]);
            order.UnitPrice = Convert.ToDouble(dr["UnitPrice"]);
            order.Quantity = Convert.ToInt32(dr["Quantity"]);
            order.Discount = Convert.ToDouble(dr["Discount"]);
            order.TotalAmount = Convert.ToDouble(dr["TotalAmount"]);

            orderList.Add(order);
        }
        return orderList;
    }
}

download the working example of source code in C# here and in VB here.

Following are some example of how to use the class from the code.

Template sheet where data needs to be exported

Note: When saving the template excel sheet, make sure you not selected the cells of Data sheet (where the data required to be exported - For ex: Figure 1). If it has been selected and saved - it will raise error "Select method of Range class failed" while exporting data at runtime.

Pivot table sheet which pulls data from Template sheet to provide analytical data

Template data after data exported

After refresh (right click the pivot table -> Refresh) the pivot table

The other links on Export to Excel from GridView:

1. Exporting to Excel from GridView - Getting Started
2. Exporting to Excel from GridView (All columns and rows - Normal Method)
3. Exporting to Excel from GridView (Adding & Removing columns with all rows)
4. Exporting to Excel from GridView (using a custom class without GridView)
5. Exporting to Excel using Excel Application object
6. Exporting the data to a Templated Excel sheet using Excel Application object
7. Exporting the data to Excel sheet with image embedded using Excel Application object