import openpyxl
from openpyxl import Workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
class Excel_Operation():
def Create_Workbook(self, strWorkbookPath):
"""Create New excel workbook in the given folder location
Parameters:
strWorkbookPath (String): Path to store excel workbook
Returns:
Object: Workbook
"""
wb = Workbook()
wb.save(strWorkbookPath)
return wb
def Load_Workbook(self,strWorkbookPath):
wb = openpyxl.load_workbook(strWorkbookPath)
return wb
def Change_worksheetName(self, strWorkbookPath, strOldSheetName, strNewSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strOldSheetName]
ws.title = strNewSheetName
wb.save(strWorkbookPath)
def Create_Worksheet(self, strWorkbookPath, intIndex, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
wb.create_sheet(index = intIndex , title = strWorkSheetName)
wb.save(strWorkbookPath)
def Delete_Worksheet(self, strWorkbookPath, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
wb.remove(ws) #del wb[sheetname]
wb.save(strWorkbookPath)
def SaveAs(self, strOld_WorkbookPath, strNew_WorkbookPath):
wb = openpyxl.load_workbook(strOld_WorkbookPath)
wb.save(strNew_WorkbookPath)
def Filter_column(self, strWorkbookPath, strWorkSheetName, strRange, listFilterValue):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
ws.auto_filter.ref = strRange
ws.auto_filter.add_filter_column(0, listFilterValue)
#ws.auto_filter.add_sort_condition(strRange)
wb.save(strWorkbookPath)
def Append_Row(self, strWorkbookPath, strWorkSheetName, List_Tuple_Values):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
ws.append(List_Tuple_Values)
wb.save(strWorkbookPath)
def Add_formula(self, strWorkbookPath, strWorkSheetName,strFormula, int_FormulaColumn, int_Staring_Row_number, int_Ending_Row_number):
wb = openpyxl.load_workbook(strWorkbookPath, data_only=False)
ws = wb[strWorkSheetName]
#formula pattern
#'=IF($A${0}=$B${0},"Match","Mismatch")'.format(int_Staring_Row_number)
for int_Staring_Row_number in range(int_Staring_Row_number,int_Ending_Row_number+1):
ws.cell(int_Staring_Row_number,int_FormulaColumn).value = strFormula.format(int_Staring_Row_number)
wb.save(strWorkbookPath)
def Last_Row(self, strWorkbookPath, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
return ws.max_row
def First_Row(self, strWorkbookPath, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
return ws.min_row
def Last_Column(self, strWorkbookPath, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
return ws.max_column
def First_Column(self, strWorkbookPath, strWorkSheetName):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
return ws.min_column
def WorkSheet_to_DataFrame(self, strWorkbookPath, strWorkSheetName):
'''Returns argument is - DataFrame.'''
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
data = ws.values
# Get the first line in file as a header line
columns = next(data)[0:]
# Create a DataFrame based on the second and subsequent lines of data
df = pd.DataFrame(data, columns=columns)
return df
def DataFrame_to_Rows(self, strWorkbookPath, strWorkSheetName, objDataFrame, bln_DataFrameHeader, bln_Index):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
for r in dataframe_to_rows(objDataFrame, index=bln_Index, header=bln_DataFrameHeader):
ws.append(r)
wb.save(strWorkbookPath)
def Assign_DataFrame(self, objDataFrame):
df = pd.DataFrame(data=objDataFrame)
return df
def Filfer_DataFrame_Columnvalues_by_String(self, objDataFrame, str_Column_Name, list_FilterValues):
rtn_Dataframe = objDataFrame.loc[objDataFrame[str_Column_Name].isin(list_FilterValues)]
#rtn_Dataframe = objDataFrame.loc[objDataFrame[str_Column_Name] == list_FilterValues]
return rtn_Dataframe
def Filter_DataFrame_By_Column_Header(self, objDataFrame, ListColumnName):
rtn_Dataframe = objDataFrame[ListColumnName]
return rtn_Dataframe
#Copy range of cells as a nested list
#Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(self, strWorkbookPath, strWorkSheetName, intstartCol, intstartRow, intendCol, intendRow):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
rangeSelected = []
#Loops through selected Rows
for i in range(intstartRow,intendRow + 1,1):
#Appends the row to a RowSelected list
rowSelected = []
for j in range(intstartCol,intendCol+1,1):
rowSelected.append(ws.cell(row = i, column = j).value)
#Adds the RowSelected List and nests inside the rangeSelected
rangeSelected.append(rowSelected)
return rangeSelected
#Paste data from copyRange into template sheet
def pasteRange(self, strWorkbookPath, strWorkSheetName, intstartCol, intstartRow, intendCol, intendRow, copiedData):
wb = openpyxl.load_workbook(strWorkbookPath)
ws = wb[strWorkSheetName]
countRow = 0
for i in range(intstartRow,intendRow+1,1):
countCol = 0
for j in range(intstartCol,intendCol+1,1):
ws.cell(row = i, column = j).value = copiedData[countRow][countCol]
countCol += 1
countRow += 1
wb.save(strWorkbookPath)
def Replace_Values_in_Column(self, objDataFrame, strColumnHeader, ListValues, variants_ReplaceText):
rtn_Dataframe = objDataFrame[strColumnHeader].replace(ListValues, variants_ReplaceText)
return rtn_Dataframe
def Replace_Dataframe_Column(self, objOldDataFrame, strOldDataFrame_Header, objNewDataFrame, strNewDataFrame_Header):
objOldDataFrame[strOldDataFrame_Header] = objNewDataFrame[strNewDataFrame_Header]
return objOldDataFrame
def Append_DataFrames(self, ListDataframes, blnSortData):
frames = ListDataframes
rtn_Dataframe = pd.concat(frames, sort=blnSortData)
return rtn_Dataframe
def Vlookup_DataFrame(self, DataFrame_Left, DataFrame_Right, strDataFrame_Header):
rtn_Dataframe = DataFrame_Left.merge(DataFrame_Right,on=strDataFrame_Header)
return rtn_Dataframe
def Rename_DataFrame_Header(self, DataFrame, strOld_Dataframe_Header, strNew_Dataframe_Header):
rtn_Dataframe = DataFrame.rename(columns={strOld_Dataframe_Header: strNew_Dataframe_Header})
return rtn_Dataframe
Comments
Post a Comment