Archive

How To Set Cell Borders Using xlwings?

Method

Border settings 

`Index` can be one of the following `XlBordersIndex` constants: xlDiagonalDown、xlDiagonalUp、xlEdgeBottom、xlEdgeLeft、xlEdgeRight、xlEdgeTop、xlInsideHorizontal or xlInsideVertical。

 

#xlwings API

sht.api.Range(‘B2’).CurrentRegion.Borders.LineStyle=xw.constants.LineStyle.xlContinuous

#sht.api.Range(‘B2’).CurrentRegion.Borders.ColorIndex=3

#sht.api.Range(‘B2’).CurrentRegion.Borders.Weight=xw.constants.BorderWeight.xlThick

Sample Code

#Cell comments

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings API
sht.api.Range('B2').CurrentRegion.Borders.LineStyle=xw.constants.LineStyle.xlContinuous
sht.api.Range('B2').CurrentRegion.Borders.ColorIndex=3
sht.api.Range('B2').CurrentRegion.Borders.Weight=xw.constants.BorderWeight.xlThick

#bk.close()
#app.kill()

How To Set Cell Background Color Using xlwings?

Method

#xlwings

sht.range(‘A1:E1’).color=(210, 67, 9)

#sht[‘A:A, B2, C5, D7:E9’].color=(100,200,150)

 

#xlwings API

#sht.api.Range(‘A1:E1’).Interior.Color=xw.utils.rgb_to_int((0, 255, 0))

#sht.api.Range(‘A1:E1’).Interior.Color=65280

#sht.api.Range(‘A1:E1’).Interior.ColorIndex=6

#sht.api.Range(‘A1:E1’).Interior.ThemeColor=5

Sample Code

#Background color of the cell

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
#sht.range('A1:E1').color=(210, 67, 9)
sht['A:A, B2, C5, D7:E9'].color=(100,200,150)

#xlwings API
#sht.api.Range('A1:E1').Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range('A1:E1').Interior.Color=65280
#sht.api.Range('A1:E1').Interior.ColorIndex=6
#sht.api.Range('A1:E1').Interior.ThemeColor=5

#bk.close()
#app.kill()

How To Set Cell Alignment Using xlwings?

Method

#Setting the alignments of cell C3

sht.api.Range(‘C3’).HorizontalAlignment = xw.constants.Constants.xlCenter 

#sht.api.Range(‘C3’).VerticalAlignment = xw.constants.Constants.xlCenter

sht.api.Range(‘C3′).Value=’Test1’

Sample Code

#Cell Alignment

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#Setting the alignments of cell C3
sht.api.Range('C3').HorizontalAlignment = xw.constants.Constants.xlCenter  
sht.api.Range('C3').VerticalAlignment = xw.constants.Constants.xlCenter
sht.api.Range('C3').Value='Test1'

#bk.close()
#app.kill()

How To Set Cell Font Using xlwings?

Method

#xlwings API

sht.api.Range(‘A1:E1’).Font.Name = ‘Arial’       #Set the font to Arial

sht.api.Range(‘A1:E1’).Font.ColorIndex = 3       #Set the font color to red

sht.api.Range(‘A1:E1’).Font.Size = 20       #Set the font size to 20

sht.api.Range(‘A1:E1’).Font.Bold = True      #Set the font to bold

sht.api.Range(‘A1:E1’).Font.Italic = True      #Set the font to italic

sht.api.Range(‘A1:E1’).Font.Underline=xw.constants.UnderlineStyle.xlUnderlineStyleDouble       #Add a double underline to the text

sht.api.Range(‘C1′).Value=’Test123’

 

sht.api.Range(‘A3:E3’).Font.Color =xw.utils.rgb_to_int((0, 0, 255))

#sht.api.Range(‘A3:E3’).Font.Color =16711680    # or 0x0000FF

#sht.api.Range(‘A1:E1’).Font.ColorIndex = 3

#sht.api.Range(‘A3:E3’).Font.ThemeColor =5

sht.api.Range(‘C3′).Value=’Test456’

Sample Code

#Cell Font Styles

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings API
sht.api.Range('A1:E1').Font.Name = 'Arial'       #Set the font to Arial
sht.api.Range('A1:E1').Font.ColorIndex = 3       #Set the font color to red
sht.api.Range('A1:E1').Font.Size = 20       #Set the font size to 20
sht.api.Range('A1:E1').Font.Bold = True      #Set the font to bold
sht.api.Range('A1:E1').Font.Italic = True      #Set the font to italic
sht.api.Range('A1:E1').Font.Underline=xw.constants.UnderlineStyle.xlUnderlineStyleDouble       #Add a double underline to the text
sht.api.Range('C1').Value='Test123'

sht.api.Range('A3:E3').Font.Color =xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range('A3:E3').Font.Color =16711680    # 或0x0000FF
#sht.api.Range('A1:E1').Font.ColorIndex = 3
#sht.api.Range('A3:E3').Font.ThemeColor =5
sht.api.Range('C3').Value='Test456'

#bk.close()
#app.kill()

How To Insert Cell Comments Using xlwings?

Method

#xlwings API

sht.api.Range(‘A3′).AddComment(Text=’Cell comments’)

 

#Check if cell A3 has a comment

if sht.api.Range(‘A3’).Comment is None:

print(‘Cell A3 has no comment.’)

else:

print(‘Cell A3 has a comment.’)

 

#Hide a comment in cell A3

sht.api.Range(‘A3’).Comment.Visible=False

 

#Delete a comment in cell A3

sht.api.Range(‘A3’).Comment.Delete()

Sample Code

#Cell comments

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings API
sht.api.Range('A3').AddComment(Text='Cell comments')

#Check if cell A3 has a comment
if sht.api.Range('A3').Comment is None:
    print('Cell A3 has no comment.')
else:
    print('Cell A3 has a comment.')

#Hide a comment in cell A3
sht.api.Range('A3').Comment.Visible=False

#Delete a comment in cell A3
sht.api.Range('A3').Comment.Delete()

#bk.close()
#app.kill()

How To Define Cell Names Using xlwings?

Method

Cells

#xlwings

cl=sht.cells(3,3)

#cl.name=’test’

#sht.range(‘test’).select()

 

#xlwings API

#cl=sht.api.Range(‘C3’)

#cl.Name=’test’

#sht.api.Range(‘test’).Select()

 

Cell ranges

#xlwings

#cl =sht.range(‘A3:C8’)

#cl.name =’MyData’

#sht.range(‘MyData’).select()

 

#xlwings API

#cl=sht.api.Range(‘A3:C8’)

#cl.Name =’MyData’

#sht.api.Range(‘MyData’).Select()

Sample Code

#Cell Names

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#Cells
#xlwings
cl=sht.cells(3,3)
cl.name='test'
sht.range('test').select()

#xlwings API
#cl=sht.api.Range('C3')
#cl.Name='test'
#sht.api.Range('test').Select()

#Cell ranges
#xlwings
#cl =sht.range('A3:C8')
#cl.name ='MyData'
#sht.range('MyData').select()

#xlwings API
#cl=sht.api.Range('A3:C8')
#cl.Name ='MyData'
#sht.api.Range('MyData').Select()

#bk.close()
#app.kill()

How To Copy/Paste/Cut and Delete Cells Using xlwings?

Method

RangeObject.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

xlPasteColumnWidths    Copy column widths

Name	Value	Description
xlPasteSpecialOperationAdd	2	Data will be added to the target cell’s value
xlPasteSpecialOperationDivide	5	Data will divide the value in the target cell
xlPasteSpecialOperationMultiply	4	Data will multiply with the value in the target cell
xlPasteSpecialOperationNone	-4142	No operation performed during paste
xlPasteSpecialOperationSubtract	3	Data will subtract from the target cell’s value

Copy

#xlwings API

sht.api.Range(‘C2’).Copy(sht.api.Range(‘G2’))

#sht.api.Range(‘B2’).CurrentRegion.Copy(sht.api.Range(‘G2’))

 

Special Paste options

#sht.api.Range(‘B2:D5’).Copy()

#sht.api.Range(‘G2:I5’).PasteSpecial(Paste=xw.constants.PasteType.xlPasteValues)

 

Comments

#sht.api.Range(‘B2:D5’).Copy()

#sht.api.Range(‘G2:I5’).PasteSpecial(Paste=xw.constants.PasteType.xlPasteComments)

 

Formatting

#sht.api.Range(‘B2:D5’).Copy()

#sht.api.Range(‘A6:E6’).PasteSpecial(Paste=xw.constants.PasteType.xlPasteFormats)

 

Cutting cells

#xlwings API

#sht.api.Range(‘B2:D5’).Cut(Destination=sht.api.Range(‘G2’))

#sht.api.Range(‘B2:D5’).Cut(sht.api.Range(‘G2’))

 

Deleting cells

#xlwings

#sht[‘C2′].delete(shift=’up’)

#sht[‘B2:D5’].delete()

 

#xlwings API

#sht.api.Range(‘C2’).Delete(Shift=xw.constants.DeleteShiftDirection.xlShiftToUp)

#sht.api.Range(‘B2:D5’).Delete()

Sample Code

#Copying/Pasting/Cutting and Deleting Cells

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.open('special.xlsx')
sht=bk.sheets.active    #Get the active worksheet

#xlwings API
sht.api.Range('C2').Copy(sht.api.Range('G2'))
#sht.api.Range('B2').CurrentRegion.Copy(sht.api.Range('G2'))

#Special Paste options
#sht.api.Range('B2:D5').Copy()
#sht.api.Range('G2:I5').PasteSpecial(Paste=xw.constants.PasteType.xlPasteValues)

#Comments 
#sht.api.Range('B2:D5').Copy()
#sht.api.Range('G2:I5').PasteSpecial(Paste=xw.constants.PasteType.xlPasteComments)

#Formatting
#sht.api.Range('B2:D5').Copy()
#sht.api.Range('A6:E6').PasteSpecial(Paste=xw.constants.PasteType.xlPasteFormats)

#Cutting cells
#xlwings API
#sht.api.Range('B2:D5').Cut(Destination=sht.api.Range('G2'))
#sht.api.Range('B2:D5').Cut(sht.api.Range('G2'))

#Deleting cells
#xlwings
#sht['C2'].delete(shift='up')
#sht['B2:D5'].delete()

#xlwings API
#sht.api.Range('C2').Delete(Shift=xw.constants.DeleteShiftDirection.xlShiftToUp)
#sht.api.Range('B2:D5').Delete()

#bk.close()
#app.kill()

How To Select and Clear Cells (Ranges) Using xlwings?

Method

Select

#xlwings

sht.range(‘A1:B10’).select()

 

#xlwings API

#sht.api.Range(‘A1:B10’).Select()

#sht.api.Range(‘A1:B10’).Activate()

 

Select non-contiguous cell ranges

#xlwings

#sht.range(‘A1:A5,C3,E1:E5’).select()

 

#xlwings API

#sht.api.Range(‘A1:A5,C3,E1:E5’).Activate()

#sht.api.Range(‘A1:A5,C3,E1:E5’).Select()

 

Clear all content from a cell range

#xlwings

#sht.range(‘B2:D5′).clear()

 

#xlwings API

#sht.api.Range(‘B2:D5′).Clear()

 

Clear text content of a cell

#xlwings

#sht.range(‘B2:D5′).clear_contents()

 

#xlwings API

#sht.api.Range(‘B2:D5′).ClearContents()

 

Clear cell comments

#xlwings API

#sht.api.Range(‘B2:D5′).ClearComments()

 

Clear cell formatting

#xlwings API

#sht.api.Range(‘B2:D5′).ClearFormats()

Sample Code

#Select and clear cells 

import xlwings as xw  #Import the xlwings package

app=xw.App()
bk=app.books.open('special.xlsx')
sht=bk.sheets.active  #Get the active worksheet

#Select
#xlwings
#sht.range('A1:B10').select()

#xlwings API
#sht.api.Range('A1:B10').Select()
#sht.api.Range('A1:B10').Activate()

#Select non-contiguous cell ranges
#xlwings
#sht.range('A1:A5,C3,E1:E5').select()

#xlwings API
#sht.api.Range('A1:A5,C3,E1:E5').Activate()
#sht.api.Range('A1:A5,C3,E1:E5').Select()

#Clear all content from a cell range
#xlwings
#sht.range('B2:D5').clear()

#xlwings API
sht.api.Range('B2:D5').Clear()

#Clear text content of a cell
#xlwings
#sht.range('B2:D5').clear_contents()

#xlwings API
#sht.api.Range('B2:D5').ClearContents()

#Clear cell comments
#xlwings API
#sht.api.Range('B2:D5').ClearComments()

#Clear cell formatting
#xlwings API
#sht.api.Range('B2:D5').ClearFormats()

#bk.close()
#app.kill()

How To Insert Cells or Ranges Using xlwings?

Method

RangeObject.Insert(Shift, CopyOrigin)

 

Shift:

xlShiftDown

xlShiftToRight

CopyOrigin:

xlFormatFromLeftOrAbove

xlFormatFromRightOrBelow

 

#xlwings

sht.range(‘C3‘).insert(shift=’down’,copy_origin=’format_from_left_or_above’)

sht.range(‘B3:C5′).insert()

 

#xlwings API

#sht.api.Range(‘C3‘).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown, CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)

#sht.api.Range(‘B3:C5′).Insert()

Sample Code

#Inserting Cells or Ranges

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.open('insert.xlsx')
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.range('C3').insert(shift='down',copy_origin='format_from_left_or_above')
#sht.range('B3:C5').insert()

#xlwings API
#sht.api.Range('A2').Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown, CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)
#sht.api.Range('B4:C5').Insert()

#bk.close()
#app.kill()

How To Get Rows/Columns/Top-Left/Bottom-Right/Shape/Size of a Range Using xlwings?

Method

Row count and column count

#xlwings

sht.used_range.rows.count

#sht.used_range.columns.count

 

#xlwings API

#sht.api.UsedRange.Rows.Count

#sht.api.UsedRange.Columns.Count

 

Top-left corner coordinates

#xlwings

#sht.used_range.row

#sht.used_range.column

 

#xlwings API

#sht.api.UsedRange.Row

#sht.api.UsedRange.Column

 

Bottom-right corner coordinates

#xlwings

#sht.used_range.last_cell.row

#sht.used_range.last_cell.column

 

#xlwings API

#rng=sht.api.UsedRange

#rng.Rows(rng.Rows.Count).Row

#rng.Columns(rng.Columns.Count).Column

 

Shape

#xlwings

#sht.used_range.shape

 

Size

#xlwings

#sht.used_range.size

Sample Code

#Properties of Cell Ranges

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.open('current.xlsx')
sht=bk.sheets.active    #Get the active worksheet

#Row count and column count
#xlwings
sht.used_range.select()
print(sht.used_range.rows.count)
#sht.used_range.columns.count

#xlwings API
#sht.api.UsedRange.Rows.Count
#sht.api.UsedRange.Columns.Count

#Top-left corner coordinates
#xlwings
#sht.used_range.row
#sht.used_range.column

#xlwings API
#sht.api.UsedRange.Row
#sht.api.UsedRange.Column

#Bottom-right corner coordinates
#xlwings
#sht.used_range.last_cell.row
#sht.used_range.last_cell.column

#xlwings API
#rng=sht.api.UsedRange
#rng.Rows(rng.Rows.Count).Row
#rng.Columns(rng.Columns.Count).Column

#Shape
#xlwings
print(sht.used_range.shape)

#Size
#xlwings
print(sht.used_range.size)

#bk.close()
#app.kill()