Blog
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()How To Reference Special Cells Using xlwings?
Method
#xlwings API
sht.api.Range(‘A1’).CurrentRegion.SpecialCells(xw.constants.CellType.xlCellTypeBlanks).Select()
sht.api.Range(‘B2’).CurrentRegion.SpecialCells(\
xw.constants.CellType.xlCellTypeBlanks).Select()
#sht.api.Range(‘B2’).CurrentRegion.SpecialCells(\
# xw.constants.CellType.xlCellTypeConstants).Select()
#sht.api.Range(‘B2’).CurrentRegion.SpecialCells(\
# xw.constants.CellType.xlCellTypeFormulas).Select()
#xlCellTypeComments #Cells with annotations
#xlCellTypeConstants #Cells with data
#xlCellTypeFormulas #Cells with formatting
RangeObject.SpecialCells(Type,Value)
|
Name |
Value |
Description |
|
xlCellTypeAllFormatConditions |
-4172 |
Cells with any format conditions |
|
xlCellTypeAllValidation |
-4174 |
Cells with validation conditions |
|
xlCellTypeBlanks |
4 |
Empty cells |
|
xlCellTypeComments |
-4144 |
Cells with comments |
|
xlCellTypeConstants |
2 |
Cells containing constants |
|
xlCellTypeFormulas |
-4123 |
Cells containing formulas |
|
xlCellTypeLastCell |
11 |
The last cell in the used range |
|
xlCellTypeSameFormatConditions |
-4173 |
Cells with the same format conditions |
|
xlCellTypeSameValidation |
-4175 |
Cells with the same validation |
|
xlCellTypeVisible |
12 |
All visible cells |
Sample Code
#Reference special cells
import xlwings as xw #Import the xlwings package
app=xw.App()
bk=app.books.open('special.xlsx') #Get the active workbook
sht=bk.sheets.active #Get the active worksheet
#xlwings API
sht.api.Range('B2').CurrentRegion.SpecialCells(\
xw.constants.CellType.xlCellTypeBlanks).Select()
#sht.api.Range('B2').CurrentRegion.SpecialCells(\
# xw.constants.CellType.xlCellTypeConstants).Select()
#sht.api.Range('B2').CurrentRegion.SpecialCells(\
# xw.constants.CellType.xlCellTypeFormulas).Select()
#xlCellTypeComments #Cells with annotations
#xlCellTypeConstants #Cells with data
#xlCellTypeFormulas #Cells with formatting
#bk.close()
#app.kill()
How To Reference the Last Row or Column Using xlwings?
Method
Last row
#xlwings
sht.range(‘A1’).end(‘down’).select()
sht.range(‘A1’).end(‘down’).row
#sht.cells(1,1).end(‘down’).row
#sht.range(‘A’+str(sht.api.Rows.Count)).end(‘up’).row
#sht.cells(sht.api.Rows.Count,1).end(‘up’).row
#xlwings API
#sht.api.Range(‘A1’).End(xw.constants.Direction.xlDown).Row
#sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row
#sht.api.Range(‘A’+str(sht.api.Rows.Count)).End(xw.constants.Direction.xlUp).Row
#sht.api.Cells(sht.api.Rows.Count,1).End(xw.constants.Direction.xlUp).Row
Last column
#xlwings
#sht.range(‘A1’).end(‘right’).column
#sht.cells(1,1).end(‘right’).column
#sht.cells(1,sht.api.Columns.Count).end(‘left’).column
#xlwings API
#sht.api.Range(‘A1’).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,1).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,sht.api.Columns.Count).End(xw.constants.Direction.xlToLeft).Column
Sample Code
#Referencing the Last Row or Column
import xlwings as xw #Import the xlwings package
app=xw.App()
bk=app.books.open('last.xlsx') #Get the active workbook
sht=bk.sheets.active #Get the active worksheet
#Last row
#xlwings
sht.range('A1').end('down').select()
print(sht.range('A1').end('down').row)
#sht.cells(1,1).end('down').row
#sht.range('A'+str(sht.api.Rows.Count)).end('up').row
#sht.cells(sht.api.Rows.Count,1).end('up').row
#xlwings API
#sht.api.Range('A1').End(xw.constants.Direction.xlDown).Row
#sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row
#sht.api.Range('A'+str(sht.api.Rows.Count)).End(xw.constants.Direction.xlUp).Row
#sht.api.Cells(sht.api.Rows.Count,1).End(xw.constants.Direction.xlUp).Row
#Last column
#xlwings
#sht.range('A1').end('right').column
#sht.cells(1,1).end('right').column
#sht.cells(1,sht.api.Columns.Count).end('left').column
#xlwings API
#sht.api.Range('A1').End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,1).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,sht.api.Columns.Count).End(xw.constants.Direction.xlToLeft).Column
#bk.close()
#app.kill()
How To Expand the Reference to Cell Ranges in the Current Worksheet Using xlwings?
Method
#xlwings
sht.range(‘C2’).resize(3).select() #Create a cell range C2:C4
#sht.range(‘C2’).resize(1, 3).select() #Create a cell range C2:E2
#sht.range(‘C2’).resize(3, 3).select() #Create a cell range C2:E4
#expand method
#sht.range(‘C4’).expand(‘table’).select()
#sht.range(‘C4’).expand().select() #Equivalent to the method above
#sht.range(‘C4’).expand(‘down’).select()
#sht.range(‘C4’).expand(‘right’).select()
#xlwings API
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(3)).Select()
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(1, 3)).Select()
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(3, 3)).Select()
Sample Code
#Expand the reference to the current worksheet's cell range
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('C2').resize(3).select() #Create a cell range C2:C4
#sht.range('C2').resize(1, 3).select() #Create a cell range C2:E2
#sht.range('C2').resize(3, 3).select() #Create a cell range C2:E4
#expand method
#sht.range('C4').expand('table').select()
#sht.range('C4').expand().select() #Equivalent to the method above
#sht.range('C4').expand('down').select()
#sht.range('C4').expand('right').select()
#xlwings API
#sht.api.Range('C2', sht.api.Range('C2').Resize(3)).Select()
#sht.api.Range('C2', sht.api.Range('C2').Resize(1, 3)).Select()
#sht.api.Range('C2', sht.api.Range('C2').Resize(3, 3)).Select()
#bk.close()
#app.kill()
How To Reference a Set of Cell Ranges Using xlwings?
Method
#xlwings API
#Union of two cell ranges
app.api.Union(sht.api.Range(‘B4:D8’), sht.api.Range(‘C2:F5’)).Select()
#Intersect of two cell ranges
#app.api.Intersect(sht.api.Range(‘B4:D8’), sht.api.Range(‘C2:F5’)).Select()
Sample Code
#Set Operations of 2 Cell Ranges
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
#Union of two cell ranges
#app.api.Union(sht.api.Range('B4:D8'), sht.api.Range('C2:F5')).Select()
#Intersect of two cell ranges
app.api.Intersect(sht.api.Range('B4:D8'), sht.api.Range('C2:F5')).Select()
#bk.close()
#app.kill()