public void GetSheetData()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
Assert.True(!sheet.IsNotValid());
}
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 SwapColumns()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
sheet.SwapColumns(0, 1);
Assert.True(!document.IsNotValid());
}
public void GetData()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
List <object[]> data = document.GetSheetData(@"Report")
.GetData();
Assert.True(data != null);
}
public void SheetDataHtmlTable()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
string table = GridBuilder.HtmlTable(sheet);
Assert.True(!table.IsNotValid());
}
public void SetRowFontAndBackgroundColor()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
Row row = sheet.GetRow(0);
row.SetColor(SystemColor.White, SystemColor.Purple);
Assert.True(row.Descendants <Cell>().ToList().TrueForAll(c => c.StyleIndex >= 0));
}
public void SetCellFontAndBackgroundColor()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Report");
Cell cell = sheet.GetRow(0)
.GetCell(0);
cell.SetColor(SystemColor.White, SystemColor.Purple);
Assert.True(cell.StyleIndex >= 0);
}
public async Task AddRows()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
List <object[]> data = new List <object[]>(1)
{
new object[] { @"Israel", DateTime.Now.AddYears(-27), 27, @"*****@*****.**" }
};
SheetData sheet = document.GetSheetData(@"Report");
await sheet.AddRows(data);
Row row = sheet.GetRow(0);
Assert.True(!row.IsNotValid());
}
public async Task SetTemplateValues()
{
SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
SheetData sheet = document.GetSheetData(@"Entity");
List <KeyValuePair <string, object> > data = new List <KeyValuePair <string, object> >(3)
{
new KeyValuePair <string, object>(@"{{Name}}", @"Israel"),
new KeyValuePair <string, object>(@"{{Age}}", DateTime.Now.AddYears(-27)),
new KeyValuePair <string, object>(@"{{Email}}", @"*****@*****.**")
};
await sheet.SetTemplateValues(data);
Assert.True(!document.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);
}
public static IEnumerable <Row> GetAllRows(this SpreadsheetDocument spreadSheet, int?headerRowIndex)
{
var sheetData = spreadSheet.GetSheetData(0);
return(GetAllRows(sheetData, headerRowIndex));
}