Sep-09-2019, 02:27 PM
(This post was last modified: Sep-09-2019, 02:27 PM by Patriot1017.)
It has come time where I need some guidance on a use case I have. I have 3 different .xlsx workbooks and what I am trying to do is add a column and concat 2 columns together to populate the newly added column. I think I have accomplished that but need to make some minor tweaks. My next issue is I am trying to copy a range from the workbook where I added the column and values and paste it into another workbook at with a specific cell range as well. I was able to find some code to accomplish this but I dont think its working in conjunction with the dataframe I created.
Also I am fairly new to the python world so please take it easy on me.
Also I am fairly new to the python world so please take it easy on me.
import pandas as pd import numpy as np import openpyxl df1 = pd.read_excel('C:/Users/u678153/Desktop/FIN_-_Find_Customer_Invoices_Lines_with_Worktags.xlsx', index_col=None, sheet_name='Detail') a = np.char.array(df1["Account Prefix (CBF)"].astype(object)) b = np.char.array(df1["IPPER Plan Code (CBF)"].astype(object)) df1.insert(15, 'CON/PLAN', (a+b)) #File to be copied wb = openpyxl.load_workbook('C:/Users/u678153/Desktop/07_19 Custom.xlsx') sheet = wb.get_sheet_by_name("Static") #Add Sheet name #File to be pasted into template = openpyxl.load_workbook('C:/Users/u678153/Desktop/FIN_-_Find_Customer_Invoices_Lines_with_Worktags.xlsx') temp_sheet = template.get_sheet_by_name("Detail") #Copy range of cells as a nested list #Takes: start cell, end cell, and sheet you want to copy from. def copyRange(startCol, startRow, endCol, endRow, sheet): rangeSelected = [] #Loops through selected Rows for i in range(startRow,endRow + 1,1): #Appends the row to a RowSelected list rowSelected = [] for j in range(startCol,endCol+1,1): rowSelected.append(sheet.cell(row = i, column = j).value) #Adds the RowSelected List and nests inside the rangeSelected rangeSelected.append(rowSelected) return rangeSelected #Paste range #Paste data from copyRange into template sheet def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving,copiedData): countRow = 0 for i in range(startRow,endRow+1,1): countCol = 0 for j in range(startCol,endCol+1,1): sheetReceiving.cell(row = i, column = j).value = copiedData[countRow][countCol] countCol += 1 countRow += 1 def createData(): print("Processing...") selectedRange = copyRange(2,2,25,2769,sheet) pastingRange = pasteRange(1,2,25,2769,temp_sheet,selectedRange) template.save('C:/Users/u678153/Desktop/test1.xlsx') print("Range copied and pasted!")