-
Notifications
You must be signed in to change notification settings - Fork 0
Spreadsheet
Simplified Excel or compatible spreadsheet generator.
Import the generator namespace.
using BlackDigital.Report;The static class 'ReportGenerator' is where report generation starts. From there, it calls the spreadsheet report builder by calling the 'Spreadsheet()' method.
ReportGenerator.Spreadsheet()...The BlackDigital.Report library uses Fluent Interface to build the report.
When generating the report file, indicate the name of the company.
ReportGenerator.Spreadsheet()
.SetCompany("CompanyName")Other attributes, such as author, are not supported, indicate here if you are interested in adding other attributes when generating the file.
To create multiple sheets in the same report, use the "AddSheet" method, passing the sheet name:
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")When adding a sheet, the Builder will change so that all of the following calls are created within that sheet, but you can switch Builder back to the spreadsheet whenever you want by calling the "Spreadsheet" method:
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.Spreadsheet()
.AddSheet("Sheet 2")
.Spreadsheet()
.AddSheet("Sheet 3")Inside the sheet, you can add a value in any cell, for that use the "AddValue" method. You can indicate the cell that will receive the value, by reference or row and column position, but if you hide the cell reference, the value will be added in cell "A1".
If two values are added in the same cell, the last value added will overwrite the old value.
Example the value adding the text "My Text" in cell B2 by cell code:
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddValue("My Text", "B2")Example the value by adding the local date and time in cell D3 via row/column number:
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddValue(DateTime.Now, 4, 3)| C# Type | Excel Type |
|---|---|
| string | String |
| short | Number |
| int | Number |
| long | Number |
| float | Number |
| double | Number |
| decimal | Number |
| DateTime | Date |
| DateTimeOffset | Date |
| TimeSpan | Number |
Other types are not yet supported, indicate here if you are interested in other types.
Formula creation is not supported, please indicate here if interested.
Inside the sheet, you can fill multiple data at once using the "Fill" method
In the Fill method you can pass a two-dimensional array of object, but only the types listed in Supported Types will appear.
It is possible to indicate the cell that will start receiving the values, by the reference or position of the row and column, but if you hide the cell reference, the values will start with cell "A1".
The first matrix will be written in each new row and the second matrix will be written in each new column.
Example of filling in two-dimensional array values starting at cell B2:
var list = new List<List<object>>();
list.Add(new List<object>() { "Line 1", 10, DateTime.Today, TimeSpan.FromHours(3) });
list.Add(new List<object>() { "Line 2", -10, DateTime.Now, TimeSpan.FromMinutes(12) });
list.Add(new List<object>() { "Line 3", 10.6m, DateTime.UtcNow, TimeSpan.FromMinutes(45).Add(TimeSpan.FromSeconds(31)) });
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.Fill(list, "B2")Inside the sheet, you can fill with a list of objects at once using the "FillObject" method.
In the FillObject method you pass a list of an object, where each object will be a row and each property will be a column, but only the property types listed in Supported Types will appear.
It is possible to indicate the cell that will start receiving the values from the list, by the reference or position of the row and column, but if you hide the cell reference, the values will start with cell "A1".
In FillObject you can indicate whether or not a header will be automatically generated, passing the 'generateHeader' parameter with a value of true or false. If true, the object's property name will be the name shown in the header. "Display" attribute to get header name is not supported, please indicate here if interested. And see in Globalization how to do automatic translations of headers according to a language.
Example of filling object list starting in cell B2:
public class TestModel
{
public TestModel(string name, double number, DateTime objDate, TimeSpan time)
{
Name = name;
Number = number;
ObjDate = objDate;
Time = time;
}
public string Name { get; set; }
public double Number { get; set; }
public DateTime ObjDate { get; set; }
public TimeSpan Time { get; set; }
}
List<TestModel> list = new();
list.Add(new("Line 1", 10, DateTime.Today, TimeSpan.FromHours(3)));
list.Add(new("Line 2", -10, DateTime.Now, TimeSpan.FromMinutes(12)));
list.Add(new("Line 3", 10.6d, DateTime.UtcNow, TimeSpan.FromMinutes(45).Add(TimeSpan.FromSeconds(31))));
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.FillObject(list, 2, 2)Within a sheet, it is possible to add tables, use the "AddTable" method, passing the name of the table and the reference of the cell that will start the table, passing the value of the reference in string or position of the row and column, but if hide the cell reference, the table will start with cell "A1".
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddTable("My Table", "B2")When adding a table, the Builder will change so that all the following calls are created inside that table, but you can switch the Builder back to the sheet whenever you want by calling the "Sheet" method or go back directly to the spreadsheet using the "Spreadsheet":
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddTable("My Table")
.Sheet()
.AddTable("My Table 2", "Z2")Inside the table, it is possible to fill it using the "Fill" method, passing a two-dimensional array, its use is similar to what occurs directly in Sheet, however, it is not necessary to pass the reference of the cell, since the table already has this position:
var list = new List<List<object>>();
list.Add(new List<object>() { "Line 1", 10, DateTime.Today, TimeSpan.FromHours(3) });
list.Add(new List<object>() { "Line 2", -10, DateTime.Now, TimeSpan.FromMinutes(12) });
list.Add(new List<object>() { "Line 3", 10.6m, DateTime.UtcNow, TimeSpan.FromMinutes(45).Add(TimeSpan.FromSeconds(31)) });
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddTable("My Table")
.Fill(list)Inside the table, it is possible to fill it with a list of objects using the "FillObject" method, its use is similar to what occurs directly in the Sheet, however, it is not necessary to pass the cell reference, since the table already has this position:
public class TestModel
{
public TestModel(string name, double number, DateTime objDate, TimeSpan time)
{
Name = name;
Number = number;
ObjDate = objDate;
Time = time;
}
public string Name { get; set; }
public double Number { get; set; }
public DateTime ObjDate { get; set; }
public TimeSpan Time { get; set; }
}
List<TestModel> list = new();
list.Add(new("Line 1", 10, DateTime.Today, TimeSpan.FromHours(3)));
list.Add(new("Line 2", -10, DateTime.Now, TimeSpan.FromMinutes(12)));
list.Add(new("Line 3", 10.6d, DateTime.UtcNow, TimeSpan.FromMinutes(45).Add(TimeSpan.FromSeconds(31))));
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddTable("My Table")
.FillObject(list)It is possible to add headers of the table, when it is filled with two-dimensional array or bypassing the header generation of filling with object list, using the "AddHeader" method and passing a list of strings.
List<string> headers = new()
{
"Column 1",
"Column 2",
"Column 3",
"Column 4"
};
ReportGenerator.Spreadsheet()
.AddSheet("Sheet 1")
.AddTable("My Table")
.Fill(list)
.AddHeader(headers )Some data generated may be different for each country, currently only the generation of object list headers uses globalization. You need to pass two parameters, ResourceManager with the strings for translation and the current CultureInfo you want to use. In the Spreadsheet builder, pass the necessary attributes:
ReportGenerator.Spreadsheet()
.SetResourceManager(Texts.ResourceManager)
.SetCultureInfo(new CultureInfo("pt"))If there are more items that need to go through the globalization process, please let us know here.
Cell formatting is not supported, please indicate here if interested.
Style customization is not supported, please indicate here if interested.
Currently it is possible to generate report in three different ways, saving in file, byte array and Stream.
ReportGenerator.Spreadsheet()
...
.BuildAsync(@"test.xlsx");
MemoryStream ms = new();
ReportGenerator.Spreadsheet()
...
.BuildAsync(ms); byte[] buffer = await ReportGenerator.Spreadsheet()
...
.BuildAsync();Using Fluent Interface development it is possible to create some advanced scenarios, let's explore a use case.
The simple and recommended way to extend the API in your code is using extension methods, the following are the classes you can use to extend and facilitate the development of reports within your own project, all in the namespace: BlackDigital.Report.Spreadsheet
| Class | Description |
|---|---|
| SpreadsheetBuilder | Spreadsheet file-level builder |
| SheetBuilder | Builder at sheet level |
| TableBuilder | Builder at the table level |
To create an export pattern within your project, in this scenario, this template consists of having at the top of the worksheet the company name, date and time of generation table name, then the table with the data.
Creating the extension method for this scenario:
public static class ReportExtension
{
public static SpreadsheetBuilder MyReport<TModel>(this SpreadsheetBuilder builder,
string name,
IEnumerable<TModel> list)
{
return builder.SetCompany("My Company")
.AddSheet(name)
.AddValue("My Company")
.AddValue("Date: ", "A2")
.AddValue(DateTime.Now, "B2")
.AddValue(name, "A3")
.AddTable("report", "A5")
.FillObject(list)
.Spreadsheet();
}
}And to call this method in the middle of your code:
ReportGenerator.Spreadsheet()
.MyReport("My report", list)Another possible scenario is generating the report in a temporary download folder.
public static async Task<string> BuilderReportAsync(this SpreadsheetBuilder builder)
{
string filename = Guid.NewGuid().ToString();
filename = filename.Replace("-", "");
filename = $"{filename}.xlsx";
await builder.BuildAsync($"/var/www/downloads/reports/{filename}");
return filename;
}Example of use:
string filename = await ReportGenerator.Spreadsheet()
.MyReport("My report", list)
.BuilderReportAsync();Languages