Range Template Cell

GcExcel supports Range Template cell type which allows you to specify a cell range in the worksheet which acts as a range template. The range template is considered as a single cell and can be applied to a cell or cell range, as desired. The data into the range template can be loaded from a data source.

This feature is particularly useful when you want to display some specific ranges of data with identical structures (as displayed in the screenshots below) without having to configure the same style for multiple ranges again and again.

The above Range Template when applied to a cell range A1:B2 and is loaded with data from data source looks like below:

The following steps must be performed to create a Range Template cell type:

Create a Range Template: Design the layout of Range Template in a worksheet. The template can be bound to data by using BindingPath property.
Configure Data: Configure a Data source to bind the template.
Create & Apply Range Template cell type: Create a Range Template cell type by using RangeTemplateCellType method and apply it to the desired cell range.

Refer to the following code to create a Range Template cell type.

    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    GrapeCity.Documents.Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer();

    var sheet1 = workbook.ActiveSheet;
    // Step 1. Create a worksheet for designing range template
    var sheet2 = workbook.Worksheets.Add();

    // Step 2. Configure Data
    var record1 = new PersonalAssets
    {
        Name = "Peyton",
        Savings = 25000,
        Shares = 55000,
        Stocks = 15000,
        House = 250000,
        Bonds = 11000,
        Car = 7500
    };
    var record2 = new PersonalAssets
    {
        Name = "Icey",
        Savings = 30000,
        Shares = 45000,
        Stocks = 25000,
        House = 20000,
        Bonds = 18000,
        Car = 75000
    };
    var record3 = new PersonalAssets
    {
        Name = "Walter",
        Savings = 20000,
        Shares = 4000,
        Stocks = 95000,
        House = 30000,
        Bonds = 10000,
        Car = 56000
    };
    var record4 = new PersonalAssets
    {
        Name = "Chris",
        Savings = 70000,
        Shares = 85000,
        Stocks = 35000,
        House = 20000,
        Bonds = 15000,
        Car = 45000
    };

    // Set binding path for cell.
    sheet2.Range["A1:C1"].Merge();
    sheet2.Range["A1:C1"].HorizontalAlignment = HorizontalAlignment.Center;
    sheet2.Range["A1:C1"].VerticalAlignment = VerticalAlignment.Center;

    sheet2.Range["A1"].BindingPath = "Name";
    sheet2.Range["A1"].Font.Name = "Arial";
    sheet2.Range["A1"].Font.Size = 15;
    sheet2.Range["1:1"].RowHeight = 30;
    sheet2.Range["A2"].Value = "Asset Type";
    sheet2.Range["B2"].Value = "Amount";
    sheet2.Range["C2"].Value = "Rate";
    sheet2.Range["A3"].Value = "Savings";
    sheet2.Range["A3"].Interior.Color = Color.FromArgb(145, 159, 129);
    sheet2.Range["B3"].BindingPath = "Savings";
    sheet2.Range["C3"].Formula = "=B3/B9";
    sheet2.Range["A4"].Value = "Shares";
    sheet2.Range["A4"].Interior.Color = Color.FromArgb(215, 145, 62);
    sheet2.Range["B4"].BindingPath = "Shares";
    sheet2.Range["C4"].Formula = "=B4/B9";
    sheet2.Range["A5"].Value = "Stocks";
    sheet2.Range["A5"].Interior.Color = Color.FromArgb(206, 167, 34);
    sheet2.Range["B5"].BindingPath = "Stocks";
    sheet2.Range["C5"].Formula = "=B5/B9";
    sheet2.Range["A6"].Value = "House";
    sheet2.Range["A6"].Interior.Color = Color.FromArgb(181, 128, 145);
    sheet2.Range["B6"].BindingPath = "House";
    sheet2.Range["C6"].Formula = "=B6/B9";
    sheet2.Range["A7"].Value = "Bonds";
    sheet2.Range["A7"].Interior.Color = Color.FromArgb(137, 116, 169);
    sheet2.Range["B7"].BindingPath = "Bonds";
    sheet2.Range["C7"].Formula = "=B7/B9";
    sheet2.Range["A8"].Value = "Car";
    sheet2.Range["A8"].Interior.Color = Color.FromArgb(114, 139, 173);
    sheet2.Range["B8"].BindingPath = "Car";
    sheet2.Range["C8"].Formula = "=B8/B9";
    sheet2.Range["A9"].Value = "Total";

    sheet2.Range["B9:C9"].Merge();
    sheet2.Range["B9:C9"].HorizontalAlignment = HorizontalAlignment.Center;
    sheet2.Range["B9:C9"].NumberFormat = "$#,##0_);($#,##0)";
    sheet2.Range["B9:C9"].Formula = "=SUM(B3:B8)";

    sheet2.Range["B3:B8"].NumberFormat = "$#,##0_);($#,##0)";
    sheet2.Range["C3:C8"].NumberFormat = "0.00%";
    sheet2.Range["C3:C8"].FormatConditions.AddDatabar();

    // Set data source
    sheet1.Range["A:B"].ColumnWidthInPixel = 300;
    sheet1.Range["1:2"].RowHeightInPixel = 200;
    sheet1.Range["A1"].Value = record1;
    sheet1.Range["B1"].Value = record2;
    sheet1.Range["A2"].Value = record3;
    sheet1.Range["B2"].Value = record4;

    // Step 3. Create a range template celltype
    var rangeTemplateCelltype = new RangeTemplateCellType(sheet2);

    // Apply cell type to "A1:B2"
    sheet1.Range["A1:B2"].CellType = rangeTemplateCelltype;

    //save to a pdf file
    workbook.Save("addrangetemplatecelltype.pdf");

}
class CustomObjectJsonSerializer : IJsonSerializer
{
    public object Deserialize(string json)
    {
        return Newtonsoft.Json.JsonConvert.DeserializeObject(json);
    }

    public string Serialize(object value)
    {
        return Newtonsoft.Json.JsonConvert.SerializeObject(value);
    }
}
class PersonalAssets
{
    public string Name;
    public int Savings;
    public int Shares;
    public int Stocks;
    public int House;
    public int Bonds;
    public int Car;
}

Limitation

Excel doesn't support Range Template cell type. Hence, it would be lost after saving to xlsx file.