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#:
- Building a WPF Application
- Defining the User Interface for a WPF Application
- Importing an Excel File into a Spreadsheet Component
- Exporting an Excel File from Spread. NET
- Retrieving Historical Stock Data
- Adding Stock Data to the Spreadsheet
- 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:
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:
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:
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:
Next, drag the GcSpreadSheet component from the Toolbox (CTRL+ALT+X) and drop it into the first cell of the grid:
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:
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:
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 SubNext, 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.
Run the application and select the Excel file just created. The file is imported and displayed in the spreadsheet control of the application.
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 SubRun 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):
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"
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 ClassAdding 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.DataAdd 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 SubOne 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 SubNext, 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.
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 SubThe 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.
To download the C# and/or VB sample projects for this blog, please click here:
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 Forms, import/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
Related Blogs
Try Our .NET Spreadsheet Components
Spread.NET Resources
Sean Lawyer