How to set value in Excel footer using Microsoft.Office.Interop.Excel in iManage extension?

Question

I'm building a desktop extensions for iManage work desktop in .NET Framework 4.8 that should be able to set a particular value in the footer of Word, Excel and Powerpoint documents.

I've managed to make it work in Word, but it doesn't work in Excel. By reading the interop documentation my conclusion is that the footer value can be set with: https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.pagesetup.centerfooter?view=excel-pia#microsoft-office-interop-excel-pagesetup-centerfooter

However, using the pageSetup.CenterFooter I can successfully retrieve the value in the footer, but not set it.

I've tried by subscribing to the WorkbookBeforeSave & WorkbookSaveAs events on the IExcelPlugInHostfrom iManage.Work.Tools in Initialize I can successfully trigger functions when the events are raised in iManage.

I can successfully change the value of a cell each time the event is triggered, but I can't manage to set a value in the footer.

using System;
using iManage.Work.Tools;
using Microsoft.Office.Interop.Excel;

namespace iManageExcelPlugin
{
    public class AddFooterOnFileSaveExcel : PlugInBase
    {
        private int testvalue = 1;

        public override bool Initialize(iManage.Work.Tools.IPlugInHost host)
        {
            try
            {
                PlugInHost = host;
                PlugInId = "AddFooterOnFileSaveExcel";
                IExcelPlugInHost wdHost = host as IExcelPlugInHost;
                if (wdHost == null)
                {
                    throw new ArgumentNullException(nameof(wdHost), "Typecast to IExcelPlugInHost failed.");
                }

                wdHost.WorkbookBeforeSave += BeforeSaveTest;

                return true;
            }
            catch (Exception ex)
            {
                WLog.Error($"An error occured during initialize: {ex}");
                return false;
            }

        }

        private void BeforeSaveTest(object sender, WOfficeEventArgs e)
        {
            Workbook workbook = e.OfficeDocument as Workbook;
            Worksheet worksheet = workbook.Worksheets.Item[1] as Worksheet;

            var testString = $"Testing: {testvalue}";

            worksheet.Cells[1, 1] = testString;
            worksheet.PageSetup.CenterFooter = testString;

            testvalue++;
        }
    }
}

I'll also include an example from the iManage Work Desktop for Windows SDK Guide documentation from October 2022 version 10.8.0 below. This example displays how the footer can be set in a word document. Unfortunately there is no documentation for Excel & Powerpoint.

using System;
using iManage.Work.Tools;
using Word = Microsoft.Office.Interop.Word;
namespace Work10OfficePlugins
{
    public class WordPlugIn2 : PlugInBase
    {
        public override bool Initialize(IPlugInHost host)
        {
            PlugInHost = host;
            PlugInId = "myId";
            IWordPlugInHost wordhost = host as IWordPlugInHost;
            
            wordhost.DocumentBeforeSave += this.onDocumentBeforeSave;
            return true;
        }
        private void onDocumentBeforeSave(object sender, WOfficeSaveAsEventArgs e)
        {
            WLog.TraceEnter();
            WLog.Debug("onDocumentBeforeSaveCalled");
            
            var testString = "this is a test!";
            
            Word.Document documentBeingSaved = (Word.Document)e.OfficeDocument;
                        documentBeingSaved.Sections[1].Footers[Word.WdHeaderFooterIndex.wdHeaderFooterPrimary].Range.Text = stateOfDocument;
            WLog.TraceExit();
        }
    }
}

I'm referencing Microsoft.OFfice.Interop.Excel version 15.0.0.0.

Any ideas of what I'm doing wrong?

Answer 1

After further testing I've managed to correctly update the footer value as needed.

The WorkbookSaveAs event successfully set the footer value once, but the WorkbookBeforeSave event never updated the footer value. My theory is that PageSetup is only used when creating the document, thus works the first time when the document is created and saved.

To edit an existing document the Page class has to be used: https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.page?view=excel-pia

Thus by changing the code to:

    private void BeforeSaveTest(object sender, WOfficeEventArgs e)
    {
        Workbook workbook = e.OfficeDocument as Workbook;
        
            var testString = $"&\"Arial,Regular\"&11 this is some text";
        
        foreach(Worksheet worksheet in workbook.Worksheets)
        {
            foreach(Page page in worksheet.PageSetup.Pages)
            {
                page.CenterFooter.Text = testString;
            }
        }

        testvalue++;
    }

The page.CenterFooter.Text can be used to update the text and apply fonts and font weight. However, I've not yet managed to set color this way. Any ideas?