Page Layout customization Excel Addin c#

Page Layout customization Excel Addin c#

Page Layout:

Ideally in excel most of the users don’t mind what is the page size or data flowing within or outside sheet. But when it comes to create a presentation or take hard copy the page layout comes into the picture to adjust your content. In this article we will learn how to setup page layout manually then we write peace of code to understand properties involve to build a page layout customization.

Followings are the steps to setup page layout in Excel:

  1. From the Page Layout tab, click Margins. Select Custom Margins… from the drop-down menu
  2. The Page Setup dialog box will appear
  3. Adjust the values for each margin, then click OK
  4. The margins of the workbook will be changed

Page setup:

Let’s put some code, Please note the solution is built using VSTO C# hence following prerequisites are required to run the code:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example:

private void btnPageMargin_Click(object sender, RibbonControlEventArgs e)
{
	//reference active sheet
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//turn Off print communication
	Globals.ThisAddIn.Application.PrintCommunication = false;
	//Clear print area
	oWorksheet.PageSetup.PrintArea = string.Empty;
	//turn On print communication
	Globals.ThisAddIn.Application.PrintCommunication = true;
	//get Page Setup object reference
	excel.PageSetup oPageSetup = oWorksheet.PageSetup;

	//set margins
	oPageSetup.LeftMargin = 1.25; //Left margin
	oPageSetup.RightMargin = 1.4; //Right margin
	oPageSetup.TopMargin = 1.8; //Top margin
	oPageSetup.BottomMargin = 1.8; //Bottom margin

	//set header margin
	oPageSetup.HeaderMargin = 2;
	oPageSetup.FooterMargin = 2;

	//Set header text
	oPageSetup.LeftHeader = "";
	oPageSetup.CenterHeader = "";
	oPageSetup.RightHeader = "";
	
	//set footer text
	oPageSetup.LeftFooter = "";
	oPageSetup.CenterFooter = "";
	oPageSetup.RightFooter = "";

	//Toggle Print Heading
	oPageSetup.PrintHeadings = false;

	//Toggle print Grid Lines
	oPageSetup.PrintGridlines = false;

	//Print comments can be determined as xlPrintNoComments, xlPrintInPlace, xlPrintSheetEnd
	oPageSetup.PrintComments = excel.XlPrintLocation.xlPrintNoComments;

	//Print quality
	oPageSetup.PrintQuality = 600;

	//Page number
	oPageSetup.FirstPageNumber = 1;

	//paper size
	oPageSetup.PaperSize = excel.XlPaperSize.xlPaperA4;

}

In above code I tried to give maximum parameters which required to setup page layout however there are still some parameters which you might want to code as part of solution are listed below:

  • CenterHorizontally : refers boolean property
  • CenterVertically : refers boolean property
  • Orientation : takes either constant as xlPortrait or xlLandscape derived from XlPageOrientation enum
  • Draft : refers boolean property
  • Order : takes either constant as xlDownThenOver or xlOverThenDown derived from XlOrder enum
  • BlackAndWhite : refers boolean property
  • Zoom : takes dynamic value and can be passed any form like int, double or percentage
  • PrintErrors : there are four constants derived through XlPrintErrors enum which can be passed as
    • xlPrintErrorsBlank
    • xlPrintErrorsDash
    • xlPrintErrorsDisplayed
    • xlPrintErrorsNA
  • OddAndEvenPagesHeaderFooter : refers boolean property
  • DifferentFirstPageHeaderFooter : refers boolean property
  • ScaleWithDocHeaderFooter : refers boolean property
  • AlignMarginsHeaderFooter : refers boolean property
  • EvenPage : has Text object which has six properties as:
    • CenterFooter
    • CenterHeader
    • LeftFooter
    • LeftHeader
    • RightFooter
    • RightHeader
  • FirstPage : has Text object which has six properties as:
    • CenterFooter
    • CenterHeader
    • LeftFooter
    • LeftHeader
    • RightFooter
    • RightHeader

Next : Toggle page orientation in Excel Sheet using C#

Leave a Reply

Your email address will not be published.