pl/sql利用xml控制excel格式,输出报表
1. 定义标头格式
l_report_title1 VARCHAR2(32676) := '<?xml version="1.0" encoding="ENCODE_CHARSET" ?>
<?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">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="m202287020">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
</Style>
<Style ss:ID="s33">
<Alignment ss:Vertical="Center"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Size="12"
ss:Color="#000000"/>
</Style>
<Style ss:ID="s34">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Size="12"
ss:Color="#000000"/>
</Style>
<Style ss:ID="s35">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s36">
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"
ss:Bold="1"/>
</Style>
<Style ss:ID="s37">
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Size="12"
ss:Color="#000000"/>
</Style>
<Style ss:ID="s38">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"
ss:Bold="1"/>
</Style>
<Style ss:ID="s39">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s40">
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s41">
<Alignment ss:Horizontal="Right" ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"
ss:Bold="1"/>
</Style>
<Style ss:ID="s42">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s43">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#333333"/>
</Style>
<Style ss:ID="s44">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="仿宋" x:CharSet="134" x:Family="Modern"/>
</Style>
<Style ss:ID="s45">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s46">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s47">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s48">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s49">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s50">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
<NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ "/>
</Style>
<Style ss:ID="s59">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s60">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s61">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<Borders/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"
ss:Bold="1"/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Vertical="Center"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Size="12"/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Vertical="Center"/>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern"/>
</Style>
<Style ss:ID="s104">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="2"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#333333"/>
</Style>
<Style ss:ID="s106">
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
<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="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/>
</Borders>
<Font ss:FontName="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#333333"/>
</Style>
<Style ss:ID="s114">
<Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
<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="仿宋" x:CharSet="134" x:Family="Modern" ss:Color="#000000"/>
</Style>';
定义excel 内容,引用格式
l_sheet_header VARCHAR2(32767) := '<Worksheet ss:Name="excel输出表格">
<Table ss:ExpandedColumnCount="50" ss:ExpandedRowCount="300" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s35" ss:DefaultColumnWidth="80"
ss:DefaultRowHeight="12">
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="45"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="29.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="115.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="72.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="28.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="57.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="39"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="28.5"/>
<Column ss:Index="10" ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="85.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="27.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75" ss:Span="1"/>
<Column ss:Index="23" ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75"/>
<Column ss:Index="27" ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5" ss:Span="1"/>
<Column ss:Index="30" ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.75"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5" ss:Span="1"/>
<Column ss:Index="43" ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.5"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:StyleID="s35" ss:AutoFitWidth="0" ss:Width="80" ss:Span="1"/>
<Row ss:AutoFitHeight="0" ss:Height="16.5">
<Cell ss:StyleID="s33"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
<Cell ss:StyleID="s34"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="24">
<Cell ss:MergeAcross="43" ss:StyleID="s119"><Data ss:Type="String">Excel汇总表</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="15.75" ss:StyleID="s37">
<Cell ss:StyleID="s36"><Data ss:Type="String">参数1:'||g_parameter||'</Data></Cell>
<Cell ss:StyleID="s36"/>
<Cell ss:StyleID="s36"/>
<Cell ss:Index="12" ss:StyleID="s36"><Data ss:Type="String">参数2:'||g_parameter2||'</Data></Cell>
<Cell ss:Index="15" ss:StyleID="s38"/>
<Cell ss:StyleID="s39"/>
<Cell ss:StyleID="s40"/>
<Cell ss:Index="19" ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"><Data ss:Type="String">YEAR 年度</Data></Cell>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:StyleID="s38"/>
<Cell ss:Index="46" ss:StyleID="s41"><Data ss:Type="String">金额单位:元</Data></Cell>
</Row>
l_sheet_footer VARCHAR2(32767) := ' </Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Layout x:Orientation="Landscape"/>
<Header x:Margin="0.31496062992125984"/>
<Footer x:Margin="0.31496062992125984"/>
<PageMargins x:Bottom="0.74803149606299213" x:Left="0.70866141732283472"
x:Right="0.70866141732283472" x:Top="0.74803149606299213"/>
</PageSetup>
<FitToPage/>
<Print>
<FitHeight>0</FitHeight>
<ValidPrinterInfo/>
<PaperSizeIndex>8</PaperSizeIndex>
<Scale>74</Scale>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<TopRowVisible>9</TopRowVisible>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>2</ActiveCol>
<RangeSelection>R4C3:R7C3</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R34C5:R65485C5,R19C5,R17C5,R3C5</Range>
<Type>List</Type>
<CellRangeList/>
<Value>"自主研发,合作研发,委托研发,集中研发"</Value>
<InputMessage>自主研发 合作研发 委托研发 集中研发</InputMessage>
</DataValidation>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R34C6:R65485C6,R19C6,R3C6,R14C6:R17C6</Range>
<Type>List</Type>
<CellRangeList/>
<Value>"费用化支出,资本化支出"</Value>
<InputMessage>费用化支出 资本化支出</InputMessage>
</DataValidation>
<DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R14C9:R15C9</Range>
<Type>List</Type>
<CellRangeList/>
<Value>"非委托项目,委托境内,委托境外"</Value>
<InputMessage>非委托项目 委托境内 委托境外</InputMessage>
</DataValidation>
</Worksheet>'
给定义好的Excel内容赋值
l_sheet_header := REPLACE(l_sheet_header ,
'g_parameter',
‘hello world’);
####查看输出结果