Live Razor Page Samples

DataTable to Excel Workbook with Chart

This sample shows how to load a workbook containing a chart, insert data from a DataTable which automatically expands the chart link range, and stream it to Microsoft Excel.

Supporting Files

The following files are utilized by this sample:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Reporting
    public partial class DataTableToExcelWorkbookChartModel : PageModel
        public FileResult OnGet()
            // Open a workbook template containing a chart
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook("files/chartsalesbyquarter.xlsx");
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["2020 Sales"];

            // Create a DataSet from an XML file.  Modify this code to use
            // any DataSet such as one returned from a database query.
            System.Data.DataSet dataset = new System.Data.DataSet();
            System.Data.DataTable datatable = dataset.Tables["Products"];

            // Copy the DataTable to the worksheet starting at the top left cell
            SpreadsheetGear.IRange range = worksheet.Cells[0, 0, 2, 0];
            range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.InsertCells);

            // Auto size all worksheet columns which contain data

            // 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 the beginning of the 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-DataTableToExcelWorkbook.xlsx";
            return File(workbookStream, contentType, fileName);