首页 　· 　知识　· 　生产制造
系统设置不能直接调用EXCEL，并要求用PACKAGE
网友　　 http://ema100.blog.sohu.com/entry/7939428/　　　　编辑:德仔 　　图片来源:网络
采用的办法是先输出HTML文件，然后再用HTML脚本进行转化为EXCEL。 =============================================

采用的办法是先输出HTML文件，然后再用HTML脚本进行转化为EXCEL。

===========================================================

PROCEDURE MAIN(ERRCODE  OUT VARCHAR2,
                 ERRBUF   OUT VARCHAR2,
                 P_ORG_ID IN NUMBER) IS
    CURSOR CUR_A IS
      SELECT PV.VENDOR_ID,
             PV.VENDOR_NAME,
             PV.SEGMENT1,
             ASSA.VENDOR_SITE_ID,
             ASSA.VENDOR_SITE_CODE,
             HPS.PARTY_SITE_NAME,
             ASSA.ORG_ID
        FROM PO_VENDORS            PV,
             AP_SUPPLIER_SITES_ALL ASSA, --地点
             HZ_PARTY_SITES        HPS --地址
       WHERE PV.VENDOR_ID = ASSA.VENDOR_ID(+)
         AND ASSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
         AND ASSA.ORG_ID = P_ORG_ID
       ORDER BY VENDOR_ID;
    BEGIN
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<html>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<head>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<meta http-equiv="Content-Type" content="text/html; charset=gb2312">'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<style type="text/css">'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  h2 {font-size: 30pt; font-family:''宋体, Arial''}')); --24--35pt
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  td, body {font-size: 15px; font-family:''宋体, Arial''}')); --16--18px
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  div.breakafter { page-break-after:always; }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  div.breakbefore { page-break-before:always; }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</style>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<script defer>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('function SetPrintSettings() {'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('  factory.printing.header = "";'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('  factory.printing.footer = "";'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  factory.printing.portrait = true;'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  factory.printing.leftMargin = 10.0;')); --25
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  factory.printing.topMargin = 10.0;')); --20
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  factory.printing.rightMargin = 10.0;'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('  factory.printing.bottomMargin = 10.0;'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('}'));
    -- CNC_PRJNL_PUT_LINE(p_srs_flag,'OUTFILE', 'SetPrintSettings();');
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</script>'));
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<script>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('var excelObj; var $Table; '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('function printTable(talbleId){'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' var rows,cols; var i,j; '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      (' $Table=document.all(talbleId); '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' rows=$Table.rows.length;'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' if(rows>0){ '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('   try{'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('        cols=$Table.rows(0).children.length; '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('        excelObj = new ActiveXObject("Excel.Application");'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('        excelObj.Visible = true; '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('        excelObj.WorkBooks.Add;'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('        for(i=1;i<=rows;i++) '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('           for(j=1;j<=cols;j++){'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('              showInExcel(i,j,$Table.rows(i-1).children(j-1).innerText);'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('           }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('   }catch(e){alert("生成Excel失败!");} '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' else{'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('    alert("no data");}'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('function showInExcel(row,col,value){'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      (' excelObj.Cells(row, col).Value=value; '));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, (' }'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</script>'));
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</head>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<body>'));
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<table name="excel_table" id="excel_table" align=center style="width: 850px; vertical-align: center; text-align: left;" border="1" cellpadding="0" cellspacing="0">'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<tbody>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<tr>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商ID</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商名称</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 10%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商编码</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 10%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商地点ID</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商地点编码</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商地址</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">供应商地点对应组织ID</span></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</tr>'));
 
    FOR REC_A IN CUR_A LOOP
   
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<tr>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.VENDOR_ID || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.VENDOR_NAME || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 10%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.SEGMENT1 || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 10%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.VENDOR_SITE_ID || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.VENDOR_SITE_CODE || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.PARTY_SITE_NAME || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                        ('<td style="width: 6%; vertical-align: middle; text-align: center;"><span style="font-size: 12; font-family: 宋体">' ||
                        REC_A.ORG_ID || '</span></td>'));
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</tr>'));
    END LOOP;
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</tbody></table>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<table ><tbody>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<table align=center style="width: 850px; vertical-align: center; text-align: left;" border="0" cellpadding="0" cellspacing="0">'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<tbody>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('<tr>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ('<td align="right"><input type="submit" name="C_EXCEL" value="保存成Excel" id="C_EXCEL" onclick="printTable(''excel_table'')" /></td>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</tr>'));
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ('</tbody></table>'));
 
  END MAIN;    

本文作者:网友 来源: http://ema100.blog.sohu.com/entry/7939428/
CIO之家 www.ciozj.com 微信公众号:imciow
　　　　>>频道首页　　>>网站首页 　　纠错　　>>投诉
版权声明:CIO之家尊重行业规范，每篇文章都注明有明确的作者和来源；CIO之家的原创文章，请转载时务必注明文章作者和来源；
延伸阅读
也许感兴趣的
我们推荐的
主题最新
看看其它的
上一篇
中文patch 的安装方法
下一篇
系统设置不能直接调用EXCEL，并要求用PACKAGE
作者其它的文章
收藏至微信