Search code examples
javascriptexceloffice-jsexcel-addins

Format Header in Excel with Office JS

I'm currently trying to implement some automations for an Excel Workbook. Part of it is to set a common header for all sheets in that workbook.

The header should look like this: Screenshot of the aimed header style and content

I found here question (52295459) how to access the headers, but i did not manage to format them. The code snippet is attached below. I read the documentation for the footerHeader class and for the references format codes. But I'm stuck.

So, these are my questions:

  1. Is it possible to set a header for all sheets in a workbook in a more general way, than accessing each by getItem and their name?
  2. How can I change the font color to #5E5E5E? I tried with &5E5E5E,&c5E5E5E, &color5E5E5E, &#5E5E5E,&K5E5E5E, &K"5E5E5E" - but nothing worked. Edit: I want to change the colour of the header section.
  3. I only need to adjust the center section of the header and may keep the content of left and right sections. However, if the left and right sections are not empty, whatever I specify in the center section will be attached to the end of the right section (attached string "center" wrongfully to right section). Is there a way to keep the left and right section and only change the center section?
  4. If not: How can I insert the logo as an image and maybe format the height/width of it?

The working part of the code so far:

//An array with the names of my wokrsheets
const worksheetNames : string[] = ["Titelseite", "GewerkeModule", "Statusanzeige", "Verteilerliste"];

//Access header of each sheet individually
for (const sheet of worksheetNames) {
    const headerFooter : Excel.HeaderFooter = context.workbook.worksheets
                                                .getItem(sheet)
                                                .pageLayout
                                                .headersFooters.defaultForAllPages;

    //Set the strings for the header sections,
    //  in which also the formatting should be contained (I guess)
    headerFooter.centerHeader = "center";
    headerFooter.leftHeader   = "left";
    headerFooter.rightHeader  = "right"
}

Thank you already for your help! Kind regards

Update: I tried to solve my issues again, and indeed i cannot reproduce problem 3. anymore, too. I dont know why and what i have changed. And by printing out the current header string i found at least formatting for the coloring of the header.

So, to sum up, the answers to my problems:

  1. No common solution for setting headers in all sheets - Thanks for clarifying to Rita. However: The solution with looping through all sheets seems to work (see my code above)

  2. This sets the colour to a light grey and also include a "page X of y" part in the third line. BUT: These shortcodes (&Z, &S, &A) have nothing to do with the format codes referenced by microsoft. I guess these are the German format codes, because my Excel installation is set to German. But that would be annoying if different codes are necessary for different language settings? context.workbook.worksheets.getItem(sheet).pageLayout.headersFooters.defaultForAllPages.centerHeader = "&Z&M01+043Heder Line1 \nNextLine\nPage &S of &A" &Z centers the output ("zentrieren") &M01+043 seems to set the font colour to light grey \n for line breaks &S current page number ("Seite") &A number of all pages in total

  3. I cannot reproduce the error anymore either. Magic? It seems to be connected to the formatting...

  4. Not implemented yet - thanks again to Rita

Solution

  • Thanks post the question here.

    1. Currently it's not possible to set header and footer for all sheet with one API, this might be one new request.

    2. var worksheet = context.workbook.worksheets.getItem("Sheet7"); var range = worksheet.getRange("A1"); range.format.font.color = "5e5e5e"; can help set color

    3. cannot repro, tried set middle header when left and right header are set, it works good in win32, so which platform do you hit?

    4. Sorry to let you know the image is not supported yet in api level, you can submit new request Click here!