如何使用 Pandas 在单元格中保存 *.xlsx 长 URL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35440528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to save in *.xlsx long URL in cell using Pandas
提问by chinskiy
For example I read excel file into DataFrame with 2 columns(id and URL). URLs in input file are like text(without hyperlinks):
例如，我将 excel 文件读入具有 2 列（id 和 URL）的 DataFrame。输入文件中的 URL 类似于文本（没有超链接）：
input_f = pd.read_excel("input.xlsx")
Watch what inside this DataFrame - everything was successfully read, all URLs are ok in input_f. After that when I wan't to save this file to_excel
观察这个 DataFrame 里面的内容 - 一切都已成功读取，所有 URL 在input_f. 之后，当我不想将此文件保存到_excel
input_f.to_excel("output.xlsx", index=False)
I got warning.
我得到警告。
Path\worksheet.py:836: UserWarning: Ignoring URL 'http:// here long URL'with link or location/anchor > 255 characters since it exceeds Excel's limit for URLS force_unicode(url))
路径\worksheet.py:836: UserWarning: Ignoring URL 'http:// here long URL'with link or location/anchor > 255 characters 因为它超出了 Excel 对 URLS force_unicode(url) 的限制)
And in output.xlsx cells with long URL were empty, and URLs become hyperlinks.
而在 output.xlsx 中，长 URL 的单元格为空，并且 URL 成为超链接。
How to fix this?
如何解决这个问题？
回答by Ophir Yoktan
You can create an ExcelWriter object with the option not to convert strings to urls:
您可以使用不将字符串转换为 url 的选项来创建 ExcelWriter 对象：
writer = pandas.ExcelWriter(r'file.xlsx', engine='xlsxwriter',options={'strings_to_urls': False})
df.to_excel(writer)
writer.close()
回答by bvmcode
I tried it myself and got the same problem. You could try to create a temp csv file and then use xlsxwriter to create an excel file. Once done then delete the tmp file. xlsxwriter has a write_string method that will override the auto hyperlinking that excel does. This worked for me.
我自己试过了，也遇到了同样的问题。您可以尝试创建一个临时 csv 文件，然后使用 xlsxwriter 创建一个 excel 文件。完成后删除tmp文件。xlsxwriter 有一个 write_string 方法，它将覆盖 excel 所做的自动超链接。这对我有用。
import pandas as pd
import csv
import os
from xlsxwriter.workbook import Workbook
inData = "C:/Users/martbar/Desktop/test.xlsx"
tmp = "C:/Users/martbar/Desktop/tmp.csv"
exFile = "C:/Users/martbar/Desktop/output.xlsx"
#read in data
df = pd.read_excel(inData)
#send to csv
df.to_csv(tmp, index=False)
#convert to excel
workbook = Workbook(exFile)
worksheet = workbook.add_worksheet()
with open(tmp, 'r') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
#if you use write instead of write_string you will get the error
worksheet.write_string(r, c, col)
workbook.close()
#delete tmp file
os.remove(tmp)
