Archive

How To Set Row Height and Column Width Using xlwings?

Method

Row height

#xlwings API

sht.api.Rows(3).RowHeight = 30

#sht.api.Range(‘C5’).EntireRow.RowHeight = 40

#sht.api.Range(‘C5’).RowHeight = 40

#sht.api.Cells.RowHeight = 30

Column width

#sht.api.Columns(2).ColumnWidth = 20

#sht.api.Range(‘C4’).ColumnWidth = 15

#sht.api.Range(‘C4’).EntireColumn.ColumnWidth = 15

#sht.api.Cells.ColumnWidth = 10

Sample Code

#Setting Row Heights and Column Widths

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

#Row height
#xlwings API
sht.api.Rows(3).RowHeight = 30
#sht.api.Range('C5').EntireRow.RowHeight = 40
#sht.api.Range('C5').RowHeight = 40
#sht.api.Cells.RowHeight = 30

#Column width
sht.api.Columns(2).ColumnWidth = 20
#sht.api.Range('C4').ColumnWidth = 15
#sht.api.Range('C4').EntireColumn.ColumnWidth = 15
#sht.api.Cells.ColumnWidth = 10

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

How To Delete Rows and Columns Using xlwings?

Method

Delete empty rows

#xlwings API

sht.api.Columns(‘A:A’).SpecialCells(xw.constants.CellType.xlCellTypeBlanks).EntireRow.Delete()

Delete duplicate rows

#a=sht.cells(sht.api.Rows.Count, 1).end(‘up’).row

#for i in range(a,1,-1):

#    if app.api.WorksheetFunction.CountIf(sht.api.Columns(1), sht.api.Cells(i,1))>1:

#        sht.api.Rows(i).Delete()

Sample Code

#Delete rows

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
sht.range('a1').value='abd'
sht.range('a3').value='efg'
sht.range('a4').value='efg'

#Delete empty rows
#xlwings API
#sht.api.Columns('A:A').SpecialCells(xw.constants.CellType.xlCellTypeBlanks).EntireRow.Delete()

#Delete duplicate rows
a=sht.cells(sht.api.Rows.Count, 1).end('up').row
for i in range(a,1,-1):
    if app.api.WorksheetFunction.CountIf(sht.api.Columns(1), sht.api.Cells(i,1))>1:
        sht.api.Rows(i).Delete()

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

How To Insert Rows and Columns Using xlwings?

Method

Row

#xlwings

sht.range(‘A2’).color=(0,255,0)

#sht.range(‘C2’).color=(0,0,255)

#sht.range(‘E2’).color=(255,0,0)

#sht[‘3:3′].insert(shift=’down’,copy_origin=’format_from_left_or_above’)

 

#xlwings API

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

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

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

#sht.api.Rows(3).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown,CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)

 

Column

#xlwings

#sht[‘B:B’].insert()

 

#xlwings API

#sht.api.Columns(2).Insert()

Sample Code

# Insert rows and columns

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

#Row
#xlwings
sht.range('A2').color=(0,255,0)
sht.range('C2').color=(0,0,255)
sht.range('E2').color=(255,0,0)
sht['3:3'].insert(shift='down',copy_origin='format_from_left_or_above')

#xlwings API
#sht.api.Range('A2').Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range('C2').Interior.Color=xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range('E2').Interior.Color=xw.utils.rgb_to_int((255, 0, 0))
#sht.api.Rows(3).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown,CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)

#Column
#xlwings
#sht['B:B'].insert()

#xlwings API
#sht.api.Columns(2).Insert()

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

How To Copy/Cut Rows and Columns Using xlwings?

Method

Row

#xlwings API

sht.api.Rows(‘2:2’).Copy()

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

#sht.api.Paste()

 

#sht.api.Rows (‘2:3’).Select()

#bk.selection.api.Cut()

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

#sht.api.Paste()

 

Column

#sht.api.Columns(‘A:A’).Copy()

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

#sht.api.Paste()

 

#sht.api.Columns(‘B:C’).Select()

#bk.selection.api.Cut()

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

#sht.api.Paste()

Sample Code

#Copying/Cutting Rows and Columns 

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

#Row
#xlwings API
sht.api.Rows('2:2').Copy()
sht.api.Range('A7').Select()
sht.api.Paste()

#sht.api.Rows ('2:3').Select()
#bk.selection.api.Cut()
#sht.api.Range('A7').Select()
#sht.api.Paste()

#Column
#sht.api.Columns('A:A').Copy()
#sht.api.Range('E1').Select()
#sht.api.Paste()

#sht.api.Columns('B:C').Select()
#bk.selection.api.Cut()
#sht.api.Range('F1').Select()
#sht.api.Paste()

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

How To Select Rows and Columns Using xlwings?

Method

#Select a single row

#xlwings

sht[‘1:1’].select()

 

#xlwings API

#sht.api.Rows(1) .Select()

#sht.api.Range(‘1:1’).Select()

#sht.api.Range(‘a1’).EntireRow.Select()

 

#Select multiple consecutive rows

#xlwings

#sht[‘1:5’].select()

#sht[0:5,:].select()

 

#xlwings API

#sht.api.Rows(‘1:5’).Select()

#sht.api.Range(‘1:5’).Select()

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

 

#Select multiple non-consecutive rows

#xlwings

#sht.range(‘1:5,7:10’).select()

 

#xlwings API

#sht.api.Range(‘1:5,7:10’).Select()

 

#————————-

 

#Select a single column

#xlwings

#sht.range(‘A:A’).select()

 

#xlwings API

#sht.api.Columns(1).Select()

#sht.api.Columns(‘A’).Select()

#sht.api.Range(‘A:A’).Select()

#sht.api.Range(‘A1’).EntireColumn.Select()

 

#Select multiple consecutive columns

#xlwings

#sht.range(‘B:C’).select()

#sht[:,1:3].select()

 

#xlwings API

#sht.api.Columns(‘B:C’).Select()

#sht.api.Range(‘B:C’).Select()

#sht.api.Range(‘B1:C2’).EntireColumn.Select()

 

#Select multiple non-consecutive columns

#xlwings

#sht.range(‘C:E,G:I’).select()

 

#xlwings API

#sht.api.Range(‘C:E,G:I’).Select()

Sample Code

#Selecting Rows and Columns

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

#Select a single row
#xlwings
sht['1:1'].select()

#xlwings API
#sht.api.Rows(1) .Select()
#sht.api.Range('1:1').Select()
#sht.api.Range('a1').EntireRow.Select()

#Select multiple consecutive rows
#xlwings
#sht['1:5'].select()
#sht[0:5,:].select()

#xlwings API
#sht.api.Rows('1:5').Select()
#sht.api.Range('1:5').Select()
#sht.api.Range('A1:A5').EntireRow.Select()

#Select multiple non-consecutive rows
#xlwings
#sht.range('1:5,7:10').select()

#xlwings API
#sht.api.Range('1:5,7:10').Select()

#-------------------------

#Select a single column
#xlwings
#sht.range('A:A').select()

#xlwings API
#sht.api.Columns(1).Select()
#sht.api.Columns('A').Select()
#sht.api.Range('A:A').Select()
#sht.api.Range('A1').EntireColumn.Select()

#Select multiple consecutive columns
#xlwings
#sht.range('B:C').select()
#sht[:,1:3].select()

#xlwings API
#sht.api.Columns('B:C').Select()
#sht.api.Range('B:C').Select()
#sht.api.Range('B1:C2').EntireColumn.Select()

#Select multiple non-consecutive columns
#xlwings
#sht.range('C:E,G:I').select()

#xlwings API
#sht.api.Range('C:E,G:I').Select()

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

How To Hide and Show Worksheets Using xlwings?

Method

Normal hide

#xlwings API

bk.api.Sheets(‘Sheet1’).Visible = False

#bk.api.Sheets(‘Sheet1’).Visible = xw.constants.SheetVisibility.xlSheetHidden

#bk.api.Sheets(‘Sheet1’).Visible = 0

 

Deep hide

#xlwings API

#bk.api.Sheets(‘Sheet1’).Visible=xw.constants.SheetVisibility.xlSheetVeryHidden

#bk.api.Sheets(‘Sheet1’).Visible=2

 

Unhide

#xlwings

#bk.sheets(‘Sheet1’).visible = True

#bk.sheets(‘Sheet1’).visible = 1

 

#xlwings API

#bk.api.Sheets(‘Sheet1’).Visible = True

#bk.api.Sheets(‘Sheet1’).Visible = xw.constants.SheetVisibility.xlSheetVisible

#bk.api.Sheets(‘Sheet1’).Visible = 1

#bk.api.Sheets(‘Sheet1’).Visible = -1

Sample Code

#Hiding and Showing Worksheets

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
sht2=bk.sheets.add()

#Normal hide
#xlwings API
#bk.api.Sheets('Sheet1').Visible = False 
#bk.api.Sheets('Sheet1').Visible = xw.constants.SheetVisibility.xlSheetHidden
#bk.api.Sheets('Sheet1').Visible = 0

#Deep hide
#xlwings API
bk.api.Sheets('Sheet1').Visible=xw.constants.SheetVisibility.xlSheetVeryHidden
#bk.api.Sheets('Sheet1').Visible=2

#Unhide
#xlwings
bk.sheets('Sheet1').visible = True
#bk.sheets('Sheet1').visible = 1

#xlwings API
#bk.api.Sheets('Sheet1').Visible = True
#bk.api.Sheets('Sheet1').Visible = xw.constants.SheetVisibility.xlSheetVisible
#bk.api.Sheets('Sheet1').Visible = 1
#bk.api.Sheets('Sheet1').Visible = -1

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

How To Activate, Copy, Move, and Delete Worksheets Using xlwings?

Method

Activate a worksheet

#xlwings

bk.sheets[1].activate()

#bk.sheets[1].select()

 

#xlwings API

#bk.api.Worksheets(2).Activate()

#bk.api.Worksheets(2).Select()

 

Copy worksheets

#xlwings API

#bk.api.Sheets(‘Sheet1’).Copy(Before=bk.api.Sheets(‘Sheet2’))

#bk.api.Sheets(‘Sheet1’).Copy(After=bk.api.Sheets(‘Sheet2’))

 

Copy across workbooks

#bk.api.Sheets(‘Sheet1’).Copy(Before=bk2.api.Sheets(‘Sheet2’))

#bk.api.Sheets(‘Sheet1’).Copy(After=bk2.api.Sheets(‘Sheet2’))

 

Move worksheets

#xlwings API

#bk.api.Sheets(‘Sheet1’).Move(Before=bk.api.Sheets(‘Sheet3’))

#bk.api.Sheets(‘Sheet1’).Move(After=bk.api.Sheets(‘Sheet3’))

#bk.Sheets([‘Sheet2’, ‘Sheet3’]).Move(Before=bk.Sheets(1))

 

Delete worksheets

#xlwings

#bk.sheets(‘Sheet1’).delete()

#bk.sheets([‘Sheet2’, ‘Sheet3’]).delete()

 

#xlwings API

#bk.api.Sheets(‘Sheet1’).Delete()

#bk.api.Sheets([‘Sheet2’, ‘Sheet3’]).Delete()

Sample Code

#Activating, Copying, Moving, and Deleting Worksheets

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active
sht2=bk.sheets.add()
sht3=bk.sheets.add()
sht4=bk.sheets.add()

#Activate a worksheet
#xlwings
bk.sheets[1].activate()
#bk.sheets[1].select()

#xlwings API
#bk.api.Worksheets(2).Activate()
#bk.api.Worksheets(2).Select()

#Copy worksheets 
#xlwings API
#bk.api.Sheets('Sheet1').Copy(Before=bk.api.Sheets('Sheet2'))
#bk.api.Sheets('Sheet1').Copy(After=bk.api.Sheets('Sheet2'))

#Copy across workbooks
#bk.api.Sheets('Sheet1').Copy(Before=bk2.api.Sheets('Sheet2'))
#bk.api.Sheets('Sheet1').Copy(After=bk2.api.Sheets('Sheet2'))

#Move worksheets
#xlwings API
#bk.api.Sheets('Sheet1').Move(Before=bk.api.Sheets('Sheet3'))
#bk.api.Sheets('Sheet1').Move(After=bk.api.Sheets('Sheet3'))
#bk.Sheets(['Sheet2', 'Sheet3']).Move(Before=bk.Sheets(1))

#Delete worksheets
#xlwings
#bk.sheets('Sheet1').delete()
#bk.sheets(['Sheet2', 'Sheet3']).delete()

#xlwings API
#bk.api.Sheets('Sheet1').Delete()
#bk.api.Sheets(['Sheet2', 'Sheet3']).Delete()

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

How To Create and Reference Worksheets Using xlwings?

Method

【xlwings】

bk.sheets.add(name=None, before=None, after=None)

【xlwings API】

bk.WorkSheets.Add(Before, After, Count, Type)

 

#xlwings

bk.sheets.add()

#Insert before the 2nd worksheet

#bk.sheets.add(before=bk.sheets(2))

#Insert after the 2nd worksheet

#bk.sheets.add(after=bk.sheets(2))

#Reference a worksheet

#bk.sheets[0]

#bk.sheets(1)

#sht=bk.sheets[‘Sheet1’]

#sht.name=’MySheet’

 

#xlwings API

#bk.api.Worksheets.Add()

#Insert before the 2nd worksheet

#bk.api.Worksheets.Add (Before=bk.api.Worksheets(2))

#Insert 3 worksheets at once, place them at the front

#bk.api.Worksheets.Add(Count=3)

#Create a new chart worksheet

#bk.api.Worksheets.Add(Type=xw.constants.SheetType.xlChart)

 

#Use the Sheets object

#sht=bk.api.Sheets.Add()

#Reference the worksheet

#sht=Worksheets(1)

#sht=Worksheets(‘Sheet1’)

Sample Code

#Creating and Referencing Worksheets

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
bk.sheets.add()
#Insert before the 2nd worksheet
#bk.sheets.add(before=bk.sheets(2))
#Insert after the 2nd worksheet
#bk.sheets.add(after=bk.sheets(2))
#Reference a worksheet 
#bk.sheets[0]
#bk.sheets(1)
#sht=bk.sheets['Sheet1']
#sht.name='MySheet'

#xlwings API
#bk.api.Worksheets.Add()
#Insert before the 2nd worksheet
#bk.api.Worksheets.Add (Before=bk.api.Worksheets(2))
#Insert 3 worksheets at once, place them at the front
#bk.api.Worksheets.Add(Count=3)
#Create a new chart worksheet
#bk.api.Worksheets.Add(Type=xw.constants.SheetType.xlChart)

#Use the Sheets object
#sht=bk.api.Sheets.Add()
#Reference the worksheet
#sht=Worksheets(1) 
#sht=Worksheets('Sheet1') 

bk.close()
app.kill()