BForms Excel Builder component is designed to export the Grid's items into an excel file.


Excel result

BsExcelResult is an ActionResult that helps you return an excel file.

Constructor

You can use it by simply providing a list of items and a filename.

BsExcelResult(string fileName, IEnumerable<T> items)


If you wish to configure the style or the display text of the cells, use the BsGridExcelBuilder<T>.

BsExcelResult(string fileName, BsGridExcelBuilder<T> builder)



Builder

BsGridExcelBuilder<T> is the component behind the excel file creation.

Constructor

BsGridExcelBuilder()


Example

IEnumerable<ContributorRowModel> items = _gridRepository.GetItems(settings, ids);

var builder = new BsGridExcelBuilder<ContributorRowModel>();

var sheet = builder.AddSheet(items, "Contributors sheet");


Configuration

By default, the builder styles the header bold, renders its content based on the DisplayAttribute name, and columns width based on the BsGridColumnAttribute width value.

If you use the same model for the grid and excel export, you can set the Usage property of the BsGridColumnAttribute, to specify the property's scope.

[BsGridColumn(Width = 5, Usage = BsGridColumnUsage.Both)]
public string Contributions { get; set; }

[BsGridColumn(Width = 1, Usage = BsGridColumnUsage.Excel)]
public bool Enabled { get; set; }

Here's a list of methods you could use to configure the builder, adding custom styles and text:

Configure Header

Sets custom style for header, or for a specific header column.

BsGridExcelSheetBuilder<T> ConfigureHeader(Action<BsGridExcelCellFactory<T>> config)


Example

sheet.ConfigureHeader(header =>
        {
            // general style
            header.Style.Font.Bold = true;
            header.Style.FillColor = BsGridExcelColor.Ivory;

            // specific style
            header.For(x => x.StartDate)
                    .Text("Contributor since")
                    .Style(style => style.Font.Italic = true);
        });

Configure Rows

Sets custom style for rows, or for a specific row.

BsGridExcelSheetBuilder<T> ConfigureRows(Func<T, BsGridExcelStyle> config)

BsGridExcelSheetBuilder<T> ConfigureRows(Action<T, BsGridExcelStyle> config)


Example

sheet.ConfigureRows((row, style) =>
        {
            if (row.Role == "TeamLeader")
            {
                style.Font.Bold = true;
            }
            if (row.Role == "Tester")
            {
                style.Font.Italic = true;
            }
        });

Configure Columns

Sets custom style for columns, or for a specific column cell.

The builder will iterate through the model's properties and create cells based on their values and datatype. The supported types are long, int, double, DateTime and string. If your model has other properties that are not supported, you have to use the column configuration and specify what text should be displayed for that column via the Text() method. You can also style the column (Style()), or set it's position (Order()).

BsGridExcelSheetBuilder<T> ConfigureColumns(Action<BsGridExcelColumnFactory<T>> config)

Example

sheet.ConfigureColumns(columns =>
        {
            columns.For(x => x.Enabled)
                   .Order(3) // column position 
                   .Text(x => x.Enabled ? 
                        Resource.Yes : Resource.No) // type bool => specify what text to display
                   .Style((row, style) => style.FillColor = row.Enabled ? 
                        BsGridExcelColor.LightGreen : BsGridExcelColor.Red);

            columns.For(x => x.Role)
                   .Order(2)
                   .Text(x => x.Role.ToString()) // type enum => specify what text to display
                   .Style(style => style.FillColor = BsGridExcelColor.Lavender);

            columns.For(x => x.StartDate)
                   .Text(x => x.StartDate.ToMonthNameDate()) // custom date display
                   .Style(style => style.Font.Italic = true);
        });

Style

BsGridExcelStyle is the class behind the cells style formatting.

You can style a specific cell, row, column or header by setting the Style property in the builder configuration part.

Class

class BsGridExcelStyle
{
    BsGridExcelFont Font { get; set; } // cell(s) font
    BsGridExcelColor? FillColor { get; set; } // cell(s) background color
}

class BsGridExcelFont
{
    bool? Bold { get; set; } // is bold ?
    bool? Italic { get; set; } // is italic ?
    string Family { get; set; } // font family
    int? Size { get; set; } // font size
    BsGridExcelColor? Color { get; set; } // font color
}

Color

The framework provides a set of excel specific colors to help you style the file content via the BsGridExcelColor enum. Below you can find the list:

enum BsGridExcelColor
{
    Black,
    White,
    Red,
    BrightGreen,
    Blue,
    Yellow,
    Pink,
    Turquoise,
    DarkRed,
    Green,
    DarkBlue,
    DarkYellow,
    Violet,
    Teal,
    Gray25,
    Gray50,
    Periwinkle,
    Plum,
    Ivory,
    LightTurquoise,
    DarkPurple,
    Coral,
    OceanBlue,
    IceBlue,
    SkyBlue,
    LightGreen,
    LightYellow,
    PaleBlue,
    Rose,
    Lavender,
    Tan,
    LightBlue,
    Aqua,
    Lime,
    Gold,
    LightOrange,
    Orange,
    BlueGray,
    GrayGray40,
    DarkTeal,
    SeaGreen,
    DarkGreen,
    OliveGreen,
    Brown,
    Indigo,
    Gray80
}

Complete example


Get items, create new builder:

IEnumerable<ContributorRowModel> items = _gridRepository.GetItems(settings, ids);

var builder = new BsGridExcelBuilder<ContributorRowModel>();

var sheet = builder.AddSheet(items, "Contributors sheet");


Configure header:

sheet.ConfigureHeader(header =>
        {
            // general style
            header.Style.Font.Bold = true;
            header.Style.FillColor = BsGridExcelColor.Ivory;

            // specific style
            header.For(x => x.StartDate)
                    .Text("Contributor since")
                    .Style(style => style.Font.Italic = true);
        });

Configure rows:

sheet.ConfigureRows((row, style) =>
        {
            if (row.Role == "TeamLeader")
            {
                style.Font.Bold = true;
            }
            if (row.Role == "Tester")
            {
                style.Font.Italic = true;
            }
        });

Configure columns:

sheet.ConfigureColumns(columns =>
        {
            columns.For(x => x.Enabled)
                   .Order(3) // column position 
                   .Text(x => x.Enabled ? 
                        Resource.Yes : Resource.No) // type bool => specify what text to display
                   .Style((row, style) => style.FillColor = row.Enabled ? 
                        BsGridExcelColor.LightGreen : BsGridExcelColor.Red);

            columns.For(x => x.Role)
                   .Order(2)
                   .Text(x => x.Role.ToString()) // type enum => specify what text to display
                   .Style(style => style.FillColor = BsGridExcelColor.Lavender);

            columns.For(x => x.StartDate)
                   .Text(x => x.StartDate.ToMonthNameDate()) // custom date display
                   .Style(style => style.Font.Italic = true);
        });

Return excel result:

return new BsExcelResult<ContributorRowModel>("BForms Contributors.xlsx", builder);