My solution was to save a sample as xml using Excel, then to break it up into chunks of static partials, a bit like this.
- workbook start
- worksheet start
- worksheet end
- worksheet start
- worksheet end
- workbook end
I chose to combine workbook start with worksheet start, worksheet end with worksheet start and worksheet end with workbook end
I created quite a few .rxml files that internally looked like this...
xml << %Q¬
<?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>author</Author> <Company>Company</Company>
<Version>11.8107</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7545</WindowHeight>
<WindowWidth>16260</WindowWidth>
<WindowTopX>165</WindowTopX>
<WindowTopY>105</WindowTopY>
<ActiveSheet>2</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
¬
NOTE: There are more Excel Style sections than shown above.
I called this partial _wb_start.rxml.
If all of your worksheets share common header rows you can create another partial with this column data in it, like so...
xml << %Q¬
<Column ss:AutoFitWidth="0" ss:Width="100.5"/>
<Column ss:AutoFitWidth="0" ss:Width="65.5"/>
<Column ss:AutoFitWidth="0" ss:Width="134.25"/>
<Column ss:AutoFitWidth="0" ss:Width="134.25"/>
<Column ss:AutoFitWidth="0" ss:Width="105"/>
<Column ss:AutoFitWidth="0" ss:Width="105"/>
<Column ss:AutoFitWidth="0" ss:Width="28.5"/>
<Column ss:AutoFitWidth="0" ss:Width="251.5"/>
<Column ss:AutoFitWidth="0" ss:Width="63.75"/>
<Column ss:AutoFitWidth="0" ss:Width="15.5"/>
<Column ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:AutoFitWidth="0" ss:Width="465.5"/>
<Row ss:AutoFitHeight="0" ss:Height="18" ss:StyleID="s24">
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
<Cell ss:StyleID="s23"/>
</Row>
<Row>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 01</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 02</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 03</Data></Cell>
<Cell ss:StyleID="s43"><Data ss:Type="String">Col head 04</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 05</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 06</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 07</Data></Cell>
<Cell ss:StyleID="s42"><Data ss:Type="String">Col head 08</Data></Cell>
<Cell ss:StyleID="s43"><Data ss:Type="String">Col head 09</Data></Cell>
<Cell ss:StyleID="s45"/>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 10</Data></Cell>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 11</Data></Cell>
<Cell ss:StyleID="s46"><Data ss:Type="String">Col head 12</Data></Cell>
</Row>
¬
You would then include this partial for each worksheet, details later.
Then you need to create another partial which ends one worksheet and starts the next, or you could have a generic end-worksheet partial. Keep doing this until you have a bunch to partials which describe the start and end of the workbook and sheets.
Here is an example of a worksheet end with a worksheet start
xml << %Q¬
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.51181102362204722"/>
<Footer x:Margin="0.51181102362204722"/>
<PageMargins x:Bottom="0.59055118110236227" x:Left="0.74803149606299213"
x:Right="0.55118110236220474" x:Top="0.59055118110236227"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>0</VerticalResolution>
</Print>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>36</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<Sorting xmlns="urn:schemas-microsoft-com:office:excel">
<Sort>Part Number</Sort>
</Sorting>
</Worksheet>
<Worksheet ss:Name="RMA Form">
¬
I then created a rxml file which is rendered from the action in the controller.
xml << render(:partial => 'wb_start_ws1_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end #table
xml << render(:partial => 'ws1_end_ws2_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end # table
xml << render(:partial => 'ws2_end_ws3_start')
xml.Table do
xml << render(:partial => 'ws_headings')
#dynamic data here
end # table
xml << render(:partial => 'ws3_end_wb_end')
Note the bit that goes: xml << render(:partial => 'wb_start')
This is how you render the static xml into the dynamic Builder output at certain points.
The #dynamic data bit looks like this
aattrs = [:fld1,:fld2,:fld3,:fld4]
atypes = ['String','String','Number','String']
len = aattrs.length - 1
@collection.each do obj
xml.Row do
(0..len).each do i
xml.Cell 'ss:StyleID'=>"s76" do
dat = obj.send(aattrs[i])
if dat
xml.Data dat, 'ss:Type' => atypes[i]
else
xml.Data "#N/A", 'ss:Type' => 'String'
end
end
end
# add extra cells to pad out the styles
xml.Cell 'ss:StyleID'=>"s45"
3.times do
xml.Cell 'ss:StyleID'=>"s46" #nice bordered light blue boxes
end
end
So there you have it. You can produce Excel XML multiple worksheet workbook exports from Rails with exactly the styles and number formats as a regular Excel workbook.
Far better than CSV.
No comments:
Post a Comment