Hi Team,
I wanted to convert an excel file to a pdf. The excel file, when converted to pdf, will show each page on different pdf pages and also the fixed header in the excel file gets printed on all the pdf pages. Could you please help me how can I change the excel print settings in order to resolve this.
Thanks for your query.
Please see the document on how to render one PDF page per whole Excel worksheet for your reference:
To add header on all rendered pages, you may accomplish the task using the following ways. You have to add the code as per the topics before rendering/saving to PDF file format:
If you want to add title rows/columns (in the worksheet), you may try the printing tile rows/column option(s): Rows to repeat at top/Columns to repeat at left:
Page Setup and Printing Options|Documentation
If you need to add separate headers/footers for your needs, see the document for your reference:
Setting Headers and Footers|Documentation
Hope, this helps a bit.
Hi Amjad,
Thank you for your help. I have a thing to ask… when I set the worksheet.PageSetup.FitToPagesTall property to 1 it tries to fit the complete sheet into the same page on the PDF.
I want to know is there any method in Aspose where if the sheet size is large it moves the cells after a particular number of rows to the next pdf page?
Should I try to do something with Printarea property if it overflows move the data to next page in PDF? Could you please help me with this.
Best,
Ishi
Well, when you specify FitToPagesTall option to 1 (together with FitToPagesWide option to 1), it will try to render (by scaling or shrinking) all the rows and columns of the sheet in one page. If you specify FitToPagesTall option to 1 (together with FitToPagesWide option to null/0), it will not care about columns and try to render all the rows (vertically) only, so if there are lots of columns in the sheet, certain columns might be rendered in other pages.
For your needs, I think you may try inserting page breaks instead at your specified/desired location in the sheet, see the document for your reference:
Hope, this helps a bit.
This doesn’t seem to help. I want a page break after a particular interval. If a sheet has more data it should itself flow to the next page in the pdf.
Also, is there a way to hide a particular sheet in excel so that it doesn’t show up in the pdf.
Why it does not work? You can insert page breaks at your desired location (cell) as per the document example (I suggested to check). For your information, MS Excel itself puts automatic page breaks in the worksheet at regular intervals based on your worksheet data accordingly, so if the data is large, MS Excel puts different sets of data at different pages (by inserting automatic page breaks). As you need to insert your custom page breaks, so you may add horizontal/vertical page breaks at your your desired location.
Well, you may try to use Worksheet.IsVisible Boolean attribute to hide/show your desired worksheets in the workbook before rendering to PDF file format.
Thanks for using Aspose APIs.
Setting page break may not work, if there are too many data in a page break content, it will also split to several pages.
You can try copy data to a new sheet, setting one page per sheet and then save to Pdf. Please see the following sample code, its input Excel file and its output Pdf file for a reference.
Download Link:
Input Excel File and Output Pdf.zip (62.7 KB)
C#
//Load the sample Excel file
Workbook wb = new Workbook("testOnePages.xlsx");
//Access first worksheet
Worksheet sourceSheet = wb.Worksheets[0];
//Find/Calculated Split Ranges
Cells cells = sourceSheet.Cells;
Range totalRange = cells.MaxDisplayRange;
//e.g. split at Cell P70: cell[69, 15]
int[] rowSplits = { totalRange.FirstRow, 69 + 1, totalRange.FirstRow + totalRange.RowCount };
int[] columnSplits = { totalRange.FirstColumn, 15 + 1, totalRange.FirstColumn + totalRange.ColumnCount };
List<Range> splitRanges = new List<Range>();
for (int i = 0; i < rowSplits.Length - 1; i++)
{
for (int j = 0; j < columnSplits.Length - 1; j++)
{
Range range = cells.CreateRange(rowSplits[i], columnSplits[j], rowSplits[i + 1] - rowSplits[i], columnSplits[j + 1] - columnSplits[j]);
splitRanges.Add(range);
}
}
//Copy ranges to new worksheets
CopyOptions pageSetupCopyOpt = new CopyOptions();
foreach (var splitedRange in splitRanges)
{
int addedSheetIndex = wb.Worksheets.Add();
Worksheet addedSheet = wb.Worksheets[addedSheetIndex];
addedSheet.PageSetup.Copy(sourceSheet.PageSetup, pageSetupCopyOpt);
Range destRange = addedSheet.Cells.CreateRange(0, 0, splitedRange.RowCount, splitedRange.ColumnCount);
destRange.Copy(splitedRange);
}
sourceSheet.IsVisible = false;
//Save to Pdf - one page per sheet true
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.OnePagePerSheet = true;
wb.Save("test.pdf", pdfSaveOptions);
@shakeel.faiz thank you for the help. But this seems to be a little confusing i have only some of the excel worksheets where there is large data. When I do a fit to page it fits the complete sheet into one page in the pdf and I want to retain that setting but for some sheets where the data is large, the font size becomes completely invisible. So I wanted something like when the page fills in the data flows to the next page or i can iterate through the sheet and insert a page break after a regular interval. I am not having a clear idea that how should i proceed to break a particular sheet.
Thanks for using Aspose.Cells.
Please create your simple sample Excel file manually using Microsoft Excel and then also create the expected Excel file or its Pdf manually and provide it to us.
i.e.
We need the following things to look into this issue deeper.
1 - Sample Excel File
2 - Expected Output Excel File or Pdf
Please use the following code. It fits all columns in a single page but rows can take multiple pages. The code generates two pages pdf. But since, there are too many columns, so font scale is small.
If you do not want to fit all columns in a single page, then just open and save your Excel file in Pdf format.
Output Pdf
output.pdf (149.6 KB)
C#
Workbook wb = new Workbook("EXCEL.xlsx");
Worksheet ws = wb.Worksheets[0];
//Comment these two lines if you want to print like MS-Excel Print Preview
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.FitToPagesTall = 0;
wb.Save("output.pdf");@shakeel.faiz : thank you for all the help, i figured it out. Thank you. I will ask if i got stuck with some other doubt.
@shakeel.faiz : i have a fixed header. Where the fixed header is repeating on every page. is there a way it doesnt appear on all the pages
It is good to know that you were able to sort out a part of problem. Please provide us the actual output Pdf and expected output Pdf for comparison. We will look into them and provide you sample code to achieve the expected output Pdf.
@shakeel.faiz : actually it is the table header that is getting repeated. When we split the page after the table is finished there are some notes which get diplayed on the next page in the pdf but that page has the table header on it even when the table is not there
@shakeel.faiz : surely i will wait to hear back from you on this.
Also, we have data grouped using aspose in the rows. Can we ungroup the data before converting it to pdf so as we dont see all the values.
I am afraid, I am not able to replicate the table header problem. As I requested earlier, I need your simplified input Excel file, your actual Pdf and your expected Pdf. You can create your expected Pdf manually using Microsoft Excel and Save As… Pdf option.
Without comparing the Excel file, Actual Pdf and Expected Pdf, it is not possible to resolve this issue and provide you sample code.
Please create a separate thread for this issue. However, you can group and ungroup data with these methods.
Worksheet.Cells.GroupRows()
Worksheet.Cells.GroupColumns()
Worksheet.Cells.UngroupRows()
Worksheet.Cells.UngroupColumns()@shakeel.faiz : hi shakeel i tired to replicate it … When i do it with the normal sheet the table header is not coming on the next page if the page is divided but with my excel this is happening.
When there are so many columns in table and page size has limited width, then it is inevitable that some of the columns will move to next page.
However, when you are rendering it to Pdf, you can control the page order. You can render it in Horizontal-wise or Vertical-wise. By default, it renders vertical-wise.
The following sample code will render the worksheet Horizontal-wise. It means, it will first render entire table headings in multiple pages if needed and then it will start rendering first set of rows, then second set of rows etc.
C#
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Render Horizontal Vise
ws.PageSetup.Order = PrintOrderType.OverThenDown;