How to insert an image in an excel page header using delphi?

Question

I'm trying to figure out how can I insert an image into an excel page header using delphi. I've seen how I can do it into a normal cell but not into the page header. Anybody can help? Thanks.

Answer 1

When using excel automation object PageSetup it is possible to manage several properties of an excel sheet page header and footer.

https://msdn.microsoft.com/en-us/library/office/ff196103.aspx

The properties of interest in this particular question could be one of the following pairs:

PageSetup.LeftHeader PageSetup.LeftHeaderPicture

PageSetup.CenterHeader PageSetup.CenterHeaderPicture

PageSetup.RightHeader PageSetup.RightHeaderPicture

For instance, you can use the LeftHeader property string to display simple text or special itens such as a picture in which case you have to assign the code '&G' to this property for the image to show up. You can specify the image file using the LeftHeaderPicture.FileName property.

By the way you can also use other special formating codes such as '&D' for current date, '&P' for page number, '&N' for total number of pages, and others.

https://msdn.microsoft.com/en-us/library/bb225426(v=office.12).aspx

Managing excel sheet headers and footers is useful when you need to show page level information such as page number, total pages, etc. Or when you want to show a logo or document title in all of the document pages. Handling normal sheet cells will not bring you the same results.

Simple delphi example:

var Excel: Variant;
    Sheet: OleVariant;
    filename: String;
...

Excel:=CreateOleObject('Excel.application');
Excel.Visible:=False;
Excel.WorkBooks.Add(-4167);

Sheet:=Excel.ActiveSheet;

Sheet.PageSetup.RightHeader:='my document title';
Sheet.PageSetup.LeftFooter:='my footer message';
Sheet.PageSetup.CenterFooter:='&D';
Sheet.PageSetup.RightFooter:='&P/&N';

filename:=ExtractFilePath(Application.ExeName)+'\logo.bmp';

if FileExists(filename) then begin
  Sheet.PageSetup.LeftHeader:='&G';
  Sheet.PageSetup.LeftHeaderPicture.FileName:=filename;
end;

Sheet.Cells[1,1].Value:='example of page header and page footer handling of an excel sheet...';

Excel.Visible:=True;