Live Razor Page Samples

DataTable to Excel Workbook

This sample shows how to create a new workbook, copy data from a DataTable, and stream it to Microsoft Excel.

Supporting Files

The following file is utilized by this sample:

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

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class DataTableToExcelWorkbookModel : PageModel
    {
        public FileResult OnGet()
        {
            // 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();
            dataset.ReadXml(INTERNAL_GetSupportingFileStream("spiceorder.xml"));
            dataset.ReadXml("files/spiceorder.xml");
            System.Data.DataTable datatable = dataset.Tables["OrderItems"];

            // Create a new workbook and worksheet.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            worksheet.Name = "Spice Order";

            // Get the top left cell for the DataTable.
            SpreadsheetGear.IRange range = worksheet.Cells["A1"];

            // Copy the DataTable to the worksheet range.
            range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.None);

            // Auto size all worksheet columns which contain data
            worksheet.UsedRange.Columns.AutoFit();

            // 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-ExcelReportOutlines.xlsx";
            return File(workbookStream, contentType, fileName);
        }
    }
}