【MATLAB】ハイパーリンク付きテキストのExcel への書き込み自動化

17164 ワード
自動化 QiitaAPI Excel matlab matlab テキストリンク

やったこと

ハイパーリンク付きのテキストを MATLAB から Excel に書き込みました。

Web サイトのリスト (2000個程度）をクリックすれば飛べる形で Excel にまとめるのに MATLAB を使ったのでポイントを紹介します。もし同じような目にあっちゃった時には、参考にしてください（笑）

ポイント

  1. Excel の hyperlink 関数
  2. 記号（"）を含む文字列の string 型での定義方法
  3. writecell vs xlswrite
  4. error code: 0x800A03EC

ここではサンプルとして、私が今までに投稿した Qiita 記事をリストすることにします。成果物はこれ。

実行環境など

Windows 10
Excel1
MATLAB R2019b、本体のみ2

はじめに：HYPERLINK on Excel

「さて、どうやるんだ？ ActiveX 使うのは面倒だなぁ、、」と思いながら まずは Google 検索から入りまして、以下の MATLAB Answers を見つけました。

MATLAB Answers: Add a hyperlink in excell through matlab

Excel に HYPERLINK という関数があるようです。例えば Excel のセルに

=hyperlink("https://qiita.com/eigs","eigs@qiita")

と書けばいい。

MATLAB でこの計算式を文字列で定義して、Excel に書き出せば良さそうですね。やってみます。

sample1.m
url = "https://qiita.com/eigs";
text = "eigs@qiita"; 
string2Excel = "=HYPERLINK(""" + url + """,""" + text + """" + ")"
xlswrite('sample1.xlsx',{string2Excel}); % 1x1 cell として出力

実行すると

こんな感じになります。ちゃんとハイパーリンク化されとる。

注意点：writecell vs xlswrite

R2019a から writecell や writematrix などの関数が登場して Excel などにデータを出力する関数として推奨されています。実際に書き込み速度は xlswrite に比べるとかなり速いケースが多いです。ですが今回 Excel に書き込んだ計算式を実行させるには xlswrite でないとうまくいきませんでした。

writecell で実行すると以下の通り。

文字として記録されちゃっています。

[2019/10/28 追記]
writecell で実行する場合も、'UseExcel' オプションを true に設定するとうまくいくことが分かりました。

複数行分まとめて書き出してみよう

今回のサンプルデータは過去に書いた記事。

それをまとめて Excel にまとめてみます。Qiita API を使ってこのデータを取得する MATLAB コードはページ下記に記してますので興味あれば見てください。

Excel に書き出す計算式作成

Excel に書き込む計算式を作りましょう。itemList.url と itemList.title を hyperlink 関数内にいれた文字列を作ればOKです。

% タイトルの " に対応
itemList.title2Excel = replace(itemList.title,"""",""""""); % ここ重要！後に記載します。

% 計算式作成
itemList.toExcel = "=hyperlink(""" + itemList.url + """,""" ...
 + itemList.title2Excel + """" + ")";

1つ目の計算式を見てみると

>> itemList.toExcel(1)
ans = 
    "=hyperlink("https://qiita.com/eigs/items/bfba81f1d3e2d7690c58","MATLAB Answers での回答者週間ランキング（日本向け）")"

ということで、この列を Excel に書き出せばうまくいきそうです。

何個 " を付ければいいのか問題

MATLAB の string 型は定義時に " で挟む必要があります。Excel の計算式の入力値も " で挟む必要あります。結果として書き出すべき文字列の中に " が入ってきちゃうのですが、その " は " で挟む必要があり、結果として " だらけになります。" がコードの可読性を悪くしています。

例えば 「"」 という1文字を string 型で定義しようとすると、「""""」となります。他にも、

text = "【MATLAB】パス設定に関する""基礎""知識";

だと、「"基礎"」 を string 型の文字列内に入れるために 「""基礎""」と" の前に " を１つ追加。

string2Excel = "=HYPERLINK(""" + url + """,""" + text + """" + ")"

はさらに込み合っていますが、Excel の計算式内で "(url)", "(text)" と入力させるために、4か所で " を追加しています。

結局はうまくいくまで " を足したり引いたりして試行錯誤することになるんですが。

タイトルに " が入っている場合は注意

今回タイトルに " が入っているものがあり、そのまま hyperlink 関数に入れてしまうと Excel 側でエラーを引き起こします。Excel 側で計算式を評価するときにはじめてエラーになるので、明確なエラーメッセージが出てこず書き込みに失敗するのみ。原因を見つけるのに苦労しました。

itemList.title2Excel = replace(itemList.title,"""",""""""); % 最重要！

で対応しています。replace 関数で """"（" という文字列）を """"""（"" という文字列）で置き換えろという内容です。結果

>> itemList.title(4)
ans = 
    "【MATLAB】パス設定に関する"基礎"知識"

が

>> itemList.title2Excel(4)
ans = 
    "【MATLAB】パス設定に関する""基礎""知識"

と " が 2 つになっていますね。これで Excel 側での処理はOK。エラーは以下で体験できます。

error1.m
url = "http";
text = "【MATLAB】パス設定に関する""基礎""知識"; 
string2Excel = "=HYPERLINK(""" + url + """,""" + text + """" + ")"
xlswrite('error1.xlsx',{string2Excel}); % 1x1 cell として出力

参考：Why do I receive an error (error code: 0x800A03EC) when using XLSWRITE in MATLAB?

文字列の Excel への書き出し


% 投稿した日付とタイトルを出力します。
tmp = table2cell(itemList(:,["created_at","toExcel"]));
xlswrite('qiitaTitles.xlsx',tmp);

% タイトルだけ出力するならこれでも可
% xlswrite('qiitaTitles_v2.xlsx',itemList.toExcel);

できあがり！

まとめ

Excel の計算式を MATLAB で定義して、Excel に書き出すことで、ハイパーリンク付きのテキストの並んだ Excel ファイル作成を自動化しました。これで何千個、何万個であろうが大丈夫、自動化が可能ですね。10個程度であれば手作業でやりますけどね。

Appendix: コード全文

ネットさえつながっていればこのまま実行できるはず。

getQiitaTitles.m

% QiitaAPI で記事タイトルを取ってきます。
% accessToken = 'Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; % ダミー
% opts = weboptions('HeaderFields',{'Authorization',accessToken});
opts = weboptions; % accessToken 使わない場合

url = "https://qiita.com/api/v2/users/eigs/items";
tmp = webread(url,opts);
tmp = struct2table(tmp); % 構造体からtable型に変更。

% 投稿日、タイトル、URL だけ確保
itemList = tmp(:,{'created_at','title','url'});

% 日付は datetime 型に変えておきます。
itemList.created_at = datetime(itemList.created_at,...
    'InputFormat', "uuuu-MM-dd'T'HH:mm:ss'+09:00", ...
    'Format', "uuuu-MM-dd");

% タイトルとURLと日付は string 型に変更
itemList.title = string(itemList.title);
itemList.url = string(itemList.url);
itemList.created_at = string(itemList.created_at);

% タイトルの " に対応
itemList.title2Excel = replace(itemList.title,"""","""""");

% 計算式作成
itemList.toExcel = "=hyperlink(""" + itemList.url + """,""" ...
 + itemList.title2Excel + """" + ")";

% 投稿した日付とタイトルを出力します。
tmp = table2cell(itemList(:,["created_at","toExcel"]));
xlswrite('qiitaTitles.xlsx',tmp);

% タイトルだけ出力するならこれでも可
% xlswrite('qiitaTitles_v2.xlsx',itemList.toExcel);

  1. あまり Excel のバージョンを気にしたことはないですが・・関係あるのかな？ ↩

  2. table 型変数 と xlswrite 関数が使えれば R2019b より古いバージョンでも OK なはず（未確認） ↩