Empty header

Aspose.Cells Product Family
1

Hi guys,

I’m trying to insert a Header and a Footer into Excel document. However, it only works if the input excel already have a header and a footer. Am I missing something?

final WorksheetCollection collection = workbook.getWorksheets();
final InputStream inFile = DocumentBuilderBase.class.getClassLoader().getResourceAsStream(“Watermark.png”);
final byte[] picData = IOUtils.toByteArray(inFile);

    for (int i = 0; i < collection.getCount(); i++) {
        final Worksheet sheet = workbook.getWorksheets().get(i);
        final com.aspose.cells.PageSetup pageSetup = sheet.getPageSetup();
        pageSetup.setHeader(1, "&G");
        pageSetup.setHeaderPicture(1, picData);

        pageSetup.setFooter(1, "&Arial&" + FONT_SIZE + WATERMARK_TEXT);
    }
2

@fcri

Thanks for using Aspose APIs.

The picture will be visible when you will set the PAGE_LAYOUT_VIEW. Please see the following sample code and its output Excel file as well as screenshot for a reference.

Download Link:
Output Excel File.zip (253.4 KB)

Java

Workbook wb = new Workbook();

Worksheet ws = wb.getWorksheets().get(0);

ws.getPageSetup().setHeader(1, "&G");
ws.getPageSetup().setHeaderPicture(1, picBytes);

ws.setViewType(ViewType.PAGE_LAYOUT_VIEW);

wb.save("output.xlsx");

Screenshot:

3

Hello Shakeel,

It’s solved, thank you very much :slight_smile:

I’ve two more questions:

1- My footer is: &Arial&18Evaluation only. Created with a Trial Version.
But when the output shows: IssuesrialEvaluation only. Created with a Trial Version of Xporter.
I’ve noticed that “Issues” is the name of the sheet. It’s possible to delete the “Issuesrial”?

2- Every time I insert a header or a footer, the excel splits the sheet in two, as you can see on your screenshot. It’s possible to show the document as “normal”?

Best Regards,

4

@fcri

Thanks for using Aspose APIs.

Please share with us your sample code, sample Excel file(s) and screenshots showing the error. Please note, when you set the page layout view, only then you will see header and it will always split the sheet into number of pages. The other way is to render the Excel to Pdf and then header will appear on every page.

5

Hi,

Thank you for your help. I will send you a zip file with my sample code, the result Excel and a screenshot of the error.

Best regards,
AsposeFooter.zip (174.5 KB)

6

@fcri

Thanks for using Aspose APIs.

Please open the DEMO-24 (3).xlsx in Microsoft Excel and fix it manually and provide it back to us. We need your expected Excel file. Please also provide the picture you used in the expected Excel file.

Besides, you provided the sample code in a screenshot, provide it in text as you did earlier. We will examine your expected Excel file and provide you a sample code so that you could test it at your end.

7

Hi,

Here it goes what you’ve asked. If you need anything more, please ask me.
AsposeFooter2.zip (20.9 KB)

Best regards,

9

@fcri

Please use the following script and it will fix your issue. i.e.

&18Evaluation only. Created with a Trial Version.

Please see the following sample code, its output Excel file and screenshot for a reference.

Download Link:
Output Excel File.zip (5.5 KB)

Java

Workbook wb = new Workbook();

Worksheet ws = wb.getWorksheets().get(0);

ws.getPageSetup().setFooter(1, "&18Evaluation only. Created with a Trial Version.");

ws.setViewType(ViewType.PAGE_LAYOUT_VIEW);

wb.save(dirPath + "output.xlsx");

Screenshot

10

Hi,

That works fine. But isn’t possible to use a script with the FontName and FontSize? Like: &Arial&18

Best regards,

11

@fcri

You may create your desired header and footer manually using Microsoft Excel and save it on disk. Then you load the saved Excel file in Aspose.Cells APIs and retrieve the header and footer script using the following code. This will give you idea what script you are to use in your program/code.

String s1 = ws.getPageSetup().getHeader(0);
s1 = ws.getPageSetup().getHeader(1);
s1 = ws.getPageSetup().getHeader(2);

s1 = ws.getPageSetup().getFooter(0);
s1 = ws.getPageSetup().getFooter(1);
s1 = ws.getPageSetup().getFooter(2);

Hopefully, it will be helpful for you.

12

Hi,
Thank you for your help, I’m almost there. Just one more question, if I execute the code I’ve sent to you and save the document as .csv, footer and header are empty. See this example:
CSVExperiment.zip (6.3 KB)

13

@fcri

CSV format is a very simple and text-based format. It is a comma separated file. Except commas, it does not have any sophisticated meta data. Just create Header & Footer in Microsoft Excel and save it in CSV format and then open it again in Microsoft Excel, you will see, Header & Footer information is lost. Then open it in Notepad and you will see, there is no Header & Footer information inside the CSV file. It means, CSV file does not and cannot support Header & Footer information.