Blog
How To Create a Chart Worksheet Using xlwings?
Method
To create a chart worksheet (a chart that occupies the entire worksheet), use the `Add` method from the `Charts` collection. The syntax is as follows:
wb.api.Charts.Add(Before,After,Count,Type)
Here, `wb` represents the specified workbook object. The parameters are optional and are explained as follows:
– `Before` – Specifies a worksheet object, and the newly created chart worksheet will be placed before this worksheet.
– `After` – Specifies a worksheet object, and the newly created chart worksheet will be placed after this worksheet.
– `Count` – Specifies the number of chart worksheets to add. The default value is 1.
– `Type` – Specifies the chart type to add.
Note: If both `Before` and `After` are omitted, the newly created chart worksheet will be inserted before the active worksheet.
cht=wb.api.Charts.Add()
cht.SetSourceData(Source=sht.api.Range(“A1:H7”), PlotBy=1)
cht.ChartType=xw.constants.ChartType.xlColumnClustered
cht.HasTitle=True
Sample Code
#Create chart worksheet
import xlwings as xw
import os
root=os.getcwd()
app=xw.App(visible=True, add_book=False)
wb=app.books.open('GDP.xlsx',read_only=False)
sht=wb.sheets('Sheet1')
cht=wb.api.Charts.Add()
cht.SetSourceData(Source=sht.api.Range("A1:H7"), PlotBy=1)
cht.ChartType=xw.constants.ChartType.xlColumnClustered
cht.HasTitle=True
#wb.save()
#wb.close()
#app.kill()
How To Create a Chart by Adding Series One by One?
Method
A chart can also be created by adding series one by one.
sht.api.Range(‘A2:A7’).Select() #Data
shp=sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlColumnClustered, 200, 20, 350, 250, True)
cht=shp.Chart #Add chart
ser=cht.SeriesCollection().NewSeries()
ser.ChartType=xw.constants.ChartType.xlLine
ser.Values=sht.api.Range(“B2:B7”)
cht.HasLegend=True
Sample Code
#Create chart - Add series one by one
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open('GDP.xlsx',read_only=False)
sht=wb.sheets('Sheet1')
sht.api.Range('B2:B7').Select() #Data
shp=sht.api.Shapes.AddChart2(-1, xw.constants.ChartType.xlColumnClustered, 200, 20, 350, 250, True)
cht=shp.Chart #Add chart
ser=cht.SeriesCollection().NewSeries()
ser.ChartType=xw.constants.ChartType.xlLine
ser.Values=sht.api.Range("C2:C7")
cht.HasLegend=True
#wb.save()
#wb.close()
#app.kill()
How To Create a Chart Using the Shapes Object?
Method
Creating a chart using the `Shapes` object is essentially achieved through the API method in `xlwings`. In this approach, the `AddChart2` method of the `Shapes` object is used to create the chart. The syntax is as follows:
sht.api.Shapes.AddChart2(Style,XlChartType,Left,Top,Width,Height,NewLayout)
Here, `sht` represents the specified worksheet, and there are seven parameters, all of which are optional:
– `Style` – The chart style. If the value is `-1`, it represents the default style for each chart type.
– `XlChartType` – The chart type, represented by the `XlChartType` enumeration. A partial list of values is provided in Table 6-1.
– `Left` – The left position of the chart. If omitted, the chart will be horizontally centered.
– `Top` – The top position of the chart. If omitted, the chart will be vertically centered.
– `Width` – The width of the chart. If omitted, the default value is 354.
– `Height` – The height of the chart. If omitted, the default value is 210.
– `NewLayout` – Indicates the chart layout. If `True`, only composite charts will display legends.
This method returns a `Shape` object representing the chart.
sht.api.Range(‘A1’).CurrentRegion.Select()
sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,30,150,300,200,True)
Sample Code
#Create chart - Shapes object
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open('GDP.xlsx',read_only=False)
sht=wb.sheets(1)
sht.api.Range('A1').CurrentRegion.Select()
sht.api.Shapes.AddChart2(-1,xw.constants.ChartType.xlColumnClustered,30,150,300,200,True)
#wb.save()
#wb.close()
#app.kill()
How To Create a Chart Using the API?
Method
cht=sht.api.ChartObjects().Add(20,200,355,211).Chart
cht.SetSourceData(sht.api.Range(‘A1:H7’),1)
cht.ChartType=xw.constants.ChartType.xlColumnClustered
cht.HasTitle=True
Sample Code
#Create Chart - xlwings API
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open('GDP.xlsx',read_only=False)
sht=wb.sheets('Sheet1')
cht=sht.api.ChartObjects().Add(20,200,355,211).Chart
cht.SetSourceData(sht.api.Range('A1:H7'),1)
cht.ChartType=xw.constants.ChartType.xlColumnClustered
cht.HasTitle=True
#wb.save()
#wb.close()
#app.kill()
How To Create a Chart Using xlwings?
Method
The `charts.add` method provided by the `xlwings` package can be used to create a chart. The syntax is as follows:
sht.charts.add(left=0, top=0, width=355, height=211)
Here, `sht` represents the worksheet object, and there are four parameters:
– `left` – Specifies the position of the chart’s left side, in points. The default value is 0.
– `top` – Specifies the position of the chart’s top side, in points. The default value is 0.
– `width` – Specifies the width of the chart, in points. The default value is 355.
– `height` – Specifies the height of the chart, in points. The default value is 211.
This method returns a `chart` object.
cht=sht.charts.add(50, 200) #Add a chart
cht.set_source_data(sht.range(‘A1’).expand()) #Bind data to chart
cht.chart_type=’column_clustered’ #Chart type
cht.api[1].HasTitle=True #Chart has a title
Sample Code
#Create Chart - xlwings
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open('GDP.xlsx',read_only=False)
sht=wb.sheets(1)
cht=sht.charts.add(50, 200) #Add a chart
cht.set_source_data(sht.range('A1').expand()) #Bind data to chart
cht.chart_type='column_clustered' #Chart type
cht.api[1].HasTitle=True #Chart has a title
#wb.save()
#wb.close()
#app.kill()
How To Implement Geometric Transformation of Images Using xlwings?
Method
Using the methods provided by the `Shape` object, you can perform translation, rotation, scaling, and reflection transformations on a given graphic. The implementation of these methods is exactly the same for images.
shp=sht.api.Shapes.AddPicture(r”D:\picpy.jpg”, True, True, 100, 50, 100, 100)
shp.IncrementRotation(30) #Rotate 30° clockwise around the center
shp.Flip(0) #Horizontal flip
Sample Code
#Picture Geometric Transformations
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.AddPicture(r"D:\picpy.jpg", True, True, 100, 50, 100, 100)
shp.IncrementRotation(30) #Rotate 30° clockwise around the center
shp.Flip(0) #Horizontal flip
#bk.save()
#bk.close()
#app.kill()
How To Add Image In the Worksheet Using xlwings?
Method
Use the `AddPicture` method of the `Shape` object to create an image from an existing file. This method returns a `Shape` object representing the new image. The syntax of this method is:
sht.api.Shapes.AddPicture(FileName,LinkToFile,SaveWithDocument,Left,Top,Width,Height)
Here, `sht` represents the worksheet, and the parameters are as follows:
|
Name |
Required/Optional |
Data Type |
Description |
|
FileName |
Required |
String |
The image file name |
|
LinkToFile |
Required |
MsoTriState |
Set to `False` to make the image an independent copy, not linked. Set to `True` to link the image to the file from which it was created |
|
SaveWithDocument |
Required |
MsoTriState |
Saves the image with the document. Use `False` to store only the link information, and `True` to save the linked image with the document. This parameter must be `True` if `LinkToFile` is `False` |
|
Left |
Required |
Single |
The position of the top-left corner of the image relative to the document’s top-left corner (in points) |
|
Top |
Required |
Single |
The position of the top-left corner of the image relative to the document’s top (in points) |
|
Width |
Required |
Single |
The width of the image in points (enter `-1` to retain the original width) |
|
Height |
Required |
Single |
The height of the image in points (enter `-1` to retain the original height) |
sht.api.Shapes.AddPicture(root+”/picpy.jpg”, True, True, 100, 50, 100, 100)
Sample Code
#Add Picture
import xlwings as xw #Import xlwings package
import os
root = os.getcwd()
app=xw.App()
bk=app.books.active #Get the active workbook
sht=bk.sheets.active #Get the active worksheet
sht.api.Shapes.AddPicture(r"D:\picpy.jpg", True, True, 100, 50, 100, 100)
#bk.save()
#bk.close()
#app.kill()
How To Implement Graphic Flipping Using xlwings?
Method
Graphic flipping, also known as image reflection or symmetry transformation, is achieved using the `Flip` method of the `Shape` object. This method flips the graphic around either the horizontal or vertical axis. It has one parameter that specifies whether to flip horizontally or vertically. The corresponding values for horizontal and vertical flips are 0 and 1, respectively.
shp=sht.api.Shapes.AddShape(1, 100, 50, 200, 100) #Rectangular area
shp.Fill.PresetTextured(22) #Preset texture: Walnut wood
shp.Flip(0) #Horizontal flip
shp.Flip(1) #Vertical flip
Sample Code
#Iterating Over Shapes in the Worksheet
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
shp1=sht.api.Shapes.AddShape(1, 50, 50, 200, 100) #Rectangular area
shp2=sht.api.Shapes.AddShape(9, 30, 80, 200, 100) #Elliptical area
shp2.Fill.ForeColor.RGB=xw.utils.rgb_to_int((255,0,0))
shp2.Fill.Transparency=0.7
shp3=sht.api.Shapes.AddLine(10,10,200,120) #Line segment
sht.range('F1').value=['Name', 'Type', 'Top-left X', 'Top-left Y', 'Width', 'Height'] # Header
i=0
for shp in sht.api.Shapes: #Iterate through each Shape object in the worksheet
i+=1
sht.api.Cells(i+1, 'F').Value=shp.Name #Output each Shape object's properties
sht.api.Cells(i+1, 'G').Value=shp.Type
sht.api.Cells(i+1, 'H').Value=shp.Left
sht.api.Cells(i+1, 'I').Value=shp.Top
sht.api.Cells(i+1, 'J').Value=shp.Width
sht.api.Cells(i+1, 'K').Value=shp.Height
#bk.save()
#bk.close()
#app.kill()
How To Implement Graphic Scaling Using xlwings?
Method
Graphic scaling, also known as affine transformation, is the process of enlarging or reducing a given graphic by a certain ratio. Use the `ScaleWidth` and `ScaleHeight` methods of the `Shape` object to specify the scaling ratios for the horizontal and vertical directions, respectively, to achieve the scaling operation.
Both `ScaleWidth` and `ScaleHeight` methods have three parameters, as shown in the table.
|
Name |
Required/Optional |
Data Type |
Description |
|
Factor |
Required |
Single |
Specifies the ratio of the adjusted width to the current or original width |
|
RelativeToOriginalSize |
Required |
MsoTriState |
When `False`, scales relative to the current size. This parameter can only be set to `True` when the graphic is a picture or OLE object. |
|
Scale |
Optional |
Variant |
One of the constants from MsoScaleFrom type, specifying which part of the graphic remains at the original position when scaling. |
The `Scale` parameter takes constants from the `MsoScaleFrom` enumeration, indicating which part of the graphic remains at the original position after scaling. The possible values are as follows:
|
Name |
Value |
Description |
|
msoScaleFromBottomRight |
2 |
The bottom-right corner of the graphic remains at the original position |
|
msoScaleFromMiddle |
1 |
The midpoint of the graphic remains at the original position |
|
msoScaleFromTopLeft |
0 |
The top-left corner of the graphic remains at the original position |
shp=sht.api.Shapes.AddShape(9, 100, 50, 200, 100) #Elliptical area
ff=shp.Fill
ff.PresetTextured(12) #Preset texture: Granite
shp.ScaleWidth(0.75,False) #Width × 0.75
shp.ScaleHeight(1.75,False) #Height × 1.75
Sample Code
#Flip transformation
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(1, 100, 50, 200, 100) #Rectangular area
shp.Fill.PresetTextured(22) #Preset texture: Walnut wood
shp.Flip(0) #Horizontal flip
shp.Flip(1) #Vertical flip
#bk.save()
#bk.close()
#app.kill()
How To Implement Graphic Rotation Using xlwings?
Method
Use the `IncrementRotation` method of the `Shape` object to rotate the graphic. This method rotates the graphic around the Z-axis by a specified angle. It has one parameter, representing the angle of rotation in degrees. A positive value rotates the graphic clockwise, while a negative value rotates the graphic counterclockwise.
shp=sht.api.Shapes.AddShape(1, 100, 50, 200, 100) #Rectangular area
shp.Fill.PresetTextured(5) #Preset texture: Water drop
shp.IncrementRotation(30) #Rotate clockwise by 30°°
Sample Code
#Scaling transformation
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, 100, 50, 200, 100) #Elliptical area
ff=shp.Fill
ff.PresetTextured(12) #Preset texture: Granite
shp.ScaleWidth(0.75,False) #Width × 0.75
shp.ScaleHeight(1.75,False) #Height × 1.75
#bk.save()
#bk.close()
#app.kill()