Export to Excel - a solution

4 posts, 0 answers
  1. Ed
    Ed avatar
    18 posts
    Member since:
    Apr 2011

    Posted 22 Feb 2013 Link to this post

    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.

  2. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 27 Feb 2013 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Ed
    Ed avatar
    18 posts
    Member since:
    Apr 2011

    Posted 27 Feb 2013 Link to this post

    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.
  5. Kostadin
    Admin
    Kostadin avatar
    1713 posts

    Posted 04 Mar 2013 Link to this post

    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.
Back to Top