I am using xlwings in python and I am trying to insert a new row that does not copy formatting from adjacent rows. Currently I have:

ws.range("4:4").api.Insert(InsertShiftDirection.xlShiftToRight)

According to excel docs (https://docs.microsoft.com/en-us/office/vba/api/excel.range.insert) the dafault option for CopyOrigin is xlFormatFromLeftOrAbove and there is no option for no formatting, but it can be achieved using the clear formats method:

With Range("B2:E5")
.Insert xlShiftDown
.ClearFormats

Above is VB code but I'm not sure how to use the ClearFormats method in python, I have tried:

ws.range("4:4").api.Insert(InsertShiftDirection.xlShiftToRight.ClearFormats)

and others ways of adding ClearFormats but cant get it to work. Thanks

CodePudding user response：

Using the xlwings api:

ws = xw.apps.active.books.active.sheets.active
ws.range('8:8').insert(shift='down', copy_origin='format_from_left_or_above')
ws.range('8:8').clear()  # clears content and formatting

• shift - 'down' or 'right'
• copy_origin - 'format_from_left_or_above' or 'format_from_right_or_below'

If you would still like to use the native excel api:

ws = xw.apps.active.books.active.sheets.active
ws.range('I:I').api.Insert(Shift=-4161, CopyOrigin=1) # shift right, format from right or below
ws.range('I:I').api.ClearFormats()

The respective values for Shift and CopyOrigin below:

• Shift param

Name            Value
xlShiftDown     -4121
xlShiftToRight  -4161

• CopyOrigin param

Name                    Value
xlFormatFromLeftOrAbove     0
xlFormatFromRightOrBelow    1