This is a migrated thread and some comments may be shown as answers.

Export to Excel - a solution

3 Answers 262 Views
PivotGrid
This is a migrated thread and some comments may be shown as answers.
Ed
Top achievements
Rank 1
Ed asked on 22 Feb 2013, 09:22 PM
This all started when I found myself having issues with the RadGrid export. I did a little research and developed the following solution:
  1. Find a suitable library to generate the Excel file (should be free)
  2. Find a suitable library to compress the generated file as they could be quite large (again, should be free)
  3. 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;

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.

3 Answers, 1 is accepted

Sort by
0
Kostadin
Telerik team
answered on 27 Feb 2013, 02:25 PM
Hello Ed,

I am glad this exporting functionality full fit your needs and you are sharing it with our community. I carefully examined your solution and I noticed that this approach just export the data but does not create the pivot layout. Our developers are making researching at the moment and try to export the pivot structure not just the data in it. You could track the process in our Ideas & Feedback portal.

All the best,
Kostadin
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Ed
Top achievements
Rank 1
answered on 27 Feb 2013, 03:41 PM
I expected that you would be working on a solution. My solution actually started when I could not get the radgrid to correctly export in the Excel 2007 format. I then tried it on the pivot grid and it worked fine.
0
Kostadin
Telerik team
answered on 04 Mar 2013, 11:56 AM
Hello Ed,

As I already mentioned in my previous reply, our developers are making researching at the moment and development of such feature will start as soon as they collect enough information on this matter. If you would like you could create a code library which may serve the needs of our customers until then. You will be accordingly awarded with Telerik points for your effort.

Kind regards,
Kostadin
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
PivotGrid
Asked by
Ed
Top achievements
Rank 1
Answers by
Kostadin
Telerik team
Ed
Top achievements
Rank 1
Share this question
or