如何用oracle产生excle文件

通过Oracle产生CSV文件先决条件是：
创建Oracle Directory，将Write权限付给执行Procedure的用户 
创建可以发送邮件附件的包（见文章“一个在Oracle 9i中发送带附件邮件的包 PowerMail”） 
以下是创建Oracle Directory的方法和步骤：


在Oracle服务器的本地磁盘上新建一个文件夹，如D:\MyDirectory 
以sys身份用sqlplus登陆Oracle服务器 
执行目录创建命令：create or replace directory MYDIR as 'D:\MyDirectory'; 
执行用户授权命令：Grant read, write on directory MYDIR to HR;


下列是调用示例：


create or replace PROCEDURE STATIONARY_LIST
(department_name in varchar2,email_subject in varchar2) AS
file_location varchar2(20):='MYDIR';
file_name varchar2(200);
fhnd utl_file.file_type;
line_buff nvarchar2(4000);
SendorAddress varchar2(30) := 'HR@SomeCompany.com';
RecieverAddress varchar2(2000) :='Peter.Sword@SomeCompany.com';
EmailServer varchar2(30) :='Mail.SomeCompany.com';
Port number := 25;
conn utl_smtp.connection;
data RAW(2000);
error_string nvarchar2(500);
cursor item_list is select Index,Item_Name,Price,Quantity,Holder from StationaryInventory where Department=department_name;
begin
  begin
    --generate attchement file
    file_name:='2007-1-1 Report.csv';
    begin
      fhnd:=utl_file.fopen(file_location,file_name,'W');
      line_buff:='Index,Item Name,Price,Quantity,Holder';
      utl_file.put_line(fhnd,line_buff);
      for eachitem in item_list loop
         line_buff:=eachitem.index||','||eachitem.item_name||','||eachitem.price||','||eachitem.quantity||','        ||eachitem.holder;
          utl_file.put_line(fhnd,line_buff);
      end loop;
      utl_file.fclose(fhnd);
    end;
    --send email
    begin
    PowerMail.smtp_host:= EmailServer;
    PowerMail.smtp_port:= Port;
    conn:= PowerMail.begin_mail(SendorAddress,RecieverAddress,email_subject,PowerMail.MULTIPART_MIME_TYPE);
    PowerMail.attach_text(conn,'Items list of stationary for '||department_name||'.<br>'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'<br>HR System of Some Company','text/html');
    PowerMail.begin_attachment(conn,'application/vnd.ms-excel',TRUE,'2007-1-1 Report.csv','base64');
      begin
      fhnd:=utl_file.fopen(file_location,file_name,'r');
        loop
          utl_file.get_raw(fhnd, data, PowerMail.MAX_BASE64_LINE_WIDTH);
          PowerMail.write_raw(conn,utl_encode.base64_encode(data));
        end loop;
        utl_file.fclose(fhnd);
      exception when others then
          utl_file.fclose(fhnd);
      end;
    PowerMail.end_attachment(conn); 
    PowerMail.end_mail(conn);
    utl_file.fremove(file_location,file_name);
    end;
  --if occurs error, get error info;
  exception when others then
  error_string:=SQLERRM;
  end;
  insert into SYSTEM_LOG (LOG_TYPE,RUN_TIME,ERROR_INFO) values ('HR Stationary List',sysdate,error_string);
end;