Generating Excel by PL/SQL UTL_FILE, Style error

Question

I'm using ExcelDocTypeUtils pkg for exporting Query data to EXCEL FILE. When I run the procedure I'm getting a excel file, but when I try to open it this error pops out: Program came up in the following areas during load: Style

The original line that I think that fails is:

-- Prepare Headers
owa_util.mime_header('application/vnd.ms-excel',FALSE);

I have already tried setting:

<?xml version="1.0" encoding="UTF-8"?>'

But when I do that, the error changes to the following: Program came up in the following areas during load: 'Strict Parse Error'

Here is how I execute my procedure:

/* Starts */
execute employeereport;


CREATE OR REPLACE PROCEDURE employeeReport AS

   v_sql_salary        VARCHAR2(200) := 'SELECT last_name,first_name,salary FROM hr.employees ORDER BY last_name,first_name';
   v_sql_contact       VARCHAR2(200) := 'SELECT last_name,first_name,phone_number,email FROM hr.employees ORDER BY last_name,first_name';
   v_sql_hiredate      VARCHAR2(200) := 'SELECT last_name,first_name,to_char(hire_date,''MM/DD/YYYY'') hire_date FROM hr.employees ORDER BY last_name,first_name';
   excelReport         ExcelDocumentType := ExcelDocumentType();
   v_worksheet_rec     ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;
   v_worksheet_array   ExcelDocTypeUtils.WORKSHEET_TABLE  :=           }  
   ExcelDocTypeUtils.WORKSHEET_TABLE();
   documentArray    ExcelDocumentLine := ExcelDocumentLine();
   v_file                    UTL_FILE.FILE_TYPE;


    BEGIN

    -- Salary
    v_worksheet_rec.query           := v_sql_salary;
    v_worksheet_rec.worksheet_name  := 'Salaries';
    v_worksheet_rec.col_count       := 3;
    v_worksheet_rec.col_width_list  := '25,20,15';
    v_worksheet_rec.col_header_list := 'Lastname,Firstname,Salary';

    v_worksheet_array.EXTEND;
    v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

    -- Contact
    v_worksheet_rec.query           := v_sql_contact;
    v_worksheet_rec.worksheet_name  := 'Contact_Info';
    v_worksheet_rec.col_count       := 4;
    v_worksheet_rec.col_width_list  := '25,20,20,25';
    v_worksheet_rec.col_header_list := 'Lastname,Firstname,Phone,Email';

    v_worksheet_array.EXTEND;
    v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;

    -- Contact
    v_worksheet_rec.query           := v_sql_hiredate;
    v_worksheet_rec.worksheet_name  := 'Hiredate';
    v_worksheet_rec.col_count       := 3;
    v_worksheet_rec.col_width_list  := '25,20,20';
    v_worksheet_rec.col_header_list := 'Lastname,Firstname,Hiredate';

    v_worksheet_array.EXTEND;
    v_worksheet_array(v_worksheet_array.count) := v_worksheet_rec;
    owa.num_cgi_vars := NVL(owa.num_cgi_vars, 0);
    excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array);
    documentArray := excelReport.getDocumentData;

    v_file := UTL_FILE.fopen('C:\','test.xml','W',4000);

    FOR x IN 1 .. documentArray.COUNT LOOP

        UTL_FILE.put_line(v_file,documentArray(x));

    END LOOP;

    UTL_FILE.fclose(v_file);

    --excelReport.displayDocument;

    END;

Not sure but I think that package gives you a document in the "xlsx" format, for which the mime type is `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`. I may be wrong but might be worth trying. — Jeffrey Kemp, Mar 09 '15 at 06:58

score 0 · Answer 1 · answered Mar 13 '15 at 14:04

0

It's solved. I don't know what did it, the only thing that I changed it's .xls extension instead of .xml Thanks.

answered Mar 13 '15 at 14:04

Dexter

31
3

See the comment by @JeffreyKemp - that package generates .xlsx, which is a compressed xml document (new Excel format) whereas .xls is a proprietary binary format (old Excel format). So the most sensible thing to do would be to use .xlsx as the file extension. – Frank Schmitt Mar 13 '15 at 14:29

Generating Excel by PL/SQL UTL_FILE, Style error

1 Answers1