Daten reihenweise in Excel einfügen

[riven]

hallo,

ich möchte durch python alle elemente einer liste in eine reihe einfügen.
wie es mit einfügen von elementen in einer spalte geht, weiss ich, und zwar mit:

Code: Alles auswählen

def genExcelName(row, col):
    """Translate (0,0) into "A1"."""
    if col < 26:
        colName = chr(col + ord('A'))
    else:
        colName = chr((col / 26)-1 + ord('A')) + chr((col % 26) + ord('A'))
    return "%s%s" % (colName, row + 1)

def addDataColumn(worksheet, columnIdx, data):
    range = worksheet.Range("%s:%s" % (
        genExcelName(0, columnIdx),
        genExcelName(len(data) - 1, columnIdx),
        ))
    for idx, cell in enumerate(range):
        cell.Value = data[idx]
    return range

ich habe versucht, addDataColumn in addDataRow umzustellen, hat aber nicht geklappt:

Code: Alles auswählen

def addDataRow(worksheet, rowIdx, data):
    range = worksheet.Range("%s:%s" % (
        genExcelName(rowIdx, 0),
        genExcelName(rowIdx,len(data) - 1),
        ))
    for idx, cell in enumerate(range):
        cell.Value = data[idx]
    return range

danke

[riven]

hi, wie man daten so einfügt, dass man durch auswählen ein oberflächendiagramm erstellen kann, habe ich mit folgendem skript hinbekommen:

Code: Alles auswählen

from win32com.client import Dispatch, constants

def plot(x, y, z)):
    # acquire application object, which may start application
    application = Dispatch("Excel.Application")

    # create new file ('Workbook' in Excel-vocabulary)
    workbook = application.Workbooks.Add()

    # store default worksheet object so we can delete it later
    defaultWorksheet = workbook.Worksheets(1)

    # create data worksheet
    worksheet = workbook.Worksheets.Add()
    worksheet.Name = "Sheet"
    
    # install data
    xColumn = addDataColumn(worksheet, 0, x)
    
    yRow = addDataRow(worksheet, 1, y)

    for i in range(1,len(z)):
            zRow= addDataRow(worksheet, 1, z,rowIdx=i)

    chart = workbook.Charts.Add()
    chart.ChartType = constants.xlSurface
    chart.SetSourceData(Source=Sheets("Sheet").Range("A1:"+str(genExcelName(len(z),1)),PlotBy=xlColumns))
    chart.Location(Where =xlLocationAsNewSheet)
    chart.HasLegend=True
    chart.HasTitle = True
    chart.ChartTitle.Characters.Text = "title"
    chart.Axes(xlCategory).HasTitle = True
    chart.Axes(xlCategory).AxisTitle.Characters.Text = "a"
    chart.Axes(xlSeries).HasTitle = True
    chart.Axes(xlSeries).AxisTitle.Characters.Text = "b"
    chart.Axes(xlValue).HasTitle = True
    chart.Axes(xlValue).AxisTitle.Characters.Text = "c"
    chart.Elevation = 15
    chart.Perspective = 30
    chart.Rotation = 20
    chart.RightAngleAxes = True
    chart.HeightPercent = 100
    chart.AutoScaling = True

    # remove default worksheet
    #defaultWorksheet.Delete()
    workbook.Worksheets(3).Delete()
    workbook.Worksheets(2).Delete()
    # make stuff visible now.
    #chart.Activate()
    application.Visible = True

def genExcelName(row, col):#, only_coord=False):
    """Translate (0,0) into "A1"."""
    if col < 26:
        colName = chr(col + ord('A'))
    else:
        colName = chr((col / 26)-1 + ord('A')) + chr((col % 26) + ord('A'))
    #if only_coord:
     #   return
    return "%s%s" % (colName, row + 1)

def addDataColumn(worksheet, columnIdx, data):
    print len(data)
    range = worksheet.Range("%s:%s" % (
        genExcelName(1, columnIdx),
        genExcelName(len(data), columnIdx),
        ))
    for idx, cell in enumerate(range):
        cell.Value = data[idx]
    return range

def addDataRow(worksheet, columnIdx, data, rowIdx=0):
    print len(data)
    range = worksheet.Range("%s:%s" % (
        genExcelName(rowIdx, columnIdx),
        genExcelName(rowIdx,len(data)),
        ))
    
    for idx, cell in enumerate(range):
        print range
        cell.Value = data[idx]
    return range

Es kommt aber beim Erstellen des Diagramms von Excel die Fehlermeldung "A surface chart must contain at least two series", obwohl ich die Grenzen festgelegt habe.

Das dazu entsprechende VBA-Skript lautet:

Sub Macro14()

Cells.Select
Charts.Add
ActiveChart.ChartType = xlSurface
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:I25"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "a"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "b"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "c"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "d"
End With
ActiveChart.PlotArea.Select
With ActiveChart
.Elevation = 15
.Perspective = 30
.Rotation = 20
.RightAngleAxes = True
.HeightPercent = 100
.AutoScaling = True
End With
End Sub

Kann mir da jemand weiterhelfen?