Blog

How To Create Rectangles, Rounded Rectangles, Ellipses, and Circles Using xlwings?

Method

Use the `AddShape` method of the Shapes object to create rectangles, rounded rectangles, ellipses, and circles.

sht.api.Shapes.AddShape(1, 50, 50, 100, 200)    #Draw a rectangle face

sht.api.Shapes.AddShape(5, 100, 100, 100, 200)    #Draw a rounded rectangle face

sht.api.Shapes.AddShape(9, 150, 150, 100, 200)    #Draw an ellipse face

sht.api.Shapes.AddShape(9, 200, 200, 100, 100)    #Draw a circle face

shp1=sht.api.Shapes.AddShape(1, 250, 50, 100, 200)    #Draw a rectangle

shp2=sht.api.Shapes.AddShape(5, 300, 100, 100, 200)    #Draw a rounded rectangle

shp3=sht.api.Shapes.AddShape(9, 350, 150, 100, 200)    #Draw an ellipse

shp4=sht.api.Shapes.AddShape(9, 400, 200, 100, 100)    #Draw a circle

shp1.Fill.Visible=False

shp2.Fill.Visible=False

shp3.Fill.Visible=False

shp4.Fill.Visible=False

Sample Code

#Copy and paste formulas

import xlwings as xw    #Import the xlwings package
from xlwings import constants as con
import os    #Import the os package

root = os.getcwd()    #Get the current path
#Create an Excel application window, visible, 
#without opening a workbook  
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula2.xlsx',read_only=False)
sht=bk.api.Sheets(1)    #Get the worksheet

#Copy and paste formulas
sht.Range('B3:E5').Copy()
sht.Range('D7:G9').PasteSpecial(Paste=con.PasteType.xlPasteFormulas)

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

How To Create Line Segments Using xlwings?

Method

Use the `AddLine` method of the Shapes object to create a line segment. The method syntax is:

sht.api.Shapes.AddLine(BeginX, BeginY, EndX, EndY)

Where `sht` refers to a worksheet object. The parameters `BeginX, BeginY` represent the coordinates of the starting point, and `EndX, EndY` represent the coordinates of the endpoint. This method returns a Shape object representing the line segment.

shp=sht.api.Shapes.AddLine(10,10,250,250)    #Create a line segment `Shape` object

ln=shp.Line    #Get the line shape object

#Set properties of the line shape object: line style, color, and width

ln.DashStyle=3

ln.ForeColor.RGB=xw.utils.rgb_to_int((255, 0, 0))

ln.Weight=5

Sample Code

#Check if a specified cell range contains a formula

import xlwings as xw    #Import the xlwings package
import os    #Import the os package

root = os.getcwd()    #Get the current path
#Create an Excel application window, visible, 
#without opening a workbook  
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula2.xlsx',read_only=False)
sht=bk.api.Sheets(1)    #Get the worksheet

#Check if a specified cell range contains a formula
rng=sht.Range('B3:E5')
for cell in rng:
    if cell.HasFormula==True:
        print(cell.Row,cell.Column,'Yes')

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

How To Create Points Using xlwings?

Method

The Shapes object does not provide a specific method to draw points, but it offers several special shapes that can represent points, such as stars, rectangles, circles, diamonds, etc. These AutoShapes can be created using the `AddShape` method of the Shapes object. The method syntax is:

sht.api.Shapes.AddShape(Type, Left, Top, Width, Height)

Using the xlwings package API call. `sht` refers to a worksheet object. This method returns a Shape object. The parameters are as follows:

sht.api.Shapes.AddShape(92,180,80,10,10)

sht.api.Shapes.AddShape(150,150,40,15,15)

sht.api.Shapes.AddShape(96,80,80,3,3)

Sample Code

#Find cells containing formulas

import xlwings as xw    #Import the xlwings package
from xlwings import constants as con
import os    #Import the os package

root = os.getcwd()    #Get the current path
#Create an Excel application window, visible, 
#without opening a workbook  
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula2.xlsx',read_only=False)
sht=bk.api.Sheets(1)    #Get the worksheet

#Find cells containing formulas
rng=sht.Range('B3:E5').SpecialCells(con.CellType.xlCellTypeFormulas)
rng.Select()

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