Possibility of copying data from one excel sheet to another on a different column using openpyxl-CodePudding

Is it possible to copy data from one excel sheet i.e copy data from column A,B,C and paste in another sheet on column G,H,I using openpyxl and python?

CodePudding user response：

Yes its possible, please take a look at the following code:

source-data: 'Sheet1'

# Import openpyxl
# Note: openpyxl package provides both read and write capabilities to excel
import openpyxl
import string
import os


# Class definitions should use CamelCase convention based on pep-8 guidelines
class CustomOpenpyxl:

    # Initialize the class with filename as only argument
    def __init__(self, _my_file_name):
        assert _my_file_name.split('.')[-1] == 'xlsx', 'Input file is not xlsx'
        self.my_filename = _my_file_name
        self.my_base_wb = openpyxl.load_workbook(self.my_filename, read_only=False)

    # Method to get a mapping of column letters and their index values as reference
    # This works for upto 26 columns in a worksheet
    # No arguments for this method
    def get_col_idx_and_col_letter_map(self):
        _my_map = dict()
        for _idx, _val in enumerate(string.ascii_uppercase, start=1):
            _my_map[_val] = _idx
        return _my_map

    # Method to copy any column from one worksheet to another column in another worksheet of same workbook
    # Arguments to this method are: - Source worksheet name, source column letter,
    # Target worksheet name, target column letter
    def copy_src_col_paste_tgt_col_diff_sheets(self, _src_ws_name, _src_col_letter, _tgt_ws_name, _tgt_col_letter):
        # Iterate over the values of source worksheet and start the running index from 1
        # usually index start from 0, but for writing cells in excel row it cannot start from zero
        for _row_idx, _val in enumerate(self.my_base_wb[_src_ws_name].values, start=1):
            # Write the values to target worksheet and target column (LHS)
            # from values in source column of source worksheet (RHS)
            self.my_base_wb[_tgt_ws_name].cell(row=_row_idx,
                                               column=self.get_col_idx_and_col_letter_map()[_tgt_col_letter]).value = \
            self.my_base_wb[_src_ws_name].cell(row=_row_idx,
                                               column=self.get_col_idx_and_col_letter_map()[_src_col_letter]).value

    # Method to save the workbook
    # No arguments to this method
    def save_wb(self):
        return self.my_base_wb.save(self.my_filename)


# we are using the in-built os package and getcwd() method
_my_file_path = os.getcwd()
_my_file_name = 'Book1.xlsx'

# Instantiate an object using the newly created class in this code block
# So that this object gets access to all methods in the class
_my_src_obj = CustomOpenpyxl(_my_file_name)
_my_src_obj.copy_src_col_paste_tgt_col_diff_sheets('Sheet1', 'A', 'Sheet2', 'G')
_my_src_obj.save_wb()

Result Data