首页 > 代码库 > Report_报表以PLSQL输出HTML标记实现(案例)

Report_报表以PLSQL输出HTML标记实现(案例)

2014-05-31 BaoXinjian In Capgemini

一. 摘要

Oracle Report Builder开发的字符模式的报表,成为系统报表

  • 优点
    • 采用图形化的开发方式,比较直观
    • 有功能强大的向导
    • 对比较复杂格式的报表也不要通过太多代码来实现
    • 最终结果以一个独立的文件的形式展示,比较容易传递
  • 缺点
    • 很难转成execel格式文件
    • 虽然有布局编辑器,但是实际编写过程中,其宽度和位置的拖动比较繁琐

通过在数据库中直接产生HTML格式的文件,成为HMTL报表

  • 优点
    • 输出的文件,可以直接另存为execel或者word格式
    • 布局简单
    • 在调试时,只需数据库编译即可,不要像rdf文件上传至server
  • 缺点
    • 程序包方式的开发,没有可视化界面
    • 对比较复杂格式的报表需要较大的代码量
    • 输出的为HTML标记,产生的文件会比较大

HTML报表的实现方式

  • 通过在数据库端调用过程fnd_file.put_line生产文件
  • 在生成文件过程中通过动态加入html标记实现文件的格式化
  • html文件本身可以直接转换为excel形式或者word当个事

 

二、案例

需求:客户要求以HTML的形式呈现供应商信息

1. 建立Procedure, 以输出HTML标记

2. 建立Program Execuable, 设定程式为bxj_html_invoice_report.main

3. 建立Concurrent Program并发程式,输出方式为HTML

4. 运行程式并测试http://i.cnblogs.com/EditPosts.aspx?opt=1

Step1. 建立Procedure, 以输出HTML标记

 1 procedure bxj_html_invoice_report(Errbuf Out Varchar2, Retcode Out Number) is
 2   cursor cur_invoice is
 3     select pv.VENDOR_NAME,
 4            aiv.invoice_num,
 5            aiv.invoice_date,
 6            aiv.invoice_amount,
 7            aiv.invoice_currency_code
 8       from Ap_Invoices_All aiv, po.po_vendors pv
 9      where rownum <= 100;
10   l_invoice cur_invoice%rowtype;
11 begin
12   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, <html><head>);
13   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, <style type="text/css">);
14   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,* { margin: 0; padding: 0;});
15   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,.all { width: 100%; border: 1px solid#000000;});
16   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, .title {width: 99%; });
17   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,table {width: 100%; border: 1px solid#FF00FF; border-collapse: collapse; table-layout:fixed; });
18   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,table tr th { border: 1px solid #FF00FF;overflow: hidden; word-break: break-all; text-overflow: ellipsis; white-space: nowrap;});
19   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,table tr td { border: 1px solid #FF00FF;overflow: hidden; /*word-wrap: break-word; 内容将在边界内换行*/word-break: break-all;);
20   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,text-overflow: ellipsis; white-space:nowrap;});
21   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,.content {width: 100%; height: 80%;overflow: scroll; });
22   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,.content div { width: 100%; }</style>);
23   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,</head><body><div class="all">);
24   --表头
25   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<div class="title"><table><tr>);
26   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:30%">VENDOR NAME</th>);
27   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:20%">INVOICE Number</th>);
28   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:20%">INVOICE Date</th>);
29   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:10%">INVOICE Amount</th>);
30   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:10%">Currency Code</th>);
31   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<thstyle="width:10%">Note</th> );
32   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, </tr></table></div>);
33   --内容
34   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<div class="content"><div><table>);
35   open cur_invoice;
36   loop
37     fetch cur_invoice
38       into l_invoice;
39     exit when cur_invoice%notfound;
40     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, <tr>);
41     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:30%"> || l_invoice.VENDOR_NAME || </td>);
42     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:20%"> || l_invoice.invoice_num || </td>);
43     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:20%"> || l_invoice.invoice_date || </td>);
44     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:10%"> || l_invoice.invoice_amount || </td>);
45     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:10%"> || l_invoice.invoice_currency_code || </td>);
46     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT,<td style="width:10%">&nbsp;</td>);
47     apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, </tr>);
48   end loop;
49   close cur_invoice;
50   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, </table></div></div>);
51   apps.Fnd_File.Put_line(apps.FND_FILE.OUTPUT, </body></HTML>);
52 end;
53 end cux_html_invoice_report;
输出HTML标记程式

 

Step2. 建立Program Execuable, 设定程式为bxj_html_invoice_report.main

 

Step3. 建立Concurrent Program并发程式,注意输出方式HTML,其他设定与普通CP无太大差异

 

三、案例测试

Test1. 调用Concurrent Program并发程式,输出方式为HTML,查看输出

 

参考:赛意咨询 http://wenku.baidu.com/link?url=lDoNquiWPnFoazuRRCtvjjBoFBh9TuuFG_5gXVNXKHunzvDMcp1ZQA19mwcnuCFWengm0eBdV23_F_qzMuaKoCsjZzDN1rBi7ZS4w6OJ-f7 

参考:王重东 http://wenku.baidu.com/link?url=VcQejF0TbWNkYERcGkRZVJOA_NUtaeiziImjTXkmyB0BgSI9IS3lErFhy-1hPoBYbsXf5MfYP1mh0Az83jec7Tm23rxKjQsYE0GiTUpEJmG

 

Thanks and Regards