Worksheet Page Setup from C#/VB.NET |
The PageSetup of a worksheet (ExcelWorksheetPageSetup) controls how the worksheet is printed: the paper size and orientation, the margins in inches, the header and the footer, the print area, the rows repeated on each page, the grid lines and the scaling.
The header and the footer have a left, a center and a right part. Their text uses the Excel format codes, for example &P for the page number, &N for the number of pages, &D for the date, &T for the time, &F for the file name, &A for the worksheet name and &G for the picture of that part.
// paper, orientation and margins (inches) worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4; worksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape; worksheet.PageSetup.LeftMargin = 1; worksheet.PageSetup.RightMargin = 1; worksheet.PageSetup.TopMargin = 1; worksheet.PageSetup.BottomMargin = 1; // header: a logo on the left and the worksheet name on the right Image logo = Image.FromFile(Path.Combine(imagesFolder, "logo.jpg")); worksheet.PageSetup.LeftHeaderFormat = "&G"; worksheet.PageSetup.LeftHeaderPicture = logo; worksheet.PageSetup.RightHeaderFormat = "&A"; // footer: the file name, page N of M and the date worksheet.PageSetup.LeftFooterFormat = "&F"; worksheet.PageSetup.CenterFooterFormat = "Page &P of &N"; worksheet.PageSetup.RightFooterFormat = "&D"; // print the range A1:G40, repeat rows 1 to 5 on each page, fit one page wide worksheet.PageSetup.PrintArea = "A1:G40"; worksheet.PageSetup.PrintTitleRows = "$1:$5"; worksheet.PageSetup.FitToPagesMode = true; worksheet.PageSetup.FitToPagesWide = 1; worksheet.PageSetup.FitToPagesTall = 0; worksheet.PageSetup.PrintGridlines = true; // the picture of the header is saved with the workbook: dispose it after the save workbook.Save("PageSetup.xlsx"); logo.Dispose();
Page breaks are added to the horizontal and vertical page breaks collections of the worksheet: