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); }