trouble copiyng a xlsx file to another using python

Viewed 44

So This might looks silly to some of you but I am new at python so i don't quite know what is happening,

I need to delet the first column and the first 7 rows of a excel sheet, after looking it up I found here on this website that open another file and coping only what I needed would be easier, so I tried something like this

import openpyxl



#File to be copied

wb = openpyxl.load_workbook(r"C:\Users\gb2gaet\Nova pasta\old.xlsx") #Add file name

sheet = wb["Sheet1"]#Add Sheet name



#File to be pasted into

template = openpyxl.load_workbook(r"C:\Users\gb2gaet\Nova pasta\new.xlsx") #Add file name

temp_sheet = wb["Sheet1"] #Add Sheet name



#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 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,8,17,100000,sheet)

    pasteRange(1,1,16,100000,temp_sheet,selectedRange)

    wb.save("new.xlsx")

    print("Range copied and pasted!")

the program runs without any error but when I look into the new table it is completely empty, what am I missing? If you guys can think of any easier solution to delete the rows and columns I am open to change all the code though

1 Answers

I'd recommend doing this through pandas. Import the excel file into a data frame with the pandas.read_excel() function, then use the dataframe.drop() function to drop the columns and rows you want, then export the dataframe to a new excel file with the to_excel() function.

Code would look something like this:

import pandas as pd

df = pd.read_excel(r"C:\Users\gb2gaet\Nova pasta\old.xlsx")
#careful with how this imports different sheets. If you have multiple,
#it will basically import the excel file as a dictionary of dataframes
#where each key-value pair corresponds to one sheet.

df = df.drop(columns = <columns you want removed>)

df.to_excel('new.xlsx')
#this will save the new file in the same place as your python script

Here is some documentation on those functions:

read_excel(): https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html?highlight=read_excel

Drop(): https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop.html

to_excel(): https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html?highlight=to_excel#pandas.DataFrame.to_excel

Related