Live Razor Page Samples

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

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class ExcelReportWithChartModel : PageModel
    {
        public FileResult OnGet()
        {
            // Create a new workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
            SpreadsheetGear.IRange cells = worksheet.Cells;

            // Set the worksheet name and turn off the default gridlines.
            worksheet.Name = "2010 Sales";
            worksheet.WindowInfo.DisplayGridlines = false;

            // Add worksheet title.
            SpreadsheetGear.IRange titleCell = cells["B1"];
            titleCell.Value = "Excel Report with Chart\nCreated with SpreadsheetGear and ASP.NET Core";
            titleCell.HorizontalAlignment = SpreadsheetGear.HAlign.Center;
            titleCell.VerticalAlignment = SpreadsheetGear.VAlign.Center;
            titleCell.Style = workbook.Styles["Heading 1"];
            titleCell.RowHeight = 48.0;
            cells["B1:G1"].Merge();

            // Create column headings for four regions and a total column.
            SpreadsheetGear.IRange columnHeadingCells = cells["B2:G2"];
            columnHeadingCells.Value = new string[,] { { "", "North", "South", "East", "West", "Total" } };
            SpreadsheetGear.IStyle columnHeadingStyle = workbook.Styles["Heading 2"];
            columnHeadingCells.Style = columnHeadingStyle;
            columnHeadingCells.HorizontalAlignment = SpreadsheetGear.HAlign.Center;

            // Create row headings for four quarters and a total row.
            SpreadsheetGear.IRange rowHeadingCells = cells["B3:B7"];
            rowHeadingCells.Value = new string[,] { { "Q1" }, { "Q2" }, { "Q3" }, { "Q4" }, { "Total" } };
            rowHeadingCells.HorizontalAlignment = SpreadsheetGear.HAlign.Right;

            // Create random data using multiple cell range.
            cells["C3:F6"].Formula = "=RAND()*1000000";

            // Add formulas which use the SUM worksheet function to total
            // the sales for each quarter and sales for each region.
            cells["G3:G6"].Formula = "=SUM(C3:F3)";
            cells["C7:G7"].Formula = "=SUM(C3:C6)";

            // Format the row headings and row totals.
            SpreadsheetGear.IRange rowHeadingAndTotalCells = cells["B3:B6,G3:G6"];
            rowHeadingAndTotalCells.Font.Bold = true;
            rowHeadingAndTotalCells.Font.Size = rowHeadingAndTotalCells.Font.Size + 1;

            // Format the grand totals.
            SpreadsheetGear.IRange grandTotalCells = cells["B7:G7"];
            grandTotalCells.Font.Bold = true;
            grandTotalCells.Font.Size = grandTotalCells.Font.Size + 2;
            grandTotalCells.Borders[SpreadsheetGear.BordersIndex.EdgeTop].Weight = SpreadsheetGear.BorderWeight.Medium;
            grandTotalCells.Borders[SpreadsheetGear.BordersIndex.EdgeBottom].LineStyle = SpreadsheetGear.LineStyle.Double;

            // Format data with "Currency (0)" named cell style.
            cells["C3:G7"].Style = workbook.Styles["Currency [0]"];

            // Add a conditional format to make every other row use 
            // the color from the Heading 2 bottom border.
            SpreadsheetGear.IFormatCondition condition = cells["B3:G6"].FormatConditions.Add(
                SpreadsheetGear.FormatConditionType.Expression,
                // Operator Does hnot matter for Expression
                SpreadsheetGear.FormatConditionOperator.Between,
                "=MOD(ROW(),2)=0", null);
            condition.Interior.Color = columnHeadingStyle.Borders[SpreadsheetGear.BordersIndex.EdgeBottom].Color;

            // Autofit column B and set other columns to absolute widths since
            // they use random numbers, which could lead to "#######" showing up
            // in cells if they are autofitted.
            cells["B:B"].Columns.AutoFit();
            cells["C:F"].ColumnWidth = 13.6;
            cells["G:G"].ColumnWidth = 14.9;

            // Add a chart to the worksheet's shape collection.
            // NOTE: Calculate the coordinates of the chart by converting row
            //       and column coordinates to points.  Use fractional row 
            //       and colum values to get coordinates anywhere in between 
            //       row and column boundaries.
            double left = windowInfo.ColumnToPoints(1.0) + (72.0 / 6.0);
            double top = windowInfo.RowToPoints(8.0) + 3;
            double right = windowInfo.ColumnToPoints(7.0) - (72.0 / 6.0);
            double bottom = windowInfo.RowToPoints(22.0) - 3;
            SpreadsheetGear.Shapes.IShape chartShape =
                worksheet.Shapes.AddChart(left, top, right - left, bottom - top);
            SpreadsheetGear.Charts.IChart chart = chartShape.Chart;

            // Turn off the border around the chart for a cleaner look.
            chart.ChartArea.Format.Line.Visible = false;

            // Give the chart shape a name which will be used to refer to the chart
            // in other samples.
            chartShape.Name = "SalesByRegionChart";

            // Set the chart's source data range for the individual sales figures,
            // plotting series in columns.
            SpreadsheetGear.IRange source = cells["B2:F6"];
            chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

            // Set the chart type.
            chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnClustered;

            // Add a new series for the totals and link it's series name 
            // and values to the worksheet.
            SpreadsheetGear.Charts.ISeries totalsSeries = chart.SeriesCollection.Add();
            totalsSeries.Name = "=G2";
            totalsSeries.Values = "=G3:G6";

            // Change the total series chart type and plot the total series 
            // on the secondary axis.
            //
            // NOTE: This creates a combination chart using multiple chart groups
            //       and utilizes both primary and secondary axes sets.
            totalsSeries.ChartType = SpreadsheetGear.Charts.ChartType.Line;
            totalsSeries.AxisGroup = SpreadsheetGear.Charts.AxisGroup.Secondary;

            // Add a chart title, set the text to refer to the title in the
            // worksheet, and change the font size.
            chart.HasTitle = true;
            chart.ChartTitle.Text = "=B1";
            chart.ChartTitle.Font.Size = 12;

            // Change the legend position to the bottom of the chart and
            // set the legend font to bold.
            chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom;
            chart.Legend.Font.Bold = true;

            // Change the value major gridlines from the default black to gray.
            chart.Axes[SpreadsheetGear.Charts.AxisType.Value].MajorGridlines.Format.Line.ForeColor.RGB = SpreadsheetGear.Colors.Gray;

            // Add a defined name which will be used by other samples to refer 
            // to the range of cells containing the data and the chart.       
            workbook.Names.Add("SalesDataAndChartRange", "=B1:G22");

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