CODEWITHSUNDEEP
pythonpandasxlwings
armara
armara

Reputation: 557

New column keep formatting xlwings

I want to keep the formatting of columns (lines, colors, bold text) to follow when I read/write tables to/from excel-files through xlwings. Here's an example, I start with connecting to an empty excel-file Book1.xlsx;

import xlwings as xw
book = xw.Book('Book1.xlsx')
sheet = book.sheets['Sheet1']

Inserting a small table with one datetime column

import pandas as pd
from datetime import datetime
df = pd.DataFrame(columns=[datetime(2020,10,31)], index=['a', 'b', 'c'])
df.index.name = 'My table'
df.loc[:, datetime(2020,10,31)] = [100,200,300]
sheet.range('A1').value = df

Now I manually format the table inside the excel-file, here is a resulting pic

formatting

Now there's a new month, and we should add new values. So I read the table from the excel-file and add a new column like so

df = sheet.range('A1').options(pd.DataFrame, expand='table').value
new_month = datetime(2020,11,30)
df.loc[:, new_month] = [101,202,301]

When I write the dataframe back to the excel-file, there is no formatting for the new column.

sheet.range('A1').value = df

See pic below. Any xlwings-way of fixing this?

no formatting

Upvotes: 1

Views: 1956

Answers (1)

mouwsy
mouwsy

Reputation: 1933

You can copy and paste the formatting to the new columns. See the example:

import xlwings as xw

book = xw.Book(r"Book1.xlsx")
sheet = book.sheets["Sheet1"]

sheet.range("B1").expand("down").copy()
sheet.range("B1").expand("table").paste(paste="formats")
sheet.api.Application.CutCopyMode = False

Upvotes: 3

Related Questions