About Radio Button...

Sham Khole

unread,

Apr 19, 2024, 7:04:23 AM4/19/24

to Excel-DNA

Hi ,
I created a Excel ribbon project in which i want to use Radio Button , so how can i used radio button can anyone help.

So I used .net framework C# to create excel add in project .
Below is my Addin.dna file code -

<?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'>
<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='setting_group' label='Settings'>
<button id='settings_button' label='Settings' size='large' onAction='OnSettings' image='Settings'/>
</group>
<group id='version_group' label='Info'>
<button id='version_button' label='Version' size='large' onAction='OnShowVersion' image='VersionIcon'/>
</group>
<group id='manual_group' label='Help'>
<button id='user_manual_button' label='User Manual' size='large' onAction='OnUserManual' image='UserManual'/>
</group>
<group id='database_group' label='Data Sources'>
<toggleButton id='radio_sql' label='SQL' size='large'
onAction='OnRadioSql'
image='SQLIcon'
getPressed='GetRadioSqlPressed'
screentip='Select SQL data source'
supertip='Select this option to fetch data from an SQL database' />

<toggleButton id='radio_influx' label='Influx' size='large'
onAction='OnRadioInflux'
image='InfluxIcon'
getPressed='GetRadioInfluxPressed'
screentip='Select InfluxDB data source'
supertip='Select this option to fetch data from InfluxDB' />

<toggleButton id='radio_bpipe' label='B Pipe' size='large'
onAction='OnRadioBPipe'
image='BPipeIcon'
getPressed='GetRadioBPipePressed'
screentip='Select B Pipe data source'
supertip='Select this option to fetch data from B Pipe' />
</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' />
<Image Name='InfluxIcon' Path='Images\InfluxIcon.png' Pack='true' />
<Image Name='BPipeIcon' Path='Images\BPipeIcon.png' Pack='true' />

</DnaLibrary>

Govert van Drimmelen

unread,

Apr 19, 2024, 3:55:29 PM4/19/24

to exce...@googlegroups.com

Hi Sham,

There is no radio button control on the Office ribbon.

However, you can make some toggle buttons, and then ensure at most (or exactly) one of them is toggled as a time.

The ribbon might look like this:

<?xml version="1.0" encoding="UTF-8"?>

<tabs>

</buttonGroup>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

With backing code like this:

using System.Runtime.InteropServices;

using ExcelDna.Integration.CustomUI;

namespace RibbonStart

{

[ComVisible(true)]

public class MyRibbon : ExcelRibbon

{

public override string GetCustomUI(string RibbonID)

{

return RibbonResources.Ribbon;

}

public override object LoadImage(string imageId)

{

// This will return the image resource with the name specified in the image='xxxx' tag

return RibbonResources.ResourceManager.GetObject(imageId);

}

IRibbonUI _ribbon;

public void OnLoad(IRibbonUI ribbon)

{

_ribbon = ribbon;

}

string _pressedButtonId;

public void OnButtonPressed(IRibbonControl control, bool pressed)

{

if (_pressedButtonId == control.Id)

{

// Unpress the button

_pressedButtonId = null;

}

else

{

_pressedButtonId = control.Id;

}

// Invalidate all the buttons in our group

_ribbon.InvalidateControl("button1");

_ribbon.InvalidateControl("button2");

_ribbon.InvalidateControl("button3");

// Could also invalidate the whole ribbon

// _ribbon.Invalidate();

}

public bool GetPressed(IRibbonControl control)

{

return control.Id == _pressedButtonId;

}

-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/c167f903-8dd0-4b7d-9f0a-dab077e7807dn%40googlegroups.com.

Sham Khole

unread,

Apr 22, 2024, 2:07:41 AM4/22/24

to Excel-DNA

Hi Govert,

Thank you for your detailed explanation. I'll proceed with implementing toggle buttons as you suggested.

However, I encountered a couple of issues with my project:

Resource File: Unfortunately, I don't have a resource file (RibbonResources) in my project. Could you please guide me on how to handle the absence of this resource file?
OnLoad Method: It seems that the OnLoad method in my project is not getting called when I run the project. As a result, the ribbon is not loading properly. Do you have any insights on why this might be happening? 3. Below is code for you reference .

ExcelRibbonUDFAddin File -
using ExcelDna.Integration;

namespace BanorCapital
{
public class ExcelRibbonUDFAddin : IExcelAddIn
{
public void AutoOpen()
{
// startup code
}

public void AutoClose()
{
// clean up
}
}
}

=================
Add-In.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'>
<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='setting_group' label='Settings'>
<button id='settings_button' label='Settings' size='large' onAction='OnSettings' image='Settings'/>
</group>
<group id='version_group' label='Info'>
<button id='version_button' label='Version' size='large' onAction='OnShowVersion' image='VersionIcon'/>
</group>
<group id='manual_group' label='Help'>
<button id='user_manual_button' label='User Manual' size='large' onAction='OnUserManual' image='UserManual'/>
</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>
</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' />
<Image Name='InfluxIcon' Path='Images\InfluxIcon.png' Pack='true' />
<Image Name='BPipeIcon' Path='Images\BPipeIcon.png' Pack='true' />

</DnaLibrary>

===============
RibbonController.cs File
=============

using ExcelDna.Integration.CustomUI;
using ExcelDna.Integration;
using System;
using System.Runtime.InteropServices;
using System.Net.Http;
using System.Linq;
using Newtonsoft.Json.Linq;
using System.Net;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BanorCapital
{
[ComVisible(true)]
public class RibbonController : ExcelRibbon, IDisposable
{
private Microsoft.Office.Core.IRibbonUI _ribbonUi;
private string selectedDataSource = ""; // Default selection

/// <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;
}
/// <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()
{
// Check if the user is authenticated (you can store this information in a flag after login)
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)
{
//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 "Fetch Data" button click on the Ribbon.
/// Prompts the user for a fund number, then fetches data from the server and displays it in the active Excel sheet.
/// </summary>
public async void OnFetchData(Microsoft.Office.Core.IRibbonControl control)
{
//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
//}
try
{
Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
double fundNumberDouble = excelApp.InputBox("Enter Fund Code:", Type: 1);

int fundNumber = (int)fundNumberDouble;

using (var client = new HttpClient())
{
ServicePointManager.SecurityProtocol |= SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

var response = await client.GetAsync($"http://172.16.5.21:5027/get-excel-portfolio/{fundNumber}");

if (response.IsSuccessStatusCode)
{
var responseBody = await response.Content.ReadAsStringAsync();

var dataArray = JArray.Parse(responseBody);

var headers = dataArray[0].ToObject<JObject>().Properties().Select(p => p.Name).ToList();

ExcelAsyncUtil.QueueAsMacro(() =>
{
try
{
var activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)App.ActiveSheet;

if (activeSheet != null)
{
for (int i = 0; i < headers.Count; i++)
{
activeSheet.Cells[1, i + 1].Value = headers[i];
}

for (int i = 0; i < dataArray.Count; i++)
{
var item = dataArray[i].ToObject<JObject>();
var values = item.Properties().Select(p => p.Value.ToString()).ToList();
for (int j = 0; j < values.Count; j++)
{
activeSheet.Cells[i + 2, j + 1].Value = values[j];
}
}

activeSheet.Columns.AutoFit();
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
});
}
else
{
MessageBox.Show($"Failed to fetch data. Status code: {response.StatusCode}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (FormatException)
{
Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
excelApp.StatusBar = "Invalid input format. Please enter a valid number.";
}
catch (Exception ex)
{
Microsoft.Office.Interop.Excel.Application excelApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
excelApp.StatusBar = $"An error occurred: {ex.Message}";
}
}

//public void OnSettings(Microsoft.Office.Core.IRibbonControl control)
//{
// MessageBox.Show($"This functionality is under development.");
//}

/// <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)
{
//if (!LoginForm.IsAuthenticated)
//{
// MessageBox.Show("You must be logged in to access this functionality.", "Access Denied", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
//}
ShowAvailableFieldsPopup();
}

/// <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.1";

MessageBox.Show($"BanorCapital Add-In Version: {projectVersion}", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

public void OnUserManual(Microsoft.Office.Core.IRibbonControl control)
{
var userManualForm = new UserManualForm();
userManualForm.ShowDialog();
}

public void OnCheckBoxSql(Microsoft.Office.Core.IRibbonControl control, bool isPressed)
{
if (isPressed)
{
// Set the selected data source and invalidate the other checkboxes
selectedDataSource = "SQL";
MessageBox.Show("SQL");
InvalidateOtherCheckBoxes(control.Id);
}
}

public void OnCheckBoxInflux(Microsoft.Office.Core.IRibbonControl control, bool isPressed)
{
if (isPressed)
{
// Set the selected data source and invalidate the other checkboxes
selectedDataSource = "Influx";
MessageBox.Show("Influx");
InvalidateOtherCheckBoxes(control.Id);
}
}

public void OnCheckBoxBPipe(Microsoft.Office.Core.IRibbonControl control, bool isPressed)
{
if (isPressed)
{
// Set the selected data source and invalidate the other checkboxes
selectedDataSource = "B Pipe";
MessageBox.Show("B Pipe");
InvalidateOtherCheckBoxes(control.Id);
}
}

public bool GetCheckBoxSqlPressed(Microsoft.Office.Core.IRibbonControl control)
{
return selectedDataSource == "SQL";
}

public bool GetCheckBoxInfluxPressed(Microsoft.Office.Core.IRibbonControl control)
{
return selectedDataSource == "Influx";
}

public bool GetCheckBoxBPipePressed(Microsoft.Office.Core.IRibbonControl control)
{
return selectedDataSource == "B Pipe";
}

private void InvalidateOtherCheckBoxes(string selectedCheckBoxId)
{
// Check if _ribbonUi is not null before calling InvalidateControl
if (_ribbonUi != null)
{
// Invalidate the checkboxes other than the selected one
if (selectedCheckBoxId != "checkbox_sql")
{
_ribbonUi.InvalidateControl("checkbox_sql");
}
if (selectedCheckBoxId != "checkbox_influx")
{
_ribbonUi.InvalidateControl("checkbox_influx");
}
if (selectedCheckBoxId != "checkbox_bpipe")
{
_ribbonUi.InvalidateControl("checkbox_bpipe");
}
}
}

/// <summary>
/// Method to fetch data from SQL database and update the active Excel sheet.
/// </summary>
private void FetchDataFromSql()
{
// Implement your code to fetch data from SQL database
// Then update the active Excel sheet with the fetched data
MessageBox.Show("Hello from SQL");
}

/// <summary>
/// Method to fetch data from InfluxDB and update the active Excel sheet.
/// </summary>
private void FetchDataFromInflux()
{
// Implement your code to fetch data from InfluxDB
// Then update the active Excel sheet with the fetched data
MessageBox.Show("Hello from Influx");
}

/// <summary>
/// Method to fetch data from Bloomberg Pipe (B Pipe) and update the active Excel sheet.
/// </summary>
private void FetchDataFromBPipe()
{
// Implement your code to fetch data from B Pipe
// Then update the active Excel sheet with the fetched data
MessageBox.Show("Hello from B Pipe");
}

/// <summary>
/// Fetches funds data from the server asynchronously.
/// </summary>
/// <returns>The funds data as a JSON string.</returns>
private async Task<string> FetchFundsDataAsync()
{
using (HttpClient client = new HttpClient())
{
try
{
HttpResponseMessage response = await client.GetAsync("http://172.16.5.22:5090/info");
response.EnsureSuccessStatusCode();

string data = await response.Content.ReadAsStringAsync();

data = WebUtility.HtmlDecode(data);
return data;
}
catch (Exception ex)
{
MessageBox.Show($"Error fetching data from API: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
}

/// <summary>
/// Displays a pop-up with information about all funds.
/// </summary>
private async void ShowAllFundsPopup()
{
string fundsData = await FetchFundsDataAsync();

DisplayFundsPopup(fundsData);
}

/// <summary>
/// Method to show the pop-up window
/// </summary>
private void ShowAvailableFieldsPopup()
{
var popup = new AvailableFieldsPopup();
popup.ShowDialog();
}

/// <summary>
/// Displays a pop-up with funds data.
/// </summary>
/// <param name="data">The funds data to display.</param>
private void DisplayFundsPopup(string data)
{
if (data == null)
{
return;
}

var popup = new FundsPopup(data);

popup.ShowDialog();
}

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

Your assistance would be greatly appreciated..

Thanks, Danish

Govert van Drimmelen

unread,

Apr 22, 2024, 1:39:46 PM4/22/24

to Excel-DNA

You don't need to put the ribbon markup code in a resource file - it should work fine from the .dna file too, or you can return as a string from a method in the ribbon class that overrides the 'GetCustomUI' method.

For the onLoad, you need to add the callback specification to the <customUI> tag like this (in the .dna file this is the inside one, the one with a small "c"):

Then the OnLoad method in the ribbon class will be called, giving you the IRibbonUI interface.

-Govert

Sham Khole

unread,

Apr 23, 2024, 1:07:20 AM4/23/24

to Excel-DNA

Hi Govert,

Thank you for the clarification! I appreciate your guidance on using the ribbon markup directly in the .dna file and specifying the onLoad callback in the <customUI> tag. It worked perfectly, and I was able to implement the OnLoad method in the ribbon class to receive the IRibbonUI interface as expected.

Your assistance has been invaluable in helping me understand the intricacies of Excel-DNA integration. Thanks again for your support!

Best regards, [Sham]

Reply all

Reply to author

Forward