Export to Excel 2007 Format (.xlsx) with Proper Formatting

• Download demo project source files - 455 KB

Introduction

This tip shows how to export Data from an Entity DataSource / GridView / Datatable in ASP.NET to Excel 2007 (.xlsx) format, in which we can add and customize Title / Header of the created Excel File, we can define backgrounds, fonts, color and data types of columns.

Background

Since one month, I was facing a scenario where I have had to Export data / report from my ASP.NET webpage to Excel format. I know, I know ;), there are thousands of examples on the internet on how to export data from ASP.NET to Excel.

But in my case, the problems that I was facing were:

1. I was not able to add a customized header so that it depicts which report it is.
2. In case I added a report title by using some HTML / HTML Text Writer, the file created was supposed to be in Excel 97-2003 format & while opening it, office used to show a message like below:

To solve this problem, I used ClosedXML library available on CodePlex.

Using the Code

Let us now see how we can export data from EDMX result to Excel format with proper formatting.

Method to Convert EDMX Result to DataTable

public static DataTable CopyGenericToDataTable<T>(IEnumerable<T> items)
    {
        var properties = typeof(T).GetProperties();
        var result = new DataTable();

        //Build the columns
        foreach (var prop in properties)
        {
            // if value is of nullable type convert it to Int32
            if (prop.ToString().Contains("Nullable"))
            {
                result.Columns.Add(prop.Name, typeof (Int32));
            }
            else
            {
                result.Columns.Add(prop.Name, prop.PropertyType);
            }
        }

        //Fill the DataTable
        foreach (var item in items)
        {
            var row = result.NewRow();

            foreach (var prop in properties)
            {
                var itemValue = prop.GetValue(item, new object[] { });
                row[prop.Name] = itemValue;
            }

            result.Rows.Add(row);
        }

        return result;
    }

This method converts a Result set obtained from a EDMX to DataTable.

Code To Convert DataTable to Excel Format

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI.WebControls;
using ClosedXML.Excel;
using System.Collections;

public class VKSXLExporter
{
    
    public VKSXLExporter()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    /// <summary>
    /// Exports Data from Gridview  to Excel 2007/2010/2013 format
    /// </summary>
    /// <param name="Title">Title to be shown on Top of Exported Excel File</param>
    /// <param name="HeaderBackgroundColor">Background Color of Title</param>
    /// <param name="HeaderForeColor">Fore Color of Title</param>
    /// <param name="HeaderFont">Font size of Title</param>
    /// <param name="DateRange">Specify if Date Range is to be shown or not.</param>
    /// <param name="FromDate">Value to be stored in From Date of Date Range</param>
    /// <param name="ToDate">Value to be stored in To Date of Date Range</param>
    /// <param name="DateRangeBackgroundColor">Background Color of Date Range</param>
    /// <param name="DateRangeForeColor">Fore Color of Date Range</param>
    /// <param name="DateRangeFont">Font Size of Date Range</param>
    /// <param name="gv">GridView Containing Data. Should not be a templated Gridview</param>
    /// <param name="ColumnBackgroundColor">Background Color of Columns</param>
    /// <param name="ColumnForeColor">Fore Color of Columns</param>
    /// <param name="SheetName">Name of Excel WorkSheet</param>
    /// <param name="FileName">Name of Excel File to be Created</param>
    /// <param name="response">Page Response of the Calling Page</param>

    public string Export2Excel(string Title, XLColor HeaderBackgroundColor, XLColor HeaderForeColor, int HeaderFont,

                             bool DateRange, string FromDate, string ToDate, XLColor DateRangeBackgroundColor,
                             XLColor DateRangeForeColor, int DateRangeFont, DataTable gv, XLColor ColumnBackgroundColor,
                             XLColor ColumnForeColor, string SheetName, string FileName,HttpResponse response)
    {
        DataTable table = gv;
        if (gv != null)
        {
            //creating a new Workbook
            var wb = new XLWorkbook();
            // adding a new sheet in workbook
            var ws = wb.Worksheets.Add(SheetName);
            //adding content
            //Title
            ws.Cell("A1").Value = Title;
            if (DateRange)
            {
                ws.Cell("A2").Value = "Date Range :" + FromDate + " - " + ToDate;
            }
            else
            {
                ws.Cell("A2").Value = "";
            }

            //add columns
            string[] cols = new string[table.Columns.Count];
            for (int c = 0; c < table.Columns.Count; c++)
            {
                var a = table.Columns[c].ToString();
                cols[c] = table.Columns[c].ToString().Replace('_', ' ');
            }

            char StartCharCols = 'A';
            int StartIndexCols = 3;
            #region CreatingColumnHeaders
            for (int i = 1; i <= cols.Length; i++)
            {
                if (i == cols.Length)
                {
                    string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
                    ws.Cell(DataCell).Value = cols[i - 1];
                    ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
                    ws.Cell(DataCell).Style.Font.Bold = true;
                    ws.Cell(DataCell).Style.Fill.BackgroundColor = ColumnBackgroundColor;
                    ws.Cell(DataCell).Style.Font.FontColor = ColumnForeColor;
                }
                else
                {
                    string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
                    ws.Cell(DataCell).Value = cols[i - 1];
                    ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
                    ws.Cell(DataCell).Style.Font.Bold = true;
                    ws.Cell(DataCell).Style.Fill.BackgroundColor = ColumnBackgroundColor;
                    ws.Cell(DataCell).Style.Font.FontColor = ColumnForeColor;
                    StartCharCols++;
                }
            }
            #endregion

            //Merging Header

            string Range = "A1:" + StartCharCols.ToString() + "1";

            ws.Range(Range).Merge();
            ws.Range(Range).Style.Font.FontSize = HeaderFont;
            ws.Range(Range).Style.Font.Bold = true;
            
            ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
            ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
            if (HeaderBackgroundColor != null && HeaderForeColor != null)
            {
                ws.Range(Range).Style.Fill.BackgroundColor = HeaderBackgroundColor;
                ws.Range(Range).Style.Font.FontColor = HeaderForeColor;
            }

            //Style definitions for Date range
            Range = "A2:" + StartCharCols.ToString() + "2";

            ws.Range(Range).Merge();
            ws.Range(Range).Style.Font.FontSize = 10;
            ws.Range(Range).Style.Font.Bold = true;
            ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom);
            ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);

            //border definitions for Columns
            Range = "A3:" + StartCharCols.ToString() + "3";
            ws.Range(Range).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.RightBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.TopBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.BottomBorder = XLBorderStyleValues.Thin;

            char StartCharData = 'A';
            int StartIndexData = 4;

            char StartCharDataCol = char.MinValue;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {

                    string DataCell = StartCharData.ToString() + StartIndexData;
                    var a = table.Rows[i][j].ToString();
                    a = a.Replace("&nbsp;", " ");
                    a = a.Replace("&amp;", "&");
                    //check if value is of integer type
                    int val = 0;
                    DateTime dt = DateTime.Now;
                    if (int.TryParse(a, out val))
                    {
                        //    ws.Cell(DataCell).Style.NumberFormat.NumberFormatId = 15;
                        ws.Cell(DataCell).Value = val;
                    }
                    //check if datetime type
                    else if (DateTime.TryParse(a, out dt))
                    {
                        ws.Cell(DataCell).Value = dt.ToShortDateString();
                    }
                    ws.Cell(DataCell).SetValue(a);
                    StartCharData++;
                }
                StartCharData = 'A';
                StartIndexData++;
            }

            char LastChar = Convert.ToChar(StartCharData + table.Columns.Count - 1);
            int TotalRows = table.Rows.Count + 3;
            Range = "A4:" + LastChar + TotalRows;
            ws.Range(Range).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.RightBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.TopBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            //Code to save the file
            HttpResponse httpResponse = response;
            httpResponse.Clear();
            httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            httpResponse.AddHeader("content-disposition", "attachment;filename="+FileName);

            // Flush the workbook to the Response.OutputStream
            using (MemoryStream memoryStream = new MemoryStream())
            {
                wb.SaveAs(memoryStream);
                memoryStream.WriteTo(httpResponse.OutputStream);
                memoryStream.Close();
            }

            httpResponse.End();
            return "Ok";
        }
        else
        {
            return "Invalid GridView. It is null";
        }
    }    
}

Explaining the Above Code

using ClosedXML.Excel;

This is the namespace which needs to be imported in order to use this code. Make sure you have added reference to ClosedXML.dll and DocumentFormat.OpenXml.dll.

//creating a new WorkBook
var wb = new XLWorkbook();
// adding a new sheet in workbook
var ws = wb.Worksheets.Add("Test");

In the first line of the above code, we have created a new Excel workbook by creating a new object of WorkBook class and storing it in wb variable which we will use further.

The second line is used to add to new Sheet to workbook wb.

ws.Cell("A1").Value = "My Test Report";

We are adding the Title of our report to the very first cell. Further, we will Merge this cell so that it looks like a proper header, something like this:

if (DateRange)
            {
                ws.Cell("A2").Value = "Date Range :" + FromDate + " - " + ToDate;
            }
            else
            {
                ws.Cell("A2").Value = "";
            }

In this section of code, we will add something like report parameters, i.e., From Date & To Date. If it is set to true, it will display From Date - To Date in next cell A2, which will again be merged and will be aligned to right.

//add columns
            string[] cols = new string[table.Columns.Count];
            for (int c = 0; c < table.Columns.Count; c++)
            {
                var a = table.Columns[c].ToString();
                cols[c] = table.Columns[c].ToString().Replace('_', ' ');
            }

Here, we are taking the Columns from data table and storing it in a string array, replacing '_' with space as in C# space is not allowed in names, it adds '_' which we surely don't want to display in Excel file. Our next step will be to write these columns to our Excel sheet.

char StartCharCols = 'A';
int StartIndexCols = 3;
#region CreatingColumnHeaders
for (int i = 1; i <= cols.Length; i++)
{
     if (i == cols.Length)
     {
        string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
        ws.Cell(DataCell).Value = cols[i - 1];
        ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
        ws.Cell(DataCell).Style.Font.Bold = true;
        ws.Cell(DataCell).Style.Fill.BackgroundColor = XLColor.Orange;
        ws.Cell(DataCell).Style.Font.FontColor = XLColor.White;
     }
     else
     {
        string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
        ws.Cell(DataCell).Value = cols[i - 1];
        ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
        ws.Cell(DataCell).Style.Font.Bold = true;
        ws.Cell(DataCell).Style.Fill.BackgroundColor = XLColor.Orange;
        ws.Cell(DataCell).Style.Font.FontColor = XLColor.White;
        StartCharCols++;
     }
}
#endregion

When we will be adding columns to Excel file, first let's get the cell address methodology in our mind.

First, Cell A1 is filled by title.

Second, Cell A2 is filled by Report Parameters.

That means our first column will be in A3, second in B3, third in C3 and so on. So here, we have to increment the first part of the Cell address that is A to B , then B to C, then C to D.

For this purpose, I created a char variable StartCharCols which will store A as initial value and we will increment it in the last of loop, which is being used to get values from cols array which we created in the previous step.

In this code, we are also formatting the columns we are adding, i.e., setting width, setting it bold, background color and ForeColor.

string Range = "A1:" + StartCharCols.ToString() + "1";
ws.Range(Range).Merge();
ws.Range(Range).Style.Font.FontSize = 15;
ws.Range(Range).Style.Font.Bold = true;
ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
if (HeaderBackgroundColor != null && HeaderForeColor != null)
{
  ws.Range(Range).Style.Fill.BackgroundColor = XLColor.White;
  ws.Range(Range).Style.Font.FontColor = XLColor.Black;
}

Here, we are merging the title of the Excel file which we had set earlier. Number of cells to be merged is calculated on the basis of number of columns. Then, we are setting its alignment and background and forecolors.

//Style definitions for Date range
           Range = "A2:" + StartCharCols.ToString() + "2";

           ws.Range(Range).Merge();
           ws.Range(Range).Style.Font.FontSize = 10;
           ws.Range(Range).Style.Font.Bold = true;
           ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom);
           ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);

In this section of code, we are defining Styles for the Date Range / Report Parameter field.

//border definitions for Columns
           Range = "A3:" + StartCharCols.ToString() + "3";
           ws.Range(Range).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
           ws.Range(Range).Style.Border.RightBorder = XLBorderStyleValues.Thin;
           ws.Range(Range).Style.Border.TopBorder = XLBorderStyleValues.Thin;
           ws.Range(Range).Style.Border.BottomBorder = XLBorderStyleValues.Thin;

This section created a nice border around the columns to give it a proper Excel like bordering, the one we select in Excel call All borders.

char StartCharData = 'A';
            int StartIndexData = 4;

            char StartCharDataCol = char.MinValue;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {

                    string DataCell = StartCharData.ToString() + StartIndexData;
                    var a = table.Rows[i][j].ToString();
                    a = a.Replace("&nbsp;", " ");
                    a = a.Replace("&amp;", "&");
                    //check if value is of integer type
                    int val = 0;
                    DateTime dt = DateTime.Now;
                    if (int.TryParse(a, out val))
                    {
                        ws.Cell(DataCell).Style.NumberFormat.NumberFormatId = 15;
                        ws.Cell(DataCell).Value = val;
                    }
                    //check if datetime type
                    else if (DateTime.TryParse(a, out dt))
                    {
                        ws.Cell(DataCell).Value = dt.ToShortDateString();
                    }
                    ws.Cell(DataCell).SetValue(a);
                    StartCharData++;
                }
                StartCharData = 'A';
                StartIndexData++;
            }

Here is the awesome section which is used to add Data to the Excel sheets below columns. Here we will start to add rows from A4 cell. First, we will increment the column value in which the first part, i.e., the Alphabet is to be incremented which is stored in char variable named StartCharData to No of columns and add data cell by cell on basis of columns, taking the data from datatable. Then, we will increment the row index stored in variable StartIndexData. This section consists of nested loops used to add data row by row.

char LastChar = Convert.ToChar(StartCharData + table.Columns.Count - 1);
            int TotalRows = table.Rows.Count + 3;
            Range = "A4:" + LastChar + TotalRows;
            ws.Range(Range).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.RightBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.TopBorder = XLBorderStyleValues.Thin;
            ws.Range(Range).Style.Border.BottomBorder = XLBorderStyleValues.Thin;

After Data is added, we will apply formatting and borders to it in the same way as we did above.

HttpResponse httpResponse = response;
           httpResponse.Clear();
           httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
           httpResponse.AddHeader("content-disposition", "attachment;filename=MyFile.xlsx");

           // Flush the workbook to the Response.OutputStream
           using (MemoryStream memoryStream = new MemoryStream())
           {
               wb.SaveAs(memoryStream);
               memoryStream.WriteTo(httpResponse.OutputStream);
               memoryStream.Close();
           }

           httpResponse.End();

Then, finally the code which is responsible to save the Excel file to the client side.

If you are using this code as a method, you can use the code given at the start which is a single method which can be called something like below:

protected void Button1_Click(object sender, EventArgs e)
   {
       var ctx = new MyEntities();
       GridView gv = new GridView();
       var res = ctx.sp_GetReports();
       DataTable tbl = VKSXLExporter.CopyGenericToDataTable(res);
       VKSXLExporter vksxl=new VKSXLExporter();
       vksxl.Export2Excel("My Exported Data", XLColor.White, XLColor.Black, 20, true, "01/06/2014", "01/07/2014", XLColor.White,
                          XLColor.Black, 10, tbl, XLColor.Orange, XLColor.White, "MyData",
                          "MyData.xlsx", Page.Response);


   }

A sneak look at the finally exported Excel file is below:

Points of Interest

There are many examples, but I faced a major problem in Exporting to Excel 2007 format with proper formatting and without any office warning. So I wrote this code and shared it with you all. Hope it helps you somewhere.