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

Question

It's all in the heading. I have a sheet called "Chart" in an excel document. The table or sheet has a defined format with various colours and etc. I was wondering how to make a replica of this sheet in a new Excel file.

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

Answer 1

Using Python and the openpyxl package, you may copy a specified sheet with formatting from an Excel file to a new output file by using the copy worksheet() function, which is defined in the code provided in the question. The following describes how it operates:

1. The copy_worksheet() function takes two arguments: source_ws and target_ws, which are the source and target worksheets, respectively.

2. It uses nested for loops to iterate through all cells in the source worksheet, row by row.

3. 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.

4. 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.

You must ensure that the openpyxl library is present in your Python environment in order to use this code. Pip install openpyxl can be used to accomplish this in your terminal or command prompt.

The code should be saved to a file with a.py extension and executed through Python. Ensure that the source and output file paths are updated to reflect your actual file directories.