使用 Oracle 中的 UTL_FILE 包在 ExcelSheet 中写入 [英] Writing in ExcelSheet using UTL_FILE package in Oracle

问题描述

我在存储在某个预定义目录中的 Excel 表中写入数据没有问题.

I have no problem in writing data in excel sheet which is stored in some predefined directory.

现在我有 10 组数据，对于每组数据，我必须创建 10 个单独的 excel 表.但我想要的是创建工作簿 conating sheet1,sheet2,.表 10.其中将有所有10组记录.如果我的问题不清楚，请告诉我.

Now I have 10 sets of data and for each set I have to create 10 seperate excel sheet. But what I want is to create Workbook conating sheet1,sheet2,. Sheet10. Which will have all 10 sets of record. If my question is not clear let me know.

我使用的是 PL/SQL Oracle 9i

I'm using PL/SQL Oracle 9i

我的代码将为一组数据写入excel.如果我有更多数据集并且不想拥有多个 Excel 工作表，而是想要一个带有差异工作表的工作簿，该怎么办.

My Code which will write to excel for one set of data. What if I have more set of data and I don't want to have multiple excel sheet instead I want one workbook with diff sheet.

CREATE OR REPLACE PROCEDURE SP_ORACLE_EXCEL(I_FILE_NAME    IN VARCHAR2) AS

  FILENAME  UTL_FILE.FILE_TYPE;
  FILENAME1 VARCHAR2(1000);
  CURSOR C1 IS
    SELECT * FROM MY_TABLE;
  VARC1 C1%ROWTYPE;
BEGIN

  FILENAME1   := 'TEST_' || I_FILE_NAME || '_' || SYSDATE || '.CSV';

  FILENAME    := UTL_FILE.FOPEN('TEMP_DIR', FILENAME1, 'W');

  /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
    UTL_FILE.PUT_LINE(FILENAME,
                   'HEADER1' || ',' || 'HEADER2' || ',' || 'HEADER3' || ',' ||
                    'HEADER4' || ',' || 'HEADER5');
  OPEN C1;
  LOOP
    FETCH C1
       INTO VARC1;
    EXIT WHEN C1%NOTFOUND;
     /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
    UTL_FILE.PUT_LINE(FILENAME,
                      '"' || VARC1.COL1 || '"' || ' ,' || '"' ||
                      VARC1.COL2 || '"' || ' ,' || '"' ||
                      VARC1.COL3 || '"' || ' ,' || '"' ||
                      VARC1.COL4 || '"' || ' ,' || '"' ||
                      VARC1.COL5|| '"');


  END LOOP;

  UTL_FILE.FCLOSE(FILENAME);

END SP_ORACLE_EXCEL;

推荐答案

我知道没有现成的实现.

There's no ready-to-use implementation that I'm aware of.

Excel 文件 (.xslx) 自 07 年以来实际上是包含每个工作表的单独 xml 文件的 zip 存档.

Excel files (.xslx) since '07 are actually zip archives containing separate xml files for each worksheet.

他们使用的 XML 架构是非常简单.您必须使用 Java 来创建文件夹并进行 zip 压缩才能写入此类文件.

The XML schema they're using is pretty straight forward. You'd have to use Java to create the folders and do the zip compression in order to write such files.

这篇关于使用 Oracle 中的 UTL_FILE 包在 ExcelSheet 中写入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！