pandas导出到Excel报URLS数量超出65530警告

今天在整理数据时报警告:

>>> df_all.to_excel('xxxx.xlsx', index=False)
/home/microfat/.local/lib/python3.6/site-packages/xlsxwriter/worksheet.py:945: UserWarning: Ignoring URL 'https://xxxxxxx' since it exceeds Excel's limit of 65,530 URLS per worksheet.

这是由于Excel单个工作表限制URL类型数据量为65530,超出的部分会被舍弃
只要将strings_to_urls自动转换功能关闭就好了

>>> with pd.ExcelWriter('xxxx.xlsx',engine='xlsxwriter',options={'strings_to_urls': False}) as writer:
		df_all.to_excel(writer, index=False)

或者

>>> writer = pd.ExcelWriter('xxxx.xlsx',engine='xlsxwriter',options={'strings_to_urls': False})
>>>	df_all.to_excel(writer, index=False)
>>> writer.close()

很明显用with as语法更优雅

2022-03-23更新

如果出现如下警告

/Users/microfat/Git/kudu_to_jdy/module/JDYWebapi.py:382: FutureWarning: Use of **kwargs is deprecated, use engine_kwargs instead.
  with pd.ExcelWriter(bio, engine='xlsxwriter', options={'strings_to_urls': False}) as writer:

需改为：

>>> writer = pd.ExcelWriter('xxxx.xlsx',engine='xlsxwriter',engine_kwargs={'options': {'strings_to_urls': False}})
>>>	df_all.to_excel(writer, index=False)
>>> writer.close()