How to Update Ribbon Icon Based on Login Status.

[Sham Khole]

Description: We aim to modify our existing C# code to periodically check the user's login status by sending requests to a external Java application (As it is a External Application). Based on the response received, we intend to dynamically update the icon or indicator displayed in the Excel Ribbon accordingly.

Given our project requirements and technology stack, we are considering leveraging .NET Framework C# along with Excel DNA for this implementation. Can you confirm if this functionality is achievable with these technologies?

Best regards,

Sham 

[Govert van Drimmelen]

Hi Sham,

 

Yes, you can do what you require from an Excel-DNA add-in.

Here is some step-by-step guide on getting started with the Ribbon:

Tutorials/Fundamentals/RibbonBasics at master · Excel-DNA/Tutorials (github.com)

 

Now to do the kind of update you are asking about, you would do the following:

• Add an ‘onLoad’ handler to your ribbon definition and ribbon class. This lets you get hold of the IRibbonUI interface that is used to invalidate the ribbon.
• For the indicator element, implement a callback in the ribbon class that lets you return the right indicator based on some internal backing variable in the class.
• When the external response is received, update the indicator backing variable, and then call _ribbonUI.InvalidateControl(…) to cause Excel to refresh the callbacks, getting the new value.
• If the external call is happening on a worker or i/o completion thread, it’s best to wrap the ribbon InvalidateControl call in a helper that will call it from the main thread –

ExcelAsyncUtil.QueueAsMacro(() => _ribbonUI.InvalidateControl("loginIndicator"));

 

You can also search for these keywords to find further discussion and examples on the Google group: “onload”, “Invalidate”, “QueueAsMacro”.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/ee0275f9-46f3-43c9-bc94-4e33759df3a0n%40googlegroups.com.

[Sham Khole]

Hi Govert,

While implementing functionality in our Excel add-in, I encountered an error when attempting to add a custom image to the GetButtonImage method. Specifically, I received the following error messages:

1. "Unknown Office control id: ToggleImage"
2. "The callback GetButtonImage returned a value that could not be converted to the expected type."

This error does not occur when using inbuilt images (imageMso), but it persists when attempting to add custom images.

Additionally, I would like to mention that I have successfully implemented functionality to update the Ribbon icon based on login status. However, I am encountering difficulty when trying to update a custom image in the Ribbon.

Could you kindly provide insights into the possible reasons for this issue or suggest steps to resolve it? I have reviewed the code and ensured that the image path is correctly specified, but the error persists.

Please refer below code for clear understanding.

Ribboncontroller.cs file -

using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration;
using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using System.Net;
using Newtonsoft.Json;
using System.Timers;

namespace BanorCapital
{
    [ComVisible(true)]
    public partial class RibbonController : ExcelRibbon, IDisposable
    {
        private Microsoft.Office.Core.IRibbonUI _ribbonUi;
        private string selectedDataSource = "Influx";
        private static RibbonController instance;
        private bool isFlagEnabled = false;
        string username = "a.pagare";
        private System.Timers.Timer timer;


        public static RibbonController Instance
        {
            get { return instance; }
        }

        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.2";

            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 ToggleFlagAndIcon()
        {
            isFlagEnabled = !isFlagEnabled; // Toggle the flag
        }

        public void OnToggleButton(Microsoft.Office.Core.IRibbonControl control)
        {
            ToggleFlagAndIcon(); // Toggle the flag and update the image
        }

        public RibbonController()
        {
            // Initialize timer with 10 seconds interval
            timer = new System.Timers.Timer(10000);
            timer.Elapsed += Timer_Elapsed;
            timer.Start();
        }

        private void Timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            // Call GetButtonImage to update the button image
            _ribbonUi.InvalidateControl("toggle_button");
        }
        public string GetButtonImage(Microsoft.Office.Core.IRibbonControl control)
        {
            bool flagStatus = FetchFlagStatusFromEndpoint(username);
           // bool flagStatus = false;
            return flagStatus ? "PersonaStatusOnline" : "InkToolsClose";
            //return !isFlagEnabled ? "ToggleImage" : "SQLIcon";
        }
        private bool FetchFlagStatusFromEndpoint(string username)
        {
            string endpointUrl = $"http://172.16.5.21:5027/get-isuseractive/{username}";

            try
            {
                using (var client = new WebClient())
                {
                    string jsonResponse = client.DownloadString(endpointUrl);
                    dynamic responseData = JsonConvert.DeserializeObject(jsonResponse);
                    bool flagStatus = responseData.flagStatus;

                    isFlagEnabled = flagStatus;

                    _ribbonUi.InvalidateControl("toggle_button");
                    return flagStatus;
                }
            }
            catch (WebException ex)
            {
                Console.WriteLine($"Error fetching flag status: {ex.Message}");
                throw ex;
            }
            catch (JsonException ex)
            {
                Console.WriteLine($"Error parsing JSON response: {ex.Message}");
                throw ex;
            }
        }


        /// <summary>
        /// Disposes of resources used by the RibbonController.
        /// </summary>
        public void Dispose()
        {

        }
    }
}

.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='toggle_button' label='Status' size='large' onAction='OnToggleButton' getImage='GetButtonImage'/>
</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='ToggleImage' Path='Images\ToggleImage.png' Pack='true' />
<Image Name='SQLIcon' Path='Images\SQLIcon.png' Pack='true' />

</DnaLibrary>

[Govert van Drimmelen]

Hi Sham,

Your ribbon callback method for getImage should have the signature:

        public object getImage(string imageId) { ...}

You can return one of the following:

        1. An IPictureDisp
        2. A System.Drawing.Bitmap
        3. A string containing an imageMso identifier

For your own images, it's easiest just to load it like this:

    var image = System.Drawing.Image.FromFile(filePath);

You can return this image directly from the callback if it is a Bitmap.

-Govert

[Sham Khole]

Hi Govert,

Thanks for the clarification!

I've updated the getImage callback method according to your guidance, and it's now working smoothly with both custom and built-in images.

Best regards, Sham