/// <summary>
/// Adds all the specified data table values as new rows
/// </summary>
/// <param name="sheet">SheetData reference</param>
/// <param name="table">DataTable reference</param>
/// <param name="includeColumns">Include the table columnas as first row</param>
/// <returns>Task State</returns>
public static async Task AddRows(this SheetData sheet, DataTable table, bool includeColumns = true)
{
//Check whether the sheet reference or the sheet data is a non-valid to work with object value
if (sheet.IsNotValid() || table == null || !table.Rows.Count.Equals(0) || table.HasErrors)
{
return;
}
//Include the columns name as first row
if (includeColumns)
{
string[] columns = new string[table.Columns.Count];
for (int x = 0; x < table.Columns.Count; x++)
{
columns[x] = table.Columns[x].ColumnName;
}
await sheet.AddRows(new List <string[]>(1) { columns });
}
//Include all the rows inside the table
List <object[]> rows = new List <object[]>(table.Rows.Count);
for (int r = 0; r < table.Rows.Count; r++)
{
rows.Add(table.Rows[r].ItemArray);
}
await sheet.AddRows(rows);
}
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 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());
}
/// <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);
}