How to go about copying a subsection of "xlsx" file into a new sheet?

Question

So here is the challenge I am facing. I want to copy a certain range of rows and columns from one sheet in a workbook into a new sheet. Seems pretty easy enough right? Here's the catch: The entire process has to be done using python and the copying process should preserve the formatting. i.e. any and all formatting in the original range has to be there in the new sheet. (So that effectively rules out reading and writing one cell at a time) I have bee trying to rack my brains on it, but cant seem to figure out a simple elegant solution.

Can anyone point me in the right direction? Or perhaps towards some documentation that might help?

For full compatibility (under Windows) you can interact directly with an Excel object via `win32com`, e.g.`excel = win32com.gencache.EnsureDispatch('Excel.Application')` — Martin Evans, Mar 17 '18 at 16:41
Then using the libraries might not preserve ALL the formatting, but should suffice for most situations. — Martin Evans, Mar 17 '18 at 16:57
I do not need compatibility with Windows. The entire operation is to be performed on Debian... — Anshuman Mishra, Mar 17 '18 at 17:10

score 0 · Answer 1 · answered Mar 17 '18 at 18:12

You could use openpyxl to do this as follows:

from copy import copy
import openpyxl

wb = openpyxl.load_workbook('input.xlsx')
ws1 = wb['Sheet1']
ws2 = wb['Sheet2']

for src in ws1['A1:D5']:
    for cell_src in src:
        cell_dst = ws2.cell(row=cell_src.row, column=cell_src.col_idx, value=cell_src.value)

        for att in ['font', 'border', 'fill', 'number_format', 'protection', 'alignment']:
            if cell_src.has_style:
                setattr(cell_dst, att, copy(getattr(cell_src, att)))

wb.save('output.xlsx')

This should preserve most formatting.

How to go about copying a subsection of "xlsx" file into a new sheet?

1 Answers1