Does anyone know how to modify the content of the Excel ribbon at runtime with VSTO 2005SE? Not only update labels or dynamic menus, but also add or remove buttons, tabs, groups, drop downs etc. At runtime means not only at my add-in startup, but also during work with Excel.
C# – Add Excel ribbon controls at runtime (VSTO 2005SE)
cexcelvsto
Related Solutions
You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License
Here some example code for ExcelLibrary:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
There are two possible ways to control the visibility of your ribbon controls. You can either use the visible property or the getVisible event. Both approachs require modifying the xml file you are already using.
If this is your partial xml file now:
<button id="MyButton" label="Hello" onAction="MyButtonOnAction"/>
Then you can hide the control by changing it to:
<button id="MyButton" label="Hello" onAction="MyButtonOnAction" visible="false"/>
This isn't much use as it is hardcoded. To get closer to what you are looking for change the xml to:
<button id="MyButton" label="Hello" onAction="MyButtonOnAction" getVisible="MyButtonGetVisible"/>
And then in the same way you have done the MyButtonOnAction callback you create the MyButtonGetVisible callback which happens to have this signature (C#):
bool MyButtonGetVisible(IRibbonControl control)
With this method you can then return true or false depending on whether you want to show/hide the button. The next question you might have is, the MyButtonGetVisible callback is only ever called once right after my add-in is loaded. I want to show/hide the button later on, how do I get the callback to trigger?
To accomplish this you can use that IRibbonUI object you have and call the Invalidate function which will cause your entire ribbon to be invalidated which will cause the callback to be called. If you require finer control you can call the InvalidateControl function which takes the ControlID as a parameter, and will only invalidate one control, which will cause the callback to be called.
As for adding controls on the fly, I don't believe that to be possible.
Best Answer
I agree with Mike, working with the visibility callback on controls or groups is probably your best bet (that's what we are using).
The entire ribbon layout is loaded from an XML string. I don't know if it is possible to trigger a reload of the XML, which you could then customize to load different XML content.