Indsæt række i Excel-regneark ved hjælp af openpyxl i Python

Jeg er på udkig efter den bedste fremgangsmåde til at indsætte en række i et regneark ved hjælp af openpyxl.

Effektivt, jeg har et regneark (Excel 2007), som har en kolonneoverskrift, efterfulgt af (højst) et par tusind rækker af data. Jeg glæder mig til at indsætte den række, som den første række af de faktiske data, så efter overskriften. Min forståelse er, at tilføje funktion er velegnet til at tilføje indhold til slutningen af filen.

At læse dokumentation for både openpyxl og xlrd (og xlwt), jeg kan ikke finde nogen entydige måder at gøre dette, uden at loope gennem indhold manuelt, og indsætter den i et nyt ark (efter at du har indsat de nødvendige række).

Givet min hidtil begrænset erfaring med Python, jeg forsøger at forstå, hvis dette er faktisk den bedste mulighed for at træffe de mest pythonic!), og hvis så kunne nogen give et eksplicit eksempel. Specielt kan jeg læse og skrive rækker med openpyxl, eller skal jeg have for at få adgang celler? Derudover kan jeg (over)skrive den samme fil(navn)?

Har du mulighed for at eksportere til CSV? Og er denne ene gang-processen? eller har du brug for at gøre dette flere gange?
nej, vi sidder fast med dette format, og det er en gentagende proces: Den fil, der rent faktisk får FTP-gerne til en anden server og behandles af en SSIS-pakke.

OriginalForfatteren Nick | 2013-06-25

10 svar

  1. 17

    == Opdateret til en fuldt funktionel version, baseret på feedback her: groups.google.com/forum/#!emne/openpyxl-brugere/wHGecdQg3Iw. ==

    Som andre har påpeget, openpyxl ikke levere denne funktionalitet, men jeg har udvidet Worksheet klasse som følger for at gennemføre indsætte rækker. Håber dette viser sig at være nyttige for andre.

    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
    Kopiering lyder som overkill. Du måske lyst til at prøve noget lignende det forslag, jeg har lavet på mailing listen for at indsætte kolonner groups.google.com/forum/#!emne/openpyxl-brugere/wHGecdQg3Iw Det er uprøvet og 2.2 specifikke, fordi det er baseret på interne, men det giver den generelle stak.
    Jeg er enig i, at ændre koordinere data er ideelle, men dette blev gjort uden at se på de interne dele. Ikke sikker på, hvorfor holdet ikke vil blot tilføje denne funktionalitet, da det er noget, der bruges meget ofte i Excel.
    Ingen har forelagt den relevante kode. openpyxl er open source, er der ingen hold.
    Dallas: Hvor ofte noget er brugt, har ingen indvirkning på, hvor let det er at gennemføre. Som @CharlieClark, der er ingen “team” (og han burde vide!). Dette er et open source projekt baseret off phpexcel. Væsentlige ændringer til den celle/koordinatsystemer, som har gjort dette projekt fortsætter med at modne. Disse ændringer var nødvendige for den ønskede funktionalitet, ellers løsninger (som mine ovenfor) ville være upålidelige på bedste, og ikke egner sig for en større version. Giv det tid (eller bedre, bidrage!) for den funktionalitet, der skal gennemføres.
    Se venligst min Snippit i arkivet. Jeg har implementeret denne yderligere…jeg vil sige, temmelig tæt til fuldt ud at. ;^)

    OriginalForfatteren Dallas

  2. 9

    Besvarelsen af dette med den kode, som jeg nu bruger til at opnå det ønskede resultat. Bemærk, at jeg manuelt at indsætte træk på position 1, men det burde være nemt nok at justere til specifikke behov. Du kan også nemt at justere dette, for at indsætte mere end én række, og blot udfylde resten af data, der starter på den relevante position.

    Bemærk også, at på grund af downstream-afhængigheder, vi er manuelt at angive data fra ‘Ark1’, og data bliver kopieret til et nyt ark, der er indsat i begyndelsen af projektmappen, og samtidig omdøbe det oprindelige regneark til ‘Ark1.5’.

    EDIT: jeg har også tilføjet (senere) en ændring til format_code at løse problemer, hvor standard kopieringen her fjerner al formatering: new_cell.style.number_format.format_code = 'mm/dd/yyyy'. Jeg kunne ikke finde nogen dokumentation for, at dette var afvikles, det var mere et tilfælde af forsøg og fejl!

    Endelig, glem ikke dette eksempel er at spare i forhold til den oprindelige. Du kan ændre spar sti, hvor det er relevant for at undgå dette.

        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)
    Dette manipulerer den fil, som forventet, selvom jeg føler, at jeg bør tilføje, at vi nu har besluttet at gennemføre en CSV-eksport i stedet for. Dette er på grund af kodning spørgsmål med openpyxl bibliotek, som er off topic, for det spørgsmål, men jeg følte, at jeg skulle nævne det!

    OriginalForfatteren Nick

  3. 6

    At tilføje et svar, der gælder for de nyere udgivelser, v2.5+,openpyxl:

    Der er nu en insert_rows() og insert_cols().

    insert_rows(idx, amount=1)

    Indsæt række eller rækker, før row==idx

    Desværre understøtter ikke flettede celler (de holde fast i deres række, position)

    OriginalForfatteren aneroid

  4. 5

    Openpyxl Regneark har begrænset funktionalitet, når det kommer til at gøre række eller kolonne niveau operationer. De eneste egenskaber, et Regneark er, at der vedrører rækker/kolonner er de egenskaber row_dimensions og column_dimensions, hvilken butik “RowDimensions” og “ColumnDimensions” objekter for hver række og kolonne, hhv. Disse ordbøger er også brugt til at fungere som get_highest_row() og get_highest_column().

    Alt andet opererer på et celle-niveau, med Celle objekter, der spores i ordbogen, _cells (og deres stil, der spores i ordbogen _styles). De fleste funktioner, der ser ud som om de laver noget på en række eller kolonne-niveau, faktisk opererer på et udvalg af celler (som ovennævnte append()).

    Den enkleste ting at gøre ville være, hvad du foreslog: oprette et nyt ark, skal du føje din kolonneoverskrift, skal du føje din nye data rækker, tilføje dine gamle data rækker, skal du slette den gamle ark, og derefter omdøbe din nye plade til den gamle. Problemer, der kan være præsenteret med denne metode er tabet af række/kolonne dimensioner og attributter celleformater, medmindre du specifikt kopiere dem, også.

    Alternativt, du kunne oprette dine egne funktioner til at indsætte rækker eller kolonner.

    Jeg havde et stort antal af meget simpelt regneark, at jeg havde brug for at slette kolonner fra. Da du spurgte, for eksplicitte eksempler, og jeg vil give den funktion jeg hurtigt kastede sammen for at gøre dette:

    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()

    Jeg passerer det regneark, som jeg arbejder med, og den kolonne, række, jeg ønsker slettet, og derfra går det. Jeg ved det ikke præcis, hvad du ønskede, men jeg håber, at denne oplysninger hjalp!

    EDIT: Bemærket nogen gav dette en anden stemme, og regnede med at jeg skulle opdatere det. Det koordinatsystem i Openpyxl oplevet nogle ændringer i løbet af det passerede par år, indføre en coordinate attribut for elementer i _cell. Dette skal redigeres, også, eller de rækker, der vil være tom (i stedet for slettet), og Excel vil smide en fejl om problemer med fil. Dette virker for Openpyxl 2.2.3 (testet med de nyere versioner)

    OriginalForfatteren Rejected

  5. 2

    Som af openpyxl 1,5 du nu kan bruge .insert_rows(idx, row_qty)

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

    Det vil ikke afhente den formatering af idx række, som det ville, hvis du gjorde det manuelt i Excel. vil du anvende den korrekte formatering, dvs celle farve bagefter.

    Desværre virker ikke, hvis du har flettede celler

    OriginalForfatteren PrestonDocks

  6. 1

    Jeg tog Dallas løsning, og tilføjet understøttelse til flettede celler:

        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
                        )
    Se dette uddrag for bedre funktionalitet: bitbucket.org/snippets/openpyxl/qyzKn

    OriginalForfatteren Ran S

  7. 0

    Redigeret Nick ‘ s løsning, denne udgave tager udgangspunkt i træk, antallet af rækker, du vil indsætte, og et filnavn, og indsætter det nødvendige antal tomme rækker.

    #! 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)

    OriginalForfatteren mut3

  8. 0

    Til indsæt række i Excel-regneark ved hjælp af openpyxl i Python

    Nedenstående kode kan hjælpe dig med :-

    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)

    For at indsætte kolonnen også openpyxl har lignende funktion jeg.e.insert_cols(idx, mængde=1)

    OriginalForfatteren yugal sinha

  9. -1

    Desværre er der ikke rigtig en bedre måde at gøre i, at læse i filen, og bruge et bibliotek som xlwt til at skrive en ny excel-fil (med din ny række, indsættes øverst). Excel virker ikke som en database, som du kan læse og og føje til. Du desværre bare nødt til at læse i information og manipulere i hukommelsen og skrive ud til, hvad der er i det væsentlige en ny fil.

    OriginalForfatteren sedavidw

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *