Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel issues please help!
#1
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.

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!")
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python to Excel date to number fix | performance issues Prashant 1 3,253 Apr-28-2018, 11:21 AM
Last Post: killerrex

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020