Wednesday, 12 January 2011

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



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


11 Responses to “Exporting the data to a Templated Excel sheet using Excel Application object”

  • Anonymous says:
    4 October 2011 at 20:19

    Hi, thanks for the example.
    I tried to implement your code, but I got this complie error:
    "The type or namespace 'Order' could not be found(are you missing a using directive or an assembly reference?"
    I think the
    private IList OrderList()
    {
    .....
    Should be implemented with Default.aspx.cs page, right? Please let me know if there are anything wrong with my implementation. thanks

  • Thiru says:
    10 October 2011 at 13:30

    It seems, the Order class is not refered in Default.cs file. It could be the issue if you are using different namespace for Default code behind and Order class.

    For Ex: If Default.aspx.cs file does not contains any namespace (If you are creating a Website, it wont add any namespace in code behind)

    public partial class _Default : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    }

    and in Order.cs file containsnamespace
    ExportToTemplateExcel
    {
    public class Order
    {
    }
    }

    If you are using Order class in Default code behind, it will throw the same error. To solve the issue,

    1. you can remove the namespace used in Order.cs file.
    2. You can add the reference in the Default code behind by adding below code
    Using ExportToTemplateExcel
    3. You can add the same namespace used in Order class (ExportToTemplateExcel) to the Default code behind and script.

    The requirement is to have same namespace in both Default code and Order class (if Default code does not have namespace defined, remove namespace in the Order class). Alternatively you can add the reference by Using/Import statement.

  • Anonymous says:
    21 July 2012 at 20:06

    Wow, this is great work. I got it working nicely. I need a tip though, how could I bypass the use of a class (Orders class)? Maybe display the data directly from the datareader or simular? See, my data changes and I just want to output string values but of no specific order or object. What I'm needing here is a one size fits all solution. Any tips?

  • Anonymous says:
    21 July 2012 at 20:13

    (continued)...
    I'm getting stuck on the line 'exportor.AddExportColumn(new ExportColumn("OrderID", "Order ID", 2, 1));'. How can I avoid explicitly referencing the OrderID property (1st param)? Any help is appreciated.

  • Thiru says:
    21 July 2012 at 21:53

    Hi, In this implementation, we need to pass an entity object (like Order) to the ExcelAppExportor class object. So in the code, I will be looping the collection of entity object (IList) and get individual entity object. Then get the value from the object using the property name and put to the Excel sheet.

    You can change this code for your requirement by passing datatable etc., If you want to bypass entity object (Order), there will be lots of changes in the code. AddExportColumn method will be used to add column of the entity property name.

  • Anonymous says:
    28 January 2013 at 10:27

    Can you tell me whether the data can be exported in one column only in different rows.

    eg.
    excel sheet should be like this:
    -----------------------------------------------------
    Column1 | Column2
    -------------------------------------------------------
    OrderID | 1
    CustomerID | 1.1
    CompanyName | XYZ
    UnitPrice | 10.00
    ------------------------------------------------------

    Headers are defined in one column and values to be exported in other column.

    Hope so you have understood my problem.

    kindly provide the solution to this through template excel sheet.

  • Thirumalai M says:
    28 January 2013 at 11:08

    Hi, Headers are in a column may not be possible in direct export. You may need to bring the headers as a data values of column 1 and export. Even in pivot table also the data values on columns will be distributed across the pivot table.

    If you can bring the header values such as OrderId, CustomerID, CompanyName, UnitPrice as a row data, it will be possible (considering GridView and Excel as per this example). Correct me I am wrong.

    You may need to consider some other way to do this I hope.

  • Cambodian Creation says:
    12 July 2013 at 05:27

    when i export to excel, i was error with this code
    "string strExportFileName = Path.Combine(@"C:\Temp", Guid.NewGuid().ToString("N")) + ".xls";"
    file not found. can anyone help me?

  • Thirumalai M says:
    12 July 2013 at 11:00

    Hi, The path C:\Temp folder need to be created before running this sample. As we are saving the exported file into that folder.

    Because the path has been given from the ASP.NET code (not created from class ExcelAppExportor), the code need to make sure the folder been created.

  • Akhterul Mustafa says:
    5 August 2013 at 23:58

    Do you have the solution as a zip file?

  • Apichart Lao-tep says:
    6 June 2014 at 11:37

    Thank for the example.

Post a Comment