Copying a sheet with specific formatting from an excel file to a new output

Question

Heading says it all. I have an excel file with a sheet called "Chart". The table/sheet is formatted in a specific way with different colors and what now. I was wondering how I can copy this sheet over to a new excel file keeping it an exact copy.

import openpyxl

def copy_worksheet(source_ws, target_ws):
    for row in source_ws.iter_rows():
        for cell in row:
            target_ws[cell.coordinate].value = cell.value
            target_ws[cell.coordinate].font = cell.font.copy()
            target_ws[cell.coordinate].border = cell.border.copy()
            target_ws[cell.coordinate].fill = cell.fill.copy()
            target_ws[cell.coordinate].number_format = cell.number_format
            target_ws[cell.coordinate].protection = cell.protection.copy()
            target_ws[cell.coordinate].alignment = cell.alignment.copy()
            target_ws[cell.coordinate].comment = cell.comment

def main():
    source_file = "C:/Users/user1/testsheet.xlsx"
    source_sheet_name = "Chart"
    output_file = "C:/Users/user1/output.xlsx"

    source_wb = openpyxl.load_workbook(source_file)
    source_ws = source_wb[source_sheet_name]

    output_wb = openpyxl.Workbook()
    output_ws = output_wb.active
    output_ws.title = source_sheet_name

    copy_worksheet(source_ws, output_ws)
    output_wb.save(output_file)

if __name__ == "__main__":
    main()

Look at the WorksheetCopy function in the worksheet.copier module - this is what it uses in the internal copy_worksheet function (which of course doesn't cross workbooks) https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/worksheet/copier.html#WorksheetCopy — J Manifold, Mar 30 '23 at 19:24
Does this answer your question? [How to copy worksheet from one workbook to another one using openpyxl?](https://stackoverflow.com/questions/42344041/how-to-copy-worksheet-from-one-workbook-to-another-one-using-openpyxl) — J Manifold, Mar 30 '23 at 19:34
Open the workbook then save it with a new name. Then you have a copy of the original workbook with all formatting. If there are other sheets from the original you dont want, delete them. — moken, Mar 31 '23 at 11:16

score 0 · Answer 1 · answered Mar 30 '23 at 19:37

To copy a specific sheet with formatting from an Excel file to a new output file using Python and the openpyxl library, you can use the copy_worksheet() function defined in the code provided in the question. Here's an explanation of how it works:

The copy_worksheet() function takes two arguments: source_ws and target_ws, which are the source and target worksheets, respectively.
It uses nested for loops to iterate through all cells in the source worksheet, row by row.
For each cell, it copies the value and all formatting attributes (font, border, fill, number format, protection, alignment, and comment) to the corresponding cell in the target worksheet.
The main function loads the source file and worksheet, creates a new output workbook and worksheet, sets the title of the output worksheet to the name of the source worksheet, calls copy_worksheet() to copy the data and formatting from the source worksheet to the output worksheet, and saves the output workbook to a file.

To use this code, you need to make sure that you have installed the openpyxl library in your Python environment. You can do this by running pip install openpyxl in your terminal/command prompt.

To run the code, save it to a file with a .py extension and run it using Python. Make sure to update the file paths for the source and output files to match your own file paths.

The copy_worksheet function cannot copy worksheets between workbooks. worksheets can only be copied within the workbook that they belong. This will not work for the OP's question. Also this is triggering AI content detector — J Manifold, Mar 30 '23 at 21:07

Copying a sheet with specific formatting from an excel file to a new output

1 Answers1