Starter questions, adding ribbon.xml to a solution and using ExcelAsyncUtil.Run function for REST API

100 views
Skip to first unread message

Stefan Cvetkovic

unread,
Aug 27, 2023, 6:31:46 PM8/27/23
to Excel-DNA
Hello!
First you have my respect for doing this excel-dna project alive all these years, well done!

Im from .net and c# world.

I have a task to perform and let me explain briefly what is all about:
1. I need to create an XML ribbon which will have for now one button. When User clicks on that button it need to show a gif image like working, while Im collecting the data from some rest api endpoint.
I dont know but my dla file is not created when I started the project, so I just imported the whole ribbon in GetCustomUI override method.

2. Collecting the data that I tested with httpClient works fine, but Im not sure how to insert those data into a sheet with async function ExcelAsyncUtil.Run?

I attached my solution, but please be aware that its not buildable, this is just an example of my work.

Any help would be welcomed, firstly I would like to have seprate xml file for my ribbon, and to know how can I import for each child of root element in my json response a new line in my sheet and keep going like that.

Hope that Im not too much confusing :)

Thanks!


ExcelDNA_SefEndpoint.zip

Govert van Drimmelen

unread,
Aug 28, 2023, 5:28:34 AM8/28/23
to exce...@googlegroups.com

Hi Stefan,

 

To use an xml file, you add it as a resource in the C# project, then extract and return this resource from your GetCustomUI().

The code for this is standard, you can search for “C# ResourceManager”.

 

For the async write-back, you would use ExcelAsyncUtil.QueueAsMacro and not ExcelAsyncUtil.Run (which is used to implement async worksheet functions).

I’ve modified your example a bit and added some notes.

I paste the updated code below.

 

-Govert

 

using ExcelDna.Integration;

using ExcelDna.Integration.CustomUI;

using Microsoft.Office.Interop.Excel;

using System;

 

namespace ClassLibrary2

{

    public class CustomRibbon : ExcelRibbon

    {

        public override string GetCustomUI(string RibbonID)

        {

            // NOTE: There were errors in the xml here.

            // To get Excel to display any xml errors when loading the ribbon, enable

            // File -> Options -> Advanced -> General -> "Show add-in user interface errors"

            return @"

              <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>

                <ribbon>

                    <tabs>

                        <tab id='sef' label='SEF Demo'>

                            <group id='grp_1' label='Podesavanja'>

                                <button id='btn_showGreeting' label='Učitaj fakture' onAction='GetInvoiceData'/>

                            </group>

                        </tab>

                    </tabs>

                </ribbon>

              </customUI>";

        }

 

        // NOTE: Using an "async void" event handler is OK here, since we don't need to wait for anything to complete

        // But we have to be careful to catch any exceptions, since they will otherwise be unobserved

        public async void GetInvoiceData(IRibbonControl ribbonControl)

        {

            try

            {

                var result = await GetInvoiceDataAsync();

                ExcelAsyncUtil.QueueAsMacro(() =>

                    {

                        // NOTE: Install the ExcelDna.Interop package to get a reference to the Excel COM object model

                        // (the Microsoft.Office.Interop.Excel namespace)

                        // Then use ExcelDnaUtil.Application to get the root Application object

                        Application xlApp = (Application)ExcelDnaUtil.Application;

                        Worksheet targetSheet = xlApp.ActiveWorkbook.Sheets["Sheet1"];

                        targetSheet.Range["A1"].Value = result.ToString();

 

                        // NOTE: The workbook and sheet that was "Active" might not be the active one after the async call

                        // (They might not even exists anymore)

                    });

            }

            catch (Exception ex)

            {

                // NOTE: Decide on how to handle errors

                // - maybe write to a "Results" range in the sheet,

                // show a pop-up, display on a Custom Task Pane, write to a log etc.

                Application xlApp = (Application)ExcelDnaUtil.Application;

                Worksheet targetSheet = xlApp.ActiveWorkbook.Sheets["Sheet1"];

                targetSheet.Range["A1"].Value = ex.Message;

            }

        }

 

 

        // NOTE: Better to have only one HttpClient instance for the whole add-in

        // Otherwise it is like opening a new borwser for every request.

        HttpClient _httpClient = new HttpClient();

        public async Task<object> GetInvoiceDataAsync()

        {

            // NOTE: Here we can do all the api key setups etc.

            // We should not call back to Excel here, just fetch and return the results,

            // or throw an Exception if there are errors.

            return await _httpClient.GetStringAsync(https://www.google.com);

--
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/cccbdcf1-59ce-4ff2-b821-d68ac5fe6707n%40googlegroups.com.

Reply all
Reply to author
Forward
0 new messages