I am creating an Excel file manually from within an iOS app I wrote. It just spits out an XML sheet with an Excel file extension attached to it that opens in Excel without issue.
The problem is that the file has 5 columns. For whatever reason - Excel always places the page break after the 4th column.
I'm trying to make the process as simple as possible for my end users - so they simply press the button then print the worksheet this app creates - however as of now they must first go into the pagebreak preview and adjust the pagebreak. I'd like to eliminate that by manually getting rid of the pagebreaks or moving it to the end of the content (5th column).
The data is in no way 'too wide' or requiring page breaks, which is why I'm so lost as to why it's creating them!
My XML file looks like this once created:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Me</Author>
<LastAuthor>template</LastAuthor>
<Created>2014-11-21T16:11:02Z</Created>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9375</WindowHeight>
<WindowWidth>9705</WindowWidth>
<WindowTopX>270</WindowTopX>
<WindowTopY>615</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Arial" ss:Bold="1"/>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" ss:Bold="1"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" ss:Bold="1"/>
<Interior/>
<NumberFormat ss:Format=""$"#,##0.00"/>
<Protection/>
</Style>
<Style ss:ID="s66">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="54Mileage">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="52" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="117" ss:DefaultRowHeight="15">
<Column ss:Width="99.75"/>
<Column ss:Width="95.25" ss:Span="3"/>
<Row>
<Cell><Data ss:Type="String">Person Making Claim:</Data></Cell>
<Cell><Data ss:Type="String">XXXXXX XXXXXXXXX</Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String">Signature: </Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
</Row>
<Row ss:Index="3">
<Cell><Data ss:Type="String">Month/Year:</Data></Cell>
<Cell><Data ss:Type="String">November 2014</Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String">Account Code:</Data></Cell>
</Row>
<Row ss:Index="5">
<Cell><Data ss:Type="String">School:</Data></Cell>
<Cell><Data ss:Type="String">XXXXXX</Data></Cell>
<Cell><Data ss:Type="String"></Data></Cell>
<Cell><Data ss:Type="String">Home Address:</Data></Cell>
<Cell><Data ss:Type="String">XXXXXXXXXX</Data></Cell>
</Row>
<Row ss:Index="7">
<Cell ss:StyleID="s62"><Data ss:Type="String">Date</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">From</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">To</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">Number Of Miles</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">Reason For Trip</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s63"><Data ss:Type="String">11/03/2014</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="String">Hanover</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="String">Einstein</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="Number">2.1</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s63"><Data ss:Type="String">11/20/2014</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="String">Hale</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="String">Fox</Data></Cell>
<Cell ss:StyleID="s63"><Data ss:Type="Number">2</Data></Cell>
</Row>
<Row ss:Index="50">
<Cell ss:StyleID="s64"><Data ss:Type="String">Total Miles:</Data></Cell>
<Cell ss:StyleID="s64" ss:Formula="=SUM(R[-42]C[2]:R[-2]C[2])"><Data
ss:Type="Number">112.90000000000002</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="String">@0.56</Data></Cell>
<Cell ss:StyleID="s65" ss:Formula="=RC[-2]*0.56"><Data ss:Type="Number">63.224000000000018</Data></Cell>
</Row>
<Row ss:Index="52">
<Cell ss:StyleID="s66"><Data ss:Type="String">Approved By (signature):</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">Date:</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<ShowPageBreakZoom/>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
Is there a spot where I can manually enter in a line of XML to adjust page breaks programmatically? Or is this a step my users will ALWAYS have to take?
I found that I had to specify the print settings in order to get this to manually set the pagebreak - the pagebreak is just set using some excel specific xml.
The following is how I was able to set the pagebreak manually:
Without the first section of 'worksheet options' and the horizontal and vertical resolutions - the pagebreaks would not take. Those must be present in order to set the pagebreak manually.