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

# やったこと

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

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

### ポイント

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

ここではサンプルとして、私が今までに投稿した Qiita 記事をリストすることにします。成果物はこれ。
![Capture.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149511/46d82dc6-b8c4-50eb-0d09-f1828543748e.png)

### 実行環境など

Windows 10
Excel[^1]
MATLAB R2019b、本体のみ[^2]

[^1]: あまり Excel のバージョンを気にしたことはないですが・・関係あるのかな？
[^2]: table 型変数 と `xlswrite` 関数が使えれば R2019b より古いバージョンでも OK なはず（未確認）

# はじめに：`HYPERLINK` on Excel

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

[MATLAB Answers: Add a hyperlink in excell through matlab](https://jp.mathworks.com/matlabcentral/answers/22768-add-a-hyperlink-in-excell-through-matlab?s_eid=PSM_29435)

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

```
=hyperlink("https://qiita.com/eigs","eigs@qiita")
```
と書けばいい。

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

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

実行すると
![Capture.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149511/4ccfcd95-25be-bcce-19ad-66a529a2ebc3.png)
こんな感じになります。ちゃんとハイパーリンク化されとる。

## 注意点：`writecell` vs `xlswrite`

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

`writecell` で実行すると以下の通り。
![Capture.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149511/68bf61ca-3968-5e06-4cbe-b375452964b5.png)

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

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


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

今回のサンプルデータは過去に書いた記事。
![Capture.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149511/d9d29bd1-ac43-1bbc-d041-badae465d281.png)
それをまとめて Excel にまとめてみます。Qiita API を使ってこのデータを取得する MATLAB コードはページ下記に記してますので興味あれば見てください。


# Excel に書き出す計算式作成

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

```matlab
% タイトルの " に対応
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` 型で定義しようとすると、「""""」となります。他にも、

```matlab
text = "【MATLAB】パス設定に関する""基礎""知識"; 
```
だと、「"基礎"」 を `string` 型の文字列内に入れるために 「""基礎""」と" の前に " を１つ追加。

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

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


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

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

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

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

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

が

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

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

```matlab: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?]
(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 への書き出し

```matlab

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

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

できあがり！

![Capture.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/149511/46d82dc6-b8c4-50eb-0d09-f1828543748e.png)


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

## Appendix: コード全文

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

```matlab: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);
```