Help us understand the problem. What is going on with this article?

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

やったこと

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

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

ポイント

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

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

実行環境など

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 として出力

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

注意点:writecell vs xlswrite

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

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

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

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

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

今回のサンプルデータは過去に書いた記事。
Capture.PNG
それをまとめて Excel にまとめてみます。Qiita API を使ってこのデータを取得する MATLAB コードはページ下記に記してますので興味あれば見てください。

Excel に書き出す計算式作成

Excel に書き込む計算式を作りましょう。itemList.urlitemList.titlehyperlink 関数内にいれた文字列を作れば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 型の文字列内に入れるために 「""基礎""」と" の前に " を1つ追加。

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); 

できあがり!

Capture.PNG

まとめ

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 なはず(未確認) 

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした