CODEWITHSUNDEEP
pythonexcelpandasvbaxlwings
Toby-wan
Toby-wan

Reputation: 79

XLWings data format as currency, not float

Using xlwings, I am connecting to Snowflake and extracting a Pandas dataframe. The dataframe is a mixture of text, dates, and floats, which I have verified by using df.to_excel. However, when I use xlwings to write to my workbook, it converts the floats into currency figures. Could anyone recommend a solution so that the formats of the dataframe remain intact? I have seen suggestions to use .options(index=False, numbers=float), but this just gets ignored based on the result I have seen in the Excel sheet.

# Fetch the results
results = cur.fetchall()

# Fetch the column names
column_names = [desc[0] for desc in cur.description]

# Close the cursor and connection
cur.close()
conn.close()

df = pd.DataFrame(results, columns=column_names)

# Open an Excel workbook
wb = xw.Book.caller()

# Select the sheet you want to write to
sheet = wb.sheets['Snowflake'] 
sheet.range('A1').options(index=False).value = df

Upvotes: 0

Views: 103

Answers (1)

Toby-wan
Toby-wan

Reputation: 79

It seems that the truncation happens at the assignment to .value, regardless of whether the number_forma is changed subsequently. Information is already lost by that point.

The solution is to use copy/paste, which somehow keeps the precision of the data:

df = pd.DataFrame(results, columns=column_names)
df.to_clipboard(index=False)

# Open an Excel workbook
wb = xw.Book.caller()  # Replace with your Excel file path

# Select the sheet you want to write to
sheet = wb.sheets['Snowflake']  # Replace with your sheet name
cell = sheet.range('A1')
cell.api.PasteSpecial()

Upvotes: 1

Related Questions