How to Implement Loader Functionality in Excel DNA Add-In
Sham Khole
I am currently working on an Excel DNA Add-In where I am utilizing .NET Framework C# for integration. Within the Add-In, I have configured buttons to execute various actions, including fetching data from an external endpoint.
To enhance user experience and provide visual feedback during data fetching processes, I am looking to implement a loader functionality. I aim for this loader to appear when a button is clicked, indicating to users that the operation is ongoing.
Here's a snippet of the XML code structure I'm working with:
.dna file -
<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="BanorCapital Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2020/07/dnalibrary">
<ExternalLibrary Path="BanorCapital.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" IncludePdb="false" />
<CustomUI>
<customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui' loadImage='LoadImage' onLoad='OnLoad'>
<ribbon>
<tabs>
<tab id='sample_tab' label='Banorcapital'>
<group id='sample_group' label='Operations'>
<button id='all_funds' label='Fund List' size='large' onAction='OnAllFunds' image='AllFunds'/>
<button id='fetch_data' label='Portfolio Data' size='large' onAction='OnFetchData' image='PortFolio'/>
<button id='available_fields' label='Available Fields' size='large' onAction='OnAvailableFields' image='CustomAvailableFields'/>
</group>
<group id='database_group' label='Data Sources'>
<checkBox id='checkbox_sql' label='SQL'
onAction='OnCheckBoxSql'
getPressed='GetCheckBoxSqlPressed'
screentip='Select SQL data source'
supertip='Select this option to fetch data from an SQL database'/>
<checkBox id='checkbox_influx' label='Influx'
onAction='OnCheckBoxInflux'
getPressed='GetCheckBoxInfluxPressed'
screentip='Select InfluxDB data source'
supertip='Select this option to fetch data from InfluxDB'/>
<checkBox id='checkbox_bpipe' label='B-Pipe'
onAction='OnCheckBoxBPipe'
getPressed='GetCheckBoxBPipePressed'
screentip='Select B Pipe data source'
supertip='Select this option to fetch data from B Pipe'/>
</group>
<group id='dynamic_group' label='B-Pipe App'>
<button id='status_button' label='Status' size='large' onAction='OnStatus' getImage='GetDynamicStatus'/>
</group>
<group id='setting_group' label='Settings'>
<button id='settings_button' label='Settings' size='large' onAction='OnSettings' image='Settings'/>
</group>
<group id='info_group' label='Info'>
<button id='user_manual_button' label='Help' size='large' onAction='OnUserManual' image='UserManual'/>
<button id='version_button' label='Version' size='large' onAction='OnShowVersion' image='VersionIcon'/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
</CustomUI>
<Image Name='PortFolio' Path='Images\PortFolio.png' Pack='true' />
<Image Name='CustomAvailableFields' Path='Images\AvailableFields.png' Pack='true' />
<Image Name='AllFunds' Path='Images\AllFunds.png' Pack='true' />
<Image Name='Settings' Path='Images\Settings.png' Pack='true' />
<Image Name='VersionIcon' Path='Images\VersionIcon.png' Pack='true' />
<Image Name='UserManual' Path='Images\UserManual.png' Pack='true' />
<Image Name='SQLIcon' Path='Images\SQLIcon.png' Pack='true' />
</DnaLibrary>
===========
ribboncontroller .cs
==========
using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration;
using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Timers;
using System.IO;
using TimeZoneConverter;
namespace BanorCapital
{
[ComVisible(true)]
public partial class RibbonController : ExcelRibbon, IDisposable
{
private Microsoft.Office.Core.IRibbonUI _ribbonUi;
private static RibbonController instance;
private string selectedDataSource = "Influx";
private bool isFlagEnabled = false;
private System.Timers.Timer timer;
string timezone = TimeZoneInfo.Local.Id;
public static RibbonController Instance
{
get { return instance; }
}
public RibbonController()
{
// Initialize timer with 10 seconds interval
timer = new System.Timers.Timer(10000);
timer.Elapsed += Timer_Elapsed;
timer.Start();
// Get the timezone identifier in "Continent/City" format
timezone = GetFormattedDateTimeWithTimeZone(timezone);
}
private string GetFormattedDateTimeWithTimeZone(string timezone)
{
// Get the current UTC time
DateTime now = DateTime.UtcNow;
// Format the UTC time into the desired string format
string formattedDateTime = now.ToString("yyyy-MM-dd HH:mm:ss");
return formattedDateTime;
}
private void Timer_Elapsed(object sender, ElapsedEventArgs e)
{
// Call GetButtonImage to update the button image
_ribbonUi.InvalidateControl("status_button");
}
public string SelectedDataSource
{
get { return selectedDataSource; }
set { selectedDataSource = value; }
}
/// <summary>
/// Gets the Excel application instance.
/// </summary>
private Microsoft.Office.Interop.Excel.Application App
{
get => (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
}
/// <summary>
/// Handles the Ribbon load event.
/// Initializes the IRibbonUI interface.
/// </summary>
public void OnLoad(Microsoft.Office.Core.IRibbonUI ribbonUI)
{
_ribbonUi = ribbonUI;
instance = this; // Assign the current instance to the static property
}
/// <summary>
/// Handles the "Settings" button click on the Ribbon.
/// Displays the login form if the user is not already authenticated.
/// </summary>
public void OnSettings(Microsoft.Office.Core.IRibbonControl control)
{
if (IsUserAuthenticated())
{
MessageBox.Show("You are already logged in.", "Already Logged In", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (!IsUserAuthenticated())
{
var loginForm = new LoginForm();
DialogResult result = loginForm.ShowDialog();
if (result != DialogResult.OK)
{
return;
}
}
}
/// <summary>
/// Checks if the user is authenticated.
/// </summary>
/// <returns>True if the user is authenticated, otherwise false.</returns>
private bool IsUserAuthenticated()
{
return LoginForm.IsAuthenticated;
}
/// <summary>
/// Handles the "All Funds" button click on the Ribbon.
/// Displays a pop-up with information about all available funds.
/// </summary>
public void OnAllFunds(Microsoft.Office.Core.IRibbonControl control)
{
//As per client requirement need to disable authinticate functionality.
//if (!LoginForm.IsAuthenticated)
//{
// MessageBox.Show("You must be logged in to access this functionality.", "Access Denied", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return; // Exit the method since the user is not authenticated
//}
ShowAllFundsPopup();
}
/// <summary>
/// Handles the "Available Fields" button click on the Ribbon.
/// Displays a pop-up with information about available fields.
/// </summary>
public void OnAvailableFields(Microsoft.Office.Core.IRibbonControl control)
{
//As per client requirement need to disable authinticate functionality.
//if (!LoginForm.IsAuthenticated)
//{
// MessageBox.Show("You must be logged in to access this functionality.", "Access Denied", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
//}
var popup = new AvailableFieldsPopup();
popup.ShowDialog();
}
/// <summary>
/// Handles the "Show Version" button click on the Ribbon.
/// Displays the add-in version information.
/// </summary>
public void OnShowVersion(Microsoft.Office.Core.IRibbonControl control)
{
const string projectVersion = "1.0.3";
MessageBox.Show($"BanorCapital Add-In Version: {projectVersion}", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// Handles the "User Manual" button click on the Ribbon.
/// Displays the user manual form.
/// </summary>
public void OnUserManual(Microsoft.Office.Core.IRibbonControl control)
{
var userManualForm = new UserManualForm();
userManualForm.ShowDialog();
}
public void OnStatus(Microsoft.Office.Core.IRibbonControl control)
{
}
/// <summary>
/// Retrieves the dynamic status image based on user authentication and flag status.
/// </summary>
/// <param name="control">The Ribbon control requesting the image.</param>
/// <returns>A System.Drawing.Bitmap representing the dynamic status.</returns>
public System.Drawing.Bitmap GetDynamicStatus(Microsoft.Office.Core.IRibbonControl control)
{
string username = LoginForm.AuthenticatedUsername;
if (string.IsNullOrEmpty(username))
{
string defaultImagePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Images", "LogoutUser.png");
return new System.Drawing.Bitmap(defaultImagePath);
}
try
{
string timezone = TimeZoneInfo.Local.Id;
bool flagStatus = FetchFlagStatusFromEndpoint(username, timezone);
string imageName = flagStatus ? "LoginUser.png" : "LogoutUser.png";
string imagePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Images", imageName);
System.Drawing.Bitmap image = new System.Drawing.Bitmap(imagePath);
return image;
}
catch (Exception ex)
{
Console.WriteLine($"Error fetching flag status: {ex.Message}");
string defaultImagePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Images", "LogoutUser.png");
return new System.Drawing.Bitmap(defaultImagePath);
}
}
/// <summary>
/// Disposes of resources used by the RibbonController.
/// </summary>
public void Dispose()
{
}
}
}