Daten reihenweise in Excel einfügen

Wenn du dir nicht sicher bist, in welchem der anderen Foren du die Frage stellen sollst, dann bist du hier im Forum für allgemeine Fragen sicher richtig.
riven
User
Beiträge: 35
Registriert: Mittwoch 27. September 2006, 23:01

Daten reihenweise in Excel einfügen

Beitragvon riven » Donnerstag 9. November 2006, 13:40

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
User
Beiträge: 35
Registriert: Mittwoch 27. September 2006, 23:01

Beitragvon riven » Donnerstag 9. November 2006, 18:42

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?

Wer ist online?

Mitglieder in diesem Forum: djevil