How to Create Interactive Dashboards with Excel Charts in C#

What You Will Need

• Visual Studio
• DsExcel NuGet

Controls Referenced

• Document Solutions for Excel

Tutorial Concept

C# Interactive Dashboards - Using a C# .NET Excel API and chart features, users can build interactive dashboards for their desktop applications.

---

Are you looking to make smarter decisions with your Excel data? Use charts to transform complex information into clear insights!

Identifying trends using raw data alone is very difficult. Without visual aids, it’s tough to spot patterns and key insights, which leads to slower and less accurate decision-making. Charts play a crucial role in creating dashboards or business reports by transforming complex data into clear visual insights. They help you easily identify trends, compare metrics, and see patterns in the data.

With our Document Solutions for Excel (DsExcel) API, you can effortlessly create various Excel charts and customize different parts to suit your needs. In this blog, we showcase how charts can be used with sales data to create a performance dashboard using the DsExcel API.  The Excel dashboard includes different charts to display various metrics such as sales by each representative, quantity sold of each product, sales distribution by product, and sales trends over time.

Let’s break down the creation of the dashboard into the following simple steps:

• Setup a Project with DsExcel Dependency
• Add Data to the Worksheet
• Create Charts for Dashboard

Setup a Project with DsExcel Dependency

Let's begin by setting up a new .NET 8 Console App that includes the DsExcel dependency by following these steps:

1. Open Visual Studio and select File | New | Project to create a new Console App.
2. Right-click on the project in Solution Explorer and choose Manage NuGet Packages… from the context menu.

3.Search for  Ds.Documents.Excel in the NuGet Package Manager and click on Install.

Now that we've successfully set up the project, it is time to create a new Workbook object to develop our report. The DsExcel code to initialize the new Workbook is below:

    // Create a new Workbook Object
    Workbook workbook = new Workbook();
    //Access the first sheet
    IWorksheet worksheet = workbook.Worksheets[0];

Next, we will add the sales data to our worksheet from the JSON file.

Add Data to the Worksheet

In this step, we will add the sales data to our worksheet for which we will create charts to analyze the data. We have the sales data in the JSON file, so let’s deserialize and convert it into an object array.

The DsExcel code to extract the data and assign it to the worksheet is as follows:

    public class SalesData
    {
      public string Date { get; set; }
      public string Region { get; set; }
      public string ProductCategory { get; set; }
      public string ProductName { get; set; }
      public int Sales { get; set; }
      public int QuantitySold { get; set; }
      public string CustomerSegment { get; set; }
      public string SalesRep { get; set; }
      public string SalesChannel { get; set; }
    }

    //Load JSON data into string
    string jsonString = File.ReadAllText("Data.json");
    JArray? jsonObject = JArray.Parse(jsonString);
    // Parse the data into a C# array
    SalesData[]? salesData = jsonObject?.ToObject<SalesData[]?>();
    //Create Normal Object array to assign it to Workbook
    object[,] objectSalesDataArray = new object[jsonObject!.Count + 1, typeof(SalesData).GetProperties().Length];
    int colIndex = 0;
    int rowIndex = 0;
    foreach (var property in typeof(SalesData).GetProperties())
    {
    objectSalesDataArray[rowIndex, colIndex] = property.Name;
    colIndex++;
    }
    rowIndex++;
    //Iterate sales data array to get the values
    foreach (var data in salesData!)
    {
    colIndex = 0;
    foreach (var property in typeof(SalesData).GetProperties())
    {
    objectSalesDataArray[rowIndex, colIndex] = property.GetValue(data)!;
    colIndex++;
    }
    rowIndex++;
    }
    //Assign object array to range
    worksheet.Range["A1:I11"].Value = objectSalesDataArray;

After assigning the data and applying some formatting, the worksheet looks like this:

Create Charts for Dashboard

After setting up the workbook with data, it is time to create charts to show and analyze the different metrics we initially discussed. Let’s follow the steps below to add a bar chart to the sheet showing the sales made by each sales representative. Then, we will perform various modifications to the chart.

Create a Chart

1: To create the chart, use the AddChart method of the Shapes collection of the sheet and pass the ChartType and target range to plot the chart using the code below:

    //Add BarClustered Chart to create sales by representative chart
    GrapeCity.Documents.Excel.Drawing.IShape representativeSalesChart = worksheet.Shapes.AddChart(ChartType.BarClustered, worksheet.Range["K2"]);

2: To add the data source for the chart, we will use the SetSourceData method of the Chart class that we can access via the Chart property of our IShape object. The DsExcel code implementing this is as follows:

    //Set Data Source for chart
    representativeSalesChart.Chart.SetSourceData(worksheet.Range["E1: E11"], RowCol.Columns);

3: To set the sales representative’s name in the category axis with their associated values, let’s set the CategoryNames property of the Category axes. This property takes the string array, so we fetch this data from the SalesRepresentative column using the following code:

representativeSalesChart.Chart.Axes.Item(AxisType.Category).CategoryNames = Enumerable.Range(1, 10).Select(i => worksheet.Range["H1: H11"][i, 0].Value.ToString()).ToArray();

4: To set the chart’s size and location, use the Height, Width, Top, and Left properties of the IShape object as below:

    //Set Chart Size and Position
    representativeSalesChart.Height = 230;
    representativeSalesChart.Width = 500;
    representativeSalesChart.Left = 450;
    representativeSalesChart.Top = 10;

Format a Chart

1: To set and format the chart title, use the ChartTitle class and customize its font and color using the following code:

    //Set and Format Chart Title
    representativeSalesChart.Chart.ChartTitle.Text = "Sales by Representative";
    representativeSalesChart.Chart.ChartTitle.Font.Bold = true;
    representativeSalesChart.Chart.ChartTitle.Font.Size = 24;
    representativeSalesChart.Chart.ChartTitle.TextFrame.TextRange.Paragraphs[0].Font.Color.RGB = Color.FromArgb(141, 180, 226);

2: To customize the chart’s border, use the ChartArea class to set the border color and weight, as well as round its corners.

    //Format Chart Area Border
    representativeSalesChart.Chart.ChartArea.RoundedCorners = true;
    representativeSalesChart.Chart.ChartArea.Format.Line.Color.RGB = Color.Black;
    representativeSalesChart.Chart.ChartArea.Format.Line.Weight = 2;

3: To format any data point, you can access it from the Points collection using the Points property of your series. The DsExcel code to format the third data point is as follows:

    //Customize Particular Data Point in the Chart
    representativeSalesChart.Chart.SeriesCollection[0].Points[2].Format.Fill.Color.RGB = Color.Green;
    representativeSalesChart.Chart.SeriesCollection[0].Points[2].Format.Line.Weight = 1.5;

After performing the steps above, the chart will appear as below:

In the same way, you can add the chart for other mentioned metrics. Check out the attached sample to see how they’re implemented!

The final dashboard will appear as shown below after adding all the charts:

Conclusion

In this blog post, we demonstrated how to create a sales performance dashboard using DsExcel charts. You can also leverage additional features of Excel like Pivot, Slicer, and Conditional Formatting to create more advanced reports using the DsExcel API.

For more details, please refer to the documentation and demos linked below:

• Product Demo .NET
• Documentation .NET
• Product Demo JAVA
• Documentation JAVA