Python: Creating Excel worksheets with charts

Is there any module for creating Excel charts with embedded charts in Python? The modules mentioned in this question don't seem to have that capability.

I prefer a generic module that would work under Ubuntu, not a Windows-dependent one.

E开发者_Go百科DIT: I will also appreciate ways to embed images within the created charts, as I can create the charts in an external program and place them within the right sheet.

Thanks,

Adam

I recently found xlsxwriter. It's the most capable xlsx python module I've found and works with charts and graphs. It also doesn't require any non standard python modules and works on any type of box. No need for windows or to have charting software installed.

On Windows, you'd need to use pywin32 and COM. On a *x box, you may find that a combination of Iron Python, Mono, and an Excel-manipulation library written for .NET may do the job. In either case, good luck.

It's a little bit convoluted (and/or evil), but something like this will work cross-platform (including under Linux) using JPype to wrap the SmartXLS Excel Java library.

This example uses the simple chart creation (in Charts/ChartSample.class) example from SmartXLS.

#!/usr/bin/env python

import os
import os.path

import jpype

# or wherever your java is installed
os.environ['JAVA_HOME'] = "/usr/lib64/jvm/default-java"

root = os.path.abspath(os.path.dirname(__file__))

SX_JAR = os.path.join(root, 'SX.jar')

options = [
    '-Djava.class.path=%s' % SX_JAR
]

jpype.startJVM(jpype.getDefaultJVMPath(), *options)

WorkBook = jpype.JClass('com.smartxls.WorkBook')
ChartShape = jpype.JClass('com.smartxls.ChartShape')
ChartFormat = jpype.JClass('com.smartxls.ChartFormat')
Color = jpype.JClass('java.awt.Color')

workbook = WorkBook()

workbook.setText(0,1,"Jan")
workbook.setText(0,2,"Feb")
workbook.setText(0,3,"Mar")
workbook.setText(0,4,"Apr")
workbook.setText(0,5,"Jun")

workbook.setText(1,0,"Comfrey")
workbook.setText(2,0,"Bananas")
workbook.setText(3,0,"Papaya")
workbook.setText(4,0,"Mango")
workbook.setText(5,0,"Lilikoi")

for col in range(1, 5 + 1):
    for row in range(1, 5 + 1):
        workbook.setFormula(row, col, "RAND()")
workbook.setText(6, 0, "Total")
workbook.setFormula(6, 1, "SUM(B2:B6)")
workbook.setSelection("B7:F7")
# auto fill the range with the first cell's formula or data
workbook.editCopyRight()

left = 1.0
top = 7.0
right = 13.0
bottom = 31.0

# create chart with it's location
chart = workbook.addChart(left,top,right,bottom)
chart.setChartType(ChartShape.Column)
# link data source, link each series to columns(true to rows).
chart.setLinkRange("Sheet1!$a$1:$F$6", False)
# set axis title
chart.setAxisTitle(ChartShape.XAxis, 0, "X-axis data")
chart.setAxisTitle(ChartShape.YAxis, 0, "Y-axis data")
# set series name
chart.setSeriesName(0, "My Series number 1")
chart.setSeriesName(1, "My Series number 2")
chart.setSeriesName(2, "My Series number 3")
chart.setSeriesName(3, "My Series number 4")
chart.setSeriesName(4, "My Series number 5")
chart.setTitle("My Chart")

# set plot area's color to darkgray
chartFormat = chart.getPlotFormat()
chartFormat.setSolid()
chartFormat.setForeColor(Color.DARK_GRAY.getRGB())
chart.setPlotFormat(chartFormat)

# set series 0's color to blue
seriesformat = chart.getSeriesFormat(0)
seriesformat.setSolid()
seriesformat.setForeColor(Color.BLUE.getRGB())
chart.setSeriesFormat(0, seriesformat)

# set series 1's color to red
seriesformat = chart.getSeriesFormat(1)
seriesformat.setSolid()
seriesformat.setForeColor(Color.RED.getRGB())
chart.setSeriesFormat(1, seriesformat)

# set chart title's font property
titleformat = chart.getTitleFormat()
titleformat.setFontSize(14*20)
titleformat.setFontUnderline(True)
chart.setTitleFormat(titleformat)

workbook.write("./Chart.xls")

jpype.shutdownJVM()