A collection of Excel operations often used in Python

A collection of Excel operations often used in Python

Overview

Paste the value from this Excel In another Excel, run something called a power query Another one. .. .. .. Ahhhhhh There was something like that.

To be honest, the work you are doing in Excel can be implemented with a simple Python program. Numerical verification is over Situations that it is not realistic to rewrite all the things of the predecessor

At that time, I made a program that automates only the operation. The logic that is likely to be used later has been made into a class.

Usage environment

Python 3.9.0 Visual Studio Code 1.52.0 openpyxl xlwings pandas

Source code

I wonder if openpyxl, xlwings, pandas are in the right place

#Python Excel Manipulation Library
#Libraryize operations that are likely to occur

import xlwings as xw
import openpyxl

class lib_excel_ope:
    #Constructor No processing at this time
    def __init__(self):
        pass

    def exec_macro(self,xlsm_name:str,macro_name:str):
        #Macro execution xlwings
        wb = xw.Book(xlsm_name) #Open the book
        macro = wb.macro(macro_name)    #Get macro
        macro()                          #Run macro
        
        wb.save(xlsm_name)
        wb.close
        apps = xw.apps        #Returns an application execution environment management instance
        app = apps.active
        app.kill()


    def copy_cell(self,frompath:str,topath:str,fromsheet:str,tosheet:str,fromrow:int,torow:int,frmcol:int,tocol:int):
        #Copy and paste the specified sheet
        #How to specify the range is the condition that it is within the target range On the end condition side of python+ 1
        wb1 = openpyxl.load_workbook(frompath)
        wb2 = openpyxl.load_workbook(topath)
        ws1 = wb1[fromsheet]
        ws2 = wb2[tosheet]
     
        for rownum in range(fromrow, torow + 1):
            for colnum in range(frmcol, tocol + 1):
                cellstr = ws1.cell(row=rownum,column=colnum).coordinate
                ws2[cellstr] = ws1[cellstr].value
        
        wb2.save(topath)

    def check_effctive_cell(self,filename:str,sheetname:str,checkcol:int):
        #Check how valid the line is
        wb1 = openpyxl.load_workbook(filename)
        ws1 = wb1[sheetname]
        #For the time being, assume that MAX is up to 1000 lines
        for rownum in range(1, 1000):
            cellstr = ws1.cell(row=rownum,column=checkcol).coordinate
            if ws1[cellstr].value is None :
                return rownum - 1 #Valid line is one line before None
        wb1.close()

    def ifnone_round(self,cellvalue):
        if cellvalue is None:
            return cellvalue
        else:
            return round(cellvalue)

    #Desk tractor No processing at this time
    def __del__(self):
        pass


if __name__ == '__main__': 
    #Code for unit testing
    #Python Excel Manipulation Library
    exlopeobj = lib_excel_ope()