Changing the page layout using Office Web Components

Question

I am using Office Web components to fill an Excel template with values. The template is in Excel xml format, containing all relevant fields and layout options including the page layout, landscape in this case. I'm filling this template with some real fields using the code below.

Set objSpreadsheet = Server.CreateObject("OWC11.Spreadsheet")
objSpreadsheet.XMLURL = Server.MapPath("xml") & "\MR1_Template.xls"

'Fill cells with values here
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "inline; filename=" & strFileNaam
Response.write objSpreadsheet.xmlData

After the new Excel file has been saved, the page layout options are gone. I've looked at the API documentation for the OWC but cannot find the option to specify the landscape page-layout

Answer 1

I'm not sure if you are passing in the right data. XMLURL seems like an odd method name to be passing in a XSL template into?

If all you are doing it doing a xsl transformation then why not just use DOMXmlDocument similar to this article:

http://www.codeproject.com/KB/XML/xml%5Fspreadsheet%5Fto%5Fcsv.aspx

Cut and paste for ease:

Dim xslt As New XslTransform
'Load the stylesheet.

xslt.Load(Server.MapPath(".") & "excel2csv.xsl")

Dim doc As New XmlDocument
'xmldata is string, use doc.Load(fileName) for file.

doc.LoadXml(xmlData)

'Create an XmlTextWriter which outputs to a file.

Dim fileName As String
fileName = Server.MapPath(".") & "book.csv"

Dim writer As XmlWriter = New XmlTextWriter(fileName, Nothing)
'Transform the data and send the output to the console.


xslt.Transform(doc, Nothing, writer, Nothing)
writer.Close()

Answer 2

After some detailed comparison of the template excel sheet (as xml) and the resulting xmlData I've decided to hack the page layout in the resulting Xml. These are the options I've added:

<x:WorksheetOptions>
  <x:PageSetup><x:Layout x:Orientation="Landscape"/></x:PageSetup>
  <x:FitToPage/>
  <x:Print>
    <x:FitWidth>2</x:FitWidth>
    <x:ValidPrinterInfo/>
    <x:PaperSizeIndex>9</x:PaperSizeIndex>
    <x:Scale>87</x:Scale>
  </x:Print>
</x:WorksheetOptions>