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 "";
        }
    }
}