0

I am trying to set the page margins on an excel spreadsheet that is being generated by open xml sdk. I am not opening an excel document that already exists, it is generated from scratch. I am using the PageMargins class but am not sure how to attach this instance to the worksheet. The SDK productivity tool gives this code:

PageMargins pageMargins1 = worksheet.GetFirstChild<PageMargins>();
pageMargins1.Left = 0.45D;
pageMargins1.Right = 0.45D;
pageMargins1.Top = 0.5D;
pageMargins1.Bottom = 0.5D;

The GetFirstChild() function returns null. I also tried to do

worksheet.Append(pageMargins1);

but no luck.

Also using the code from this example: How change excel 2007 document orientation to landscape by OpenXML sdk to set the page orientation does not work if creating the document from scratch. How do you add a PageSetup & PageMargin instance to the document?

Any one have knowledge of this SDK and knows how to use the margins or page setup class?

Community
  • 1
  • 1
Ian Herbert
  • 1,071
  • 2
  • 16
  • 35

1 Answers1

2

The order that things are appended to the worksheet is important. PageMargins needs to be appended before PageSetup, and they both need to be at the end after the SheetData. Also, all settings need to be set. I used this code:

PageMargins pageMargins1 = new PageMargins();
pageMargins1.Left = 0.45D;
pageMargins1.Right = 0.45D;
pageMargins1.Top = 0.5D;
pageMargins1.Bottom = 0.5D;
pageMargins1.Header = 0.3D;
pageMargins1.Footer = 0.3D;
worksheetPart.Worksheet.AppendChild(pageMargins1);

PageSetup pageSetup = new PageSetup();
pageSetup.Orientation = OrientationValues.Landscape;
pageSetup.FitToHeight = 2;
pageSetup.HorizontalDpi = 200;
pageSetup.VerticalDpi = 200;
worksheetPart.Worksheet.AppendChild(pageSetup);

A neat trick to be used along with the sdk productivity tool is to rename the .xlsx file to .zip, then extract the contents. Then open /xl/worksheets/sheet.xml and compare the markup to the markup of an excel file created by excel.

Ian Herbert
  • 1,071
  • 2
  • 16
  • 35
  • does the value for PageMargins and PageSetup need converting? (I get an error Cannot implicitly convert type 'double' to 'DocumentFormat.OpenXml.UInt32Value') also in Visula Studio 2010 I only have "PageMargin" and not "PageMargins". – Dev P Feb 27 '12 at 16:08