Live Razor Page Samples

DataTable to Excel Workbook with Formats and Formulas

This sample shows how to insert data from a DataTable into an Excel workbook, merging the data with formatting and formulas. The results are displayed in custom Tag Helper.

Supporting Files

The following files are utilized by this sample:

namespace Website.Pages.Support.Samples.RazorPages.TagHelpers
{
    public partial class DataTableToTemplateWorkbookModel : PageModel
    {
        public SpreadsheetGear.IRange Range { get; set; }

        public void OnGet()
        {
            // Create a DataSet from an XML file and retrieve an order table.
            System.Data.DataSet dataset = new System.Data.DataSet();
            dataset.ReadXml(INTERNAL_GetSupportingFileStream("spiceorder.xml"));
            dataset.ReadXml("files/spiceorder.xml");
            System.Data.DataTable datatable = dataset.Tables["OrderItems"];

            // Open the template workbook, which contains number formats and formulas, and get an IRange from a defined name
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/spiceordertemplate.xlsx");
            SpreadsheetGear.IRange range = workbook.Names["SetDataRange"].RefersToRange;

            // Insert the DataTable into the template worksheet range. The InsertCells
            // flag will cause the formatted range to be adjusted for the inserted data.
            range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.InsertCells);

            // Set range (from a defined name) to be displayed by the Tag Helper.
            Range = workbook.Names["GetDataRange"].RefersToRange;
        }
    }
}
@page
@model DataTableToTemplateWorkbookModel

<range-to-table range="@Model.Range" 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 "";
        }
    }
}