Interacting with Microsoft Excel from Python using the Win32 COM API (Example Python Code)

excelapp.py

"""
An example of using PyWin32 and the win32com library to interact
Microsoft Excel from Python. 

This can be used instead of Visual Basic for Applications (VBA)

(c) Michael Papasimeon
"""

import win32com.client
from win32com.client import constants

    # ExcelChart
    # Creates a Microsoft Excel Chart given a data range
    # and whole bunch of other parameters
class ExcelChart:
    def __init__(self, excel, workbook, chartname, afterSheet):
        self.chartname = chartname
        self.excel = excel
        self.workbook = workbook
        self.chartname = chartname
        self.afterSheet = afterSheet

    def SetTitle(self, chartTitle):
        self.chartTitle = chartTitle

    def SetType(self, chartType):
        self.chartType = chartType

    def SetSource(self, chartSource):
        self.chartSource = chartSource

    def SetPlotBy(self, plotBy):
        self.plotBy = plotBy

    def SetCategoryLabels(self, numCategoryLabels):
        self.numCategoryLabels = numCategoryLabels

    def SetSeriesLabels(self, numSeriesLabels):
        self.numSeriesLabels = numSeriesLabels

    def SetCategoryTitle(self, categoryTitle):
        self.categoryTitle = categoryTitle

    def SetValueTitle(self, valueTitle):
        self.valueTitle = valueTitle

    def CreateChart(self):
        self.chart = self.workbook.Charts.Add(After=self.afterSheet)
        self.chart.ChartWizard(Gallery=win32com.client.constants.xlColumn, \
                               CategoryLabels=1, \
                               SeriesLabels=1, \
                               CategoryTitle = self.categoryTitle, \
                               ValueTitle = self.valueTitle, \
                               PlotBy=self.plotBy, \
                               Title=self.chartTitle)
        self.chart.SetSourceData(self.chartSource, self.plotBy)
        self.chart.HasAxis = (constants.xlCategory, constants.xlPrimary)
        self.chart.Axes(constants.xlCategory).HasTitle = 1
        self.chart.Axes(constants.xlCategory).AxisTitle.Text = self.categoryTitle
        self.chart.Axes(constants.xlValue).HasTitle = 1
        self.chart.Axes(constants.xlValue).AxisTitle.Text = self.valueTitle
        self.chart.Axes(constants.xlValue).AxisTitle.Orientation = constants.xlUpward
        self.chart.PlotBy = self.plotBy 
        self.chart.Name = self.chartname
        self.chart.HasTitle = 1
        self.chart.ChartTitle.Text = self.chartTitle
        self.chart.HasDataTable = 0
        self.chart.ChartType = self.chartType 

    def SetLegendPosition(self, legendPosition):
        self.chart.Legend.Position = legendPosition

    def PlotByColumns(self):
        self.chart.PlotBy = constants.xlColumns

    def PlotByRows(self):
        self.chart.PlotBy = constants.xlRows

    def SetCategoryAxisRange(self, minValue, maxValue):
        self.chart.Axes(constants.xlCategory).MinimumScale = minValue
        self.chart.Axes(constants.xlCategory).MaximumScale = maxValue

    def SetValueAxisRange(self, minValue, maxValue):
        self.chart.Axes(constants.xlValue).MinimumScale = minValue
        self.chart.Axes(constants.xlValue).MaximumScale = maxValue

    def ApplyDataLabels(self, dataLabelType):
        self.chart.ApplyDataLabels(dataLabelType)

    def SetBorderLineStyle(self, lineStyle):
        self.chart.PlotArea.Border.LineStyle = lineStyle

    def SetInteriorStyle(self, interiorStyle):
        self.chart.PlotArea.Interior.Pattern = interiorStyle

    # ExcelWorksheet
    # Creates an Excel Worksheet
class ExcelWorksheet:
    def __init__(self, excel, workbook, sheetname):
        self.sheetname = sheetname
        self.excel = excel
        self.workbook = workbook
        self.worksheet = self.workbook.Worksheets.Add()
        self.worksheet.Name = sheetname
    def Activate(self):
        self.worksheet.Activate()
    def SetCell(self, row, col, value):
        self.worksheet.Cells(row,col).Value = value
    def GetCell(self, row, col):
        return self.worksheet.Cells(row,col).Value
    def SetFont(self, row, col, font, size):
        self.worksheet.Cells(row,col).Font.Name = font
        self.worksheet.Cells(row,col).Font.Size = size
    def GetFont(self, row, col):
        font = self.worksheet.Cells(row,col).Font.Name
        size = self.worksheet.Cells(row,col).Font.Size
        return (font, size)

    # ExcelWorkbook
    # Creates an Excel Workbook
class ExcelWorkbook:
    def __init__(self, excel, filename):
        self.filename = filename
        self.excel = excel
        self.workbook = self.excel.Workbooks.Add()
        self.worksheets = {}
    def AddWorksheet(self, name):
        worksheet = ExcelWorksheet(self.excel, self.workbook, name)
        self.worksheets[name] = worksheet
        return worksheet
    def AddChart(self, name, afterSheet):
        chart = ExcelChart(self.excel, self.workbook, name, afterSheet)
        self.worksheets[name] = chart
        return chart
    def Save(self):
        self.workbook.SaveAs(self.filename)
    def Close(self):      
        self.worksheets = {}
        self.workbook.Close()
    def SetAuthor(self, author):
        self.workbook.Author = author

	# ExcelApp
	# Encapsulates an Excel Application
class ExcelApp:
    def __init__(self):
        self.excel = win32com.client.Dispatch("Excel.Application")
        self.workbooks = []
        self.SetDefaultSheetNum(1)
    def Show(self):
        self.excel.Visible = 1
    def Hide(self):
        self.excel.Visible = 0
    def Quit(self):
        for wkb in self.workbooks:
            wkb.Close()
        self.excel.Quit()
    def SetDefaultSheetNum(self, numSheets):
        self.excel.SheetsInNewWorkbook = numSheets
    def AddWorkbook(self, filename):
        workbook = ExcelWorkbook(self.excel, filename)
        self.workbooks.append(workbook)
        return workbook
        
def Main():
    excel = ExcelApp()
    excel.Show()
    
    workbook = excel.AddWorkbook("c:\\temp\\games1.xls")
    
    games = workbook.AddWorksheet("Game Sales")
    accessories = workbook.AddWorksheet("Accessories")
    
    games.Activate()
    
    games.SetFont(1,1,"Arial",18)
    games.SetCell(1,1, "Excel Controlled from Python - Game Sales")
    
    months = ["January", "February", "March"]
    systems = ["Nintendo GameCube", "Sony Playstation 2", "Microsoft XBox"]
    for i in range(len(months)): games.SetCell(3, i+2, months[i])
    for j in range(len(systems)): games.SetCell(4 + j, 1, systems[j])
    
    for i in range(4,6+1):
        for j in range(2,4+1):
            games.SetCell(i,j, i*j)
            
    chart = workbook.AddChart("Gaming Sales Chart", games.worksheet)
    chart.SetTitle("Games Sold by Platform Type per Month")
    chart.SetSource(games.worksheet.Range("A3:D6"))
    chart.SetType(win32com.client.constants.xlColumn)
    chart.SetPlotBy(win32com.client.constants.xlRows)
    chart.SetCategoryTitle("Months")
    chart.SetValueTitle("Sales")
    chart.SetCategoryLabels(1)
    chart.SetSeriesLabels(1)
    chart.CreateChart()
    
    workbook.Save()
    
    excel.Quit()
    
if __name__ == '__main__':
    Main()
    

Is it possible to add function for loading COM add-in and running VBA Macro?

This is effectively a VBA macro but written in Python. What do you mean a COM add-in?
You can open up any other COM object using the same Dispatch mechanism shown in line 148.

By the way this is really old, I haven't tried it on a recent version of Excel so not sure if it will still work.

I am trying your example.
Thank you. It works. (table on the tab 'game sales', chart on the tab 'chart1')

but causes exception with excel 2013 in windows10.

= RESTART: C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py
Traceback (most recent call last):
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 205, in
Main()
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 198, in Main
chart.CreateChart()
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 58, in CreateChart
self.chart.HasAxis = (constants.xlCategory, constants.xlPrimary)
File "C:\Users\user\AppData\Local\Programs\Python\Python38\lib\site-packages\win32com\client_init_.py", line 482, in setattr
self.oleobj.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, '예외가 발생했습니다.', (0, None, None, None, 0, -2147467259), None)

I am trying your example.
Thank you. It works. (table on the tab 'game sales', chart on the tab 'chart1')

but causes exception with excel 2013 in windows10.

= RESTART: C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py
Traceback (most recent call last):
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 205, in
Main()
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 198, in Main
chart.CreateChart()
File "C:\Users\user\AppData\Local\Programs\Python\Python38\win23com_excel_example.py", line 58, in CreateChart
self.chart.HasAxis = (constants.xlCategory, constants.xlPrimary)
File "C:\Users\user\AppData\Local\Programs\Python\Python38\lib\site-packages\win32com\client__init__.py", line 482, in setattr
self.oleobj.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, '예외가 발생했습니다.', (0, None, None, None, 0, -2147467259), None)

Hi @4001jh
I wrote this script back in the early to mid 2000s - I think I had only ever tested it under Windows 2000 and Windows XP with Python 2.X, possibly with a version of Office 2003.

I never tested with Python 3.X not to mention a newer version of Windows or Office. It is entirely possible that Microsoft has changed the COM API for Excel since then. This is highly likely. You'll need to look at the VBA documentation for Excel to see it the chart object still has a HasAxis method. If not you'll need to change it to the modern equivalent.

I implemented this script after I read Mark Hammond's book on Python and Win32
https://www.amazon.com/Mark-Hammond-Python-Programming-Win32/dp/B008EU7452

As you can see this book is now 20 years old. I'm sure things have been updated/changed in the Excel API since then.

However looks like the project https://github.com/mhammond/pywin32
is still very active.

I found an article using a more recent version of Excel:
https://pbpython.com/windows-com.html