excel报表解决方法(ebs).doc

长久以来，oracle运行的excel报表经常采用csv格式，用户需要view output，然后另存为本地csv文件，才能用excel打开，而且不能保留excel格式，包括字段长度，字段格式掩码，以及公式等。因为项目需要excel文件包含公式，所以采用以下方案解决。（1）将格式template文件在excel中制做，保存时，选取XML Spreadsheet格式，生成xml 模板文件，这样这个文件可以包含公式等。（2）采用fnd_file.put_line方式将xml模板文件输出，数据列可以依据编程要求修改。（3）建立concurrent program采用XML输出方式。（4）这样用户在view output时候，浏览器可以自动打开这个excel文件。附上package源代码，本例子在application 11.5.8,和本机excel 2003，ie6.0测试正常一下为Sample函数包：create or replace package TEST_XML_PKG isprocedure main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2);end TEST_XML_PKG;/create or replace package body TEST_XML_PKG isprocedure main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)isbeginfnd_file.put_line(fnd_file.output,<?xml version=1.0?>);fnd_file.put_line(fnd_file.output,<?mso-application progid=Excel.Sheet?>);fnd_file.put_line(fnd_file.output,<Workbook xmlns=urn:schemas-microsoft-com:office:spreadsheet);fnd_file.put_line(fnd_file.output, xmlns:o=urn:schemas-microsoft-com:office:office);fnd_file.put_line(fnd_file.output, xmlns:x=urn:schemas-microsoft-com:office:excel);fnd_file.put_line(fnd_file.output, xmlns:ss=urn:schemas-microsoft-com:office:spreadsheet);fnd_file.put_line(fnd_file.output, xmlns:html=>/TR/REC-html40>);fnd_file.put_line(fnd_file.output, <DocumentProperties xmlns=urn:schemas-microsoft-com:office:office>);fnd_file.put_line(fnd_file.output,<Author>Authorised User</Author>);fnd_file.put_line(fnd_file.output,<LastAuthor>Authorised User</LastAuthor>);fnd_file.put_line(fnd_file.output,<Created>2005-01-26T07:43:18Z</Created>);fnd_file.put_line(fnd_file.output,<Company>test</Company>);fnd_file.put_line(fnd_file.output,<Version>11.6360</Version>);fnd_file.put_line(fnd_file.output, </DocumentProperties>);fnd_file.put_line(fnd_file.output, <ExcelWorkbook xmlns=urn:schemas-microsoft-com:office:excel>);fnd_file.put_line(fnd_file.output,<WindowHeight>5070</WindowHeight>);fnd_file.put_line(fnd_file.output,<WindowWidth>10635</WindowWidth>);fnd_file.put_line(fnd_file.output,<WindowTopX>360</WindowTopX>);fnd_file.put_line(fnd_file.output,<WindowTopY>75</WindowTopY>);fnd_file.put_line(fnd_file.output,<ProtectStructure>False</ProtectStructure>);fnd_file.put_line(fnd_file.output,<ProtectWindows>False</ProtectWindows>);fnd_file.put_line(fnd_file.output, </ExcelWorkbook>);fnd_file.put_line(fnd_file.output, <Styles>);fnd_file.put_line(fnd_file.output,<Style. ss:ID=Default ss:Name=Normal>);fnd_file.put_line(fnd_file.output, <Alignment ss:Vertical=Center/>);fnd_file.put_line(fnd_file.output, <Borders/>);fnd_file.put_line(fnd_file.output, <Font ss:FontName=新細明體 x:Family=Roman ss:Size=12/>);fnd_file.put_line(fnd_file.output, <Interior/>);fnd_file.put_line(fnd_file.output, <NumberFormat/>);fnd_file.put_line(fnd_file.output, <Protection/>);fnd_file.put_line(fnd_file.output,</Style>);fnd_file.put_line(fnd_file.output,<Style. ss:ID=s21>);fnd_file.put_line(fnd_file.output, <Font ss:FontName=Arial Unicode MS x:CharSet=134 x:Family=Swiss);fnd_file.put_line(fnd_file.output, ss:Size=12/>);fnd_file.put_line(fnd_file.output,</Style>);fnd_file.put_line(fnd_file.output,<Style. ss:ID=s22>);fnd_file.put_line(fnd_file.output, <Font ss:FontName=Arial Unicode MS x:CharSet=134 x:Family=Swiss);fnd_file.put_line(fnd_file.output, ss:Size=12 ss:Color=#FF0000/>);fnd_file.put_line(fnd_file.output,</Style>);fnd_file.put_line(fnd_file.output, </Styles>);fnd_file.put_line(fnd_file.output, <Worksheet ss:Name=Sheet1>);fnd_file.put_line(fnd_file.output,<Table ss:ExpandedColumnCount=3 ss:ExpandedRowCount=1 x:FullColumns=1);fnd_file.put_line(fnd_file.output, x:FullRows=1 ss:DefaultColumnWidth=54 ss:DefaultRowHeight=16.5>);fnd_file.put_line(fnd_file.output, <Row ss:Height=17.25>);fnd_file.put_line(fnd_file.output, <Cell ss:StyleID=s22><Data ss:Type=Number>11</Data></Cell>);fnd_file.put_line(fnd_file.output, <Cell ss:StyleID=s21><Data ss:Type=Number>4</Data></Cell>);fnd_file.put_line(fnd_file.output,<Cell ss:StyleID=s21 ss:Formula=RC-2*RC-1><Data ss:Type=Number>44</Data></Cell>);fnd_file.put_line(fnd_file.output, </Row>);fnd_file.put_line(fnd_file.output,</Table>);fnd_file.put_line(fnd_file.output,<WorksheetOptions xmlns=urn:schemas-microsoft-com:office:excel>);fnd_file.put_line(fnd_file.output, <Print>);fnd_file.put_line(fnd_file.output, <ValidPrinterInfo/>);fnd_file.put_line(fnd_file.output, <PaperSizeIndex>9</PaperSizeIndex>);fnd_file.put_line(fnd_file.output, <HorizontalResolution>600</HorizontalResolution>);fnd_file.put_line(fnd_file.output, <VerticalResolution>0</VerticalResolution>);fnd_file.put_line(fnd_file.output, </Print>);fnd_file.put_line(fnd_file.output, <Selected/>);fnd_file.put_line(fnd_file.output, <Panes>);fnd_file.put_line(fnd_file.output, <Pane>);fnd_file.put_line(fnd_file.output, <Number>3</Number>);fnd_file.put_line(fnd_file.output, <ActiveCol>2</ActiveCol>);fnd_file.put_line(fnd_file.output, </Pane>);fnd_file.put_line(fnd_file.output, </Panes>);fnd_file.put_line(fnd_file.output, <ProtectObjects>False</ProtectObjects>);fnd_file.put_line(fnd_file.output, <ProtectScenarios>False</ProtectScenarios>);fnd_file.put_line(fnd_file.output,</WorksheetOptions>);fnd_file.put_line(fnd_file.output, </Worksheet>);fnd_file.put_line(fnd_file.output, <Worksheet ss:Name=Sheet2>);fnd_file.put_line(fnd_file.output,<Table ss:ExpandedColumnCount=0 ss:ExpandedRowCount=0 x:FullColumns=1);fnd_file.put_line(fnd_file.output, x:FullRows=1 ss:DefaultColumnWidth=54 ss:DefaultRowHeight=16.5/>);fnd_file.put_line(fnd_file.output,<WorksheetOptions xmlns=urn:schemas-microsoft-com:office:excel>);fnd_file.put_line(fnd_file.output, <ProtectObjects>False</ProtectObjects>);fnd_file.put_line(fnd_file.output, <ProtectScenarios>False</ProtectScenarios>);fnd_file.put_line(fnd_file.output,</WorksheetOptions>);fnd_file.put_line(fnd_file.output, </Worksheet>);fnd_file.put_line(fnd_file.output, <Workshee