Live Razor Page Samples

Excel Workbook Consolidation

This sample shows how to utilize the IRange.Copy method to dynamically copy and consolidate data from multiple workbooks. It also demonstrates using a Tag Halper to render the contents of a range, or downloading the resulting workbook.
Start Sample
Supporting Files

The following files are utilized by this sample:
using System;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class ExcelWorkbookConsolidationModel : PageModel
    {
        [BindProperty]
        public string Region { get; set; }

        // This range will be used to populate the HTML table.
        public SpreadsheetGear.IRange DataRange { get; set; }


        public void OnGet() { }


        public void OnPostRenderInTable()
        {
            if (Region == null)
                return;

            // Gets either the consolidated or regional workbook.
            SpreadsheetGear.IWorkbook workbook = GetSalesReportWorkbook();

            if (Region == "All")
            {
                // Consolidated workbook will have a single sheet and used range will contain all the report data.
                DataRange = workbook.Worksheets[0].UsedRange;
            }
            else
            {
                // For a regional workbook, get the range for the "YearSales" defined name.
                DataRange = workbook.Names["YearSales"].RefersToRange;
            }
        }

        public FileResult OnPostDownloadWorkbook()
        {
            SpreadsheetGear.IWorkbook workbook = GetSalesReportWorkbook();

            // Save workbook to stream using the Open XML (*.xlsx) file format compatible with Excel 2007 and later.
            System.IO.Stream workbookStream = workbook.SaveToStream(SpreadsheetGear.FileFormat.OpenXMLWorkbook);

            // Reset position to beginning of stream.
            workbookStream.Seek(0, System.IO.SeekOrigin.Begin);

            // Stream the Excel workbook to the client.
            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var fileName = $"Sales-{Region}.xlsx";
            return File(workbookStream, contentType, fileName);
        }


        private SpreadsheetGear.IWorkbook GetSalesReportWorkbook()
        {
            if (Region == "All")
            {
                // Get a new workbook with all regions consolidated.
                return GetWorkbookConsolidated();
            }
            else
            {
                // Get a workbook for the specified region
                return GetWorkbookForRegion(Region);
            }
        }

        private SpreadsheetGear.IWorkbook GetWorkbookForRegion(String region)
        {
            // Get the filename from the region name
            string filename;
            switch (region)
            {
                default:
                case "North":
                    filename = "spicenorth.xlsx";
                    break;
                case "South":
                    filename = "spicesouth.xlsx";
                    break;
                case "East":
                    filename = "spiceeast.xlsx";
                    break;
                case "West":
                    filename = "spicewest.xlsx";
                    break;
            }

            // Return a workbook from the filename
            return SpreadsheetGear.Factory.GetWorkbook("files/" + filename);
        }


        private SpreadsheetGear.IWorkbook GetWorkbookConsolidated()
        {
            // Create a new workbook and name the first sheet
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
            worksheet.Name = "Total Sales";

            // Copy all region templates to the new worksheet
            CopyRegion(worksheet, "North", SpreadsheetGear.PasteOperation.None);
            CopyRegion(worksheet, "South", SpreadsheetGear.PasteOperation.Add);
            CopyRegion(worksheet, "East", SpreadsheetGear.PasteOperation.Add);
            CopyRegion(worksheet, "West", SpreadsheetGear.PasteOperation.Add);

            // Auto size all worksheet columns which contain data
            worksheet.UsedRange.Columns.AutoFit();

            return workbook;
        }


        private void CopyRegion(SpreadsheetGear.IWorksheet dstWorksheet, String region, SpreadsheetGear.PasteOperation pasteOperation)
        {
            // Open the specified region workbook and get the source range
            SpreadsheetGear.IWorkbook srcWorkbook = GetWorkbookForRegion(region);
            SpreadsheetGear.IRange srcRange = srcWorkbook.Names["YearSales"].RefersToRange;

            // Set up our destination range to match the size of the source range
            String address = srcRange.Address;
            SpreadsheetGear.IRange dstRange = dstWorksheet.Cells[address];

            // Copy the source range values and formats to the destination range
            // We have to call copy twice here since there is currently no PasteType
            // which does values and all formats together.  This is a limitation in
            // the Excel API, but probably should be added to the SpreadsheetGear API.
            srcRange.Copy(dstRange,
                SpreadsheetGear.PasteType.Values,
                pasteOperation, true, false);

            srcRange.Copy(dstRange,
                SpreadsheetGear.PasteType.Formats,
                pasteOperation, true, false);
        }
    }
}
@page
@model ExcelWorkbookConsolidationModel

<div class="row">
  <div class="col-sm-12 col-md-4 mb-3">
    <div class="card">
      <h2 class="card-header">Choose a region</h2>
      <div class="card-body">
        <!-- Input Form -->
        <form asp-page="ExcelWorkbookConsolidation" asp-page-handler="RenderInTable" method="post" id="sales-form">
          <label>Sales by Region</label>

          <div class="form-check">
            <input type="radio" asp-for="Region" class="form-check-input" value="North" id="region-north">
            <label for="region-north" class="form-check-label">North</label>
          </div>
          <div class="form-check">
            <input type="radio" asp-for="Region" class="form-check-input" value="South" id="region-south">
            <label for="region-south" class="form-check-label">South</label>
          </div>
          <div class="form-check">
            <input type="radio" asp-for="Region" class="form-check-input" value="East" id="region-east">
            <label for="region-east" class="form-check-label">East</label>
          </div>
          <div class="form-check">
            <input type="radio" asp-for="Region" class="form-check-input" value="West" id="region-west">
            <label for="region-west" class="form-check-label">West</label>
          </div>
          <div class="form-check">
            <input type="radio" asp-for="Region" class="form-check-input" value="All" id="region-all">
            <label for="region-all" class="form-check-all">All</label>
          </div>

          <div class="text-center mt-4">
            <button formaction="@Url.Page("ExcelWorkbookConsolidation", "DownloadWorkbook")" disabled="@(Model.Region == null)" class="btn btn-primary" title="@(Model.Region == null ? "Select Region to download workbook" : "Download region data to workbook")"><i class="fas fa-arrow-alt-to-bottom"></i> Download Workbook</button>
          </div>
        </form>
      </div>
    </div>
  </div>

  <div class="col-sm-12 col-md-8 mb-3">
    <range-to-table range="@Model.DataRange" first-row-is-header="true"></range-to-table>
  </div>
</div>

@section Scripts
{
  <script>
    $(function () {
      // Automatically post form to refresh table when a radio button is clicked.
      let $form = $("#sales-form");
      $form.find("input[type=radio]").change(function () {
        $form.submit();
      });
    });
  </script>
}
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 "";
        }
    }
}