Live Razor Page Samples

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

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class DataSetToWorkbookFormatsAndFormulasModel : PageModel
    {
        public FileResult OnGet()
        {
            // Create a DataSet from an XML file.
            System.Data.DataSet dataset = new System.Data.DataSet();
            dataset.ReadXml(INTERNAL_GetSupportingFileStream("nfl.xml"));
            dataset.ReadXml("files/nfl.xml");

            // Create a workbook set to hold the template workbook and the report workbook.
            SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();

            // Open the template workbook which contains formats, borders and formulas.
            SpreadsheetGear.IWorkbook templateWorkbook = workbookSet.Workbooks.Open("files/nfltemplate.xlsx");

            // Get the template range from a defined name in the template workbook.
            SpreadsheetGear.IRange templateRange = templateWorkbook.Names["NFLDivisionFormat"].RefersToRange;

            // Get the number of rows and columns in the template range.
            int templateRangeRowCount = templateRange.RowCount;
            int templateRangeColCount = templateRange.ColumnCount;

            // Create a new workbook with one blank worksheet to hold the new Excel Report.
            SpreadsheetGear.IWorkbook reportWorkbook = workbookSet.Workbooks.Add();
            SpreadsheetGear.IWorksheet reportWorksheet = reportWorkbook.Worksheets[0];
            reportWorksheet.WindowInfo.DisplayGridlines = false;
            reportWorksheet.Name = dataset.DataSetName;

            // Start at cell B2
            int row = 1;
            int col = 1;

            // Insert each DataTable from the DataSet...
            foreach (System.Data.DataTable datatable in dataset.Tables)
            {
                // Get the destination range in the report worksheet.
                SpreadsheetGear.IRange dstRange = reportWorksheet.Cells[row, col,
                    row + templateRangeRowCount - 1, col + templateRangeColCount - 1];

                // Copy the template range formats and formulas to the report worksheet.
                templateRange.Copy(dstRange, SpreadsheetGear.PasteType.All,
                    SpreadsheetGear.PasteOperation.None, false, false);

                if (row == 1)
                {
                    // Copy the template range column widths to the report worksheet once.
                    templateRange.Copy(dstRange, SpreadsheetGear.PasteType.ColumnWidths,
                        SpreadsheetGear.PasteOperation.None, false, false);
                }

                // Use the TableName for the title of the range - this is a merged
                // cell centered across the top of the destination range.
                reportWorksheet.Cells[row, col].Formula = datatable.TableName;

                // Add a defined name for the new destination range. This defined
                // name will be adjusted by IRange.CopyFromDataTable, allowing us
                // to skip over the newly inserted range and any summary rows
                // added by the template.
                SpreadsheetGear.IName dstRangeName = reportWorkbook.Names.Add(
                    datatable.TableName.Replace(" ", ""), "=" + dstRange.Address);

                // Insert the DataTable into the worksheet. This will adjust the defined name,
                // as well as the formats, cell borders and formulas which were copied from
                // the template workbook.
                reportWorksheet.Cells[row + 1, col, row + 3, col].CopyFromDataTable(datatable,
                    SpreadsheetGear.Data.SetDataFlags.InsertCells);

                // Update the row counter to the end of the inserted table
                SpreadsheetGear.IRange range = dstRangeName.RefersToRange;
                row = range.Row + range.RowCount + 1;
            }

            // Save workbook to stream using the Open XML (*.xlsx) file format compatible with Excel 2007 and later.
            System.IO.Stream workbookStream = reportWorkbook.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-DataSetToWorkbookFormatsAndFormulas.xlsx";
            return File(workbookStream, contentType, fileName);
        }
    }
}