英文:
Issue with xlsxwriter and ignoring "="
问题
I'm using Python's xlsxwriter to write records from a sql database into an excel file. One particular column has certain cells that begin with the "=" sign, which obviously poses a problem when excel then tries to interpret these cells. I found that I can escape this with a ' (single quote). After trying it manually in Excel, I went ahead and tried it in my code. It worked as I no longer was getting the Excel formula error, but unlike when I tried it manually the single quote is still visible when I open the workbook in Excel. How can I bypass this Excel error without also affecting the final Excel output?
英文:
I'm using Python's xlsxwriter to write records from a sql database into an excel file. One particular column has certain cells that begin with the "=" sign, which obviously poses a problem when excel then tries to interpret these cells. I found that I can escape this with a ' (single quote). After trying it manually in Excel, I went ahead and tried it in my code. It worked as I no longer was getting the Excel formula error, but unlike when I tried it manually the single quote is still visible when I open the workbook in Excel. How can I bypass this Excel error without also affecting the final Excel output?
with pandas.ExcelWriter(
"file.xlsx", engine="xlsxwriter", mode="w"
) as writer:
workbook = writer.book
sheet_name = 'Sheet1'
sheet_data = spark.read.csv(
"path_to_file", header=True, multiLine=True
)
current_sheet = workbook.get_worksheet_by_name(sheet_name)
spam_catch = current_sheet.write(
f"{setting['column']}{row_number}",
cell_output
)
答案1
得分: 2
以下是翻译好的部分:
你可以通过将 XlsxWriter 构造选项 传递给它,以防止将以“=”开头的字符串转换为公式。
类似这样的方式应该可以工作:
with pandas.ExcelWriter(
"file.xlsx",
engine="xlsxwriter",
engine_kwargs={'options': {'strings_to_formulas': False}})
) as writer:
另请参阅 将 XlsxWriter 构造选项传递给 Pandas。
英文:
You can pass a XlsxWriter constructor option to prevent it from converting strings that start with "=" into formulas.
Something like this should work:
with pandas.ExcelWriter(
"file.xlsx",
engine="xlsxwriter",
engine_kwargs={'options': {'strings_to_formulas': False}})
) as writer:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论