问题与xlsxwriter有关,忽略“=”。

huangapple go评论110阅读模式
英文:

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
                      )

问题与xlsxwriter有关,忽略“=”。

答案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:

See also Passing XlsxWriter constructor options to Pandas.

huangapple
  • 本文由 发表于 2023年4月11日 03:02:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/75979896.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定