Hi Team,
I have an excel where i am trying to print the page number on which each sheet is falling by using Sheet Render. It works like a charm in excel.
But, now i have to convert the excel to pdf and i have some settings while generating the PDF. When I convert the Excel to PDF I see that because of clearing page breaks and other settings the pages in pdf differ from what page numbers are shown in excel.
Is there a way i can show the right page numbers in the pdf or know the right number of pages in which an excel sheet is going to span in its converted pdf version ?
@dewan.ishi,
Please share your sample files and code snippet with us for our testing and elaborate it with screenshots. We will reproduce the problem and provide our feedback after analysis soon.
Hi PFA the input excel and output pdf. I am not able to upload excel
OutputPdf.pdf (44.0 KB)
for (int i = 0; i < workBook.Worksheets.Count; i++)
{
Worksheet worksheet = workBook.Worksheets[i];
worksheet.Cells.StandardWidth = 30;
worksheet.PageSetup.Orientation = PageOrientationType.Landscape;
worksheet.PageSetup.PaperSize = PaperSizeType.PaperLegal;
FindOptions opts = new FindOptions();
opts.LookAtType = LookAtType.EntireContent;
opts.LookInType = LookInType.Values;
ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
imgOptions.PrintingPage = PrintingPageType.Default;
worksheet.HorizontalPageBreaks.Clear();
worksheet.VerticalPageBreaks.Clear();
SheetRender sr = new SheetRender(worksheet, imgOptions);
if (worksheet.Name.Contains("AB") || worksheet.Name.Contains("CD"))
{
workBook.Worksheets[i].PageSetup.PrintTitleRows = "$1:$8";
if (aDataTable != null)
{
for (int j = 0; j < aDataTable .Rows.Count; j++)
{
if (worksheet.Name.Equals("AB"))
{
Aspose.Cells.Cell cell1 = workBook.Worksheets[0].Cells.Find(aDataTable .Rows[j][1], null, opts);
int row = cell1.Row;
workBook.Worksheets[0].Cells[row, 4].PutValue("", true);
workBook.Worksheets[0].Cells[row, 4].PutValue(bookPageCount);
workBook.Worksheets[0].Hyperlinks.Add(row, 4, 1, 1, ("'AB - (" + a.Rows[j][1] + ")'!A1"));
break;
}
else if (worksheet.Name.Contains("CD")
{
Aspose.Cells.Cell cell1 = workBook.Worksheets[0].Cells.Find(a.Rows[j][1], null, opts);
int row = cell1.Row;
workBook.Worksheets[0].Cells[row, 5].PutValue("", true);
workBook.Worksheets[0].Cells[row, 5].PutValue(bookPageCount);
workBook.Worksheets[0].Hyperlinks.Add(row, maxChildCompanyCount + 5, 1, 1, ("CD - (" + aDataTable.Rows[j][1] + ")'!A1"));
break;
}
}
}
bookPageCount = bookPageCount + sheetPageCount;
}
else
{
workBook.Worksheets[i].PageSetup.PrintTitleRows = "$1:$10";
bookPageCount = bookPageCount + sr.PageCount; //remove *2 when you remove toc reports
}
Hi,
Could you please provide a solution to this as this is pretty urgent.
@dewan.ishi,
You may please share the Excel file by zipping it and then upload here. If you face some issue, please upload it to some online file sharing server and share the link here. This file is must required to re-produce the scenario here and provide our feedback.
Actually this is mostly happening due to page break which i have moved in my template before generating the excel file. That page break shows up again in my excel file after generation due to which the page numbers change but as the print setting are set that way it does not becom
e an issue in generating pdf. Still PFA the files.
sample.zip (116.6 KB)
@dewan.ishi,
Thank you for providing the sample input file. I have tried to use your sample code but could not compile it due to missing references like aDataTable, bookPageCount, maxChildCompanyCount and sheetPageCount etc.
You may please send us a complete console application (runnable) which we can execute here to test the issue without any error. You may hard code the data for aDataTable or load it from some file so that it is populated here for complete analysis. Also if possible, please share the expected output PDF file generated by Excel for our analysis and comparison with the Aspose.Cells output.