I have a c# program that produces formatted Excel reports. Due to the upgrade of Office, I converted the program to use Microsoft Excel 16 Object Library. All was fine except for the pagesetup class. Pagesetup class is no longer supported in the new DLL. Since the code for the report is extensive, I decided to modify the Excel file at the end with OpenXML.
I studied the original OpenXML by changing the suffix of the file to zip. Below is the original OpenXML that corresponds to the pagesetup class.
<printOptions horizontalCentered="1"/>
<pageMargins left="0.5" right="0.5" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
<pageSetup orientation="landscape" r:id="rId1"/>
<headerFooter><oddHeader>&L&"Arial Narrow,Bold"&8 9825 Fairmount Drive SE
&R&"Arial Narrow,Bold"&8Ref. No.: 10-219</oddHeader>
<oddFooter>&C&"Arial Narrow,Regular"&8Page 1 of 2</oddFooter></headerFooter>
<headerFooter>
<oddHeader>&R&8&B&"Arial Narrow"Ref. No.: 10-219
&L&8&B&"Arial Narrow"9825 Fairmount Drive SE</oddHeader>
<oddFooter>&C&8&"Arial Narrow"Page 2 of 2</oddFooter></headerFooter>
However, after I formatted the Excel file with OpenXML function, Excel tells me that there is OpenXML code error in the file. Below is the extraction from the revised file:
<x:pageMargins left="0.5" right="0.5" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
<x:printOptions horizontalCentered="1" />
<x:pageSetup orientation="landscape" />
<x:headerFooter differentOddEven="0">
<x:oddHeader>&L&B&"Arial Narrow"&9825 Fairmount Drive SE&R&B&"Arial Narrow"&8Ref. No.: 10-219</x:oddHeader>
<x:oddFooter>&C&"Arial Narrow"&8Page &P of &N</x:oddFooter></x:headerFooter>
It seems to have put an x: and /x: at the beginning of each tag. Below is my OpenXML function.
public void formatLandscapeReport(String fileName, String jobNumber, String Address) {
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbookpart = document.WorkbookPart;
IEnumerable<String> worksheetIds = workbookpart.Workbook.Descendants<Sheet>().Select(w => w.Id.Value);
WorksheetPart worksheetpart = null;
foreach (String worksheetId in worksheetIds)
{
worksheetpart = ((WorksheetPart)workbookpart.GetPartById(worksheetId));
PrintOptions po = new PrintOptions();
po.HorizontalCentered = true;
worksheetpart.Worksheet.AppendChild(po);
PageMargins pm = worksheetpart.Worksheet.Descendants<PageMargins>().FirstOrDefault();
if (pm == null)
{
pm = new PageMargins();
worksheetpart.Worksheet.AppendChild(pm);
}
pm.Left = .5D;
pm.Right = .5D;
pm.Top = .75D;
pm.Bottom = .75D;
PageSetup pagesetup = worksheetpart.Worksheet.Descendants<PageSetup>().FirstOrDefault();
if (pagesetup == null)
{
pagesetup = new PageSetup();
worksheetpart.Worksheet.AppendChild(pagesetup);
}
pagesetup.Orientation = OrientationValues.Landscape;
// Header and Footer
HeaderFooter hf = new HeaderFooter();
hf.DifferentOddEven = false;
OddHeader ohdr = new OddHeader();
OddFooter oftr = new OddFooter();
ohdr.Text= "&L&B&\"Arial Narrow\"&" + Address +"&R&B&\"Arial Narrow\"&8Ref. No.: " + jobNumber;
oftr.Text = "&C&\"Arial Narrow\"&8Page &P of &N";
hf.Append(ohdr);
hf.Append(oftr);
worksheetpart.Worksheet.Append(hf);
worksheetpart.Worksheet.Save();
} // foreach
workbookpart.Workbook.Save();
} // using
} //formatLandscapeReport
If someone could please enlighten me on what I am doing wrong it would be much appreciated.
Thanks,
Carmen
The relevant part of the ECMA schema is this:
<xsd:sequence>
...
<xsd:element name="printOptions" type="CT_PrintOptions" minOccurs="0" maxOccurs="1"/>
<xsd:element name="pageMargins" type="CT_PageMargins" minOccurs="0" maxOccurs="1"/>
<xsd:element name="pageSetup" type="CT_PageSetup" minOccurs="0" maxOccurs="1"/>
<xsd:element name="headerFooter" type="CT_HeaderFooter" minOccurs="0" maxOccurs="1"/>
....
<xsd:sequence>
Note that this construct is defined as a sequence and therefore order is important.
In your original file the order is correct with the printOptions appearing before the pageMargins but in your incorrect file they are the wrong way round.
Swapping the order should fix your issue so the question then becomes "why is the order wrong?".
The issue there is most likely that your Worksheet already contains a PageMargins item so the PrintOptions are being added after them.
To solve this, you can always do the PageMargins code first and then use the InsertBefore method to insert the PrintOptions before the PageMargins:
PageMargins pm = worksheetpart.Worksheet.Descendants<PageMargins>().FirstOrDefault();
if (pm == null)
{
pm = new PageMargins();
worksheetpart.Worksheet.AppendChild(pm);
}
pm.Left = .5D;
pm.Right = .5D;
pm.Top = .75D;
pm.Bottom = .75D;
worksheetpart = ((WorksheetPart)workbookpart.GetPartById(worksheetId));
PrintOptions po = new PrintOptions();
po.HorizontalCentered = true;
//the PrintOptions must be before the PageMargins
worksheetpart.Worksheet.InsertAfter(po, pm);
The x: part is just a namespace. I don't think that would be the cause of your issue.