When generating a PDF using Aspose Cells in C#, only blank pages are printed even after setting the pageSetup property to print sheet name in the footer for empty Excel sheets

Aspose.Cells Product Family
1

When we are trying to generate PDF for an excel workbook which has an empty sheet, only blank page is being printed in the PDF.
We are trying to set the pageSetup.SetFooter(1, worksheet.Name), but SheetName is not being printed in the footer even after setting the property.
Could you please help us on this.

2

@susmithaputhana

When generating a PDF from an Excel workbook using Aspose.Cells, if you encounter an issue where only blank pages are printed for empty sheets, it is important to ensure that the footer settings are correctly applied.

Here are some steps you can follow to troubleshoot and resolve the issue:

  1. Check Sheet Content: Ensure that the sheet you are trying to convert is indeed empty. If there are no visible contents, the PDF will reflect that by showing a blank page.

  2. Set Footer Correctly: When setting the footer, make sure you are using the correct index for the footer. The SetFooter method takes two parameters: the index of the footer (0 for the left, 1 for the center, and 2 for the right) and the text you want to display. For example:

    worksheet.PageSetup.SetFooter(1, worksheet.Name);

  3. Use PdfSaveOptions: Consider using the PdfSaveOptions class to control various aspects of the PDF generation. This can help ensure that the footer is included even for empty sheets. Here’s a basic example:

    var workbook = new Workbook("path/to/your/excel/file.xlsx");
var pdfOptions = new PdfSaveOptions();
pdfOptions.AllColumnsInOnePagePerSheet = true; // Adjust as needed
workbook.Save("output.pdf", pdfOptions);

  4. Check for Updates: Ensure that you are using the latest version of Aspose.Cells, as updates may include bug fixes and improvements related to PDF generation.

  5. Test with Non-Empty Sheets: As a test, try adding some content to the sheet and see if the footer appears correctly in the generated PDF. This can help determine if the issue is specifically related to empty sheets.

If you continue to experience issues, consider reaching out to Aspose support with specific details about your implementation for further assistance.

By following these steps, you should be able to resolve the issue of blank pages being printed for empty Excel sheets in your PDF output.

3

@susmithaputhana
Would you like to provide your sample file and test code? We will check it soon.

4

EmptyExcelTesting.zip (19.8 KB)

Attached the sample excel file , generated pdf file and code snippet as well

5

I tried with the steps mentioned but still not able to print sheetName in footer for empty excel sheet.
As mentioned in point 5, for Non-Empty sheets, when trying to set footer, it is being applied correctly. We are facing this issue only for empty sheets.

6

@susmithaputhana
By testing with sample files and code on the latest version v24.8, we can reproduce the issue. Found that Header and Footer are missing when converting an empty worksheet to PDF.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-56626

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

7

@susmithaputhana ,

For an empty sheet, there is nothing to output/print. By default, Aspose.Cells outputs an empty page for it. Also, you can set PdfSaveOptions.OutputBlankPageWhenNothingToPrint to false, it will throw exception warning there is nothing to print.
If you print an empty sheet in Excel, Excel will show that “We didn’t find anyting to print.”

Solution:
You can set a print area for the empty sheet to get a blank page with header/footer.
Code:

...
pageSetup.PrintArea = "A1";
...
8

Thank you for your response. However, this solution may not fit our use case, as we are looping through all the worksheets and applying the pageSetup properties. To set pageSetup.PrintArea = "A1", we first need to identify whether the sheet is empty, which could require significant customization.

I tried implementing the suggested solution, but the output was not as expected, as shown below.
image.png (1.8 KB)

9

@susmithaputhana,

Thanks for the screenshot.

Generally, Aspose.Cells follows MS Excel standards and specifications. I tested your scenario/case manually in MS Excel. I opened a new Workbook in MS Excel which has Sheet1 which is blank by default. I then opened Page Setup dialog, clicked Header/Footer tab and then Insert sheet name in Header/Footer. Now I took the “Print Preview” of the worksheet. I got “We don’t find anything to print” message. So, if you don’t specify printable area, it won’t show header/footer for the blank sheet.

In case, you still think you can accomplish the task in MS Excel, please provide an Excel file having your desired custom header/footer for blank sheet intact which could be seen in print preview in MS Excel, save the file and provide us here. We will check on how to do it via Aspose.Cells APIs.

10

@susmithaputhana
Yes,it’s hard for you to detect whether the sheeet is empty.
We will look into whether we can add header/footer to blank pages.

11

@susmithaputhana ,

Please use the following code to check whether there is no page for a worksheet:

bool IsBlankSheet(Worksheet worksheet)
{
    //High performance to evaluate page count of a sheet.
    SheetPrintingPreview sheetPrintingPreview = new SheetPrintingPreview(worksheet, new ImageOrPrintOptions());
    return sheetPrintingPreview.EvaluatedPageCount < 1;
}