2

I want to create Excel spreadsheets with embedded graphs. Ideally I'd like to do with with Python on Mac. If I can't do that, I'd like to do it with some kind of Excel automation on Mac. If I can't do that I'm willing to do it on Windows by manipulating Excel via COM, but I would still like to do it from Python. Does anyone have any code? Thanks.

vy32
  • 28,461
  • 37
  • 122
  • 246

2 Answers2

4

Here's a basic example of Python COM on Windows:

import win32com.client
from win32com.client import constants as c

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Add()
ws = xl.ActiveSheet
ws.Range('A1').FormulaR1C1 = 'X'
ws.Range('B1').FormulaR1C1 = 'Y'
ws.Range('A2').FormulaR1C1 = 1
ws.Range('A3').FormulaR1C1 = 2
ws.Range('A4').FormulaR1C1 = 3
ws.Range('B2').FormulaR1C1 = 4
ws.Range('B3').FormulaR1C1 = 5
ws.Range('B4').FormulaR1C1 = 6
ws.Range('A1:B4').Select()
ch = ws.Shapes.AddChart().Select()
xl.ActiveChart.ChartType = c.xlXYScatterLines
xl.ActiveChart.SetSourceData(Source=ws.Range("A1:B4"))

It was translated by recording a macro in Excel. Here's the macro so you can see how similar it is. Just record what you want to do in Excel and translate it to Python syntax:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "X"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Y"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "6"
    Range("A1:B4").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$4")
End Sub
Mark Tolonen
  • 166,664
  • 26
  • 169
  • 251
  • That's great! Is there a simple way for embedding a graphic that I generate with matplotlib? – vy32 Dec 11 '11 at 13:50
0

The main tools I know of for dealing with Excel on the Mac is xlrd/xlwt and Excel 2004 XML format. AFAICT, neither of those support embedded graphs.

So, I think that leaves just the option of manipulating Excel via COM on Windows.

Raymond Hettinger
  • 216,523
  • 63
  • 388
  • 485
  • 2
    Another gun R Hettinger on an Excel tag .... some serious IT skills in that household :) – brettdj Dec 11 '11 at 08:56
  • @Raymond, AFAIK [openpyxl](https://bitbucket.org/ericgazoni/openpyxl/) supports OSX... [here](https://bitbucket.org/ericgazoni/openpyxl/issue/7/writing-a-file-from-osx-uses-n-instead-of) is a bug that was filed against [openpyxl](https://bitbucket.org/ericgazoni/openpyxl/) under OSX. It offers *some* graph support, but it is not very rich yet – Mike Pennington Dec 11 '11 at 19:07