Exporting to Excel from DataGrid

Vote:
 

I spent a bit of time trying to get this to work. I just wanted to share the code. Do let me know if you know of a better way to do this. I struggled particularly with getting the Greek and Czech characters to render correctly when the file was openend in excel. The solution seemed to be to add the xml header to the string in Response.Write.


Cheers,

Victor

 ASPX

<%@ Page Language="C#" MasterPageFile="~/Templates/Admin/Masterpages/episerverui.master"
    AutoEventWireup="true" CodeBehind="ExportRegisteredUsers.aspx.cs" Inherits="MyProject.Templates.Admin.ExportRegisteredUsers" %>

<%@ Register TagPrefix="EPiServerUI" Namespace="EPiServer.UI.WebControls" Assembly="EPiServer.UI" %>
<%@ Register TagPrefix="MyProject" Namespace="MyProject.Classes.Extension" Assembly="MyProject" %>

<asp:Content ContentPlaceHolderID="MainRegion" runat="server">
    <h1>
        Export Registered Users
    </h1>
    <p>
        <MyProject:LanguageDropDownList ID="ddExportLanguage" runat="server" OnSelectedIndexChanged="ddExportLanguage_OnSelectedIndexChanged" EnableViewState="true" />
        <br />
        <asp:Button runat="server" ID="btnExport" Text="Export" OnClick="btnExport_Click" />
    </p>
    <asp:DataGrid ID="dg" runat="server" AutoGenerateColumns="True" AllowSorting="true">
    </asp:DataGrid>
</asp:Content>

 

ASPX.CS

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web.Caching;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using EPiServer.PlugIn;
using EPiServer.Security;
using EPiServer.UI;
using MyProject.Classes;
using MyProject.Classes.Profile;
using System.Diagnostics;

namespace MyProject.Templates.Admin
{
    [GuiPlugIn(DisplayName = "Export Registered Users", Description = "Export MyProject registered users ",
        Area = PlugInArea.ActionWindow, Url = "~/Templates/Admin/ExportRegisteredUsers.aspx",
        RequiredAccess = AccessLevel.Administer, SortIndex = 2004)]
    public partial class ExportRegisteredUsers : SystemPageBase
    {
        #region Properties / Constants
        /// 
        /// Format for each row in the CSV file - each element is wrapped in "" for completeness
        /// 
        private const string CSV_ROW = "\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\"";

        /// 
        /// Cache key to store all of the users
        /// 
        private const string ALL_USERS = "aUsers";

        private const string ASSEMBLY_NAME = "MyProject";


        /// 
        /// Used to store the list of all users in the system (cached)
        /// 
        private IEnumerable _matchedUsers;

        #endregion

        #region Events


        /// 
        /// On load, set the title, get all users (from cache) and set the language
        /// 
        /// 
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            //= cache the users
            if (Cache[ALL_USERS] == null)
                Cache.Add(ALL_USERS, (from MembershipUser u in Membership.GetAllUsers()
                                      where u.Email != string.Empty
                                      select UserProfile.GetUserProfile(u.UserName))
                          , null, DateTime.MaxValue, TimeSpan.FromHours(1), CacheItemPriority.Normal, null);

            //= get the users from the cache
            _matchedUsers = (IEnumerable)Cache[ALL_USERS];

            FillDataGrid();


        }

        /// 
        /// This Event Handler will export the datagrid data to an excel file. 
        /// This was modified to prevent garbled values when exporting to Greek or Czech.
        /// 
        /// 
        /// 
        protected void btnExport_Click(object sender, EventArgs e)
        {
            //export to excel
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";

            string selectedLanguage = ddExportLanguage.SelectedLanguage;
            Response.AddHeader("Content-Disposition", string.Format("inline;filename=RegisteredUsers-{0}.xls", selectedLanguage));
            string header;
            //= set the content encoding to handle special characters
            switch (selectedLanguage)
            {
                case "el-GR":
                    Response.Charset = "";
                    Response.ContentEncoding = Encoding.GetEncoding(1253);
                    header = "";
                    break;
                case "cs-CZ":
                    Response.ContentEncoding = Encoding.GetEncoding("ISO-8859-2");
                    Response.Charset = "ISO-8859-2";
                    header = "";
                    break;
                default:
                    Response.ContentEncoding = Encoding.Default;
                    Response.Charset = "";
                    header = "";
                    break;
            }

            var oStringWriter = new StringWriter();
            var oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
            ClearControls(dg);
            dg.RenderControl(oHtmlTextWriter);
            Response.Write(header + oStringWriter.ToString());
            Response.End();
        }


        private static void ClearControls(Control control)
        {
            for (int i = control.Controls.Count - 1; i >= 0; i--)
            {
                ClearControls(control.Controls[i]);
            }
            if (!(control is TableCell))
            {
                if (control.GetType().GetProperty("SelectedItem") != null)
                {
                    var literal = new LiteralControl();
                    control.Parent.Controls.Add(literal);
                    try
                    {
                        literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
                    }
                    catch (Exception ex)
                    {
                        LogHelper.Log.Warn("Warning:", ex);
                    }
                    control.Parent.Controls.Remove(control);
                }
                else if (control.GetType().GetProperty("Text") != null)
                {
                    var literal = new LiteralControl();
                    control.Parent.Controls.Add(literal);
                    literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
                    control.Parent.Controls.Remove(control);
                }
            }
            return;
        }

        /// 
        /// This Event Handler populates a data grid.
        /// 
        /// 
        /// 
        protected void ddExportLanguage_OnSelectedIndexChanged(object sender, CommandEventArgs e)
        {

            FillDataGrid();
        }

        private void FillDataGrid()
        {
            //= get the matching users
            IEnumerable matchedUsers = GetUserLanguageMatch();

            //= populate all of the CSV rows
            string[] toExport =
                matchedUsers.Select(
                    u =>
                    string.Format(CSV_ROW, string.IsNullOrEmpty(u.Email) ? u.UserName : u.Email, u.FirstName, u.LastName,
                                  u.Phone, u.Street, u.Town, u.Region, u.ZipCode, u.OptIn.ToString())).ToArray();

            string[] columnArray = {
                                       (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.email"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.first_name"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.last_name"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.telephone"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.street"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.town"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.region"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.postzipcode"),
                                              (string) GetGlobalResourceObject(ASSEMBLY_NAME, "MyProject.registeruser.optin")
                                   }
                ;

            var dt = new DataTable();
            foreach (string column in columnArray)
            {
                //Create an ID column for adding to the Datatable
                var dcol = new DataColumn(column, typeof(String));
                dt.Columns.Add(dcol);
            }

            foreach (string user in toExport)
            {
                char[] delimiter = { '*' };
                string value = user.Replace("\",\"", "*");
                string[] rows = value.Split(delimiter);
                DataRow dr = dt.NewRow();


                int i = 0;
                while (i < columnArray.Count() - 1)
                {
                    dr[columnArray[i]] = rows[i].TrimStart('"').TrimEnd('"');
                    i++;
                }

                dt.Rows.Add(dr);
            }

            var ds = new DataSet("MyTable");
            ds.Tables.Add(dt);
            dg.DataSource = ds.Tables[0];
            dg.DataBind();
        }

        #endregion

        #region Private Methods

        /// 
        /// Gets a collection of UserProfiles that match the language branch
        /// 
        /// language branch (culture) to match users
        /// 
        private IEnumerable GetUserLanguageMatch()
        {
            string selectedLanguage = ddExportLanguage.SelectedLanguage;
            if (string.IsNullOrEmpty(selectedLanguage))
            {
                IEnumerable matchedUsers = (from UserProfile p in _matchedUsers
                                                         where string.IsNullOrEmpty(p.Language)
                                                         select p);
                return matchedUsers;
            }
            else
            {
                IEnumerable matchedUsers = (from UserProfile p in _matchedUsers
                                                         where string.Equals(p.Language, selectedLanguage, StringComparison.OrdinalIgnoreCase)
                                                         select p).ToList();

                return matchedUsers;
            }
        }

        #endregion
    }
}
#41595
Edited, Jul 14, 2010 16:56
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.