Live Razor Page Samples

Gantt Chart

This sample shows how to create a new workbook, add some values, add a chart, use stacked bars and various formatting to simulate a gantt chart, and stream it to Microsoft Excel.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Charting
{
    public partial class GanttChartModel : 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;

            // Load category labels using multiple cell text reference and iteration.
            int task = 1;
            foreach (SpreadsheetGear.IRange cell in cells["A1:A8"])
                cell.Formula = "Task " + task++;

            // Start with zero and use formulas to calculate each additional start value.
            cells["B1"].Formula = "0";
            cells["B2:B8"].Formula = "=B1+C1";

            // Load random duration values
            cells["C1:C8"].Formula = "=INT(RAND() * 10) + 2";

            // 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(3.0);
            double top = windowInfo.RowToPoints(1.0);
            double right = windowInfo.ColumnToPoints(10.0);
            double bottom = windowInfo.RowToPoints(18.0);
            SpreadsheetGear.Charts.IChart chart =
                worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart;

            // Set the chart's source data range, plotting series in columns.
            SpreadsheetGear.IRange source = cells["A1:C8"];
            chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

            // Set the chart type to stacked bar to simulate a gantt chart.
            chart.ChartType = SpreadsheetGear.Charts.ChartType.BarStacked;

            // Set the distance between bars as a percentage of the bar width.
            chart.ChartGroups[0].GapWidth = 100;

            // Hide the first (Start) series values by setting the fill to none.
            SpreadsheetGear.Charts.ISeries seriesStart = chart.SeriesCollection[0];
            seriesStart.Format.Fill.Visible = false;

            // Change the theme color of the second (Duration) series.
            SpreadsheetGear.Charts.ISeries seriesDuration = chart.SeriesCollection[1];
            seriesDuration.Format.Fill.ForeColor.ThemeColor =
                SpreadsheetGear.Themes.ColorSchemeIndex.Accent3;

            // Reverse the category axis so that values are shown top to bottom.
            chart.Axes[SpreadsheetGear.Charts.AxisType.Category].ReversePlotOrder = true;

            // Add a chart title and change the font size.
            chart.HasTitle = true;
            chart.ChartTitle.Text = "Estimated Days To Completion";
            chart.ChartTitle.Font.Size = 12;

            // Delete the legend.
            chart.HasLegend = false;

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