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

0 votes

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

Mar 31, 2023 in Others by narikkadan
• 63,720 points
10,306 views

1 answer to this question.

0 votes

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.

answered Mar 31, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
1 answer

How to save a new sheet in an existing excel file, using Pandas?

import pandas as pd import numpy as np path ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,720 points
7,350 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,720 points
7,821 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
2,395 views
0 votes
1 answer

Is it possible to get data from a webpage in real-time to an excel file?

The conventional method of obtaining data from ...READ MORE

answered Jan 17, 2023 in Others by narikkadan
• 63,720 points
465 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,348 views
0 votes
1 answer
0 votes
1 answer

How to read a dataframe from an excel sheet containing multiple tables?

Here is one option with scikit-image (highly inspired by ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,020 points
2,836 views
0 votes
1 answer

Excel VBA: how to find a description from an AD-group

First add the 'description' property to your ...READ MORE

answered Feb 16, 2023 in Others by Kithuzzz
• 38,020 points
849 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP