Exporting to Excel using Excel Application object

Tweet

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

I already blogged some post on Exporting to Excel using Grid View previously. On the same series, this post concentrates on Exporting to excel using Microsoft.Office.Interop.Excel namespace.

For exporting functionality, we are going to use ApplicationClass class defined under Microsoft.Office.Interop.Excel namespace. So to implement this concept, we must add Microsoft.Office.Interop.Excel assembly reference in the project.

Note:

• This implementation can be used in both ASP.NET and Windows Applications as it does not required Grid View or any ASP.NET controls.
• As the code using Excel objects, it required Excel Application installed in the server (where the code runs). It means, if the application is an ASP.NET application - the Web Server must have Excel 2007 software installed or if the application is a Win32, WPF etc., applications then the client system must have Excel 2007 installed).
• I have Office 2007 installed in my system, so I reference the Microsoft.Office.Interop.Excel DLL version 12.0.0.0. If you have other Office version, please refer the respective DLL version.
• 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.

The requirement in this implementation is to have a button (btnExport) control on Web Page. When the user clicks the button, the system should fetch the order details and export an Excel sheet. The system should not depend on any ASP.NET control (like Grid View) for exporting the data and it should use Excel ApplicationClass object.

To achieve this requirement I have done the following steps -

Step 1: Defining ExcelAppExporter class

I have a reusable class ExcelAppExporter in the project. This is a generic class which accepts an IList collection and list of exporting column names with its properties as input.

Below is the code for ExcelAppExporter class (look at the ExcelAppExportor.cs file in the source code - Sorry for spell mistake, I will change it soon.)

/// <summary>
/// Class to Export the Data to Excel sheet. Input must be a IList colection
/// </summary>
/// <typeparam name="T">Entity</typeparam>
public class ExcelAppExporter<T>
{
    /// <summary>
    /// Constructor
    /// </summary>
    public ExcelAppExporter()
    {
        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();
            }
        }
    }
}

As defined in the code, the class required two types of inputs.

1. An IList collection, which contains list of entity objects. The type of the entity object will be defined while creating the ExcelAppExporter object.
2. An entity object from the IList collection may contain many attributes, but we may required to export only some of the attributes (here attributes defines the columns and entity objects defines rows in excel sheet). So to define the list of ExportColumn for exporting to excel from the entity object, we have a class ExportColumn.
   So, to let the system understand what are the columns needs to be exported to excel sheet - the column name (attribute name) must be provided as input to the ExcelAppExporter. To define the export column details, we have another entity class ExportColumn which is used for adding the column using AddExportColumn method defined with ExcelAppExporter.

The ExportColumn class is defined below:

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

As already discussed, the ExportColumn class is used for adding the exporting column name, header text and format of the data. The format of the data should as per the format defined in excel sheet (Select the column in Excel Sheet -> right click the column -> select Format Cells... -> Define any format -> Select the Custom -> tab -> Copy the Type value).

1. ExportColumn(string ColumnName, string HeaderText) - Defining column name of the database table/view/query and the column header text on the Excel Sheet.
2. ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, row index where the data should start to export, column index where the data should start to export.
3. ExportColumn(string ColumnName, string HeaderText, string ValueFormat) - Defining column name, column header text, value format of the data.
4. ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, value format of the data, row index where the data should start to export, column index where the data should start to export.

The btnExport button event in the code behind will create object of ExcelAppExporter class and pass the records as IList collection. Below code shows how it works.

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

    string strExportFileName = Path.Combine(@"C:\Temp", Guid.NewGuid().ToString("N")) + ".xls";
    exportor.ExportFileName = strExportFileName;
    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();
}
/// <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;
    }
}

In the code, I use an entity class for holding order details. The Order class defined below.

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

By running the code, I got the following screen.

The output of the Excel Sheet will be

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

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