Live Razor Page Samples
Range To HTML Table Tag Helper
This sample shows how you can create a Tag Helper to convert a range of cells into an HTML Table and carry over some formatting from the source range.
using Microsoft.AspNetCore.Mvc;
using System.ComponentModel.DataAnnotations;
namespace Website.Pages.Support.Samples.RazorPages.TagHelpers
{
public partial class RangeToTagHelperModel : PageModel
{
// The range to convert to an HTML table.
public SpreadsheetGear.IRange Range { get; set; }
// Number of rows to generate (count of rows after first row).
[BindProperty]
[Range(1, 100, ErrorMessage = "Number of rows must be between 1 and 100.")]
public int NumRows { get; set; }
// Toggles whether to treat first row or Range as a header row (thead) or normal row (tbody).
[BindProperty]
public bool UseHeaderRow { get; set; }
public void OnGet()
{
NumRows = 10;
UseHeaderRow = true;
SetupRangeDataForTable();
}
public void OnPost()
{
if (!ModelState.IsValid)
return;
SetupRangeDataForTable();
}
// Generates data on a worksheet to display on web page with a Tag Helper. This routine also demonstrates a variety of ways
// to work with, reference and manipulate ranges and references, as well as apply values, formulas and formatting to cells.
private void SetupRangeDataForTable()
{
// Create a workbook andget references to the worksheet and cells.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;
// Make Row 1 a header row. Note the ability to use of a 2D array, which is more useful for populating multiple
// rows with data.
SpreadsheetGear.IRange topRow = cells["A1:D1"];
topRow.Value = new object[,] { { "Product", "Price", "Quantity", "Total" } };
// Create an IRange for the data portion of the worksheet (i.e., exclude first row).
SpreadsheetGear.IRange dataRows = cells[1, 0, NumRows, 3];
// For convenience, get references to data rows for each column. Note the use of the IRange.Intersect(...) method
// to get some sub-range of "dataRows" based on some other range. For instance, if a user entered 10 for
// "NumRows" then the "colA" variable would reference A2:A10.
SpreadsheetGear.IRange colA = dataRows.Intersect(cells["A:A"]);
SpreadsheetGear.IRange colB = dataRows.Intersect(cells["B:B"]);
SpreadsheetGear.IRange colC = dataRows.Intersect(cells["C:C"]);
SpreadsheetGear.IRange colD = dataRows.Intersect(cells["D:D"]);
// Add some product names by looping through each cell in colA.
for (int i = 0; i < colA.RowCount; i++)
colA[i, 0].Value = $"Product {i+1}";
// Add some random prices and quantities.
colB.Formula = "=RANDBETWEEN(0, 100)";
colC.Formula = "=RANDBETWEEN(1, 10)";
// Note that this single formula is filled for all cells in "colD". The relative references used for the row portion
// of the address allows the each formula to be "fixed up" for each cell going down the column.
colD.Formula = "=$B2*$C2";
// Add a summary row that sums the Quantity and Total columns.
int summaryRowIndex = dataRows.Row + dataRows.RowCount;
SpreadsheetGear.IRange qtyTotalCell = cells[summaryRowIndex, 2];
SpreadsheetGear.IRange grandTotalCell = cells[summaryRowIndex, 3];
// Note again the user of the Intersect(...) method to constuct the desired range. IRange.Address returns a string in
// A1-reference style for the range.
qtyTotalCell.Formula = $"=SUM({dataRows.Intersect(cells["C:C"]).Address})";
grandTotalCell.Formula = $"=SUM({dataRows.Intersect(cells["D:D"]).Address})";
// Format relevant ranges as desired.
// Note use of IRange.Union(...) method to join multiple ranges together (same as using a comma
// when referencing ranges, such as B1:B10,D1:D10).
colB.Union(colD).Union(grandTotalCell).NumberFormat = "$#,##0.00";
colD.Union(topRow).Union(qtyTotalCell).Union(grandTotalCell).Font.Bold = true;
qtyTotalCell.Union(grandTotalCell).Font.Underline = SpreadsheetGear.UnderlineStyle.Single;
// Set horizontal alignment on relevant ranges (all cells with numeric values.
cells[$"A1:D{NumRows + 1}"] // Start with top row and data rows.
.Subtract(cells["A:A"]) // Remove range with non-numeric / text data.
.Union(qtyTotalCell).Union(grandTotalCell) // Union in the "SUM" cells.
.HorizontalAlignment = SpreadsheetGear.HAlign.Right;
// Set target range for the Tag Helper, in this case the worksheet's Used Range.
Range = worksheet.UsedRange;
}
}
}
@page
@model RangeToTagHelperModel
<div class="row">
<div class="col-sm-12 col-md-5 mb-3">
<div class="card">
<h2 class="card-header">Report Options</h2>
<div class="card-body">
<form asp-page="RangeToTagHelper" method="post">
<div class="row mb-2">
<div class="col-md-12 col-lg-6"><label asp-for="NumRows">Number of Data Rows</label></div>
<div class="col-md-12 col-lg-6">
<input asp-for="NumRows" class="form-control" min="1" max="100" />
<span asp-validation-for="NumRows" class="form-control-feedback"></span>
</div>
</div>
<div class="row mb-2">
<div class="col-md-12 col-lg-6"><label asp-for="UseHeaderRow">Treat First Row as Header</label></div>
<div class="col-md-12 col-lg-6">
<select asp-for="UseHeaderRow" class="form-select">
<option value="True">Yes</option>
<option value="False">No</option>
</select>
</div>
</div>
<div class="text-center">
<button class="btn btn-primary">Update</button>
</div>
</form>
</div>
</div>
</div>
<div class="col-sm-12 col-md-7">
<range-to-table range="@Model.Range" first-row-is-header="@Model.UseHeaderRow"></range-to-table>
</div>
</div>
using System.Collections.Generic;
using System.Text;
using Microsoft.AspNetCore.Razor.TagHelpers;
namespace Website.Pages.Support.Samples.RazorPages
{
/// <summary>
/// Simple Tag Helper to convert a range of cells into an HTML table.
/// </summary>
[HtmlTargetElement("range-to-table")]
public class RangeToTableTagHelper : TagHelper
{
/// <summary>
/// The Range to be converted to an HTML table.
/// </summary>
public SpreadsheetGear.IRange Range { get; set; }
/// <summary>
/// Specifies whether to treat the first row in Range as a header row
/// </summary>
public bool FirstRowIsHeader { get; set; } = true;
public override void Process(TagHelperContext context, TagHelperOutput output)
{
// Can't generate anything if no range was provided.
if (Range == null)
{
output.TagName = "div";
output.Content.SetHtmlContent(@"<div class='alert alert-info'><i>No range data available.</i></div>");
return;
}
// Set output tag to <table> and add some styling courtesy of Bootstrap.
output.TagName = "table";
output.Attributes.Add("class", "table table-striped table-bordered table-hover table-sm");
// Store table in a StringBuilder as we construct it for faster processing of string concatenation.
StringBuilder sb = new StringBuilder();
// If FirstRowIsHeader we'll treat the first row as a header row and remaining rows as normal rows in the
// dataRange.
SpreadsheetGear.IRange dataRange = Range;
if (FirstRowIsHeader)
{
// Get reference to first row.
SpreadsheetGear.IRange headerRow = dataRange[0, 0, 0, dataRange.ColumnCount - 1];
sb.Append("<thead class='table-dark'><tr>");
// Loop over and render each cell in the "headerRow" range.
foreach (SpreadsheetGear.IRange cell in headerRow)
{
// Get some basic formatting from this cell.
string classes = GetClassAttribute(cell);
// Using IRange.Text here, which will return the formatted value of a cell.
sb.Append("<th" + (classes.Length > 0 ? $" class='{classes}'" : "") + ">").Append(cell.Text).Append("</th>");
}
sb.Append("</tr></thead>");
// Since the top row is a header row, we should remove it from dataRange, for which the
// IRange.Subtract(...) method is a perfect tool to do so.
dataRange = dataRange.Subtract(headerRow);
}
sb.Append("<tbody>");
// Could be null if we treated the first row as a header row and there were no additional rows.
if (dataRange != null)
{
// Calling dataRange.Rows returns the same range as dataRange, but in a form that allows
// it to be looped over in row "chunks", such as A1:C1, A2:C2, A3:C3 in the range A1:C3.
foreach (SpreadsheetGear.IRange row in dataRange.Rows)
{
sb.Append("<tr>");
// Similarly calling IRange.Columns will allow each column in the provided range to be
// iterated over (in this case each column of one row will consist of just a single cell).
foreach (SpreadsheetGear.IRange cell in row.Columns)
{
// Get some basic formatting from this cell.
string classes = GetClassAttribute(cell);
// Again, using IRange.Text here, which will return the formatted value of a cell.
sb.Append("<td" + (classes.Length > 0 ? $" class='{classes}'" : "") + ">").Append(cell.Text).Append("</td>");
}
sb.Append("</tr>");
}
}
else
{
sb.Append($"<tr><td colspan='{Range.ColumnCount}' class='text-center text-muted'>No Data Available</td></tr>");
}
sb.Append("</tbody>");
// Set output of tag helper to the HTML table.
output.Content.SetHtmlContent(sb.ToString());
}
/// <summary>
/// A very basic routine to add formatting to HTML output based on the corresponding IRange cell's formatting.
/// </summary>
/// <param name="cell">A single cell, for which formatting classes will be based off.</param>
/// <returns>A string such as ' class="..."' if formatting options are found, otherwise an empty string.</returns>
private static string GetClassAttribute(SpreadsheetGear.IRange cell)
{
List<string> classes = [];
// Horizontal Alignment
if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Center)
classes.Add("text-center");
else if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Right)
classes.Add("text-end");
// Basic font formatting
if (cell.Font.Bold)
classes.Add("fw-bold");
if (cell.Font.Italic)
classes.Add("fst-italic");
if (cell.Font.Underline != SpreadsheetGear.UnderlineStyle.None)
classes.Add("text-underline");
// If desired, this could be expanded to include other formatting, such as other font settings, cell
// interior color, etc.
if (classes.Count > 0)
return string.Join(' ', classes);
return "";
}
}
}