ワークシートにWEBSERVICEとFILTERXML関数を追加する

今回は、「DioDocs(ディオドック)」でWEBSERVICE関数を設定して外部URLからXMLデータを取得し、FILTERXML関数を設定してXMLデータから参照したいデータだけを取り出してワークシートに追加する方法を紹介したいと思います。

こちらは2021年9月29日(水)にリリースされた「DioDocs V4J SP2」でDioDocs for Excelに追加された機能です。

Excelの計算式を設定するには?

DioDocs for Excelでワークシートのセルに計算式を設定するには、Formulaプロパティを使用します。以下はC1セルにSUM関数を使った計算式「=SUM(A1:B1)」をFormulaプロパティに設定して、A1セルとB1セルの値を合計するコードです。

// 新規ワークブックの作成
var workbook = new Workbook();

// 値を設定
workbook.Worksheets[0].Range["A1"].Value = 150;

workbook.Worksheets[0].Range["B1"].Value = 200;

// 計算式を設定
workbook.Worksheets[0].Range["C1"].Formula = "=SUM(A1:B1)";

// EXCELファイル(.xlsx)に保存
workbook.Save("formula-sum.xlsx");
Excelの計算式を設定するには?

上記のSUM関数と同じように、今回もFormulaプロパティを使用してWEBSERVICE関数とFILTERXML関数を設定します。

参照するWebサービス

今回XMLデータを取得するWebサービスは「Yahoo!天気・災害」で公開しているRSSを使用します。

https://weather.yahoo.co.jp/weather/rss/

Yahoo!天気・災害では、今日・明日・1週間の天気・災害に加え、警報・注意報などのRSSファイルを配信しています。

https://weather.yahoo.co.jp/weather/rss/

例えば「東京」の天気予報データは「https://rss-weather.yahoo.co.jp/rss/days/4410.xml」で取得できます。

参照するWebサービス

天気予報データをWEBSERVICE関数で取得する

以下のコードではB1セルにデータを取得するWebサービスのURL(https://rss-weather.yahoo.co.jp/rss/days/3410.xml)を設定し、B2セルにFormulaプロパティで「=WEBSERVICE(B1)」としてWEBSERVICE関数を設定しています。WEBSERVICE関数の引数でB1セルの値を参照しています。

Workbook workbook = new Workbook();
IWorksheet ws = workbook.Worksheets[0];

ws.Range["B1"].Value = "https://rss-weather.yahoo.co.jp/rss/days/3410.xml"; // 仙台
ws.Range["B2"].Formula = "=WEBSERVICE(B1)";

実行すると以下のように取得したXMLデータがB2セルに設定されている状態を確認できます。

天気予報データをWEBSERVICE関数で取得する

取得したXMLデータは以下です。

<rss version=""2.0"">
    <channel>
        <title>Yahoo!天気・災害 - 東部(仙台)の天気</title>
        <link>https://weather.yahoo.co.jp/weather/jp/4/</link>
        <description>Yahoo! JAPANの天気・災害に掲載されている最新の情報を提供しています。</description>
        <copyright>Copyright (C) [2021] Yahoo Japan Corporation. All Rights Reserved. Copyright (C) 2021 Weather Map Co., Ltd. All Rights Reserved.</copyright>
        <language>ja</language>
        <lastBuildDate>Mon, 11 Oct 2021 11:10:46 +0900</lastBuildDate>
        <item>
            <title>【 11日(月) 東部(仙台) 】 曇のち雨 - 28℃/17℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211011</link>
            <description>曇のち雨 - 28℃/17℃</description>
            <pubDate>Mon, 11 Oct 2021 11:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 12日(火) 東部(仙台) 】 雨 - 17℃/14℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211012</link>
            <description>雨 - 17℃/14℃</description>
            <pubDate>Mon, 11 Oct 2021 11:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 13日(水) 東部(仙台) 】 曇時々雨 - 18℃/14℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211013</link>
            <description>曇時々雨 - 18℃/14℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 14日(木) 東部(仙台) 】 曇り - 22℃/14℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211014</link>
            <description>曇り - 22℃/14℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 15日(金) 東部(仙台) 】 曇り - 24℃/15℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211015</link>
            <description>曇り - 24℃/15℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 16日(土) 東部(仙台) 】 曇時々雨 - 21℃/13℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211016</link>
            <description>曇時々雨 - 21℃/13℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 17日(日) 東部(仙台) 】 曇一時雨 - 18℃/10℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211017</link>
            <description>曇一時雨 - 18℃/10℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 18日(月) 東部(仙台) 】 曇時々晴 - 17℃/8℃ - Yahoo!天気・災害</title>
            <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211018</link>
            <description>曇時々晴 - 17℃/8℃</description>
            <pubDate>Mon, 11 Oct 2021 08:00:00 +0900</pubDate>
        </item>
        <item>
            <title>【 東部仙台 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
        <item>
            <title>【 石巻地域 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
        <item>
            <title>【 東部大崎 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
        <item>
            <title>【 気仙沼地域 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
        <item>
            <title>【 東部仙南 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
        <item>
            <title>【 登米・東部栗原 】警報・注意報はありません - Yahoo!天気・災害</title>
            <link>https://typhoon.yahoo.co.jp/weather/jp/warn/4/</link>
            <description>警報・注意報はありません</description>
            <pubDate>Mon, 11 Oct 2021 08:12:00 +0900</pubDate>
        </item>
    </channel>
</rss>

天気予報データから必要な部分をFILTERXML関数で取り出す

WEBSERVICE関数で取得したXMLデータから以下のタイトルをFILTERXML関数で取得します。

<channel>
    <title>Yahoo!天気・災害 - 東部(仙台)の天気</title>
    <link>https://weather.yahoo.co.jp/weather/jp/4/</link>
    <description>Yahoo! JAPANの天気・災害に掲載されている最新の情報を提供しています。</description>
    <copyright>Copyright (C) [2021] Yahoo Japan Corporation. All Rights Reserved. Copyright (C) 2021 Weather Map Co., Ltd. All Rights Reserved.</copyright>
    <language>ja</language>
    <lastBuildDate>Mon, 11 Oct 2021 11:10:46 +0900</lastBuildDate>

以下のコードではA4セルにFormulaプロパティで「=FILTERXML(B2, \”//channel/title\”)」としてFILTERXML関数を設定しています。FILTERXML関数の引数でB2セルのXMLデータと取得したいデータが含まれるパス//channel/titleを設定しています。

Workbook workbook = new Workbook();
IWorksheet ws = workbook.Worksheets[0];

ws.Range["B1"].Value = "https://rss-weather.yahoo.co.jp/rss/days/3410.xml"; // 仙台

// WEBSERVICE関数
ws.Range["B2"].Formula = "=WEBSERVICE(B1)";

// FILTERXML関数
ws.Range["A4"].Formula = "=FILTERXML(B2, \"//channel/title\")";

実行すると以下のように取得したchannel/title要素のデータがA4セルに設定されている状態を確認できます。

天気予報データから必要な部分をFILTERXML関数で取り出す

さらに以下のような<item>要素に含まれるデータ<title>についても取得していきます。

<item>
    <title>【 11日(月) 東部(仙台) 】 曇のち雨 - 28℃/17℃ - Yahoo!天気・災害</title>
    <link>https://weather.yahoo.co.jp/weather/jp/4/3410.html?d=20211011</link>
    <description>曇のち雨 - 28℃/17℃</description>
    <pubDate>Mon, 11 Oct 2021 11:00:00 +0900</pubDate>
</item>

この<item>要素ですが、取得したXMLデータに含まれる<item>要素の数は地域ごとに変動します。「8日間分の天気予報 + 詳細な地域区分ごとの警報と注意報」となっており、後者の「 詳細な地域区分ごとの警報と注意報 」の部分が変動します。

このような変動するデータ(可変サイズの配列)を返す数式に対応するための機能として、Excelに追加されているのが「動的配列数式」になります。DioDocs for Excelでは今回リリースしたSP2からこの動的配列数式に対応しており、AllowDynamicArrayプロパティをtrueに設定すると有効化できます。

以下のコードではAllowDynamicArrayプロパティで動的配列数式を有効化してから、B4セルにFormulaプロパティで「=FILTERXML(B2, \”//channel/item/title\”)」としてFILTERXML関数を設定しています。FILTERXML関数の引数でB2セルのXMLデータと取得したいデータが含まれるパス//channel/item/titleを設定しています。

Workbook workbook = new Workbook();
IWorksheet ws = workbook.Worksheets[0];

ws.Range["B1"].Value = "https://rss-weather.yahoo.co.jp/rss/days/3410.xml"; // 仙台

// WEBSERVICE関数
ws.Range["B2"].Formula = "=WEBSERVICE(B1)";

// 動的配列数式を有効にする
workbook.AllowDynamicArray = true;

// FILTERXML関数
ws.Range["B4"].Formula = "=FILTERXML(B2, \"//channel/item/title\")";

実行すると以下のように取得したchannel/item/title要素のデータがB4~B17セルに設定されている状態を確認できます。

天気予報データから必要な部分をFILTERXML関数で取り出す

また、実際に数式が設定されているのはB4セルだけで、B5~B17セルはスピル領域となっており、スピル領域のセルにフォーカスした際に数式はグレーアウト(編集不可)になっていることも確認できます。

天気予報データから必要な部分をFILTERXML関数で取り出す

今回紹介した機能を記載しているデモはこちらです。

今回紹介した機能を記載しているサンプルはこちらです。