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:
- I was not able to add a customized header so that it depicts which report it is.
- 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();
foreach (var prop in properties)
{
if (prop.ToString().Contains("Nullable"))
{
result.Columns.Add(prop.Name, typeof (Int32));
}
else
{
result.Columns.Add(prop.Name, prop.PropertyType);
}
}
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()
{
}
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)
{
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(SheetName);
ws.Cell("A1").Value = Title;
if (DateRange)
{
ws.Cell("A2").Value = "Date Range :" + FromDate + " - " + ToDate;
}
else
{
ws.Cell("A2").Value = "";
}
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
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;
}
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);
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(" ", " ");
a = a.Replace("&", "&");
int val = 0;
DateTime dt = DateTime.Now;
if (int.TryParse(a, out val))
{
ws.Cell(DataCell).Value = val;
}
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;
HttpResponse httpResponse = response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename="+FileName);
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.
var wb = new XLWorkbook();
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.
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.
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.
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(" ", " ");
a = a.Replace("&", "&");
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;
}
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");
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.