Hi Team,
I am trying to add horizontal page breaks to the excel worksheet based on the print area end row value. When I compared the print area with Excel interop library, it is different. Aspose.Cells library is giving 1 row extra in the print area.
Is there a way to get the automatic page break indexes in Excel worksheet using Aspose.Cells.
Can you please assist me on this.
Regards,
Sasmita Sahoo
How do you get the printable page breaks via Aspose.Cells APIs in code, could you share the sample code (runnable) with template file? We will check it and help you through.
Hi Amjad,
Thanks for your reply.
Below is the sample code, which I am using to get the print area:
private void AddHPageBreaks(Workbook workbook)
{
foreach (Worksheet worksheet in workbook.Worksheets)
{
ImageOrPrintOptions printoption = new ImageOrPrintOptions();
printoption.PrintingPage = PrintingPageType.Default;
CellArea[] area = worksheet.GetPrintingPageBreaks(printoption);
}
}
Unfortunately, I cannot share the sample file right now.
Regards,
Sasmita Sahoo
Your code is ok, even you may add the following lines to get each page area:
e.g
Sample code:
........
CellArea[] area = worksheet.GetPrintingPageBreaks(printoption);
MessageBox.Show(area.Length.ToString());
for (int i = 0; i < area.Length; i++)
{
//Get the page rows of each area.
int strow = area[i].StartRow;
int stcol = area[i].StartColumn;
int numrows = area[i].EndRow;
int numcols = area[i].EndColumn;
MessageBox.Show("Page " + (i + 1).ToString() + " starts at: " + CellsHelper.CellIndexToName(strow, stcol));
MessageBox.Show("Page " + (i + 1).ToString() + " ends at: " + CellsHelper.CellIndexToName(numrows, numcols));
.........
}
......
I am afraid, without a template file, we cannot evaluate your issue. Please replace your date with some dummy data and share it with us.
PS. please zip the template Excel file prior attaching.
Hi Amjad,
Attached is the sample code with the the template file.
After you execute the code, please open the output file in Excel app with Page Break Preview/Print Preview. You can see the number of pages are not as expected.
The Excel Auto Page break indexes are different from the values provided by Aspose library.
Kindly check and let me know.
Regards,
Sasmita SahooAsposeSampleProjects.zip (2.6 MB)
I could not spot the issue even with your provided output file. The output file is same as input file regarding page breaks and everything. See the screenshot when opening your input file into MS Excel 2007 and 2016). Aspose.Cells reads it the same for different pages’ range for both input file and its generated output file (after re-adding horizontal page breaks). Could you elaborate what’s wrong with it? Also provide what the values you get for different pages’ range via some screenshots for both input and output files. We will check it further.
sc_shot1.png (99.0 KB)
PS. you are using some older version. I tested using latest version as we cannot evaluate issue using older versions.
Hi Amjad,
I re-ran the code using the latest version of Aspose.Cells library (i.e. 20.11). And the output is same. Page breaks are still showing different in MS Excel.
FYI : I have Microsoft Office 365 in my system.
FYR : Attached few screen shots.
PageBreakIssue_Screenshot2.png (76.9 KB)
PageBreakIssue_Screenshot1.png (79.7 KB)
Regards,
Sasmita Sahoo
Did you check my attached screenshot as it shows different page breaks for your template (input) file? I am using MS Excel 2007 and 2016 to open the Excel files on Windows8. What is your OS and environment details. We will also test on some different environments and different Office versions and give more details accordingly.
@sasmitasahoo,
We have tried this scenario in macOS also and observed that when your source template file is opened in MS Excel for macOS, it shows page breaks at different places as compared to Windows as shown in the following images:
Page1-macOS.jpg (1.0 MB)
Page2-macOS.jpg (990.8 KB)
However if I run the same code in Visual Studio for macOS, it generates same results as generated in Windows environment i.e. Aspose.Cells result remains un-changed. Hence it seems to be difference in behavior of MS Excel and not an issue with Aspose.Cells.
Hi @Amjad_Sahi, @ahsaniqbalsidiqui,
Thanks for writing back.
May be MS Excel behavior is different in different OS.
I am using Windows 10 OS and Office 365.
Attached few screen shots of Page breaks in the input file when opened in MS Excel.
InputFile_MSExcel_Page2.PNG (66.0 KB)
InputFile_MSExcel_Page1.PNG (66.6 KB)
Screenshots of the Output file :
PageBreakIssue_Screenshot2.png (76.9 KB)
PageBreakIssue_Screenshot1.png (79.7 KB)
Regards,
Sasmita Sahoo
@sasmitasahoo,
We are afraid that still we are not able to reproduce this issue but we need to analyze it more. This issue is logged in our database for further investigation and a fix (if possible).
This issue is logged as:
CELLSNET-47736 - Difference in page breaks as compared to MS Excel
@ahsaniqbalsidiqui,
Did you try in the same system configuration as mine, I mean with Windows 10 OS and MS Office 365 ??
To be specific, the MS Office version is Office 365 Pro Plus.
Regards,
Sasmita Sahoo
Please check whether the DPI setting of your Win10 is 100% or not.(see attachment: “DPI_setting.png”). If not, please set it to 100% and restart his win10 machine.
Also, check whether the “English(United States)” is the default Editing Language of his Microsoft Excel.(see attachment: “ExcelOptions_EditingLanguages.png”). If not, please set “English(United States)” as the default Editing Language and restart his Office.
After applying the above settings, you will see display results in Ms Excel same as ours and Aspose.Cells.
ExcelOptions_EditingLanguages.png (30.7 KB)
DPI_setting.jpg (106.2 KB)
Thanks @Amjad_Sahi
In my system, the default language for Microsoft Excel is English (United States) and the DPI settings is 125%.
DisplaySettings.PNG.jpg (184.5 KB)
ExcelOptions_Language.PNG (27.6 KB)
I modified the DPI settings to 100%. And now the page breaks are same in MS Excel and Aspose.Cells.
DPI_100_InputFile_MSExcel_Page2.PNG (67.4 KB)
DPI_100_InputFile_MSExcel_Page1.PNG (68.4 KB)
I think I have to work with 100% DPI settings to get the correct results.
Thank you so much for your help.
Regards,
Sasmita Sahoo