#Excelのハイパーリンクが消える
以前から使用していた自家製システムの挙動がおかしくなったのは、一度MACを工場初期化状態に戻して構築し直してから。
下のかんじで、Excel上のURLがハイパーリンクとして認識されない。
###自家製システムについて
取得したデータをソートしてExcelに出力するという単純なもの。
フローは以下。
- 情報の取得してリスト化
- openyxlでExcelに追記、cell.hyperlinkでハイパーリンクの設定をしている
- pandasでソート
- pandasでデプリケートチェックして重複は削除
- 出力
###環境差分
正常挙動する環境 | 今回の不具合環境 | |
---|---|---|
OS | macOS Big Sur バージョン不明 | macOS Big Sur v11.5.1 |
Excel | Excel for Mac サブスク版 | Excel for Mac サブスク版 |
開発ソフト | spider | pycharm |
python | v3.8.3 | v3.8.6 |
pandas | v1.0.5 | v1.3.1 |
openyxl | v3.0.4 | v3.0.7 |
差分ありすぎて、これは切り分けめっちゃたいへん・・・
#いざ!切り分け
##pythonを、ターミナルから直実行
変わらずハイパーリンクにならないのでpycharmの問題でもpythonの問題でもなさそう。
##excelのHYPERLINK関数の利用
Excelに直接
=HYPERLINK("https://www.amazon.co.jp/","aaa")
と入力するとハイパーリンクになることを確認。
そして、
フローの[2]cell.hyperlinkを以下に変更。
sheet.cell(row=rowmax, column=url_column+1).value = '=HYPERLINK("https://www.amazon.co.jp/", "aaa")'
結果は、なんと空。関数自体が削除された。
もしかするとExcelの問題かもしれない。。。と念頭におきつつ次の切り分け。
#pandasをコメントアウトしてみる
フローの[3],[4]だけコメントアウトしたところ、ハイパーリンクが表示される!
ということは、これはpandasをいじれば解決できるのか・・・?
フローの[3],[4]の問題のコードは以下。
print ('start deplicate check.')
#DataFrameにExcelの読み込み
df = pd.read_excel(file_path, header=None, engine='openpyxl')
#フラグを基準にして降順(False)にソート
df_flg_sort = df.sort_values(by=[flag_column,next_flag_column], ascending=False)
# 重複行の上を削除して下を残す
df_deplicate_delete = df_flg_sort.drop_duplicates(subset=url_column, keep='first')
# 上書き保存
df_deplicate_delete.to_excel(file_path, index=False, header=False)
色々調べてると、 pandasの仕様でURLが255を超過すると文字列として認知されることが判明。
ExcelWriterのオプションでoptions={'strings_to_urls':False}を指定することで、この機能をオフにできるとのこと。
読み込んでるURLは254文字以下のものばかりなのでこれが該当するかはわからんがやってみる。
print ('start deplicate check.')
#DataFrameにExcelの読み込み
df = pd.read_excel(file_path, header=None, engine='openpyxl')
# フラグを基準にして降順(False)にソート
df_flg_sort = df.sort_values(by=[flag_column,next_flag_column], ascending=False)
# 重複行の上を削除して下を残す
df_deplicate_delete = df_flg_sort.drop_duplicates(subset=url_column, keep='first')
writer = pd.ExcelWriter(file_path, index=False, header=False, options={'strings_to_urls':False})
df_deplicate_delete.to_excel(writer)
writer.close()
結果。だめ。ハイパーリンクならず。
どうやら、ただ、Excelを読んで、上書き保存するだけでハイパーリンクが消えてしまうみたい。
ソートや重複削除は一旦考えない。
.
.
.
ここまで考えてきて、ふと思った。
pandasで定義するのって、データフレーム型だから、ハイパーリンクとか定義できなくない?
pandas.DataFrameがもつ値は、value(値)、column(列)、index(行)のみ。
ということは、これまでシステムの力だと思ってたけど、
Excelパワーでhttps://〜〜〜を自動でハイパーリンクにしてくれてただけか!!
#結論
システム側でハイパーリンク化を有効にするには、データフレーム使わずにソートとデプリケートを実施するしかない。