openpyxl -> Write a slice of cells to new workbook-CodePudding

I'm generating summary sheets for large data sets by snipping the top 20 rows from 150-2000 source Excel documents and compiling them together in one doc. I am hoping to cut out some compiling time. Currently using python's openpyxl module to load and collect the segments I need, but what I'd like to do is write a slice//segment of cells to a new Workbook all at once, rather than iterate over each cell individually. Suppose I'm defining a slice as:

sheetSlice=sh[f"A1:{chr(colcount-1 65)}{min(rowlim,rowcount)}"]

Please excuse the ugliness of how I set up the slice notation

Is there any way to take this slice//group of cells and write it to a new openpyxl.Workbook() object all at once? I was unable to find any documentation regarding this type of operation. Thanks! xoxo

CodePudding user response：

No, you have to access it cell by cell. However, you can use list comprehension and ws.iter_rows() to write a function that neatly copies and pastes a range of data.

import openpyxl as op


def flatten(t):
    return [item for sublist in t for item in sublist]


def excel_copy_paste(
    ws_copy, ws_paste, row_min, row_max, col_min, col_max, paste_row=1, paste_col=1
):
    # Copying your range
    copy_range = flatten(
        [
            [cell.value for cell in row]
            for row in ws_copy.iter_rows(
                min_row=row_min, max_row=row_max, min_col=col_min, max_col=col_max
            )
        ]
    )
    paste_range = flatten(
        [
            [cell for cell in row]
            for row in ws_paste.iter_rows(
                min_row=paste_row,
                max_row=paste_row   row_max - row_min,
                min_col=paste_col,
                max_col=paste_col   col_max - col_min,
            )
        ]
    )
    for cell_copy, cell_paste in zip(copy_range, paste_range):
        cell_paste.value = cell_copy


# Workbook, worksheets
wb = op.load_workbook("EXAMPLE.xlsx")
ws = wb["All data"]
ws_new = wb["Sheet1"]

# Copy/paste
excel_copy_paste(ws, ws_new, row_min=1, row_max=5, col_min=1, col_max=7)

# Save
wb.save("EXAMPLE.xlsx")

Note that this won't preserve format.