This all started when I found myself having issues with the RadGrid export. I did a little research and developed the following solution:
The answer to #1 was ClosedXML developed by Manuel De Leon
The answer to #2 was to use the DotNetZipLib
#3 took a little work, but the result was an extender to IEnumerable:
public static String ToExport<dynamic> ( this IEnumerable<dynamic> DataList, String ExcelFileName )
{
var WorkBook = new XLWorkbook ();
var ws = WorkBook.Worksheets.Add ( "Sheet1" );
ws.Cell ( 1, 1 ).InsertTable ( DataList );
String FileName = Path.GetRandomFileName ();
FileName = Path.ChangeExtension ( FileName, "zip" );
String FullFileName = Path.Combine ( HttpContext.Current.Server.MapPath ( "~/Exports" ), FileName );
using ( MemoryStream wbStream = new MemoryStream () )
{
WorkBook.SaveAs ( wbStream );
wbStream.Seek ( 0, SeekOrigin.Begin );
using ( ZipFile Archive = new ZipFile ( FullFileName ) )
{
Archive.AddEntry ( ExcelFileName + ".xlsx", wbStream );
Archive.Save ();
}
}
return ( "~/Exports/" + FileName ).ToAbsoluteUrl ();
}
As you can see, I am creating an Excel 2007+ file and compressing it into a temporary zip file in a folder within the web site. I then return the URL for the file.
In the page, I set the source for a hidden iframe to the URL.
The other part of this is the communication from the client to the code behind. To do that, I use the PageMethods part of the RadScriptManager to call the method in the page code behind. This means that I will not have access to any page variables as it has to be a static method. In order to access the LinqDataSource query and any parameters it uses, I save them to Session variables in the Selecting event:
Session [ "DeviceBurnHoursPageDataSource" ] = this.GridDataSource;
Session [ "DeviceBurnHoursPageFilter" ] = this.ReportGrid.MasterTableView.FilterExpression;
Session [ "DeviceBurnHoursPageReportDate" ] = this.ReportDate;
And then in the page method:
IDataSource TheSource = ( IDataSource ) HttpContext.Current.Session [ "DeviceBurnHoursPageDataSource" ];
LinqDataSourceView TheView = TheSource.GetView ( "DefaultView" ) as LinqDataSourceView;
DateTime ReportDate = ( DateTime ) HttpContext.Current.Session [ "DeviceBurnHoursPageReportDate" ];
String Filter = ( String ) HttpContext.Current.Session [ "DeviceBurnHoursPageFilter" ];
if ( Filter != String.Empty )
{
String W = " && " + Filter.Replace ( " AND ", " && " );
TheView.Where += W;
}
TheView.WhereParameters [ "ReportDate" ].DefaultValue = ReportDate.ToString ( "MM/dd/yyyy" );
DataSourceSelectArguments Args = new DataSourceSelectArguments ();
return ( ( IEnumerable<dynamic> ) TheView.Select ( Args ) ).ToExport ( "Device Burn Hours " + ReportDate.ToString ( "yyyy-MM-dd" ) );
The returned file is ALL of the columns returned by the query in the order they are defined. In this case, it is suitable for creating the Pivot Table in Excel. If you are exporting from a RadGrid, you should end-up with a ready-to-use Excel file.
I am certain that there are more elegant ways of doing this, but it works for me.
- Find a suitable library to generate the Excel file (should be free)
- Find a suitable library to compress the generated file as they could be quite large (again, should be free)
- Develop a generic method for doing all of the above
The answer to #1 was ClosedXML developed by Manuel De Leon
The answer to #2 was to use the DotNetZipLib
#3 took a little work, but the result was an extender to IEnumerable:
public static String ToExport<dynamic> ( this IEnumerable<dynamic> DataList, String ExcelFileName )
{
var WorkBook = new XLWorkbook ();
var ws = WorkBook.Worksheets.Add ( "Sheet1" );
ws.Cell ( 1, 1 ).InsertTable ( DataList );
String FileName = Path.GetRandomFileName ();
FileName = Path.ChangeExtension ( FileName, "zip" );
String FullFileName = Path.Combine ( HttpContext.Current.Server.MapPath ( "~/Exports" ), FileName );
using ( MemoryStream wbStream = new MemoryStream () )
{
WorkBook.SaveAs ( wbStream );
wbStream.Seek ( 0, SeekOrigin.Begin );
using ( ZipFile Archive = new ZipFile ( FullFileName ) )
{
Archive.AddEntry ( ExcelFileName + ".xlsx", wbStream );
Archive.Save ();
}
}
return ( "~/Exports/" + FileName ).ToAbsoluteUrl ();
}
As you can see, I am creating an Excel 2007+ file and compressing it into a temporary zip file in a folder within the web site. I then return the URL for the file.
In the page, I set the source for a hidden iframe to the URL.
The other part of this is the communication from the client to the code behind. To do that, I use the PageMethods part of the RadScriptManager to call the method in the page code behind. This means that I will not have access to any page variables as it has to be a static method. In order to access the LinqDataSource query and any parameters it uses, I save them to Session variables in the Selecting event:
Session [ "DeviceBurnHoursPageDataSource" ] = this.GridDataSource;
Session [ "DeviceBurnHoursPageFilter" ] = this.ReportGrid.MasterTableView.FilterExpression;
Session [ "DeviceBurnHoursPageReportDate" ] = this.ReportDate;
IDataSource TheSource = ( IDataSource ) HttpContext.Current.Session [ "DeviceBurnHoursPageDataSource" ];
LinqDataSourceView TheView = TheSource.GetView ( "DefaultView" ) as LinqDataSourceView;
DateTime ReportDate = ( DateTime ) HttpContext.Current.Session [ "DeviceBurnHoursPageReportDate" ];
String Filter = ( String ) HttpContext.Current.Session [ "DeviceBurnHoursPageFilter" ];
if ( Filter != String.Empty )
{
String W = " && " + Filter.Replace ( " AND ", " && " );
TheView.Where += W;
}
TheView.WhereParameters [ "ReportDate" ].DefaultValue = ReportDate.ToString ( "MM/dd/yyyy" );
DataSourceSelectArguments Args = new DataSourceSelectArguments ();
return ( ( IEnumerable<dynamic> ) TheView.Select ( Args ) ).ToExport ( "Device Burn Hours " + ReportDate.ToString ( "yyyy-MM-dd" ) );
The returned file is ALL of the columns returned by the query in the order they are defined. In this case, it is suitable for creating the Pivot Table in Excel. If you are exporting from a RadGrid, you should end-up with a ready-to-use Excel file.
I am certain that there are more elegant ways of doing this, but it works for me.