Show Menu
Cheatography
  1. Home >
  2. Programming >

XLWings Cheat Sheet (DRAFT) by

This cheat sheet is for common commands to integrate XLWings into python.

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Imports

import xlwings
import Pandas as pd

Workbook and Sheet Functions

wb = xw.Boo­k('­TSL­A.x­lsx') #Connect to a workbook
wb.sheets #Show sheets in the workbook
wb.she­et[0] #Access a sheet by integer
wb.she­et[­'sh­eet­_name'] #Access a sheet by name
wb.she­ets.ad­d('­new') #Add a sheet
wb.__d­eli­tem­__(­'Sheet Name to Delete') #Delete a sheet

Read and Write into an Excel file

wb.ran­ge(­'A1­').v­alue #Return the value from cell
wb.ran­ge(­'A1­:C3­').v­alue #Return values from a range
wb.ran­ge(­'A1­').e­xp­and­('r­ight') #Returns all values in a row
wb.ran­ge(­'A1­').e­xp­and­('d­own') #Returns all values in a column
wb.ran­ge(­'A1­').v­alue = 'New Value' #assign a new value to a cell horizo­ntally
wb.ran­ge(­'A1­').o­pt­ion­s(t­ran­spo­se=­Tru­e).v­alue = [1,2,3] #Fill values to a range of cells up vertically
wb.ran­ge(­'A1­').v­alue is none #Return boolean
wb.ran­ge(­'A1­').e­xp­and­('r­igh­t').op­tio­ns(­pd.D­at­aFr­ame­).value #Create a dataframe from a row
wb.ran­ge(­'A1­:C3­').o­pt­ion­s(p­d.D­ata­Fra­me).value #Create a dataframe from a range
wb.ran­ge(­'A1­').e­xp­and­().o­pt­ion­s(p­d.D­ata­Fra­me).value #Create a dataframe for all data
Alternate to Range
wb[‘A1­’).v­alue
wb[‘B4­’].name = ‘Cell reference name’ # Creates a named reference
 

Plotting

import matplotlib as plt #import plotting libary
fig = df['Adj Close'].plot
plt.xlabel('Date')
plt.ylabel('$ price')
plt.title('TSLA Price')
plt.show()
tsla_fig = fig.ge­t_f­igure() #Save the figure
xw.she­ets.ac­tiv­e.p­ict­ure­s.a­dd(­tsl­a_fig, name='­TSL­A',­upd­ate­=True) # copy the figure to Excel

Values

tsla_s­ht.r­an­ge(­'A1­').v­alue = '$12' #This is a text value
price = tsla_sht.range('A1').value
price = float(­price) #convert text into float
price + 2.5 #You can now perform comput­ation in the float

Saving Workbooks

wb.sav­e('­tsa­l_n­ew.x­lsx') #Save as a new Excel file
app = xw.apps.activate
app.quit() #quit the active app

Saving Workbooks

wb.sav­e('­tsa­l_n­ew.x­lsx') #Save as a new Excel file
app = xw.apps.activate
app.quit() #quit the active app
 

Add Style

Store RGB Values
white = (255,2­55,255) #RGB for white
blue = (31,73­,125) #RGB for Blue
yellow = (155,1­92,0) #RGB for yellow
Store Cell Range
rowHeader = tsla.range('B1:D1')
colHeader = tsla.range('A2:A4')
dataCells = tsla.range('B2:D4')
Set Cell Color
On Single Cell
rowHea­der.Color = blue
colHeader.Color = blue
On a Range

dataCells.Color = yellow'
Set Font Color
rowHea­der.ap­i.F­ont.Color = white
colHeader.api.Font.Color = white
Set Font Bold
rowHea­der.ap­i.F­ont.Bold = True
colHeader.api.Font.Bold = True
Set Cell Border on a Cell
B6 = tsla.range('B6')
C6 = tsla.range('C6')
D6 = tsla.range('D6')
E6 = tsla.range('E6')
B6.api.Borders(1).Weight=3 #Left border
C6.api.Borders(2).Weight=3 #Right border
B6.api.Borders(3).Weight=3 #Top border
B6.api.Borders(4).Weight=3 #Bottom border
Set Border on a Range
dataçells.api.Borders(1).Weight=3
dataçells.api.Borders(2).Weight=3
dataçells.api.Borders(3).Weight=3
dataçells.api.Borders(4).Weight=3
Center Text
center = xw.constants.HAligh.xlHAlignCenter
dataCells.api.HorizontalAlignment=center