Search code examples
pandasexcelformatopenpyxlxlsxwriter

pd.to_excel() set "Text" number format

According to the pandas documentation one can use df.style.map(lambda v: "@").to_excel() to apply a number format (excel terminology) to the exported table. Instead of the "General" number format that is used by default, one can specify the "Text" number format to prevent that numbers change to dates when entering the "editing" mode (shortcut F2 in excel) and then pressing Enter.

The solution with df.style.map(...) is fine, but I would be interested to use engine_kwargs1: Is there a way to apply the "Text" number format using the engine_kwargs parameter of to_excel() instead of df.style.map(...)? Preferable is an answer that uses openpyxl as engine, but if this is not possible, using xlsxwriter as engine would also be acceptable. I am not looking for a solution using pd.ExcelWriter() as this is already well documented.

Here is an example of df.style.map(...).to_excel():

import pandas as pd

df = pd.DataFrame([["23-3", "21-1"],
                   ["8-5", "6-7"]], columns=["A", "B"])

# @ is the number format code of excel for Text.
df.style.map(lambda v: "number-format: @").to_excel(r"test.xlsx")

1To increase readability.

Solution

  • As of the date of this answer, this does not seem possible:

    XlsxWriter

    From documentation: "XlsxWriter supports several Workbook() constructor options such as strings_to_urls(). These can also be applied to the Workbook object created by Pandas using the engine_kwargs keyword."

    df.to_excel(
    r"test.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"strings_to_urls": True}},
)

    Alas, there is not a constructor option to format cells.

    Openpyxl

    The use of engine_kwargs is a bit different, and the arguments supported by the Workbook() constructor are limited to write_only and iso_dates:

    df.to_excel(
    r"test.xlsx",
    engine="openpyxl",
    engine_kwargs={"iso_dates": True},
)

    So, not an option either.