public async Task CreateReport(byte sheets = 1, int records = 1000, byte seconds = 1)
{
object[] h = new object[] { @"Name", @"BirthDate", @"Age", @"Email Address" };
List <object[]> headers = new List <object[]>(1)
{
h
};
object[] i = new object[] { @"Israel Chavez Gamez", DateTime.Now.AddYears(-27), 27, @"*****@*****.**" };
List <object[]> data = Enumerable.Repeat(i, records).ToList();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
for (byte x = 0; x < sheets; x++)
{
document.AddSheet($@"Sheet{x}");
SheetData sheet = document.GetSheetData($@"Sheet{x}");
await sheet.AddRows(headers);
await sheet.AddRows(data);
sheet.GetRow(0).SetColor(SystemColor.White, SystemColor.Purple);
}
stopwatch.Stop();
document.AutoAdjustWidth();
document.SaveAs($@"{AppDomain.CurrentDomain.BaseDirectory}TestResults\Extensions\Excel\SpreadSheet.xlsx");
Assert.True(stopwatch.Elapsed.TotalSeconds <= seconds);
}
public void AddSheet()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
document.AddSheet(@"NewSheet");
SheetData sheet = document.GetSheetData(@"NewSheet");
Assert.True(!sheet.IsNotValid());
}
/// <summary>
/// Converts a paginated collection of entities into
/// a spreadsheet document
/// </summary>
/// <typeparam name="T">Entity Type</typeparam>
/// <param name="page">Page reference</param>
/// <returns>SpreadsheetDocument</returns>
public static async Task <SpreadsheetDocument> ToExcelDocument <T>(this PaginatedCollection <T> page) where T : class, IEntity, new()
{
//Verify page collection
if (page == null || page.Collection.IsNotValid() || page.Pagination == null)
{
return(null);
}
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
//Remove previous template sheets
document.RemoveSheet(@"Report");
document.RemoveSheet(@"Entity");
document.AddSheet(@"Report");
//Add a new sheet
SheetData sheet = document.GetSheetData(@"Report");
//Add the sheet data
await sheet.AddRows(page.Collection.AsArray());
return(document);
}
/// <summary>
/// Saves the output file in memory. Please get the conten from dedicated property
/// </summary>
public ExcelContentEditor Save()
{
using (MemoryStream stream = new MemoryStream())
{
//start editing
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// add tables for sheets
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
foreach (var tblDescriptor in this._Tables.Select(x => x.Value))
{
// create an empty sheet
WorksheetPart worksheetPart = AddEmptySheet(workbookPart, tblDescriptor.SheetName, sheets);
// Constructing header
Row row = new Row();
OpenXmlElement[] cells = tblDescriptor.Columns
.Select(x => CreateTypedRowCell(1, x.HeaderText, ExcelColumnDescriptor.EnumColumnType.String, tblDescriptor.Columns.IndexOf(x), 2))
.ToArray();
row.Append(cells);
// Insert the header row to the Sheet Data
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
// populate table
var tblData = this._TableData[tblDescriptor.Name];
int rowCounter = 1;
foreach (var data in tblData)
{
rowCounter++;
// create a new row
Row currentRow = new Row();
// build array of cell for this row
OpenXmlElement[] rowCells = tblDescriptor.Columns.Select(x =>
{
if (!data.Values.ContainsKey(x.Name))
{
throw new InvalidOperationException($"Unable to find a value for column {x.Name} inside table {tblDescriptor.Name}");
}
// instance a new cell for proper type
var cell = CreateTypedRowCell(rowCounter, data.Values[x.Name], x.ColumnType, tblDescriptor.Columns.IndexOf(x), 1);
return(cell);
}).ToArray();
currentRow.Append(rowCells);
sheetData.AppendChild(currentRow);
}
worksheetPart.Worksheet.Save();
}
// add empty sheets
foreach (var sheet in _EmptySheets.OrderBy(x => x.Value))
{
document.AddSheet(sheet.Key);
}
// if I'm using formula, caluclate when file is opened.
if (workbookPart.Workbook.CalculationProperties != null)
{
workbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
workbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
}
else
{
workbookPart.Workbook.CalculationProperties = new CalculationProperties()
{
ForceFullCalculation = true,
FullCalculationOnLoad = true
}
};
// save the file
workbookPart.Workbook.Save();
}
//write on bytes
_DocumentContent = stream.ToArray();
}
return(this);
}
