I am trying to create dynamic page breaks, but I am struggling to understand how and what to do to get it right (I am no expert on Excel either).
Attached is a zip file with three files in it.
The template file is the base where I work from. This is the definition.
Note that there are a couple of lines at the top which I’ve set to print on every page as a header.
From this file, I create the actual excel ‘report data’ file and hence contains multiples of the template. I do this by means of copying the cells / rows from the template to the new file in the correct place.
Note that line(s) are then hidden where irrelevant data is.
I have a sort of manual counter process where I calculate where a page break should be. A page break should happen if the visible lines of the ‘template’ will go off the page. The count of lines per page I’ve set to 210 (and this could be wrong as I might not understand the way to calculate it correctly)
So then I insert page breaks where on the blue separator line with the code below :
workSheet.VerticalPageBreaks.Add(“A” + pb.Row); // where pb.Row is the row calculated (first one is 209)
Once the whole process is done, the excel file is converted to pdf - the third file attached, with the following piece of code :
ReportAspose.Settings.CreateCalcChain = false;
//Calculate the workbook formulas
ReportAspose.CalculateFormula();
ReportAspose.Save(_AsposeExcel, SaveFormat.Auto);
I have the following issues :
The page breaks that I have inserted don’t seem to have any effect at all. Am I supposed to use VerticalPageBreaks or HorizontalPageBreaks ? (Note that the paper is set as A0 Landscape)
When I preview the excel file, the lines on top which should be on every page, is not there, yet on the PDF they are there. Is this normal / expected behavior ?
@AlexCaudron,
We have tried this scenario using our sample code where headers are added along with the additional page breaks. You may compare the output files those are created before adding page breaks, after adding the page breaks and then clearing the page breaks. It can be observed that page breaks are added where expected and deleted as well. No issue is observed as output Excel files have proper page breaks. Note that headers are shown in the print preview only as expected and not in the Excel file.
If possible identify the issue in the output files for our reference. If issue is not identified, share your runnable console application that can be compiled and executed here without any missing reference. Also share the expected output Excel file created by MS Excel for our comparison.
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Console.WriteLine(worksheet.PageSetup);
for(int r = 0; r < 100; r++)
{
for(int c = 0; c < 30; c++)
{
workbook.Worksheets[0].Cells[r, c].Value = String.Format($@"R={r},C={c}");
}
}
ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
var data1 = worksheet.GetPrintingPageBreaks(imageOrPrintOptions);
var HorizontalPageBreaks1 = worksheet.HorizontalPageBreaks;
var VerticalPageBreaks1 = worksheet.VerticalPageBreaks;
// Setting a string at the left section of the footer and changing the font
// of a part of this string ("123")
worksheet.PageSetup.SetHeader(0, "Hello World 1!\nHello World 2!\nHello World 3! &\"Courier New\"&14 123");
workbook.Save(@"AddingPageBreaks_out1.xls");
worksheet.HorizontalPageBreaks.Add("Y30");
worksheet.VerticalPageBreaks.Add("Y30");
var data2 = worksheet.GetPrintingPageBreaks(imageOrPrintOptions);
var HorizontalPageBreaks2 = worksheet.HorizontalPageBreaks;
var VerticalPageBreaks2 = worksheet.VerticalPageBreaks;
// Save the Excel file.
workbook.Save(@"AddingPageBreaks_out2.xls");
// Clearing all page breaks
worksheet.HorizontalPageBreaks.Clear();
worksheet.VerticalPageBreaks.Clear();
// Save the Excel file.
workbook.Save(@"AddingPageBreaks_out3.xls");
You should use HorizontalPageBreaks instead of VerticalPageBreaks . e.g. sheet.HorizontalPageBreaks.Add("A209");
We could see lines on top (row 1~5) are pasted on every page in Excel print preview. see the PDF file (attached) saved by Microsoft Excel (manually) and you will notice it is same as per print preview of MS Excel. Which lines you are talking about, could you elaborate and provide some screenshots to highlight the issue? P2F_PlatHond_SavedByExcel2016.pdf (1.7 MB)