Live Razor Page Samples

Dynamic Chart Gallery

This sample dynamically creates a chart gallery using a mix of server-side Razor Pages and client-side JavaScript Ajax calls, which demonstrates rendering some of the most commonly used Excel charting features from a single Excel 2007-2019 Open XML workbook.

Supporting Files

The following file is utilized by this sample:

using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Imaging
{
    public partial class ChartGalleryModel : PageModel
    {
        public List<string> Categories { get; set; } = [];

        public void OnGet()
        {
            // Initial page load from server only needs to load the Categories. Subsequent "Options" items will be
            // populated from the client using JavaScript and an Ajax call to the "OnGetOptions" page handler method
            // found below.
            BuildCategories();
        }

        private void BuildCategories()
        {
            // Open the chart gallery workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/chartgallery.xlsx");

            // Build the category list using worksheet names.
            foreach (SpreadsheetGear.IWorksheet worksheet in workbook.Worksheets)
                Categories.Add(worksheet.Name);
        }

        public IActionResult OnGetOptions(string category)
        {
            // Open the chart gallery workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/chartgallery.xlsx");
            
            // Get the worksheet based on the provided category name.
            SpreadsheetGear.IWorksheet curWorksheet = workbook.Worksheets[category];

            // Build the Options list using shape names.
            List<string> options = [];
            foreach (SpreadsheetGear.Shapes.IShape shape in curWorksheet.Shapes)
                options.Add(shape.Name);

            // Return options as Json result.
            return new JsonResult(options);
        }
    }
}
@page
@model ChartGalleryModel

<div class="row">
  <div class="col-md-12 col-lg-4 col-xl-5 mb-4">
    <div class="card">
      <h2 class="card-header">Chart Gallery Details</h2>
      <div class="card-body small">
        <p>
          This sample dynamically creates a chart gallery which demonstrates some of the most commonly used 
          Excel charting features using a single Open XML workbook from Excel 2007-2019 or Excel for Office 365.
        </p>

        <p>
          First, the Category list box is populated from the list of worksheet names. Then, for the currently 
          selected worksheet (category), the Options list box is populated from the names of the individual charts 
          on that worksheet. Finally, the <a asp-page="/Support/Samples/RazorPages/View" asp-route-urlSlug="SpreadsheetGearImage"
          target="_blank">SpreadsheetGear Image Rendering Utility</a> is used to render the image by setting the 
          ImageUrl property of the image to:
        </p>
          <div class="mb-2">
            <label for="selected-category" class="form-label">Categories</label>
            <select id="selected-category" asp-items="new SelectList(Model.Categories)" class="form-select ms-2 me-2"></select>
          </div>
          <div class="mb-2">
            <label for="selected-option" class="form-label">Options</label>
            <select id="selected-option" size="6" class="form-select ms-2 me-2"></select>
          </div>
      </div>
    </div>
  </div>

  <div class="col-md-12 col-lg-8 col-xl-7">
    <div class="card">
      <h5 class="card-header">
        Image Generated by 
        <a asp-page="/Support/Samples/RazorPages/View" asp-route-urlSlug="SpreadsheetGearImage" target="_blank">
          SpreadsheetGear Image Rendering Utility
        </a>
      </h5>
      <div class="card-body">
        <div class="alert alert-info">
          <div class="fw-bold">URL of <span class="font-monospace">&lt;img src="&mldr;"&gt;</span></div> 
          <div class="small font-monospace" style="font-size: 9pt;"><a id="image-url-link" href="#" target="_blank"></a></div>
        </div>
        <div class="text-center">
          <img id="image-tag" style="border: 2px solid black; min-width: 100px; min-height:100px;" 
                alt="Rendered chart image will be displayed here." />
        </div>
      </div>
    </div>
  </div>
</div>

@section Scripts
{
  <script>
    // Called when page is initialized.
    $(function () {
      // Event to refresh the Options select list when the Categories selection changes.
      $("#selected-category").change(function () {
        changeCategory($(this).val(), null);
      });

      // Event to trigger refresh of the <img> tag when the Option selection changes
      $("#selected-option").change(function () {
        refreshImage();
      });

      // Initially select the "Combination" category and "Area - Column - Line" Option.
      changeCategory("Combination", "Area - Column - Line");
    });


    /**
     * Updates Categories to the specified option and refreshes the Options list.
     *
     * @@param {string} category - The category to select.
     * @@param {string} [defaultOption] - The Option sub-type of Category to select.  If not provided, the first 
     * option in the list is selected.
     */
    function changeCategory(category, defaultOption) {
      // Set Categories to selected value.
      $("#selected-category").val(category);

      // Clear existing Options list.
      let $options = $("#selected-option");
      $options.empty();

      // Make Ajax request to the the "OnGetOptions" Razor Page handler to get options associated with this Category.
      $.getJSON("/Support/Samples/RazorPages/Imaging/ChartGallery?handler=Options&category=" + category,
        function (data) {
          // Populate list with new Option values for this Category.
          for (let optionItem of data)
            $options.append(`<option value="${optionItem}">${optionItem}</option>`);

          // Resize Options <select> size to the number of items in that Category.
          $options.attr("size", data.length);

          // Select an option if one was provided (i.e., when initializing the page).
          if (defaultOption)
            $options.val("Area - Column - Line");
          // Otherwise select the first option in the list
          else
            $options.val($options.find("option:first").val());

          // Force trigger a change event to refresh the URL used by the <img> tag.
          $options.change();
        })
        .fail(function () {
          window.alert("An error occurred loading Options for the selected Category.  Please try again.");
        });
    }


    /**
     * Updates the page's <img> "src" attribute to render the chart for the current Categories and Options selections. 
     */
    function refreshImage() {
      let selectedCategory = $("#selected-category").val();
      let selectedOption = $("#selected-option").val();

      // Construct a URL to the SpreadsheetGear Image Rendering Utility using the selected Categories and 
      // Options.
      let imageRenderingUtility = "/Support/Samples/RazorPages/Imaging/SpreadsheetGearImage";
      let file = "?FileName=" + encodeURIComponent("chartgallery.xlsx");
      let worksheet = "&WorksheetName=" + encodeURIComponent(selectedCategory);
      let shape = "&ShapeName=" + encodeURIComponent(selectedOption);
      let url = imageRenderingUtility + file + worksheet + shape;

      // Update <img> tag's src attribute to new URL.
      $("#image-tag").attr("src", url);

      // Update link displayed on the sample page to reflect the new Url.
      let fullUrl = "https://www.spreadsheetgear.com" + url;
      $("#image-url-link").attr("href", fullUrl).html(fullUrl);
    }
  </script>
}