やったこと
ハイパーリンク付きのテキストを MATLAB から Excel に書き込みました。
Web サイトのリスト (2000個程度）をクリックすれば飛べる形で Excel にまとめるのに MATLAB を使ったのでポイントを紹介します。もし同じような目にあっちゃった時には、参考にしてください（笑）
ポイント
- Excel の
hyperlink関数 - 記号（"）を含む文字列の
string型での定義方法 -
writecellvsxlswrite - 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 に書き出せば良さそうですね。やってみます。
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。エラーは以下で体験できます。
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?]
(https://jp.mathworks.com/matlabcentral/answers/101631-why-do-i-receive-an-error-error-code-0x800a03ec-when-using-xlswrite-in-matlab?s_eid=PSM_29435)
文字列の Excel への書き出し
% 投稿した日付とタイトルを出力します。
tmp = table2cell(itemList(:,["created_at","toExcel"]));
xlswrite('qiitaTitles.xlsx',tmp);
% タイトルだけ出力するならこれでも可
% xlswrite('qiitaTitles_v2.xlsx',itemList.toExcel);
できあがり！
まとめ
Excel の計算式を MATLAB で定義して、Excel に書き出すことで、ハイパーリンク付きのテキストの並んだ Excel ファイル作成を自動化しました。これで何千個、何万個であろうが大丈夫、自動化が可能ですね。10個程度であれば手作業でやりますけどね。
Appendix: コード全文
ネットさえつながっていればこのまま実行できるはず。
% 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);