Live Razor Page Samples

Excel Report with Chart Linked to Defined Name

This sample shows how to load a workbook containing a chart series linked to a defined name, change the defined name range reference, and stream it to Microsoft Excel. For charts with multiple series, use Excel to set each chart series to refer to a unique defined name.

Start Sample

Supporting Files

The following file is utilized by this sample:

chartdefinedname.xlsx

Razor Page C#

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class ExcelReportWithChartDefinedNameModel : PageModel
    {
        public FileResult OnGet()
        {
            // Open a workbook template containing a chart
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/chartdefinedname.xlsx");
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

            // Load random data and format as $ using a multiple cell range.
            SpreadsheetGear.IRange range = worksheet.Cells["A1:A10"];
            range.Formula = "=1000 + (RAND() * 2000)";
            range.NumberFormat = "$#,##0_);($#,##0)";

            // Change the chart series link range by updating the defined name 
            // reference to point at the random data range
            worksheet.Names["ChartSeries1"].RefersTo = "=" + range.Address;

            // 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 = "SpreadsheetGear-Sample-ExcelReportWithChartDefinedName.xlsx";
            return File(workbookStream, contentType, fileName);
        }
    }
}