Header/footer information with -putexcel-

[Sergiy Radyakin]

Dear All,

I am looking for a way to control header/footer information for the Excel (*.xlsx) file produced in Stata by -putexcel-.

For example, I would like to specify that the footer should contain the following in the center of it:

Page &[Page] of &[Pages]

which may look for a particular page/document like:

Page 3 of 318

Is there a way to do this (Stata 17)?

Thank you, Sergiy Radyakin

[Leonardo Guizzetti]

I don't believe it's possible with Stata (yet). This was the closest I was able to find. I suspect that the document model Stata uses doesn't deal with the page view layout, perhaps because "page" is ill defined compared to the normal spreadsheet view.

I just tested a workaround that may be of interest. Create an empty Excel file as a template. In that template, create the desired sheet names and headers/footers in the Page View. From Stata, copy that template file to a new file into which you can modify its contents. When you reopen the file in Excel, you will see the headers/footers are still preserved in the Page View.

[Sergiy Radyakin]

Hello Leonardo,

thank you for your advice. Your suggested workaround is certainly useful, but will not solve my problem for two reasons:
1) I will be producing multiple sheets dynamically each with a header/footer, so pre-cooking one sheet will not be possible;
2) the text/content of the header/footer needs to be dynamic (is programmatically formed) so I can't get away with a pre-typed text.

The header/footer information is worksheet-specific, and seems to be independent of the page size, etc. I find it is sufficient to inject the following to the XML document representing a sheet:

<headerFooter>
  <oddHeader>
     &amp;LLEFT&amp;CCENTER&amp;RRIGHT
  </oddHeader>
  <oddFooter>
     Page &amp;P of &amp;N
  </oddFooter>
</headerFooter>

Here &L, &C, and &R separate the three parts of the header, which can be specified independently. (Not sure why Microsoft didn't go so far as to introduce <LEFT></LEFT>, <CENTER></CENTER>, and <RIGHT></RIGHT> tags and opted for such a weird delimited format.)

So if the customer is really peculiar about having the header/footer in exactly the specific way, I could patch the produced *.xlsx file with a few -filefilter- commands. But it would be nice to have this functionality with the -putexcel- or -xl- commands in Stata/Mata. Perhaps in a future version?

Best, Sergiy

[Leonardo Guizzetti]

The header/footer information is worksheet-specific, and seems to be independent of the page size, etc. I find it is sufficient to inject the following to the XML document representing a sheet:

<headerFooter>
<oddHeader>
&amp;LLEFT&amp;CCENTER&amp;RRIGHT
</oddHeader>
<oddFooter>
Page &amp;P of &amp;N
</oddFooter>
</headerFooter>

Here &L, &C, and &R separate the three parts of the header, which can be specified independently. (Not sure why Microsoft didn't go so far as to introduce <LEFT></LEFT>, <CENTER></CENTER>, and <RIGHT></RIGHT> tags and opted for such a weird delimited format.)

So if the customer is really peculiar about having the header/footer in exactly the specific way, I could patch the produced *.xlsx file with a few -filefilter- commands. But it would be nice to have this functionality with the -putexcel- or -xl- commands in Stata/Mata. Perhaps in a future version?

Best, Sergiy

This is good to know, Sergiy. Thanks for providing the additional information and confirming that the workbook could be patched after creation. At least that makes for a more flexible solution. I agree, these would be nice features to develop.