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.
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
}
}
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
ASPX.CS