How to Import and Export Excel Spreadsheets with C# and WPF

When working in a business setting, specifically in finance, it is common to monitor stocks. When making decisions, it is essential to know where the market was, where it is now, and where it may be heading. Therefore, you'll need the ability to import data concerning these stocks, effectively evaluate the data, and then make clear, educated decisions. Not to mention exporting the data for your clients or end-users so they can visualize and understand all the data easily.

This article will demonstrate using Spread.NET to build an application with Windows Presentation Foundation (WPF) in VB or C#. The application is responsible for importing and retrieving three months’ worth of closing price history for various stock symbols via an Excel spreadsheet import process, then exporting an Excel spreadsheet containing the new data.

Here are the seven steps to importing and exporting Excel spreadsheets with WPF in VB or C#:

  1. Building a WPF Application
  2. Defining the User Interface for a WPF Application
  3. Importing an Excel File into a Spreadsheet Component
  4. Exporting an Excel File from Spread. NET
  5. Retrieving Historical Stock Data
  6. Adding Stock Data to the Spreadsheet
  7. Creating Sparkline Charts from the Data Source

This application aims to allow the user to provide a series of stock symbols by importing an external Excel document. The application then processes these symbols.

  • It will retrieve each of the stocks' three-month closing price history via a REST API
  • The historical data retrieved from the API will be displayed alongside the symbol in the spreadsheet component
  • We’ll add a visualization of the stock price history using an inline Sparkline chart
  • After processing, the user can export the resulting data into a new Excel document

Let’s get started building the app!

Take control of your spreadsheets - Download the latest version of Spread.NET

 

Building a WPF Application

First, you’ll need to download and install the trial version of Spread.NET. Be sure to include the WPF controls when installing. After the installation process has been completed, open Visual Studio 2022 and create a new WPF (.Net Framework) desktop application project. I named my project StockTrends.WPF:

Configure Project

Once the solution has completed loading in Visual Studio, open MainWindow.xaml in the editor, then open the Visual Studio Toolbox (CTRL+ALT+X). You should see a Toolbox section named GrapeCity Spread:

Toolbox GC Spreadsheet

If you don't see this section, you will need to add the spreadsheet component to the toolbox manually – luckily, you’ll only ever have to do this once. Add the component by right-clicking in the Toolbox panel, and from the menu, select Choose Items, scroll through the list, and find and place a checkmark next to the GcSpreadSheet component located in the GrapeCity.WPF.SpreadSheet.UI namespace and press the OK button:

Choose Toolbox Items

Now you will have the GcSpreadSheet control available to drag and drop from the toolbox onto your XAML documents.

Defining the User Interface for the WPF Application

We will create a simple window that consists of two rows to satisfy the requirements of our WPF application. The first row consists of a large area that houses the Spread.NET spreadsheet component. Below are two buttons: one to import the symbols spreadsheet and one to export the results after processing.

With MainWindow.xaml open in the Visual Studio editor, replace the existing Grid element with the following markup:

MainPage.xaml Grid definition

<Grid> 
    <Grid.RowDefinitions> 
        <RowDefinition /> 
        <RowDefinition Height="100" /> 
    </Grid.RowDefinitions> 
    <Grid.ColumnDefinitions> 
        <ColumnDefinition /> 
        <ColumnDefinition /> 
    </Grid.ColumnDefinitions> 
    <Button x:Name="btnLoadSpreadsheet" Grid.Row="1" Grid.Column="0" 
        Content="Load Spreadsheet" /> 
    <Button x:Name="btnExportSpreadsheet" Grid.Row="1" Grid.Column="1" 
        Content="Export Spreadsheet" /> 
</Grid>

The designer window will now look similar to the following:

Grid Buttons

Next, drag the GcSpreadSheet component from the Toolbox (CTRL+ALT+X) and drop it into the first cell of the grid:

Toolbox GC Spreadsheet

The first time you try to create the GcSpreadsheet control, the reference for the GcSpreadsheet will be added to the project, and the designer will need to restart and show this dialog:

Restart Designer Dialog

Don't worry about this; it's normal behavior in the latest VS2022 XAML editor. Close that dialog and drag and drop the GcSpreadsheetone more time to add it to the page.

Edit the XAML element representing the spreadsheet component so that it matches the following:

XAML for GcSpreadsheet element

<ss:GcSpreadSheet x:Name="spreadControl" Grid.Row="0" Grid.Column="0" Grid.ColumnSpan="2"/>

The designer will now look similar to the following:

Spreadsheet Grid Buttons

Importing an Excel File into the Spreadsheet Component

The first piece of functionality to implement is importing an external Excel file into the Spread.NET spreadsheet component. This Excel spreadsheet contains a list of stock symbols to process in the application.

Implement this functionality using the OpenFileDialog class from the Microsoft.Win32 namespace. This class adds filters to the dialog, so the user can quickly select from existing Excel (*.XLSX) documents. Once a file is selected, the spreadsheet is loaded into the spreadsheet component using a single line of code!

In the MainWindow.xaml designer, double-click on the Load Spreadsheet button to implement the click handler. Implement the code as follows:

BtnLoadSpreadsheet_Click C#

private void btnLoadSpreadsheet_Click(object sender, RoutedEventArgs e)
{
    Microsoft.Win32.OpenFileDialog ofd = new Microsoft.Win32.OpenFileDialog();
    ofd.DefaultExt = ".xlsx";
    ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
    var sel = ofd.ShowDialog();
    if (sel == true)
    {
        // one line of code to import the Excel file into Spread.NET 
        spreadControl.OpenExcel(ofd.FileName);
    }
}

BtnLoadSpreadsheet_Click VB

Private Sub btnLoadSpreadsheet_Click(sender As Object, e As RoutedEventArgs) Handles btnLoadSpreadsheet.Click
    Dim ofd As Microsoft.Win32.OpenFileDialog = New Microsoft.Win32.OpenFileDialog()
    ofd.DefaultExt = ".xlsx"
    ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx"
    Dim sel = ofd.ShowDialog()
    If (sel = True) Then
        ' one line of code to import the Excel file into Spread.NET 
        spreadControl.OpenExcel(ofd.FileName)
    End If
End Sub

Next, create a simple spreadsheet in Excel that contains a list of stock symbols. This document consists of a single worksheet with one column. In this column, enter a stock symbol for processing. For this example, we’re not doing any data cleansing or error processing so that we can focus on the application code itself. Some rules about how the spreadsheet should be formatted: just one ticker symbol per row and enter only valid ticker symbols.

Excel Stock List

Run the application and select the Excel file just created. The file is imported and displayed in the spreadsheet control of the application.

Stocks File

Exporting an Excel File from Spread. NET

Now that the import process is functioning, we can move on to implementing the export functionality. We will be using the SaveFileDialog class located in the Microsoft.Win32 namespace because it can set extension filters in the dialog. The ability to export an Excel document from the Spread.NET component is also a simple process, accomplished with – (you got it) – a single line of code!

In the MainWindow.xaml designer, double-click on the Export Spreadsheet button to implement the click event handler code. Implement the handler code as follows:

btnExportSpreadsheet_Click C#

private void btnExportSpreadsheet_Click(object sender, RoutedEventArgs e)
{
    Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();
    sfd.FileName = "SpreadNET.xlsx";
    sfd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
    sfd.DefaultExt = ".xlsx";
    var sel = sfd.ShowDialog();
    if (sel == true)
    {
        spreadControl.SaveExcel(sfd.FileName, GrapeCity.Windows.SpreadSheet.Data.ExcelFileFormat.XLSX);
    }
}

btn_ExportSpreadsheet_Click VB

Private Sub btnExportSpreadsheet_Click(sender As Object, e As RoutedEventArgs) Handles btnExportSpreadsheet.Click
    Dim sfd As Microsoft.Win32.SaveFileDialog = New Microsoft.Win32.SaveFileDialog()
    sfd.FileName = "SpreadNET.xlsx"
    sfd.Filter = "Excel Documents (*.xlsx)|*.xlsx"
    sfd.DefaultExt = ".xlsx"
    Dim sel = sfd.ShowDialog()
    If (sel = True) Then
        spreadControl.SaveExcel(sfd.FileName, GrapeCity.Windows.SpreadSheet.Data.ExcelFileFormat.XLSX)
    End If
End Sub

Run the application once more, load the Stock Price spreadsheet once again, then press the Export Spreadsheet button to save a copy of the displayed spreadsheet onto your computer.

Retrieving Historical Stock Data

I’ve created a free account on the IEX Cloud to retrieve the three-month closing price history for the stocks in the spreadsheet for this application. Once registered for an account, log into the dashboard console to obtain the API Token to use with the IEX Cloud APIs (click the "Copy to clipboard" glyph to copy the API Token):

IEX Cloud API Token

We already know the complete list of stock symbols when calling our API. Therefore, using the batch request endpoint makes sense to obtain all data via a single call.

We will use two NuGet packages to implement the API integration. First, right-click on the project and select the Manage NuGet Packages option. Next, select the Browse tab, then search for and install the Newtonsoft.JSON and RestSharp packages – to quickly and easily find both packages, copy and paste this search string: "packageid:Newtonsoft.Json | packageid:RestSharp"

NuGet

Once you have found both packages, click on Install for each package to add them to the project.

Next, right-click on your project, and add a new class named StockHistoryRetriever. This file encapsulates all code relevant to the API integration. It also contains the definition of two additional classes that reflect the structure of the data from the IEX Cloud API.

In addition to the data encapsulation classes, this file contains the method responsible for making the call to the financial API. The code listing of StockHistoryRetriever is as follows (replace the API_TOKEN value with your own from the IEX Cloud dashboard):

StockHistoryRetriever C#

using Newtonsoft.Json.Linq;
using RestSharp;
using System;
using System.Collections.Generic;
 
namespace StockTrends.WPF
{
    public class StockHistoryRetriever
    {
        private const string API_TOKEN = "<YOUR PUBLISHABLE API TOKEN>";
        private const string BASE_URL = "https://cloud.iexapis.com";
        private const string API_VERSION = "beta";
 
        public List<ClosingStockHistory> Get3MonthHistory(List<string> symbols)
        {
            var client = new RestClient(BASE_URL);
            var endpoint = $"{API_VERSION}/stock/market/batch";
            var request = new RestRequest(endpoint, Method.Get);
            request.AddQueryParameter("symbols", string.Join(",", symbols));
            request.AddQueryParameter("types", "chart");
            request.AddQueryParameter("range", "3m");
            request.AddQueryParameter("chartCloseOnly", "true");
            request.AddQueryParameter("token", API_TOKEN);
 
            RestResponse resp = client.Execute(request);
            var stockHistory = JObject.Parse(resp.Content);
 
            var history = new List<ClosingStockHistory>();
            foreach (var symbol in symbols)
            {
                if (symbol == "")
                    continue;
                var closingStockHistory = new ClosingStockHistory();
                closingStockHistory.Symbol = symbol;
                closingStockHistory.Prices = new List<ClosingPrice>();
                var stock = stockHistory[symbol];
                var chartData = (JArray)stock["chart"];
                foreach (var itm in chartData)
                {
                    closingStockHistory.Prices.Add(new ClosingPrice()
                    {
                        Date = Convert.ToDateTime(itm["date"]),
                        Price = Convert.ToDouble(itm["close"])
                    });
                }
                history.Add(closingStockHistory);
            }
 
            return history;
        }
    }
 
    public class ClosingStockHistory
    {
        public string Symbol { get; set; }
        public List<ClosingPrice> Prices { get; set; }
    }
 
    public class ClosingPrice
    {
        public DateTime Date { get; set; }
        public double Price { get; set; }
    }
}

StockHistoryRetriever VB

Imports Newtonsoft.Json.Linq
Imports RestSharp
Imports System
Imports System.Collections.Generic
 
Public Class StockHistoryRetriever
    Private Const API_TOKEN As String = "<YOUR PUBLISHABLE API TOKEN>"
    Private Const BASE_URL As String = "https://cloud.iexapis.com"
    Private Const API_VERSION As String = "beta"
 
    Public Function Get3MonthHistory(ByVal symbols As List(Of String)) As List(Of ClosingStockHistory)
        Dim client = New RestClient(BASE_URL)
        Dim endpoint = $"{API_VERSION}/stock/market/batch"
        Dim request = New RestRequest(endpoint, Method.Get)
        request.AddQueryParameter("symbols", String.Join(",", symbols))
        request.AddQueryParameter("types", "chart")
        request.AddQueryParameter("range", "3m")
        request.AddQueryParameter("chartCloseOnly", "true")
        request.AddQueryParameter("token", API_TOKEN)
 
        Dim resp As RestResponse = client.Execute(request)
        Dim stockHistory = JObject.Parse(resp.Content)
        Dim history = New List(Of ClosingStockHistory)
        For Each symbol In symbols
            If symbol = "" Then Continue For
            Dim closingStockHistory = New ClosingStockHistory()
            closingStockHistory.Symbol = symbol
            closingStockHistory.Prices = New List(Of ClosingPrice)
            Dim stock = stockHistory(symbol)
            Dim chartData = CType(stock("chart"), JArray)
            For Each itm In chartData
                closingStockHistory.Prices.Add(New ClosingPrice() With {.ClosingDate = Convert.ToDateTime(itm("date")), .Price = Convert.ToDouble(itm("close"))})
                history.Add(closingStockHistory)
            Next
        Next
        Return history
    End Function
 
End Class
 
Public Class ClosingStockHistory
    Public Property Symbol As String
    Public Property Prices As List(Of ClosingPrice)
End Class
 
Public Class ClosingPrice
    Public Property ClosingDate As Date
    Public Property Price As Double
End Class

Adding Stock Data to the Spreadsheet

Upon successfully loading the symbols spreadsheet into the Spread.NET component, we have all the necessary information to issue the call to the API and write the retrieved closing price history back into the same worksheet.

To begin this implementation, open MainWindow.xaml.cs and add the following using or Imports statement:

using C#

using GrapeCity.Windows.SpreadSheet.Data;

Imports VB

Imports GrapeCity.Windows.SpreadSheet.Data

Add the following method to the MainWindow class. The ProcessSpreadsheet method is responsible for reading the list of symbols from the uploaded document, issuing the API calls, and writing the pricing history data back to the appropriate row in the current worksheet.

ProcessSpreadsheet() C#

private void ProcessSpreadsheet()
{
    List<string> stocks = new List<string>();
    var worksheet = spreadControl.Sheets[0];
    for (var i = 0; i < worksheet.RowCount; i++)
    {
        var stockSymbol = worksheet.Cells[i, 0].Text;
        stocks.Add(stockSymbol);
    }
    StockHistoryRetriever retriever = new StockHistoryRetriever();
    var data = retriever.Get3MonthHistory(stocks);
    // calculate max columns required, 
    // increase the number of columns available in the worksheet 
    int maxCount = data.Max(x => x.Prices.Count());
    // 1 column for the symbol, 1 column for the sparkline 
    worksheet.ColumnCount = maxCount + 1;
    //define header column style 
    var headerStyleName = "HeaderStyle";
    var headerStyle = new StyleInfo()
    {
        Background = new SolidColorBrush(Colors.LightSeaGreen),
        Name = headerStyleName,
        BorderBottom = new BorderLine(Colors.SeaGreen),
        BorderRight = new BorderLine(Colors.SeaGreen)
    };
    worksheet.NamedStyles.Add(headerStyle);
    //set column header for the symbol and sparkline column - assign header style 
    worksheet.ColumnHeader.Cells[0, 0].Text = "Symbol";
    worksheet.ColumnHeader.Cells[0, 0].StyleName = headerStyleName;
    bool setHeader = true;
    foreach (var stock in data)
    {
        // find stock index 
        int rowIdx = 0;
        int colIdx = 0;
        spreadControl.Search(0, stock.Symbol, out rowIdx, out colIdx);
        int columnCounter = 1;
        foreach (var price in stock.Prices)
        {
            // set header to date, expand column width, set column header style 
            if (setHeader)
            {
                worksheet.ColumnHeader.Cells[0, columnCounter].Text = price.Date.ToString("MM/dd/yyyy");
                worksheet.ColumnHeader.Cells[0, columnCounter].StyleName = headerStyleName;
                worksheet.Columns[columnCounter].Width = 85;
            }
            // set cell to format to currency 
            worksheet.Cells[rowIdx, columnCounter].Formatter = new GeneralFormatter(FormatMode.StandardNumericMode, "c");
            worksheet.Cells[rowIdx, columnCounter].Value = price.Price;
            columnCounter++;
        }
        setHeader = false;
        spreadControl.Invalidate();
    }
}

ProcessSpreadSheet() VB

Private Sub ProcessSpreadsheet()
    Dim stocks As New List(Of String)
    Dim worksheet = spreadControl.Sheets(0)
    For i As Integer = 0 To worksheet.RowCount - 1
        Dim stockSymbol = worksheet.Cells(i, 0).Text
        stocks.Add(stockSymbol)
    Next
    Dim retriever As StockHistoryRetriever = New StockHistoryRetriever()
    Dim data = retriever.Get3MonthHistory(stocks)
    ' calculate max columns required
    ' increase the number of columns available in the worksheet
    Dim maxCount As Integer = data.Max(Function(x) x.Prices.Count)
    ' 1 column for the symbol, 1 column for the sparkline
    worksheet.ColumnCount = maxCount + 1
    ' similar to a CSS class, define header column style
    Dim headerStyleName = "HeaderStyle"
    Dim headerStyle As New StyleInfo With {.Background = New SolidColorBrush(Colors.LightSeaGreen), .Name = headerStyleName, .BorderBottom = New BorderLine(Colors.SeaGreen), .BorderRight = New BorderLine(Colors.SeaGreen)}
    worksheet.NamedStyles.Add(headerStyle)
    ' set column header for the symbol and sparkline column - assign header style 
    worksheet.ColumnHeader.Cells(0, 0).Text = "Symbol"
    worksheet.ColumnHeader.Cells(0, 0).StyleName = headerStyleName
    Dim setHeader = True
    For Each stock In data
        ' find stock index
        Dim rowIdx = 0
        Dim colIdx = 0
        spreadControl.Search(0, stock.Symbol, rowIdx, colIdx)
        Dim columnCounter = 1
        For Each price In stock.Prices
            If setHeader Then
                ' set header text to date of the price,
                ' expand column width, set column header style
                worksheet.ColumnHeader.Cells(0, columnCounter).Text = price.ClosingDate.ToString("MM/dd/yyyy")
                worksheet.ColumnHeader.Cells(0, columnCounter).StyleName = headerStyleName
                worksheet.Columns(columnCounter).Width = 85
            End If
            ' set cell format to currency
            worksheet.Cells(rowIdx, columnCounter).Formatter = New GeneralFormatter(FormatMode.StandardNumericMode, "c")
            worksheet.Cells(rowIdx, columnCounter).Value = price.Price
            columnCounter += 1
        Next
        setHeader = False
        spreadControl.Invalidate()
    Next
End Sub

One of the main things to keep in mind is that we will be changing the dimensions of the spreadsheet. When first uploaded, the symbols sheet consisted of a single column with stock symbols defined as one per row. Attempting to write to a column that didn’t previously exist will cause errors. Adjust the number of columns in a worksheet by setting the ColumnCount property to the total number of columns desired.

Immediately following the OpenExcel statement in the method, add a call to the new ProcessSpreadsheet() function:

btnLoadSpreadsheet_Click C#

private void btnLoadSpreadsheet_Click(object sender, RoutedEventArgs e)
{
    Microsoft.Win32.OpenFileDialog ofd = new Microsoft.Win32.OpenFileDialog();
    ofd.DefaultExt = ".xlsx";
    ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
    var sel = ofd.ShowDialog();
    if (sel == true)
    {
        // one line of code to import the Excel file into Spread.NET 
        spreadControl.OpenExcel(ofd.FileName);
        ProcessSpreadsheet();
    }
}

btnLoadSpreadsheet_Click VB

Private Sub btnLoadSpreadsheet_Click(sender As Object, e As RoutedEventArgs) Handles btnLoadSpreadsheet.Click
    Dim ofd As Microsoft.Win32.OpenFileDialog = New Microsoft.Win32.OpenFileDialog()
    ofd.DefaultExt = ".xlsx"
    ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx"
    Dim sel = ofd.ShowDialog()
    If (sel = True) Then
        ' one line of code to import the Excel file into Spread.NET 
        spreadControl.OpenExcel(ofd.FileName)
        ProcessSpreadsheet()
    End If
End Sub

Next, rerun the application, and you will now see the three-month closing price history shown on the same row as the corresponding stock symbol. Finally, export the spreadsheet to take this data offline.

Run Cloud Stocks

Creating Sparkline Charts from the Data Source

Even though all of this data collected is impressive, it would be better if paired with a visual representation of the data. Sparkline graphs provide a compelling way to interpret this data at a glance. Spread.NET allows for the creation of a Sparkline graph through its powerful API. These Sparklines are fully customizable and are built using data already in the spreadsheet.

Alter ProcessSpreadsheet() with the following code:

ProcessSpreadsheet() C#

private void ProcessSpreadsheet()
{
    List<string> stocks = new List<string>();
    var worksheet = spreadControl.Sheets[0];
    for (var i = 0; i < worksheet.RowCount; i++)
    {
        var stockSymbol = worksheet.Cells[i, 0].Text;
        stocks.Add(stockSymbol);
    }
    StockHistoryRetriever retriever = new StockHistoryRetriever();
    var data = retriever.Get3MonthHistory(stocks);
    // calculate max columns required, 
    // increase the number of columns available in the worksheet 
    int maxCount = data.Max(x => x.Prices.Count());
    // 1 column for the symbol, 1 column for the sparkline 
    worksheet.ColumnCount = maxCount + 2;
    //set sparkline column width 
    worksheet.Columns[1].Width = 500;
 
    //define header column style 
    var headerStyleName = "HeaderStyle";
    var headerStyle = new StyleInfo()
    {
        Background = new SolidColorBrush(Colors.LightSeaGreen),
        Name = headerStyleName,
        BorderBottom = new BorderLine(Colors.SeaGreen),
        BorderRight = new BorderLine(Colors.SeaGreen)
    };
    worksheet.NamedStyles.Add(headerStyle);
    //set column header for the symbol and sparkline column - assign header style 
    worksheet.ColumnHeader.Cells[0, 0].Text = "Symbol";
    worksheet.ColumnHeader.Cells[0, 0].StyleName = headerStyleName;
    worksheet.ColumnHeader.Cells[0, 1].Text = "Trend";
    worksheet.ColumnHeader.Cells[0, 1].StyleName = headerStyleName;
    bool setHeader = true;
    foreach (var stock in data)
    {
        // find stock index 
        int rowIdx = 0;
        int colIdx = 0;
        spreadControl.Search(0, stock.Symbol, out rowIdx, out colIdx);
        int columnCounter = 2;
        foreach (var price in stock.Prices)
        {
            // set header to date, expand column width, set column header style 
            if (setHeader)
            {
                worksheet.ColumnHeader.Cells[0, columnCounter].Text = price.Date.ToString("MM/dd/yyyy");
                worksheet.ColumnHeader.Cells[0, columnCounter].StyleName = headerStyleName;
                worksheet.Columns[columnCounter].Width = 85;
            }
            // set cell to format to currency 
            worksheet.Cells[rowIdx, columnCounter].Formatter = new GeneralFormatter(FormatMode.StandardNumericMode, "c");
            worksheet.Cells[rowIdx, columnCounter].Value = price.Price;
            columnCounter++;
        }
        setHeader = false;
        // create sparkline 
        var range = new CellRange(rowIdx, 2, 1, stock.Prices.Count);
        var settings = new SparklineSetting();
        settings.AxisColor = SystemColors.ActiveBorderColor;
        settings.LineWeight = 1;
        settings.ShowMarkers = true;
        settings.MarkersColor = Color.FromRgb(255, 0, 128);
        settings.ShowFirst = true;
        settings.ShowHigh = true;
        settings.ShowLast = true;
        settings.ShowNegative = true;
        settings.FirstMarkerColor = Color.FromRgb(163, 73, 164);
        settings.HighMarkerColor = Color.FromRgb(49, 78, 111);
        settings.LastMarkerColor = Color.FromRgb(0, 255, 255);
        settings.NegativeColor = Color.FromRgb(255, 255, 0);
        // set row height 
        worksheet.Rows[rowIdx].Height = 250;
        worksheet.SetSparkline(rowIdx, 1, range, DataOrientation.Horizontal, SparklineType.Line, settings);
        spreadControl.Invalidate();
    }
}

ProcessSpreadSheet() VB

Private Sub ProcessSpreadsheet()
    Dim stocks As New List(Of String)
    Dim worksheet = spreadControl.Sheets(0)
    For i As Integer = 0 To worksheet.RowCount - 1
        Dim stockSymbol = worksheet.Cells(i, 0).Text
        stocks.Add(stockSymbol)
    Next
    Dim retriever As StockHistoryRetriever = New StockHistoryRetriever()
    Dim data = retriever.Get3MonthHistory(stocks)
    ' calculate max columns required
    ' increase the number of columns available in the worksheet
    Dim maxCount As Integer = data.Max(Function(x) x.Prices.Count)
    ' 1 column for the symbol, 1 column for the sparkline
    worksheet.ColumnCount = maxCount + 2
    ' set sparkline column width
    worksheet.Columns(1).Width = 500
    ' similar to a CSS class, define header column style
    Dim headerStyleName = "HeaderStyle"
    Dim headerStyle As New StyleInfo With {.Background = New SolidColorBrush(Colors.LightSeaGreen), .Name = headerStyleName, .BorderBottom = New BorderLine(Colors.SeaGreen), .BorderRight = New BorderLine(Colors.SeaGreen)}
    worksheet.NamedStyles.Add(headerStyle)
    ' set column header for the symbol and sparkline column - assign header style 
    worksheet.ColumnHeader.Cells(0, 0).Text = "Symbol"
    worksheet.ColumnHeader.Cells(0, 0).StyleName = headerStyleName
    worksheet.ColumnHeader.Cells(0, 1).Text = "Trend"
    worksheet.ColumnHeader.Cells(0, 1).StyleName = headerStyleName
    Dim setHeader = True
    For Each stock In data
        ' find stock index
        Dim rowIdx = 0
        Dim colIdx = 0
        spreadControl.Search(0, stock.Symbol, rowIdx, colIdx)
        Dim columnCounter = 2
        For Each price In stock.Prices
            If setHeader Then
                ' set header text to date of the price,
                ' expand column width, set column header style
                worksheet.ColumnHeader.Cells(0, columnCounter).Text = price.ClosingDate.ToString("MM/dd/yyyy")
                worksheet.ColumnHeader.Cells(0, columnCounter).StyleName = headerStyleName
                worksheet.Columns(columnCounter).Width = 85
            End If
            ' set cell format to currency
            worksheet.Cells(rowIdx, columnCounter).Formatter = New GeneralFormatter(FormatMode.StandardNumericMode, "c")
            worksheet.Cells(rowIdx, columnCounter).Value = price.Price
            columnCounter += 1
        Next
        setHeader = False
        ' create the sparkline
        Dim range = New CellRange(rowIdx, 2, 1, stock.Prices.Count)
        Dim settings = New SparklineSetting()
        settings.AxisColor = SystemColors.ActiveBorderColor
        settings.LineWeight = 1
        settings.ShowMarkers = True
        settings.ShowFirst = True
        settings.ShowHigh = True
        settings.ShowLast = True
        settings.ShowNegative = True
        settings.FirstMarkerColor = Color.FromArgb(255, 163, 73, 164)
        settings.HighMarkerColor = Color.FromArgb(255, 49, 78, 111)
        settings.LastMarkerColor = Color.FromArgb(255, 0, 255, 255)
        settings.NegativeColor = Color.FromArgb(255, 255, 255, 0)
        ' set row height
        worksheet.Rows(rowIdx).Height = 250
        worksheet.SetSparkline(rowIdx, 1, range, DataOrientation.Horizontal, SparklineType.Line, settings)
        spreadControl.Invalidate()
    Next
End Sub

The ProcessSpreadsheet() code now sets a row height and column width on the Sparkline column. Setting these values gives the Sparkline graph more room so that it is easier to read and interpret. You will also notice that the code now contains Sparkline customization settings such as colors and designate the cell range used to generate the graph in each row.

Run Cloud Sparklines

To download the C# and/or VB sample projects for this blog, please click here:

StockTrends.WPF.CS.zip

StockTrendsVB.WPF.zip

GrapeCity Spreadsheet Components

This article only scratches the surface of the full capabilities of the GrapeCity Spread.NET spreadsheet component. Review the documentation to see some of the many available features. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program.

In another article series, we demonstrate how to import and export excel spreadsheets in C# and VB.NET Windows Formsimport/export Excel spreadsheets using JavaScript, and how to import and export Excel spreadsheets in Angular.

Take control of your spreadsheets - Download the latest version of Spread.NET

Try SpreadJS's spreadsheet components: Download the latest version of SpreadJS

comments powered by Disqus