Blog

How To Set Line Properties Using xlwings? – Color, Line Style, and Line Width

Method

In Excel, line objects are represented by the `LineFormat` object. The `Line` property of a `Shape` object returns a `LineFormat` object. For example, a straight line, the borders of a rectangle, and a circular region are all represented as `LineFormat` objects.

 

Once you have the `LineFormat` object, you can use its properties and methods to set various attributes like color, line style, line width, arrows, transparency, and pattern fill.

 

shp=sht.api.Shapes.AddLine(20, 20, 100, 120)

lf=shp.Line

lf.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))    #Red

lf.DashStyle=5    #Line style, dotted line

lf.Weight=3    #Line width

 

shp2=sht.api.Shapes.AddShape(9, 200, 30, 120, 80)

#Line shape in elliptical area, i.e., the boundary of the area

lf2=shp2.Line

lf2.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))    #Red

lf2.DashStyle=3    #Line style, dotted line with circles

lf2.Weight=4    #Line width

Sample Code

#Line properties

import xlwings as xw    #Import xlwings package

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

shp=sht.api.Shapes.AddLine(20, 20, 100, 120)
lf=shp.Line
lf.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))    #Red
lf.DashStyle=5    #Line style, dotted line
lf.Weight=3    #Line width

shp2=sht.api.Shapes.AddShape(9, 200, 30, 120, 80)
#Line shape in elliptical area, i.e., the boundary of the area
lf2=shp2.Line
lf2.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))    #Red
lf2.DashStyle=3    #Line style, dotted line with circles
lf2.Weight=4    #Line width

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

How To Set Color Using xlwings? – Scheme Colors

Method

Excel provides a set of colors from its color scheme that can be used to fill graphical objects. For graphical objects, the `ForeColor` and `BackColor` properties return a `ColorFormat` object, which includes a `SchemeColor` property. Each color in the color scheme has an index number, which can be assigned to the `SchemeColor` property.

 

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)

shp.Fill.ForeColor.SchemeColor=3

shp.Line.ForeColor.SchemeColor=4

Sample Code

#Color - Scheme color

import xlwings as xw    #Import xlwings package

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

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)
shp.Fill.ForeColor.SchemeColor=3
shp.Line.ForeColor.SchemeColor=4

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

How To Set Color Using xlwings? – Theme Colors

Method

 

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)

shp.Fill.ForeColor.ObjectThemeColor=10

shp.Line.ForeColor.ObjectThemeColor=3

Sample Code

#Color - Theme color

import xlwings as xw    #Import xlwings package

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

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)
shp.Fill.ForeColor.ObjectThemeColor=10
shp.Line.ForeColor.ObjectThemeColor=3

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

How To Set Color Using xlwings? – Indexed Coloring

Method

Indexed coloring involves using a predefined set of colors, known as a color lookup table, where each color has a unique index number, as shown in the diagram. To apply indexed coloring, simply assign an index number to the corresponding indexed color property.

 

 

sht.api.Range(“C3”).Font.ColorIndex=3

sht.api.Range(“C3″).Value=”Hello”

Sample Code

#Color - Index color

import xlwings as xw    #Import xlwings package

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

sht.api.Range("C3").Font.ColorIndex=3
sht.api.Range("C3").Value="Hello"

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

How To Set Color Using xlwings? – RGB

Method

RGB color shading defines a color using the red, green, and blue components. You can set RGB color shading using the `Color` property of graphical objects. If you’re accustomed to specifying RGB components to set the color, you can use the `rgb_to_int` method from the `xlwings.utils` class to convert an RGB tuple, such as (255, 0, 0), into an integer, which can then be assigned to the `Color` property.

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)

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

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

shp.Line.ForeColor.RGB=16711680    # or 0x0000FF

Sample Code

#Color - RGB

import xlwings as xw    #Import xlwings package

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

shp=sht.api.Shapes.AddShape(9, 50, 50, 100, 100)
#shp.Fill.ForeColor.RGB=xw.utils.rgb_to_int((0, 255,0))
shp.Line.ForeColor.RGB=xw.utils.rgb_to_int((0,0,255))

shp.Fill.ForeColor.RGB=0x0000FF    # or 16711680

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

How To Create WordArt Using xlwings?

Method

Use the `AddTextEffect` method of the `Shapes` object to create WordArt. The syntax is:

sht.api.Shapes.AddTextEffect(PresetTextEffect,Text,FontName,FontSize,FontBold,FontItalic, Left, Top)

Where `sht` is the current worksheet. The parameters are explained in the table below.

sht.api.Shapes.AddTextEffect(9,’Learn Python’,’Arial Black’,36,False,False,10,10)

sht.api.Shapes.AddTextEffect(29,’xlwings’,’Times New Roman’,40,False,False,30,50)

Sample Code

#Drawing WordArt

import xlwings as xw    #Import xlwings package

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

sht.api.Shapes.AddTextEffect(9,'Learn Python','Arial Black',36,False,False,10,10)
sht.api.Shapes.AddTextEffect(29,'xlwings','Times New Roman',40,False,False,30,50)

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

How To Create Chart Using xlwings?

Method

Use the `AddChart2` method of the `Shapes` object to create a chart. The syntax is: 

sht.api.Shapes.AddChart2(Style, XlChartType, Left, Top, Width, Height, NewLayout)

Where `sht` is the current worksheet. There are 7 parameters, all optional.

• Style：Chart style, -1 represents the default style for the chart type
• xlChartType：Chart type, using values from the `XlChartType` enumeration, a part of which is listed below
• Left：Left position of the chart, centered horizontally if omitted
• Top：Top position of the chart, centered vertically if omitted
• Width：Width of the chart, default is 354 if omitted
• Height：Height of the chart, default is 210 if omitted
• NewLayout：Determines the chart layout. If `True`, legends are shown only for composite charts

Name	Value	Description
xlArea	1	Area chart
xlLine	4	Line chart
xlPie	5	Pie chart
xlBarClustered	57	Clustered bar chart
xlBarStacked	58	Stacked bar chart
xlXYScatter	-4169	XY scatter chart
xlBubble	个	Bubble chart
xlSurface	83	3D Surface chart
……

sht.api.Range(‘A1’).CurrentRegion.Select()

sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,30,150,300,200,True)

Sample Code

#Drawing charts

import xlwings as xw
import os

root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/GDP.xlsx',read_only=False)
sht=wb.sheets(1)

sht.api.Range('B1:H7').Select()
sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,30,150,300,200,True)

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

How To Create AutoShapes Using xlwings?

Method

AutoShapes are predefined shapes in Excel. Use the `AddShape` method of the `Shapes` object to create AutoShapes.

sht.api.Shapes.AddShape(1, 50, 50, 100, 200)

sht.api.Shapes.AddShape(12, 250, 50, 100, 100)

sht.api.Shapes.AddShape(17, 450, 50, 100, 100)

Sample Code

#Drawing shapes

import xlwings as xw    #Import xlwings package

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

sht.api.Shapes.AddShape(1, 50, 50, 100, 200)
sht.api.Shapes.AddShape(12, 250, 50, 100, 100)
sht.api.Shapes.AddShape(17, 450, 50, 100, 100)

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

How To Create Callout 2 Using xlwings?

Method

shp=sht.api.Shapes.AddCallout(2, 110, 40, 200, 60)

shp.TextFrame2.TextRange.Characters.Text=’Test Box’

shp.Callout.Accent=True

shp.Callout.Border=True

shp.Callout.Angle=2

Sample Code

#Assign a formula as a name

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()
bk=app.books.active
sht=bk.api.Sheets(1)    #Get the worksheet

#Assign a formula as a name
sht.Range('A1:C3').Value=10
sht.Names.Add(Name='SM',RefersTo='=SUM($A$1:$C$3)')
sht.Range('D4').Formula='=SM+3'

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

How To Create Callout Using xlwings?

Method

Use the `AddCallout` method of the `Shapes` object to add a callout. The syntax is: 

 

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

 

Where `sht` represents a worksheet object. The parameters are explained in the table below. This method returns a `Shape` object representing the callout.

 

The `Type` parameter values are from the `MsoCalloutType` enumeration, which specifies the type of the callout line.

 

Name	Value	Description
msoCalloutFour	4	A callout line composed of two segments, attached to the right of the text box
msoCalloutMixed	-2	A mixed combination of states
msoCalloutOne	1	A single-segment horizontal callout line
msoCalloutThree	3	A callout line composed of two segments, attached to the left of the text box
msoCalloutTwo	2	A single-segment angled callout line

 

shp=sht.api.Shapes.AddCallout(2, 10, 10, 100, 50)

shp.TextFrame2.TextRange.Characters.Text=’Test Box’

Sample Code

#Assign a formula as a name

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()
bk=app.books.active
sht=bk.api.Sheets(1)    #Get the worksheet

#Assign a formula as a name
sht.Range('A1:C3').Value=10
sht.Names.Add(Name='SM',RefersTo='=SUM($A$1:$C$3)')
sht.Range('D4').Formula='=SM+3'

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