How to Import and Export Excel Spreadsheets with C# and WPF
Tags: webdev, devops, tutorial, dotnet
Published: 2022-08-19 21:01:28

---
canonical_url: https://www.grapecity.com/blogs/how-to-import-and-export-excel-spreadsheets-with-c-sharp-and-wpf
description: Learn how to import and export Excel spreadsheets in C# and WPF applications. See more from Spread.NET today!
---

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**](https://www.grapecity.com/spreadnet) 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](#build)
2.  [Defining the User Interface for a WPF Application](#define)
3.  [Importing an Excel File into a Spreadsheet Component](#import)
4.  [Exporting an Excel File from Spread. NET](#export)
5.  [Retrieving Historical Stock Data](#retrieve)
6.  [Adding Stock Data to the Spreadsheet](#add)
7.  [Creating Sparkline Charts from the Data Source](#sparkline)

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!

### <a id="build"></a>Building a WPF Application

First, you’ll need to [download and install the trial version of Spread.NET](https://www.grapecity.com/spreadnet/download). 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](https://files.grapecity.com/gc-website-media/cp1di0tw/01-configure-project.png?width=500&height=332.84023668639054)

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](https://files.grapecity.com/gc-website-media/jl2nxyak/02-toolbox-gcspreadsheet.png?width=373&height=241)

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](https://files.grapecity.com/gc-website-media/4xvbfpze/03-choose-toolbox-items.png?width=500&height=343.57142857142856)

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

### <a id="define"></a>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](https://files.grapecity.com/gc-website-media/52ofnrb1/04-grid-buttons.png?width=500&height=282.34086242299793)

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

![Toolbox GC Spreadsheet](https://files.grapecity.com/gc-website-media/4kyfqek5/05-toolbox-gcspreadsheet.png?width=500&height=377.78631821675634)

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](https://files.grapecity.com/gc-website-media/kwshn5fx/06-restart-designer-dialog.png?width=406&height=211)

Don't worry about this; it's normal behavior in the latest **VS2022 XAML** editor. Close that dialog and drag and drop the **GcSpreadsheet**one 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](https://files.grapecity.com/gc-website-media/o3nfybux/07-spreadsheet-grid-buttons.png?width=500&height=282.34086242299793)

### <a id="import"></a>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](https://files.grapecity.com/gc-website-media/0aqnjfyx/08-excel-stock-list.png?width=500&height=398.57142857142856)

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](https://files.grapecity.com/gc-website-media/gi4nx420/09-stocks-file-gcspreadsheet.png?width=500&height=281.80661577608146)

### <a id="export"></a>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.

### <a id="retrieve"></a>Retrieving Historical Stock Data

I’ve created a free account on the [**IEX Cloud**](https://www.iexcloud.io/) 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](https://files.grapecity.com/gc-website-media/ddbnv5tz/10-iex-cloud-api-token.png?width=500&height=211.5171650055371)

We already know the complete list of stock symbols when calling our API. Therefore, using the [batch request endpoint](https://iexcloud.io/docs/api/#batch-requests) 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](https://files.grapecity.com/gc-website-media/pvjo14ee/11-nuget-newtonsoft-restsharp-install.png?width=500&height=132.8125)

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
```

### <a id="add"></a>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](https://files.grapecity.com/gc-website-media/tv2bhz5l/12-run-cloud-stocks.png?width=500&height=281.80661577608146)

### <a id="sparkline"></a>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](https://files.grapecity.com/gc-website-media/jttfslae/13-run-cloud-sparklines.png?width=500&height=377.25040916530276)

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

[StockTrends.WPF.CS.zip](https://files.grapecity.com/gc-website-media/yojphpbk/stocktrends-wpf-cs.zip)

[StockTrendsVB.WPF.zip](https://files.grapecity.com/gc-website-media/xn0kivce/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](https://help.grapecity.com/spread/SpreadWPF/webframe.html#Welcomewpf.html) 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.