-1

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.

Nuno
  • 81
  • 2
  • 7
  • Thanks Rob for your comment, but how can I address a header cell then? For instance, I can address a normal cell with a statment such as: Worksheet.Range['a1','a1'].Select But how do I address the header content? The only way I know of is via PageSetup. For example: WorkSheet.PageSetup.LeftHeader:='some text'; WorkSheet.PageSetup.RightHeader:='some text'; But in this case I'm limited to text, and I'd like to insert an image... – Nuno Mar 06 '17 at 14:58
  • @Rob I think he is referring to the page header, visible only in printouts. – Tom Brunberg Mar 06 '17 at 15:09
  • Yes, that's right, I'm referring to the page header. Sorry about my English, I'm not a native speaker... Thanks Tom. – Nuno Mar 06 '17 at 15:27
  • I can't try myself right now, but [some internet sources](http://www.c-sharpcorner.com/blogs/adding-headers-or-footers-to-a-worksheet-in-c-sharp) refer to `Sheet.PageSetup.LeftHeaderImage` (or `LeftHeaderPicture`) and then you also need to include '&G' in the `LeftHeader`. – Tom Brunberg Mar 06 '17 at 15:34
  • Thanks Tom... I tried it and it works well... requires that the image file is available on your disk though... but I guess you can always store the image file as a resource inside your executable and extract it when you need it... – Nuno Mar 06 '17 at 16:29
  • @TomBrunberg: Regarding that now-deleted answer, I wondered how it managed to appear in under 5 minutes of the q being posted. Your comment probably says it all. Shame indeed. – MartynA Mar 06 '17 at 20:50
  • @MartynA Yes, later I picked another one of his answers for which he had got upvotes and the same copy - paste of code, although piffed up with some of his own jargong. I flagged that to moderators attention, and now he has lost his uv's reps and the post is gone. I did not check his answers any further. – Tom Brunberg Mar 07 '17 at 00:08
  • 3
    Nuno, for the benefit of future readers, you could post the answer to this question yourself. I think both the question and the answer are interesting. You could also include some of the comment to Rob in your question as clarification to what you already have researched and why this is different. Maybe some of the dv's you have got could be reversed. – Tom Brunberg Mar 07 '17 at 00:19
  • @Tom Brunberg, answer posted, but you pointed out the right direction... it should be your credit. Thanks again. – Nuno Mar 07 '17 at 15:39

1 Answers1

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;
Tom Brunberg
  • 20,312
  • 8
  • 37
  • 54
Nuno
  • 81
  • 2
  • 7