webentwicklung-frage-antwort-db.com.de

Einfügen einer Zeile in die Excel-Tabelle mit openpyxl in Python

Ich bin auf der Suche nach dem besten Ansatz, um eine Zeile mit openpyxl in eine Kalkulationstabelle einzufügen.

Tatsächlich habe ich eine Tabelle (Excel 2007) mit einer Kopfzeile, gefolgt von (höchstens) einigen tausend Datenzeilen. Ich freue mich darauf, die Zeile als erste Zeile der eigentlichen Daten einzufügen, also nach dem Header. Meines Wissens ist die Append-Funktion geeignet, um das Ende der Datei Inhalt hinzuzufügen.

Beim Lesen der Dokumentation für openpyxl und xlrd (und xlwt) finde ich keine klaren Wege, dies zu tun, abgesehen vom manuellen Durchlaufen des Inhalts und dem Einfügen in ein neues Arbeitsblatt (nach Einfügen der erforderlichen Zeile). 

Angesichts meiner bisher begrenzten Erfahrung mit Python versuche ich zu verstehen, ob dies tatsächlich die beste Option ist (am meisten Pythonic!), Und wenn ja, könnte jemand ein explizites Beispiel geben. Kann ich insbesondere mit openpyxl Zeilen lesen und schreiben oder muss ich auf Zellen zugreifen? Kann ich zusätzlich (über) dieselbe Datei (Namen) schreiben?

13
Nick

Beantworte dies mit dem Code, den ich jetzt verwende, um das gewünschte Ergebnis zu erzielen. Beachten Sie, dass ich die Zeile manuell an Position 1 einfüge. Dies sollte jedoch leicht genug sein, um die speziellen Anforderungen anzupassen. Sie können dies auch leicht anpassen, um mehr als eine Zeile einzufügen, und den Rest der Daten einfach an der entsprechenden Position ausfüllen.

Beachten Sie außerdem, dass wir aufgrund von nachgeschalteten Abhängigkeiten Daten aus 'Sheet1' manuell angeben und die Daten in ein neues Blatt kopiert werden, das am Anfang der Arbeitsmappe eingefügt wird, während das ursprüngliche Arbeitsblatt in 'Sheet1.5' umbenannt wird. .

BEARBEITEN: Ich habe auch (später) eine Änderung an format_code hinzugefügt, um Probleme zu beheben, bei denen der Standardkopiervorgang hier alle Formatierungen entfernt: new_cell.style.number_format.format_code = 'mm/dd/yyyy'. Ich konnte keine Dokumentation finden, dass dies einstellbar war, es war eher ein Fall von Versuch und Irrtum!

Vergessen Sie nicht, dass dieses Beispiel das Original speichert. Sie können den Speicherpfad ggf. ändern, um dies zu vermeiden.

    import openpyxl

    wb = openpyxl.load_workbook(file)
    old_sheet = wb.get_sheet_by_name('Sheet1')
    old_sheet.title = 'Sheet1.5'
    max_row = old_sheet.get_highest_row()
    max_col = old_sheet.get_highest_column()
    wb.create_sheet(0, 'Sheet1')

    new_sheet = wb.get_sheet_by_name('Sheet1')

    # Do the header.
    for col_num in range(0, max_col):
        new_sheet.cell(row=0, column=col_num).value = old_sheet.cell(row=0, column=col_num).value

    # The row to be inserted. We're manually populating each cell.
    new_sheet.cell(row=1, column=0).value = 'DUMMY'
    new_sheet.cell(row=1, column=1).value = 'DUMMY'

    # Now do the rest of it. Note the row offset.
    for row_num in range(1, max_row):
        for col_num in range (0, max_col):
            new_sheet.cell(row = (row_num + 1), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

    wb.save(file)
9
Nick

== Auf einer voll funktionsfähigen Version aktualisiert, basierend auf Feedback hier: groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw. ==

Wie bereits erwähnt, bietet openpyxl diese Funktionalität nicht, aber ich habe die Worksheet-Klasse wie folgt erweitert, um das Einfügen von Zeilen zu implementieren. Ich hoffe, das erweist sich für andere als nützlich.

def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True):
    """Inserts new (empty) rows into worksheet at specified row index.

    :param row_idx: Row index specifying where to insert new rows.
    :param cnt: Number of rows to insert.
    :param above: Set True to insert rows above specified row index.
    :param copy_style: Set True if new rows should copy style of immediately above row.
    :param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows.

    Usage:

    * insert_rows(2, 10, above=True, copy_style=False)

    """
    CELL_RE  = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)")

    row_idx = row_idx - 1 if above else row_idx

    def replace(m):
        row = m.group('row')
        prefix = "$" if row.find("$") != -1 else ""
        row = int(row.replace("$",""))
        row += cnt if row > row_idx else 0
        return m.group('col') + prefix + str(row)

    # First, we shift all cells down cnt rows...
    old_cells = set()
    old_fas   = set()
    new_cells = dict()
    new_fas   = dict()
    for c in self._cells.values():

        old_coor = c.coordinate

        # Shift all references to anything below row_idx
        if c.data_type == Cell.TYPE_FORMULA:
            c.value = CELL_RE.sub(
                replace,
                c.value
            )
            # Here, we need to properly update the formula references to reflect new row indices
            if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]:
                self.formula_attributes[old_coor]['ref'] = CELL_RE.sub(
                    replace,
                    self.formula_attributes[old_coor]['ref']
                )

        # Do the magic to set up our actual shift    
        if c.row > row_idx:
            old_coor = c.coordinate
            old_cells.add((c.row,c.col_idx))
            c.row += cnt
            new_cells[(c.row,c.col_idx)] = c
            if old_coor in self.formula_attributes:
                old_fas.add(old_coor)
                fa = self.formula_attributes[old_coor].copy()
                new_fas[c.coordinate] = fa

    for coor in old_cells:
        del self._cells[coor]
    self._cells.update(new_cells)

    for fa in old_fas:
        del self.formula_attributes[fa]
    self.formula_attributes.update(new_fas)

    # Next, we need to shift all the Row Dimensions below our new rows down by cnt...
    for row in range(len(self.row_dimensions)-1+cnt,row_idx+cnt,-1):
        new_rd = copy.copy(self.row_dimensions[row-cnt])
        new_rd.index = row
        self.row_dimensions[row] = new_rd
        del self.row_dimensions[row-cnt]

    # Now, create our new rows, with all the pretty cells
    row_idx += 1
    for row in range(row_idx,row_idx+cnt):
        # Create a Row Dimension for our new row
        new_rd = copy.copy(self.row_dimensions[row-1])
        new_rd.index = row
        self.row_dimensions[row] = new_rd
        for col in range(1,self.max_column):
            col = get_column_letter(col)
            cell = self.cell('%s%d'%(col,row))
            cell.value = None
            source = self.cell('%s%d'%(col,row-1))
            if copy_style:
                cell.number_format = source.number_format
                cell.font      = source.font.copy()
                cell.alignment = source.alignment.copy()
                cell.border    = source.border.copy()
                cell.fill      = source.fill.copy()
            if fill_formulae and source.data_type == Cell.TYPE_FORMULA:
                s_coor = source.coordinate
                if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:
                    fa = self.formula_attributes[s_coor].copy()
                    self.formula_attributes[cell.coordinate] = fa
                # print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
                cell.value = re.sub(
                    "(\$?[A-Z]{1,3}\$?)%d"%(row - 1),
                    lambda m: m.group(1) + str(row),
                    source.value
                )   
                cell.data_type = Cell.TYPE_FORMULA

    # Check for Merged Cell Ranges that need to be expanded to contain new cells
    for cr_idx, cr in enumerate(self.merged_cell_ranges):
        self.merged_cell_ranges[cr_idx] = CELL_RE.sub(
            replace,
            cr
        )

Worksheet.insert_rows = insert_rows
17
Dallas

Hinzufügen einer Antwort für neuere Releases v2.5 + von openpyxl:

Es gibt jetzt ein insert_rows() und insert_cols().

insert_rows(idx, amount=1)

Zeile oder Zeilen vor row == idx einfügen

6
aneroid

Openpyxl-Arbeitsblätter haben eine eingeschränkte Funktionalität, wenn es um Operationen auf Zeilen- oder Spaltenebene geht. Die einzigen Eigenschaften eines Arbeitsblatts, die sich auf Zeilen/Spalten beziehen, sind die Eigenschaften row_dimensions und column_dimensions, die "RowDimensions" - und "ColumnDimensions" -Objekte für jede Zeile bzw. Spalte speichern. Diese Wörterbücher werden auch in Funktionen wie get_highest_row() und get_highest_column() verwendet.

Alles andere funktioniert auf Zellebene, wobei Cell-Objekte im Wörterbuch _cells (und ihr Stil im Wörterbuch _styles) verfolgt werden. Die meisten Funktionen, die so aussehen, als würden sie irgendetwas auf Zeilen- oder Spaltenebene ausführen, arbeiten tatsächlich in einem Bereich von Zellen (z. B. der oben genannten append()).

Am einfachsten wäre das, was Sie vorgeschlagen haben: Erstellen Sie ein neues Blatt, fügen Sie Ihre Kopfzeile hinzu, fügen Sie Ihre neuen Datenzeilen hinzu, fügen Sie Ihre alten Datenzeilen hinzu, löschen Sie das alte Blatt und benennen Sie Ihr neues Blatt dann in das alte um. Probleme, die bei dieser Methode auftreten können, sind der Verlust von Attributen und Zellstilen für Zeilen-/Spaltendimensionen, sofern Sie sie nicht auch speziell kopieren.

Alternativ können Sie auch eigene Funktionen erstellen, die Zeilen oder Spalten einfügen.

Ich hatte eine große Anzahl von very einfachen Arbeitsblättern, aus denen ich Spalten löschen musste. Da Sie um explizite Beispiele gebeten haben, gebe ich Ihnen die Funktion, die ich schnell zusammenbrachte, um dies zu tun:

from openpyxl.cell import get_column_letter

def ws_delete_column(sheet, del_column):

    for row_num in range(1, sheet.get_highest_row()+1):
        for col_num in range(del_column, sheet.get_highest_column()+1):

            coordinate = '%s%s' % (get_column_letter(col_num),
                                   row_num)
            adj_coordinate = '%s%s' % (get_column_letter(col_num + 1),
                                       row_num)

            # Handle Styles.
            # This is important to do if you have any differing
            # 'types' of data being stored, as you may otherwise get
            # an output Worksheet that's got improperly formatted cells.
            # Or worse, an error gets thrown because you tried to copy
            # a string value into a cell that's styled as a date.

            if adj_coordinate in sheet._styles:
                sheet._styles[coordinate] = sheet._styles[adj_coordinate]
                sheet._styles.pop(adj_coordinate, None)
            else:
                sheet._styles.pop(coordinate, None)

            if adj_coordinate in sheet._cells:
                sheet._cells[coordinate] = sheet._cells[adj_coordinate]
                sheet._cells[coordinate].column = get_column_letter(col_num)
                sheet._cells[coordinate].row = row_num
                sheet._cells[coordinate].coordinate = coordinate

                sheet._cells.pop(adj_coordinate, None)
            else:
                sheet._cells.pop(coordinate, None)

        # sheet.garbage_collect()

Ich übergebe das Arbeitsblatt, mit dem ich arbeite, und die Spaltennummer, die ich löschen möchte, und schon geht es weg. Ich weiß, es ist nicht genau das, was Sie wollten, aber ich hoffe, dass diese Informationen geholfen haben!

BEARBEITEN: Ich habe bemerkt, dass jemand eine weitere Stimme abgegeben hat, und dachte, ich sollte es aktualisieren. Das Koordinatensystem in Openpyxl erlebte einige Änderungen in den vergangenen paar Jahren und führte ein coordinate-Attribut für Elemente in _cell ein. Dies muss auch bearbeitet werden, oder die Zeilen bleiben leer (anstatt gelöscht), und Excel gibt einen Fehler in Bezug auf Probleme mit der Datei aus. Dies funktioniert für Openpyxl 2.2.3 (mit späteren Versionen nicht getestet)

5
Rejected

Ab openpyxl 1.5 können Sie jetzt .insert_rows (idx, row_qty) verwenden.

from openpyxl import load_workbook
wb = load_workbook('Excel_template.xlsx')
ws = wb.active
ws.insert_rows(14, 10)

Die Formatierung der idx-Zeile wird nicht übernommen, als würden Sie dies manuell in Excel tun. Sie müssen anschließend die korrekte Formatierung anwenden, d. h. die Zellenfarbe.

1
PrestonDocks

Ich nahm die Lösung von Dallas und fügte Unterstützung für zusammengeführte Zellen hinzu:

    def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True):
        skip_list = []
        try:
            idx = row_idx - 1 if above else row_idx
            for (new, old) in Zip(range(self.max_row+cnt,idx+cnt,-1),range(self.max_row,idx,-1)):
                for c_idx in range(1,self.max_column):
                  col = self.cell(row=1, column=c_idx).column #get_column_letter(c_idx)
                  print("Copying %s%d to %s%d."%(col,old,col,new))
                  source = self["%s%d"%(col,old)]
                  target = self["%s%d"%(col,new)]
                  if source.coordinate in skip_list:
                      continue

                  if source.coordinate in self.merged_cells:
                      # This is a merged cell
                      for _range in self.merged_cell_ranges:
                          merged_cells_list = [x for x in cells_from_range(_range)][0]
                          if source.coordinate in merged_cells_list:
                              skip_list = merged_cells_list
                              self.unmerge_cells(_range)
                              new_range = re.sub(str(old),str(new),_range)
                              self.merge_cells(new_range)
                              break

                  if source.data_type == Cell.TYPE_FORMULA:
                    target.value = re.sub(
                      "(\$?[A-Z]{1,3})%d"%(old),
                      lambda m: m.group(1) + str(new),
                      source.value
                    )
                  else:
                    target.value = source.value
                  target.number_format = source.number_format
                  target.font   = source.font.copy()
                  target.alignment = source.alignment.copy()
                  target.border = source.border.copy()
                  target.fill   = source.fill.copy()
            idx = idx + 1
            for row in range(idx,idx+cnt):
                for c_idx in range(1,self.max_column):
                  col = self.cell(row=1, column=c_idx).column #get_column_letter(c_idx)
                  #print("Clearing value in cell %s%d"%(col,row))
                  cell = self["%s%d"%(col,row)]
                  cell.value = None
                  source = self["%s%d"%(col,row-1)]
                  if copy_style:
                    cell.number_format = source.number_format
                    cell.font      = source.font.copy()
                    cell.alignment = source.alignment.copy()
                    cell.border    = source.border.copy()
                    cell.fill      = source.fill.copy()
                  if fill_formulae and source.data_type == Cell.TYPE_FORMULA:
                    #print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
                    cell.value = re.sub(
                      "(\$?[A-Z]{1,3})%d"%(row - 1),
                      lambda m: m.group(1) + str(row),
                      source.value
                    )
1
Ran S

So fügen Sie eine Zeile in eine Excel-Tabelle mit openpyxl in Python ein

Der folgende Code kann Ihnen dabei helfen: -

import openpyxl

file = "xyz.xlsx"
#loading XL sheet bassed on file name provided by user
book = openpyxl.load_workbook(file)
#opening sheet whose index no is 0
sheet = book.worksheets[0]

#insert_rows(idx, amount=1) Insert row or rows before row==idx, amount will be no of 
#rows you want to add and it's optional
sheet.insert_rows(13)

Für das Einfügen von Spalten haben auch openpyxl eine ähnliche Funktion, d. H. Einfügung_cols (idx, betrag = 1).

0
yugal sinha

Das hat für mich funktioniert:

    openpyxl.worksheet.worksheet.Worksheet.insert_rows(wbs,idx=row,amount=2)

Fügen Sie 2 Zeilen vor der Zeile == idx ein

Siehe: http://openpyxl.readthedocs.io/de/stable/api/openpyxl.worksheet.worksheet.html

0
ack

Die Lösung von Nick bearbeitete eine Startzeile, die Anzahl der einzufügenden Zeilen und einen Dateinamen und fügt die erforderliche Anzahl leerer Zeilen ein.

#! python 3

import openpyxl, sys

my_start = int(sys.argv[1])
my_rows = int(sys.argv[2])
str_wb = str(sys.argv[3])

wb = openpyxl.load_workbook(str_wb)
old_sheet = wb.get_sheet_by_name('Sheet')
mcol = old_sheet.max_column
mrow = old_sheet.max_row
old_sheet.title = 'Sheet1.5'
wb.create_sheet(index=0, title='Sheet')

new_sheet = wb.get_sheet_by_name('Sheet')

for row_num in range(1, my_start):
    for col_num in range(1, mcol + 1):
        new_sheet.cell(row = row_num, column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

for row_num in range(my_start + my_rows, mrow + my_rows):
    for col_num in range(1, mcol + 1):
        new_sheet.cell(row = (row_num + my_rows), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

wb.save(str_wb)
0
mut3