Back To Razor Page Samples

Live Razor Page Samples

Ranges To Multiple HTML Tables With a Tag Helper

This sample shows how to retrieve data from multiple worksheets in a Microsoft Excel workbook and display it in multiple HTML Tables using a Tag Helper.
Start Sample

Supporting Files

The following file is utilized by this sample:

using System.Collections.Generic;

namespace Website.Pages.Support.Samples.RazorPages.TagHelpers
{
    public partial class MultipleRangesToTablesModel : PageModel
    {
        public List<SpreadsheetGear.IWorksheet> Worksheets { get; set; } = [];

        public void OnGet()
        {
            // Create a workbook from an Excel file.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/spicenorth.xlsx");

            // Loop through all worksheets in the workbook and add to the "Worksheets" list.  The sheet name will 
            // be used for a table title and the worksheet's used range as the contents of the HTML table.
            // using a Tag Helper.
            foreach (SpreadsheetGear.IWorksheet worksheet in workbook.Worksheets)
            {
                Worksheets.Add(worksheet);
            }
        }
    }
}

@page
@model MultipleRangesToTablesModel
@{  Layout = "_SamplesLayout"; }

@foreach(SpreadsheetGear.IWorksheet worksheet in Model.Worksheets)
{
  <h3>@worksheet.Name</h3>
  <range-to-table range="@worksheet.UsedRange" first-row-is-header="true"></range-to-table>
}
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 "";
        }
    }
}