public static void ExportXLSX(List<IParticipant> participants, List<string> formFields)
{
string eventName = (participants.Count > 0) ? ((participants[0] != null && participants[0].EventPage != null && participants[0].EventPage != PageReference.EmptyReference) ? EPiServer.DataFactory.Instance.Get<PageData>(participants[0].EventPage).URLSegment : "NA" ) : "NA";
string fileName = EPiServer.Framework.Localization.LocalizationService.Current.GetString("/attend/edit/participants") + " - " + eventName;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
HttpContext.Current.Response.Charset = Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(Encoding.UTF8.GetPreamble());
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BufferOutput = true;
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Participants");
if(formFields != null)
for(int i = 0; i < formFields.Count; i++)
worksheet.Cells[0, i]= new Cell(formFields[i]);
else if(participants.Count > 0)
{
string[] headers = AttendRegistrationEngine.GetFormData(participants[0]).AllKeys;
worksheet.Cells[0, 0] = new Cell("Status");
worksheet.Cells[0, 1] = new Cell("E-mail");
worksheet.Cells[0, 2] = new Cell("Code");
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[0, i+3] = new Cell(headers[i]);
}
}
for (int i = 0; i < participants.Count; i++)
{
AddParticipantToWorksheet(worksheet, i+1, participants[i]);
}
// Some Excel versions requires more than 100 rows - adding empty ones
for (int i = participants.Count; i < 150; i++)
worksheet.Cells[i, 0] = new Cell(string.Empty);
workbook.Worksheets.Add(worksheet);
MemoryStream ms = new MemoryStream();
workbook.SaveToStream(ms);
ms.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
}
public static void ExportXLSX(List<IParticipant> participants, List<string> formFields)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=participants-" + participants[0].EventPage.ID + ".xls");
HttpContext.Current.Response.Charset = Encoding.UTF8.WebName;
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.BinaryWrite(Encoding.UTF8.GetPreamble());
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BufferOutput = true;
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("Participants");
if(formFields != null)
for(int i = 0; i < formFields.Count; i++)
worksheet.Cells[0, i]= new Cell(formFields[i]);
else
{
string[] headers = AttendRegistrationEngine.GetFormData(participants[0]).AllKeys;
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[0, i] = new Cell(headers[i]);
}
}
for (int i = 0; i < participants.Count; i++)
{
AddParticipantToWorksheet(worksheet, i+1, participants[i]);
}
workbook.Worksheets.Add(worksheet);
MemoryStream ms = new MemoryStream();
workbook.SaveToStream(ms);
ms.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.End();
}
public static void CreateWorkbook(Stream stream, DataSet dataset)
{
if (dataset.Tables.Count == 0)
throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");
Workbook workbook = new Workbook();
foreach (DataTable dt in dataset.Tables)
{
Worksheet worksheet = new Worksheet(dt.TableName);
for (int i = 0; i < dt.Columns.Count; i++)
{
// Add column header
worksheet.Cells[0, i] = new Cell(dt.Columns[i].ColumnName);
// Populate row data
for (int j = 0; j < dt.Rows.Count; j++)
worksheet.Cells[j + 1, i] = new Cell(dt.Rows[j][i]);
}
workbook.Worksheets.Add(worksheet);
}
workbook.SaveToStream(stream);
}
