How can I extract or read data from xlsx file in Oracle apex data parser using the worksheet name instead of worksheet code:

In the sample code below, we can see that what is used is the file worksheet code name:

SELECT line_number,
   col001,
   col002,
   col003 FROM TABLE (apex_data_parser.parse (p_content           => (SELECT file_content
                                                            FROM loader_files
                                                           WHERE id = 1),
                                  p_file_name         => 'data.xlsx',
                                  **p_xlsx_sheet_name   => 'sheet1.xml',**
                                  p_skip_rows         => 0));

My requirement is to instead of using the code name "sheet1.xml" that I use the worksheet name "employees", the reason is that we need to extract data from multiple worksheets and the position of the worksheets might change, changing the worksheet code names and therefore breaking my code.

Is there anyway this can be done?

CodePudding user response：

Use the function GET_XLSX_WORKSHEETS to convert a SHEET_DISPLAY_NAME into a SHEET_FILE_NAME:

select
    line_number, col001, col002, col003
from table
(
    apex_data_parser.parse
    (
        p_content           => (select file_content from loader_files where id = 1),
        p_file_name         => 'data.xlsx',
        p_skip_rows         => 0,
        p_xlsx_sheet_name   =>
        (
            select sheet_file_name
            from table
            (
                apex_data_parser.get_xlsx_worksheets
                (
                    p_content => (select file_content from loader_files where id = 1)
                )
            )
            where sheet_display_name = 'employees'      
        )
    )
);