SpreadsheetGear

https://www.spreadsheetgear.com
Admin
SpreadsheetGear is an Excel-compatible spreadsheet component library for .NET that enables reading,...
Tokens:106,216
Snippets:691
Trust Score:9
Update:6 months ago
Show doc for...
Tokens:
Raw
### Install SpreadsheetGear via .NET CLI

Source: https://www.spreadsheetgear.com/downloads/signed-license

Run this command in the project directory to add the SpreadsheetGear package.

```bash
dotnet add package SpreadsheetGear
```

--------------------------------

### Install SpreadsheetGear via Package Manager Console

Source: https://www.spreadsheetgear.com/downloads/signed-license

Run this command in the Visual Studio Package Manager Console to add the SpreadsheetGear package.

```powershell
Install-Package SpreadsheetGear
```

--------------------------------

### Add SpreadsheetGear.Windows Package

Source: https://www.spreadsheetgear.com/nuget/spreadsheetgear.windows/project

Use the .NET CLI to add the SpreadsheetGear.Windows package to your project. This command installs the necessary components for using SpreadsheetGear in your .NET application.

```bash
dotnet add package SpreadsheetGear.Windows
```

--------------------------------

### Create Gantt Chart with SpreadsheetGear API

Source: https://www.spreadsheetgear.com/support/samples/api/charting-gantt

This C# code snippet demonstrates how to create a Gantt chart using SpreadsheetGear. It involves initializing a workbook, setting up task labels, durations, and start times, adding a stacked bar chart, and configuring its appearance to simulate a Gantt chart.

```csharp
// Create a new workbook and get a reference to the active worksheet, window info, 
// and cells.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
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["G2:G7"])
    cell.Formula = "Task " + task++;

// Start with zero and use formulas to calculate each additional start value.
cells["H2"].Formula = "0";
cells["H3:H7"].Formula = "=H2+I2";

// Load random duration values
cells["I2:I7"].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 column values to get 
//       coordinates anywhere in between row and column boundaries.
double left = windowInfo.ColumnToPoints(0.15);
double top = windowInfo.RowToPoints(0.5);
double right = windowInfo.ColumnToPoints(5.85);
double bottom = windowInfo.RowToPoints(13.5);
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["G2:I7"];
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.
chart.SeriesCollection[0].Format.Fill.Visible = false;

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

// 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;

```

--------------------------------

### Create and Open Workbooks with SpreadsheetGear

Source: https://www.spreadsheetgear.com/support/samples/api/workbook-opening-files

Demonstrates creating a new workbook, opening various file formats, using IWorkbookSet, and applying CultureInfo for locale-sensitive content.

```csharp
// Create a new workbook
SpreadsheetGear.IWorkbook workbookNew = SpreadsheetGear.Factory.GetWorkbook();

// When opening existing files SpreadsheetGear will automatically detect the file format 
// and open it accordingly.

// Open an Excel Open XML (*.xlsx) file:
SpreadsheetGear.IWorkbook workbook1 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\workbook1.xlsx");

// Open an Excel "Macro Enabled" Open XML (*.xlsm) file:
SpreadsheetGear.IWorkbook workbook2 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\workbook2.xlsm");

// Open an Excel 97-2003 (*.xls) file:
SpreadsheetGear.IWorkbook workbook3 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\workbook3.xls");

// Open a text-based ASCII comma-delimited file (commonly saved with a *.csv file extension):
SpreadsheetGear.IWorkbook workbook4 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\data.csv");

// Open a text-based Unicode tab-delimited file (commonly saved with a *.txt file extension):
SpreadsheetGear.IWorkbook workbook5 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\data.txt");

// If you have already created an instance of an IWorkbookSet, use the 
// IWorkbookSet.Workbooks.Open(...) method to open a workbook within that workbook set object:
SpreadsheetGear.IWorkbook workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook workbook6 = workbookSet.Workbooks.Open(@"c:\path\to\workbook6.xlsx");

// Specify a second CultureInfo argument to control how locale-sensitive workbook content is 
// interpreted. For instance, a cell with a date NumberFormat could be formatted as "m/d/yyyy" if 
// opened with an "en-US" CultureInfo (this is the default if no CultureInfo is specified) or 
// "dd/mm/yyyy" if an "en-GB" CultureInfo is specified.
SpreadsheetGear.IWorkbook workbook7 = SpreadsheetGear.Factory.GetWorkbook(@"c:\path\to\workbook7.xlsx",
    System.Globalization.CultureInfo.GetCultureInfo("en-GB"));
```

--------------------------------

### Create and Open Excel and Text-Based Workbook Files in C#

Source: https://www.spreadsheetgear.com/support/samples/api

Shows how to create a new workbook from scratch and how to open existing Excel files (.xlsx, .xls) and text files (.csv, .txt). Ensure the file paths are correct.

```csharp
using SpreadsheetGear;

// ...

// Create a new workbook
IWorkbook newWorkbook = Factory.GetWorkbook();

// Open an existing Excel file
IWorkbook openedWorkbook = Factory.GetWorkbook("ExistingWorkbook.xlsx");

// Open a text file (e.g., CSV)
IWorkbook csvWorkbook = Factory.GetWorkbook("Data.csv");
```

--------------------------------

### Report Model Definition

Source: https://www.spreadsheetgear.com/support/samples/blazor/server

Defines the data model for the sales report form, including start and end dates for the report, and an option for downloading an Excel file.

```csharp
public class ReportModel
        {
            public int YearStart { get; set; } = DateTime.Today.Year;
            public int MonthStart { get; set; } = 1;

            public int YearEnd { get; set; } = DateTime.Today.Year;
            public int MonthEnd { get; set; } = 12;

            public bool DownloadExcelFile { get; set; }
        }
```

--------------------------------

### Generate Sample Sales Data in C#

Source: https://www.spreadsheetgear.com/support/samples/razor-pages/ef-core-to-workbook

Use this method to create a list of sales records for a specified number of years. It simulates regional sales growth and randomizes daily sales within each month. Ensure 'Sale' and 'Product' classes are defined.

```csharp
new Product(10001, "Basil", 1.99m),
            new Product(10002, "Black Pepper - Fine", 2.99m),
            new Product(10003, "Cayenne Pepper", 3.99m),
            new Product(10201, "Chili Powder", 2.99m),
            new Product(10202, "Cumin - Ground", 2.99m),
            new Product(10203, "Garlic Powder", 1.99m),
            new Product(10401, "Marjoram", 3.99m),
            new Product(10402, "Onion Powder", 1.99m),
            new Product(10403, "Oregano", 2.99m),
            new Product(10801, "Red Pepper - Crushed", 2.99m),
            new Product(10802, "Rosemary", 3.99m),
            new Product(10803, "Thyme", 3.99m)
```

```csharp
public static List<string> Regions = new List<string>() {"North", "South", "East", "West"};

        public static List<Sale> GenerateSales(int numYears)
        {
            var sales = new List<Sale>();
            var rand = new Random();

            DateTime startDate = new DateTime(DateTime.Today.Year - numYears, 1, 1);
            DateTime endDate = new DateTime(DateTime.Today.Year, 1, 1);

            // Used to control the rate at which each region's sales increase over time.
            var regionSalesGrowthRates = new Dictionary<string, (double Min, double Max)>() {
                { "North", (60.0, 80.0) },
                { "South", (40.0, 60.0) },
                { "East", (20.0, 40.0) },
                { "West", (5.0, 20.0) }
            };

            // Populate sales in a way that increases sales over time and at varying velocities depending on
            // the region.  This is done to provide some visual separation of each series when charting based
            // on regions...or just to make the charts prettier :)
            int idCounter = 1;
            foreach (var region in Regions)
            {
                int monthCounter = 1;
                DateTime currentMonth = startDate;

                // Increments 1 month at a time.
                while (currentMonth < endDate)
                {
                    // Let each monthly sales vary by some random but bounded amount, thereby producing a non-linear but 
                    // still-growing sales trend.  Usage of "regionSalesGrowthRates" allows each region to grow at a 
                    // different rate from other regions.
                    double monthlySalesTotalMin = regionSalesGrowthRates[region].Min * monthCounter;
                    double monthlySalesTotalMax = regionSalesGrowthRates[region].Max * monthCounter;
                    double randomBoundedMonthlySales = rand.Next((int)monthlySalesTotalMin, (int)monthlySalesTotalMax);

                    // Fill this month with sales until the sales total exceeds randomBoundedMonthlySales
                    double totalMonthlySales = 0.0;
                    do
                    {
                        var sale = new Sale() {
                            Id = idCounter++,
                            // Randomize specific day of month
                            PurchaseDate = new DateTime(currentMonth.Year, currentMonth.Month, 
                                rand.Next(1, DateTime.DaysInMonth(currentMonth.Year, currentMonth.Month) + 1)),
                            Region = region,
                            Product = Products[rand.Next(Products.Count)],
                            Quantity = rand.Next(1, 25)
                        };
                        sales.Add(sale);
                        totalMonthlySales += (double)(sale.Product.UnitCost * sale.Quantity);
                    } while (totalMonthlySales < randomBoundedMonthlySales);

                    // Setup for next iteration
                    currentMonth = currentMonth.AddMonths(1);
                    monthCounter++;
                }
            }

            return sales;
        }
```

--------------------------------

### Apply Rich-Text Formatting to Cell Content in C#

Source: https://www.spreadsheetgear.com/support/samples/api/range-formatting-font-rich-text-formatting

Uses IRange.GetCharacters to target specific character ranges for individual font styling. The start index is 0-based.

```csharp
// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Get a reference to cell A1.
SpreadsheetGear.IRange cell = cells["A1"];

// Add text to the cell.
cell.Value = "SpreadsheetGear Engine for .NET";

// Set font size for entire text to 24 points.
cell.Font.Size = 24;

// Set "Spreadsheet" text to dark blue.
SpreadsheetGear.ICharacters spreadsheetChars = cell.GetCharacters(0, 11);
spreadsheetChars.Font.Color = SpreadsheetGear.Color.FromArgb(255, 0, 0, 128);

// Set "Gear" text to a shade of red.
SpreadsheetGear.ICharacters gearChars = cell.GetCharacters(11, 4);
gearChars.Font.Color = SpreadsheetGear.Color.FromArgb(255, 233, 14, 14);

// Set "Engine" text style.
SpreadsheetGear.ICharacters engineChars = cell.GetCharacters(16, 6);
engineChars.Font.Bold = true;

// Set "for" text style.
SpreadsheetGear.ICharacters forChars = cell.GetCharacters(23, 3);
forChars.Font.Underline = SpreadsheetGear.UnderlineStyle.Single;

// Set ".NET Standard" text style.
SpreadsheetGear.ICharacters netStandardChars = cell.GetCharacters(27, 4);
netStandardChars.Font.Italic = true;

// AutoFit the column.
cell.EntireColumn.AutoFit();
```

--------------------------------

### Configure Worksheet Display Options in C#

Source: https://www.spreadsheetgear.com/support/samples/api/worksheet-display-options

Demonstrates how to initialize a workbook and adjust various display settings like zoom, split panes, gridlines, and range selection.

```csharp
// Create a new workbook and references to the worksheet, cells and window info.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;

// Add a formula to a cell.
cells["I7"].Formula = "=PI()";

// Set the zoom to 75%.  Available range is 10% - 400%.
windowInfo.Zoom = 75;

// Place a horizontal split so that 1 row is displayed above the split.
windowInfo.SplitRows = 1;

// Scroll the worksheet horizontally so that Column D is the first visible column.
windowInfo.ScrollColumn = 3;

// Place a vertical split so that 2 columns are displayed to the left of the split.
// Note that the split is positioned relative to the first visible column (Column D
// in this case) and not Column A, which effectively hides Columns A:B.
windowInfo.SplitColumns = 2;

// The vertical and horizontal splits we just created could be moved in Excel unless
// you enable FreezePanes.  Unfrozen panes also allows for the top and left panes to
// be scrollable.
windowInfo.FreezePanes = true;

// Enable or disable gridlines (enabled by default).
windowInfo.DisplayGridlines = true;

// Set gridline color.
windowInfo.GridlineColor = SpreadsheetGear.Colors.LightSkyBlue;

// Show or hide the row and column headers.
windowInfo.DisplayHeadings = false;

// Toggle showing either the calculated values of formulas or the formulas 
// themselves.
windowInfo.DisplayFormulas = true;

// Make a more complex range selection with multiple areas, and specify a specific
// cell within the second area of the range selection to be the active cell.
windowInfo.SetSelection(cells["F2:H4,H6:J8,I10:K12"], cells["I7"], 1);
```

--------------------------------

### Allow Selection of Unlocked Cells in C#

Source: https://www.spreadsheetgear.com/support/samples/api/worksheet-protection

Configure the EnableSelection property to UnlockedCells to allow users to select only cells that are not protected. This enhances usability by guiding user interaction.

```csharp
// Only allow unlocked cells to be selected
worksheet.EnableSelection = EnableSelection.UnlockedCells;
```

--------------------------------

### Generate Sample Sales Data in C#

Source: https://www.spreadsheetgear.com/support/samples/razor-pages/sql-datatable-to-workbook

Use this method to create a list of Sale objects with randomized purchase dates, regions, products, and quantities. The sales data is designed to show increasing trends over time and varying growth rates per region.

```csharp
        ];

        public static List<string> Regions = ["North", "South", "East", "West"];

        public static List<Sale> GenerateSales(int numYears)
        {
            var sales = new List<Sale>();
            var rand = new Random();

            DateTime startDate = new DateTime(DateTime.Today.Year - numYears, 1, 1);
            DateTime endDate = new DateTime(DateTime.Today.Year, 1, 1);

            // Used to control the rate at which each region's sales increase over time.
            var regionSalesGrowthRates = new Dictionary<string, (double Min, double Max)>() {
                { "North", (60.0, 80.0) },
                { "South", (40.0, 60.0) },
                { "East", (20.0, 40.0) },
                { "West", (5.0, 20.0) }
            };

            // Populate sales in a way that increases sales over time and at varying velocities depending on
            // the region.  This is done to provide some visual separation of each series when charting based
            // on regions...or just to make the charts prettier :)
            int idCounter = 1;
            foreach (var region in Regions)
            {
                int monthCounter = 1;
                DateTime currentMonth = startDate;

                // Increments 1 month at a time.
                while (currentMonth < endDate)
                {
                    // Let each monthly sales vary by some random but bounded amount, thereby producing a non-linear but 
                    // still-growing sales trend.  Usage of "regionSalesGrowthRates" allows each region to grow at a 
                    // different rate from other regions.
                    double monthlySalesTotalMin = regionSalesGrowthRates[region].Min * monthCounter;
                    double monthlySalesTotalMax = regionSalesGrowthRates[region].Max * monthCounter;
                    double randomBoundedMonthlySales = rand.Next((int)monthlySalesTotalMin, (int)monthlySalesTotalMax);

                    // Fill this month with sales until the sales total exceeds randomBoundedMonthlySales
                    double totalMonthlySales = 0.0;
                    do
                    {
                        var sale = new Sale() {
                            Id = idCounter++,
                            // Randomize specific day of month
                            PurchaseDate = new DateTime(currentMonth.Year, currentMonth.Month, 
                                rand.Next(1, DateTime.DaysInMonth(currentMonth.Year, currentMonth.Month) + 1)),
                            Region = region,
                            Product = Products[rand.Next(Products.Count)],
                            Quantity = rand.Next(1, 25)
                        };
                        sales.Add(sale);
                        totalMonthlySales += (double)(sale.Product.UnitCost * sale.Quantity);
                    } while (totalMonthlySales < randomBoundedMonthlySales);

                    // Setup for next iteration
                    currentMonth = currentMonth.AddMonths(1);
                    monthCounter++;
                }
            }

            return sales;
        }
    }
}
```

--------------------------------

### Blazor Date Selection Form

Source: https://www.spreadsheetgear.com/support/samples/blazor/server

A Blazor EditForm component used to capture start and end dates for report generation. It utilizes InputSelect components to iterate through years and months.

```razor
<div class="card">
  <h3 class="card-header">Report Details</h3>
  <div class="card-body">
    <div class="card-text">
      <EditForm Model="model" OnValidSubmit="HandleValidSubmit">
        <div class="row">
          <div class="col">
            <h5>Start Date</h5>
            <div class="mb-2 my-1">
              <label for="year-start" class="form-label mx-2">Year:</label>
              <InputSelect @bind-Value="model.YearStart" id="year-start" class="form-control">
                @for (int year = inputYearMin; year <= inputYearMax; year++)
                {
                  <option value="@year">@year</option>
                }
              </InputSelect>
            </div>
            <div class="mb-2 my-1">
              <label for="month-start" class="form-label mx-2">Month:</label>
              <InputSelect @bind-Value="model.MonthStart" id="month-start" class="form-control">
                @for (int month = 1; month <= 12; month++)
                {
	                <option value="@month">@(new DateTime(2000, month, 1).ToString("MMMM"))</option>
                }
              </InputSelect>
            </div>
          </div>

          <div class="col">
            <h5>End Date</h5>
            <div class="mb-2 my-1">
              <label for="year-end" class="form-label mx-2">Year:</label>
              <InputSelect @bind-Value="model.YearEnd" id="year-end" class="form-control">
                @for (int year = inputYearMin; year <= inputYearMax; year++)
                {
                  <option value="@year">@year</option>
                }
              </InputSelect>
            </div>
            <div class="mb-2 my-1">
```

--------------------------------

### Create a Combination Chart with SpreadsheetGear

Source: https://www.spreadsheetgear.com/support/samples/api/charting-combination

Initializes a workbook, populates sample data, and configures a combination chart with primary and secondary axes.

```csharp
// Create a new workbook and get a reference to the active worksheet, window info, 
// and cells.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Load some sample data.
cells["G3:G7"].Value =
    new string[,] { { "Mon" }, { "Tue" }, { "Wed" }, { "Thu" }, { "Fri" } };
cells["H2:K2"].Value =
    new string[,] { { "Breakfast", "Lunch", "Dinner", "Total" } };
cells["H3:J7"].Formula = "=INT(RAND() * 500) + 300";
cells["K3:K7"].Formula = "=SUM(H3:J3)";

// 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 column values to get 
//       coordinates anywhere in between row and column boundaries.
double left = windowInfo.ColumnToPoints(0.15);
double top = windowInfo.RowToPoints(0.5);
double right = windowInfo.ColumnToPoints(5.85);
double bottom = windowInfo.RowToPoints(13.5);
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["G2:K7"];
chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

// Set the chart type to a 100% stacked column.
chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked100;

// Get a reference to the chart's series collection and each series.
SpreadsheetGear.Charts.ISeriesCollection seriesCollection = chart.SeriesCollection;
SpreadsheetGear.Charts.ISeries seriesBreakfast = seriesCollection[0];
SpreadsheetGear.Charts.ISeries seriesLunch = seriesCollection[1];
SpreadsheetGear.Charts.ISeries seriesDinner = seriesCollection[2];
SpreadsheetGear.Charts.ISeries seriesTotal = seriesCollection[3];

// Change the last series chart type and plot it on the secondary axis. 
// NOTE: This creates a combination chart using multiple chart groups
//       and utilizes both primary and secondary axes sets, allowing for
//       disproportionate ranges of values to be plotted separately.
seriesTotal.ChartType = SpreadsheetGear.Charts.ChartType.Line;
seriesTotal.AxisGroup = SpreadsheetGear.Charts.AxisGroup.Secondary;

// Change the fill color of each primary series.
seriesBreakfast.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.DarkOrange;
seriesLunch.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.OrangeRed;
seriesDinner.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.Red;

// Change the line color of the total series.
seriesTotal.Format.Line.ForeColor.RGB = SpreadsheetGear.Colors.Yellow;

// Add data labels to the total series and get a reference to the data labels.
seriesTotal.HasDataLabels = true;
SpreadsheetGear.Charts.IDataLabels dataLabels = seriesTotal.DataLabels;

// Position each data label below each data point.
dataLabels.Position = SpreadsheetGear.Charts.DataLabelPosition.Below;

// Change the fill formatting of the data labels.
SpreadsheetGear.Shapes.IFillFormat fillFormat = dataLabels.Format.Fill;
fillFormat.ForeColor.RGB = SpreadsheetGear.SystemColors.Window;
fillFormat.Visible = true;

// Change the line formatting of the data labels.
SpreadsheetGear.Shapes.ILineFormat lineFormat = dataLabels.Format.Line;
lineFormat.ForeColor.RGB = SpreadsheetGear.SystemColors.WindowText;
lineFormat.Visible = true;

// Change the legend position to the top of the chart.
chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Top;

// Get a reference to the primary value axis, hide major
// gridlines, and use a fixed major unit scaling value.
SpreadsheetGear.Charts.IAxis valueAxis =
    chart.Axes[SpreadsheetGear.Charts.AxisType.Value];
valueAxis.HasMajorGridlines = false;
valueAxis.MajorUnit = 0.25;

// Change the chart font name and size.
chart.ChartArea.Font.Name = "Verdana";
chart.ChartArea.Font.Size = 8;

// Add a chart title and change the font size.
chart.HasTitle = true;
chart.ChartTitle.Text = "Weekday Calorie Intake";
chart.ChartTitle.Font.Size = 9;
```

--------------------------------

### Apply a Built-In Style to a Range

Source: https://www.spreadsheetgear.com/support/samples/api/workbook-styles-modify

Assign a cell or range of cells a given Style by setting the IRange.Style property to the desired IStyle in the IWorkbook.Styles collection. This example applies the 'Good' style to cells A1 through D10.

```csharp
worksheet.Cells["A1:D10"].Style = workbook.Styles["Good"];
```

--------------------------------

### Create and Configure a Pie Chart in C#

Source: https://www.spreadsheetgear.com/support/samples/api/charting-basic-pie

This snippet demonstrates loading a workbook, calculating chart coordinates using worksheet window info, and configuring a Pie chart with specific data series and labels.

```csharp
// Open workbook with some data for the chart and get a reference to the active 
// sheet and its cells.
SpreadsheetGear.IWorkbook workbook = 
    SpreadsheetGear.Factory.GetWorkbook("ChartData.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;

// Get a reference to the worksheet window info and shapes collection.
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
SpreadsheetGear.Shapes.IShapes shapes = worksheet.Shapes;

// 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 column values to get 
//       coordinates anywhere in between row and column boundaries.
double left = windowInfo.ColumnToPoints(0.15);
double top = windowInfo.RowToPoints(0.5);
double right = windowInfo.ColumnToPoints(5.85);
double bottom = windowInfo.RowToPoints(13.5);
SpreadsheetGear.Charts.IChart chart =
    shapes.AddChart(left, top, right - left, bottom - top).Chart;

// Get the source data range from an existing defined name.
SpreadsheetGear.IRange source = workbook.Names["TotalByQuarter"].RefersToRange;

// Set the chart's source data range, plotting series in columns.
chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

// Set the chart type to a pie chart.
chart.ChartType = SpreadsheetGear.Charts.ChartType.Pie;

// Get a reference to the single total series.
SpreadsheetGear.Charts.ISeries seriesTotal = chart.SeriesCollection[0];

// Add category labels by using an existing defined name.
seriesTotal.XValues = "=Quarters";

// Add series data labels and change to show percentage only.
seriesTotal.HasDataLabels = true;
seriesTotal.DataLabels.ShowPercentage = true;
seriesTotal.DataLabels.ShowValue = false;

// Add a chart title and change the font size.
chart.HasTitle = true;
chart.ChartTitle.Text = "Percentage of Sales by Quarter";
chart.ChartTitle.Font.Size = 12;
```

--------------------------------

### Evaluate Excel Formulas and Expressions in C#

Source: https://www.spreadsheetgear.com/support/samples/api/worksheet-evaluate-ranges-and-values

Use EvaluateValue to get results of Excel functions like SUM or COLUMN. It can reference cell values or use the active cell context. Ensure SpreadsheetGear is referenced.

```csharp
// Create a workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;
object val;

// Populate worksheet cells with some values.
cells["A1"].Value = 1;
cells["A2"].Value = 2;
cells["A3"].Value = 3;
cells["A4"].Value = 4;
cells["A5"].Value = 5;

// Use EvaluateValue(...) to return the results of Excel-compatible expressions, such as the function SUM.
{
    val = worksheet.EvaluateValue("SUM(1,2,3)");
    Console.WriteLine(val); // OUTPUT: 6

    // Or reference cells on the sheet for which EvaluateValue(...) was called.
    val = worksheet.EvaluateValue("SUM(A1:A5)");
    Console.WriteLine(val); // OUTPUT: 15

    // Some Excel functions use the currently active cell to return certain results.
    cells["C1"].Select();
    val = worksheet.EvaluateValue("COLUMN()");
    Console.WriteLine(val); // OUTPUT: 3
    cells["E1"].Select();
    val = worksheet.EvaluateValue("COLUMN()");
    Console.WriteLine(val); // OUTPUT: 6
}
```

--------------------------------

### Create and Configure Workbook Windows

Source: https://www.spreadsheetgear.com/support/samples/api/workbook-misc-window-info

Use this code to create a new workbook, add data, and then create multiple windows with distinct display settings for the workbook and its active worksheet. Requires the SpreadsheetGear library.

```csharp
// Create a new workbook
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

// Add some data to the worksheet
workbook.ActiveWorksheet.Cells["A1"].Value = "Hello World!";

// Create a new window.
workbook.WindowInfos.Add();

// Modify some options on the first window for the workbook and the active sheet.
SpreadsheetGear.IWorkbookWindowInfo wbWindow1 = workbook.WindowInfos[0];
SpreadsheetGear.IWorksheetWindowInfo wsWindow1 = workbook.ActiveWorksheet.WindowInfos[0];
wbWindow1.DisplayWorkbookTabs = false;
wsWindow1.DisplayHeadings = false;

// Modify some other options on the second window.
SpreadsheetGear.IWorkbookWindowInfo wbWindow2 = workbook.WindowInfos[1];
SpreadsheetGear.IWorksheetWindowInfo wsWindow2 = workbook.ActiveWorksheet.WindowInfos[1];
wbWindow1.DisplayVerticalScrollBar = false;
wbWindow1.DisplayHorizontalScrollBar = false;
wsWindow1.DisplayGridlines = false;
```

--------------------------------

### Create Pie Chart in C#

Source: https://www.spreadsheetgear.com/support/samples/api

Demonstrates creating a pie chart, which is suitable for showing proportions of a whole. The data should consist of categories and their corresponding values.

```csharp
using SpreadsheetGear;

// ...

IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["A1:B4"].Value = new object[,] { { "Product", "Share" }, { "A", 40 }, { "B", 30 }, { "C", 30 } };

IChart chart = worksheet.Shapes.AddChart(ChartType.Pie, 100, 50, 300, 200).Chart;
chart.SetSourceData(worksheet.Range["A1:B4"], true);
```

--------------------------------

### Get HTML Formatting Classes from SpreadsheetGear Cell

Source: https://www.spreadsheetgear.com/support/samples/razor-pages/multiple-ranges-to-tables

This helper function translates SpreadsheetGear cell formatting (alignment, font styles) into corresponding CSS classes for HTML output. It returns an empty string if no formatting is applied.

```csharp
        /// <summary>
        /// A very basic routine to add formatting to HTML output based on the corresponding IRange cell's formatting.
        /// </summary>
        /// <param name="cell">A single cell, for which formatting classes will be based off.</param>
        /// <returns>A string such as ' class="..."' if formatting options are found, otherwise an empty string.</returns>
        private static string GetClassAttribute(SpreadsheetGear.IRange cell)
        {
            List<string> classes = [];
            
            // Horizontal Alignment
            if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Center)
                classes.Add("text-center");
            else if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Right)
                classes.Add("text-end");

            // Basic font formatting
            if (cell.Font.Bold)
                classes.Add("fw-bold");
            if (cell.Font.Italic)
                classes.Add("fst-italic");
            if (cell.Font.Underline != SpreadsheetGear.UnderlineStyle.None)
                classes.Add("text-underline");

            // If desired, this could be expanded to include other formatting, such as other font settings, cell 
            // interior color, etc.

            if (classes.Count > 0)
                return string.Join(' ', classes);
            return "";
        }
    }
}
```

--------------------------------

### Create Excel Reports with Formulas and Formatting

Source: https://www.spreadsheetgear.com/support/samples/razor-pages

Demonstrates how to generate richly formatted Excel reports with formulas and formatting using SpreadsheetGear.

```csharp
using SpreadsheetGear;

// Create a new workbook.
IWorkbook workbook = Factory.GetWorkbook();

// Get the active worksheet.
IWorksheet worksheet = workbook.ActiveSheet;

// Add data and formulas.
worksheet.Cells["A1"].Value = "Sales";
worksheet.Cells["B1"].Formula = "=SUM(C2:C10)";
worksheet.Cells["A2"].Value = "Product A";
worksheet.Cells["C2"].Value = 100;

// Apply formatting.
worksheet.Cells["B1"].Interior.Color = System.Drawing.Color.Yellow;

// Save the workbook.
workbook.SaveAs("ReportWithFormulas.xlsx");
```

--------------------------------

### Generate Multi-Sheet Workbook from EF Core Data

Source: https://www.spreadsheetgear.com/support/samples/razor-pages/ef-core-to-workbook-multiple-sheets

This C# code queries product sales data by region and quarter from a database using Entity Framework Core. It then generates a multi-worksheet Excel workbook, with each sheet representing a quarter's sales and a final summary sheet. Requires setup of SamplesDbContext and SpreadsheetGear.

```C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Pages.Support.Samples.RazorPages.Database
{
    public partial class EFCoreToWorkbookMultipleSheetsModel : PageModel
    {
        // Context is injected from the PageModel's constructor which is not shown here.
        private readonly SamplesDbContext _dbContext;

        [BindProperty]
        public string Region { get; set; }

        [BindProperty]
        public int SalesYear { get; set; }


        public void OnGet()
        {
            Region = "North";
            SalesYear = DateTime.Today.Year - 1;
        }

        public async Task<FileResult> OnPost()
        {
            // Create a new workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

            // Creates a new worksheet for each quarter and populates with data from a database.
            await GenerateQuarterlySalesSheets(workbook);

            // Pulls all the totals from the Quarterly sheets onto a single summary sheet.
            SetupSummarySheet(workbook);

            // Stream the Excel workbook to the client.
            return SaveWorkbookToFileResultStream(workbook);
        }

        private async Task GenerateQuarterlySalesSheets(SpreadsheetGear.IWorkbook workbook)
        {
            // Define quarter dates and a label for the specified year and loop through each one.
            var quarters = new List<(string Label, DateTime Start, DateTime End)>() {
                ( "Q1 Sales", new DateTime(SalesYear, 1, 1), new DateTime(SalesYear, 3, 31)),
                ( "Q2 Sales", new DateTime(SalesYear, 4, 1), new DateTime(SalesYear, 6, 30)),
                ( "Q3 Sales", new DateTime(SalesYear, 7, 1), new DateTime(SalesYear, 9, 30)),
                ( "Q4 Sales", new DateTime(SalesYear, 10, 1), new DateTime(SalesYear, 12, 31))};
            foreach (var (Label, Start, End) in quarters)
            {
                // Use Entity Framework Core to query database with specified information.
                var products = await _dbContext.Products
                    .Select(p => new {
                        p.Id,
                        p.Name,
                        p.UnitCost,
                        UnitsSold = p.Sales
                            .Where(s => s.Region == Region && (s.PurchaseDate >= Start && s.PurchaseDate <= End))
                            .Sum(s => s.Quantity)
                    })
                    .OrderBy(p => p.Name)
                    .ToListAsync();

                // Create a new worksheet for this quarter, rename the sheet and setup a header row.
                SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets.Add();
                worksheet.Name = Label;
                SpreadsheetGear.IRange cells = worksheet.Cells;
                cells["A1"].Value = "Id";
                cells["B1"].Value = "Name";
                cells["C1"].Value = "Units";
                cells["D1"].Value = "Unit Cost";
                cells["E1"].Value = "Total Sale";

                // Add data to the worksheet, starting in Row 2 (A1-notation, not 0-based integer indexes).
                int currentRow = 2;
                foreach (var product in products)
                {
                    cells[$"A{currentRow}"].Value = product.Id;
                    cells[$"B{currentRow}"].Value = product.Name;
                    cells[$"C{currentRow}"].Value = product.UnitsSold;
                    cells[$"D{currentRow}"].Value = product.UnitCost;
                    currentRow++;
                }
                int lastRow = currentRow - 1;

                // Despite using the same formula string for all cells, due to the use of relative row references the actual 
                // addresses for each cell will be shifted according the row it is in, just like in Excel.
                cells[$"E2:E{lastRow}"].Value = "=C2*D2";

                // Apply some formatting.
                cells["A1:E1"].Font.Bold = true;
                cells[$"C1:E{lastRow}"].HorizontalAlignment = SpreadsheetGear.HAlign.Right;
                cells[$"D1:E{lastRow}"].NumberFormat = "$"#,##0.00";
            }
        }

        private void SetupSummarySheet(SpreadsheetGear.IWorkbook workbook)
        {

```

--------------------------------

### Zoom and FitToPages in C#

Source: https://www.spreadsheetgear.com/support/samples/api

Shows how to control the zoom level for printing and how to use the FitToPages option to scale the worksheet content to fit a specified number of pages.

```csharp
using SpreadsheetGear;

// ...

IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Set zoom level for printing
worksheet.PageSetup.Zoom = 80;

// Fit to one page wide by one page tall
worksheet.PageSetup.FitToPagesWide = 1;
worksheet.PageSetup.FitToPagesTall = 1;
```

--------------------------------

### Activate SpreadsheetGear with a Signed License

Source: https://www.spreadsheetgear.com/downloads/signed-license

Call this method once during application initialization before any other SpreadsheetGear operations.

```csharp
SpreadsheetGear.Factory.SetSignedLicense("YOUR SIGNED LICENSE HERE");
```

--------------------------------

### Create Style in C#

Source: https://www.spreadsheetgear.com/support/samples/api

Demonstrates how to create and apply a custom style to cells, including formatting for font, fill, borders, and number format. Styles promote consistency in workbook appearance.

```csharp
using SpreadsheetGear;

// ...

IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];

IStyle style = workbook.Styles.Add("MyCustomStyle");
style.Font.Bold = true;
style.Font.Color = SpreadsheetGear.Drawing.Color.Red;
style.Interior.Color = SpreadsheetGear.Drawing.Color.Yellow;

worksheet.Cells["A1"].Style = style;
```

--------------------------------

### Prompt File Download via JavaScript

Source: https://www.spreadsheetgear.com/support/samples/blazor/server

Invokes a JavaScript function to prompt the user to download a file. Requires the blazor-server-helpers.js script for the 'promptDownload' method.

```csharp
public ValueTask<object> PromptDownload(string filename, byte[] fileBytes)
        {
            // See also blazor-server-helpers.js for the promptDownload(...) method.
            return JSRuntime.InvokeAsync<object>("promptDownload", filename, Convert.ToBase64String(fileBytes));
        }
```

--------------------------------

### Configure Print Zoom and FitToPages in C#

Source: https://www.spreadsheetgear.com/support/samples/api/print-zoom-and-fit-to-pages

Sets manual zoom levels or enables automatic scaling to fit content within a specified number of pages.

```csharp
// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IRange cells = worksheet.Cells;
SpreadsheetGear.IPageSetup pageSetup;

// Zoom sample
{
    // Setup some sample data.
    cells["A1:C10"].Formula = "=ROW()+COLUMN()";
    cells["A1:C10"].Borders.LineStyle = SpreadsheetGear.LineStyle.Continuous;

    // Get reference to worksheet's page setup object
    pageSetup = worksheet.PageSetup;

    // Set Zoom to desired value (between 10% and 400%).
    pageSetup.Zoom = 325;
}

// FitToPages
{
    // Add a new worksheet and populate with some sample data that would normally
    // horizontally overflow into extra pages when printed.
    worksheet = workbook.Worksheets.Add();
    cells = worksheet.Cells;
    pageSetup = worksheet.PageSetup;
    cells["A1:Z200"].Formula = "=ROW()+COLUMN()";

    // Automatically Scale the page contents so that it fits on one page across.
    pageSetup.FitToPagesWide = 1;

    // Don't attempt to auto fit vertically.
    pageSetup.FitToPagesTall = 0;

    // Enable FitToPages.
    pageSetup.FitToPages = true;
}
```

--------------------------------

### Configure Entity Framework Core DbContext for Samples

Source: https://www.spreadsheetgear.com/support/samples/razor-pages/ef-core-to-workbook-multiple-sheets

Configures the DbContext for sample data, including entity relationships, type conversions for SQLite, and data seeding.

```csharp
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Website.Pages.Support.Samples
{
    public class SamplesDbContext(DbContextOptions<SamplesDbContext> options) : DbContext(options)
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(b => {
                b.HasMany(p => p.Sales)
                    .WithOne(oi => oi.Product);
                b.Property(p => p.UnitCost)
                    .HasConversion<double>();    // As of this writing, SQLite doesn't support decimal.
                b.HasData(SampleDataSeeder.Products);
            });

            // Seed database with some sample data.
            var sales = SampleDataSeeder.GenerateSales(5);

            modelBuilder.Entity<Sale>(b => {
                b.HasData(sales.Select(s => new { 
                    s.Id, 
                    s.PurchaseDate, 
                    s.Region, 
                    ProductId = s.Product.Id,
                    s.Quantity
                }));
            });
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<Sale> Sales { get; set; }
    }


    public class Product(int id, string name, decimal unitCost)
    {
        public int Id { get; set; } = id;
        public string Name { get; set; } = name;
        public decimal UnitCost { get; set; } = unitCost;
        public IList<Sale> Sales { get; set; } = [];
    }


    public class Sale
    {
        public int Id { get; set; }
        public DateTime PurchaseDate { get; set; }
        public string Region { get; set; }
        public Product Product { get; set; }
        public int Quantity { get; set; }
    }


    // Helper class to seed the database with some sample data.
    public static class SampleDataSeeder
    {
        public static List<Product> Products = [
            new Product(10001, "Basil", 1.99m),
            new Product(10002, "Black Pepper - Fine", 2.99m),
            new Product(10003, "Cayenne Pepper", 3.99m),
            new Product(10201, "Chili Powder", 2.99m),
            new Product(10202, "Cumin - Ground", 2.99m),
            new Product(10203, "Garlic Powder", 1.99m),
            new Product(10401, "Marjoram", 3.99m),
            new Product(10402, "Onion Powder", 1.99m),
            new Product(10403, "Oregano", 2.99m),
            new Product(10801, "Red Pepper - Crushed", 2.99m),
            new Product(10802, "Rosemary", 3.99m),
            new Product(10803, "Thyme", 3.99m)
        ];

        public static List<string> Regions = ["North", "South", "East", "West"];

        public static List<Sale> GenerateSales(int numYears)
        {
            var sales = new List<Sale>();
            var rand = new Random();

            DateTime startDate = new DateTime(DateTime.Today.Year - numYears, 1, 1);
            DateTime endDate = new DateTime(DateTime.Today.Year, 1, 1);

            // Used to control the rate at which each region's sales increase over time.
            var regionSalesGrowthRates = new Dictionary<string, (double Min, double Max)>() {
                { "North", (60.0, 80.0) },
                { "South", (40.0, 60.0) },
                { "East", (20.0, 40.0) },
                { "West", (5.0, 20.0) }
            };

            // Populate sales in a way that increases sales over time and at varying velocities depending on
            // the region.  This is done to provide some visual separation of each series when charting based
            // on regions...or just to make the charts prettier :)
            int idCounter = 1;
            foreach (var region in Regions)
            {
                int monthCounter = 1;
                DateTime currentMonth = startDate;

                // Increments 1 month at a time.
                while (currentMonth < endDate)
                {
                    // Let each monthly sales vary by some random but bounded amount, thereby producing a non-linear but 
                    // still-growing sales trend.  Usage of "regionSalesGrowthRates" allows each region to grow at a 
                    // different rate from other regions.
                    double monthlySalesTotalMin = regionSalesGrowthRates[region].Min * monthCounter;
                    double monthlySalesTotalMax = regionSalesGrowthRates[region].Max * monthCounter;

```
Tokens:
Raw