2021年7月9日金曜日

Excelのシート表示名からシート名を取得する

Excelファイルに複数のシートが含まれる際に、ロードするシートを特定する方法を紹介します。

首相官邸のページからダウンロードできる日別の実績のExcelファイルに変更がありました。


7月5日までは医療従事者等のシートが１枚含まれていました。

7月6日より、３枚のシートの内の１枚が医療従事者等になっています。

Oracle APEXはExcelのデータを操作するパッケージとして、APEX_DATA_PARSERを提供しています。

このパッケージに含まれるPARSEファンクションによって、ExcelのデータをOracle Databaseの表として扱える形に変換することができます。ファンクションですが、いわゆるパイプライン表関数なので、表(アップデートはできないので、ビューといった方が適切かも)と同様に扱うことができます。

FUNCTION PARSE(
    p_content                      IN BLOB,
    p_file_name                    IN VARCHAR2     DEFAULT NULL,
    p_file_type                    IN T_FILE_TYPE  DEFAULT NULL,
    p_file_profile                 IN CLOB         DEFAULT NULL,
    p_detect_data_types            IN VARCHAR2     DEFAULT 'Y',
    p_decimal_char                 IN VARCHAR2     DEFAULT NULL,
    p_xlsx_sheet_name              IN VARCHAR2     DEFAULT NULL,
    p_row_selector                 IN VARCHAR2     DEFAULT NULL,
    p_csv_row_delimiter            IN VARCHAR2     DEFAULT LF,
    p_csv_col_delimiter            IN VARCHAR2     DEFAULT NULL,
    p_csv_enclosed                 IN VARCHAR2     DEFAULT '"',
    p_skip_rows                    IN PLS_INTEGER  DEFAULT 0,
    p_add_headers_row              IN VARCHAR2     DEFAULT 'N',
    p_file_charset                 IN VARCHAR2     DEFAULT 'AL32UTF8',
    p_max_rows                     IN NUMBER       DEFAULT NULL,
    p_return_rows                  IN NUMBER       DEFAULT NULL,
    p_store_profile_to_collection  IN VARCHAR2     DEFAULT NULL ) RETURN apex_t_parser_table pipelined;

対象となるExcelファイルにシートが複数含まれている場合は、p_xlsx_sheet_nameに取り込むシート名を指定する必要があります。無指定の場合は、先頭のシートが対象になります。

このシート名ですが、Excelを開いた時に確認できるシート名ではありません。

Excelファイルに含まれるシートの情報を確認するために使用できるファンクションGET_XLSX_WORKSHEETSが、APEX_DATA_PARSERパッケージに含まれています。このファンクションを使用して、表示上のシート名から内部的なシート名を取得します。

こちらの記事にあるように、医療従事者等の接種数が記載されたExcelファイルが表COVID19_IRYO_DATAに保存されていることを前提とします。以下のコードで医療従事者等のシートの内部名が取得できます。

declare
l_id number;
l_blob blob;
l_sheet_file_name varchar2(80);
C_IRYO_SHEET_NAME constant varchar2(30) := '医療従事者等';
begin
-- 最後の取得したExcelファイルをロード対象とする
select id, content into l_id, l_blob from covid19_iryo_data
where id = (select max(id) from covid19_iryo_data);
-- sheet_file_nameの取得
select s.sheet_file_name into l_sheet_file_name
from apex_data_parser.get_xlsx_worksheets(
p_content => l_blob
) s
where sheet_display_name = C_IRYO_SHEET_NAME;
dbms_output.put_line(l_sheet_file_name);
end;

SQLワークショップのSQLコマンドより実行します。

結果はsheet3.xmlとなります。l_sheet_file_nameをAPEX_DATA_PARSERファンクションのp_xlsx_sheet_nameに与えることで、医療従事者等のシートのデータが読み込まれます。

ちなみに、apex_data_parser.get_xlsx_worksheets自体の実行結果は以下のようになります。

select *
from apex_data_parser.get_xlsx_worksheets(
p_content => (
select content from covid19_iryo_data
order by id desc fetch first 1 rows only
)
)

以下の結果が表示されます。SHEET_DISPLAY_NAMEがExcelから確認できるシート名、SHEET_FILE_NAMEがAPEX_DATA_PARSER.PARSEファンクションのp_xlsx_sheet_nameに与える値です。

SHEET_SEQUENCESHEET_DISPLAY_NAMESHEET_FILE_NAMESHEET_PATH
1総接種回数sheet1.xmlworksheets/sheet1.xml
2一般接種sheet2.xmlworksheets/sheet2.xml
3医療従事者等sheet3.xmlworksheets/sheet3.xml

以上になります。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

投稿者 時刻:
ラベル: ,