pythonexceltextboxopenpyxl

Insert Texbox on excel using python


I need to insert a text box in an Excel sheet using python, I have tried openpyxl, openpyxl.drawing.shape more specifically, xlsxwriter and on and it just doesn't work.

from openpyxl import Workbook
from openpyxl.drawing.text import Paragraph, ParagraphProperties
from openpyxl.drawing.text import CharacterProperties
from openpyxl.drawing.shape import Shape

# Create a shape
shape = Shape()

# Initialize GraphicalProperties
graphical_properties = GraphicalProperties()

# Assign GraphicalProperties to spPr
shape.spPr = graphical_properties

# Add the shape to the worksheet
sheet.add_shape(shape)

# Save the workbook
workbook.save("output.xlsx")

This is the latest code that I tried.


Solution

  • Openpyxl doesn't play well with shapes so better to use Xlsxwriter as mentioned or if you want to add to an existing workbook you can use Xlwings or win32com.

    The code below is an example for Xlwings;

    The AddShape command is from Excel and the shape type is from the MsoAutoShapeType enumeration, e.g. 1 is a rectangle and 9 is a circle.

    import xlwings as xw
    from xlwings.utils import rgb_to_int
    
    excel_file = 'shapes.xlsx'
    with xw.App(visible=True) as app:
        wb = xw.Book(excel_file)
        ws = wb.sheets('Sheet1')
    
        ### Excel AddShape command, Values are Type, Left, Top, Width & Height
        shape1 = ws.api.Shapes.AddShape(1, 100, 50, 150, 30)  # 1 is a rectangle
        shape2 = ws.api.Shapes.AddShape(9, 1, 1, 20, 20)  # 9 is a circle
    
        shape1_name = shape1.Name
        shape1.Fill.ForeColor.RGB = rgb_to_int((255,233,0))
    
        shape2.TextFrame2.TextRange.Text = "Hello There"
    
        ### Some parameters are accessible from ws.shapes
        for shape in ws.shapes:
            if shape.name == shape1_name:
                shape.characters.api.Text = f"Shape Name = {shape.name}"
                shape.characters.font.name = 'Arial'
                shape.characters.font.color = (0,0,0)
                shape.characters.font.bold = True
    
                ### You can also add a macro to the object
                # shape.api.OnAction = "sample_sub"
    
        wb.save(excel_file)
    

    enter image description here