public static byte[] GenerateExcelReportForContacts(CEAEDBEntities db)
{
#region GenerateDocument
var memoryStream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add Style
var stylePart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
uint sheetID = 1;
CreateContactsSheet(document, sheets, db, sheetID);
sheetID++;
CreateUsersSheet(document, sheets, db, sheetID);
sheetID++;
CreateTestResultSheet(document, sheets, db, sheetID);
//save data
document.Close();
}
var result = memoryStream.ToArray();
#endregion
return(result);
}
private static void CreateContactsSheet(SpreadsheetDocument document, OpenXmlElement sheets, CEAEDBEntities db, uint sheetID = 1)
{
var newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
var relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
var sheet = new Sheet
{
Id = relationshipId,
SheetId = sheetID,
Name = "Anonymous Users Contacts"
};
sheets.AppendChild(sheet);
var sheetData = new SheetData();
newWorksheetPart.Worksheet = new Worksheet(sheetData);
//create header rows
var projectRow = CreateHeaderRow(1);
sheetData.AppendChild(projectRow);
//creating content rows
var colIndex = 2;
var contacts = db.Contacts.ToList();
foreach (var contact in contacts)
{
//creating row for this user
var row = CreateContactRow(colIndex, contact);
sheetData.AppendChild(row);
colIndex++;
}
//set col width
var columns = new Columns();
columns.Append(new Column {
Min = 1, Max = 250, Width = 20, CustomWidth = true
});
columns.Append(new Column {
Min = 2, Max = 250, Width = 12, CustomWidth = true
});
newWorksheetPart.Worksheet.Append(columns);
newWorksheetPart.Worksheet.Save();
}
