6

I am using the following code to open an existing Excel file in python 3.6, Excel 2016:

Shnm = my_pyx.get_sheet_names() 
sheet = my_pyx.get_sheet_by_name(Shnm[0])

from openpyxl import load_workbook
# Class to manage excel data with openpyxl.

class Copy_excel:
    def __init__(self,src):
        self.wb = load_workbook(src)
        self.ws = self.wb.get_sheet_by_name(sheet)
        self.dest="destination.xlsx"

    # Write the value in the cell defined by row_dest+column_dest         
    def write_workbook(self,row_dest,column_dest,value):
        c = self.ws.cell(row = row_dest, column = column_dest)
        c.value = value

    # Save excel file
    def save_excel(self) :  
        self.wb.save(self.dest)

source

So when I do:

row_dest=2
column_dest=6   
workbook = Copy_excel(my_file)
data=60
workbook.write_workbook(2,6,data )
workbook.save_excel()

where: my_file is a str like filename.xlsx and sheet is a str with the sheet name.

It bugs me with an error stating that the sheet name mentioned does not exist.

I also tried to replace:

self.ws = self.wb.get_sheet_by_name(sheet)

with

self.ws = self.wb[sheet]

but I still receive the same error.

FabioSpaghetti
  • 790
  • 1
  • 9
  • 35
  • It seems that the variable `sheet` in `self.ws = self.wb.get_sheet_by_name(sheet)` is not passed into the init function of your class. Have you ruled that out? Also, you can list all the sheets in your workbook with `self.wb.sheetnames`. Can you add an `assert sheet in self.wb.sheetnames` to make sure the sheet actually exists? – Johannes Gontrum Feb 28 '19 at 14:09
  • physically checking the Excel file, I see it exists ! but I did not get what I exactly should do, since I am new to using classes, how do I check this in the command prompt ? – FabioSpaghetti Feb 28 '19 at 14:27
  • should I insert assert sheet in self.wb.sheetnames in a line ? – FabioSpaghetti Feb 28 '19 at 14:27

1 Answers1

1

The problem was setting:

sheet = my_pyx.get_sheet_by_name(Shnm[0])

And later set:

self.ws = self.wb[sheet]

Since sheet is not the sheet name but the actual sheet object you should use:

self.ws = self.wb[Shnm[0]]

I have tried this code and it worked for me:

from openpyxl import load_workbook
# Class to manage excel data with openpyxl.

class Copy_excel:
    def __init__(self,src):
        self.wb = load_workbook(src)
        Shnm = self.wb.sheetnames
        self.ws = self.wb[Shnm[0]]
        self.ws = self.wb[sheet]
        self.dest='path\\to\\Copy.xlsx'

    # Write the value in the cell defined by row_dest+column_dest
    def write_workbook(self,row_dest,column_dest,value):
        c = self.ws.cell(row = row_dest, column = column_dest)
        c.value = value

    # Save excel file
    def save_excel(self) :
        self.wb.save(self.dest)

row_dest=2
column_dest=6
workbook = Copy_excel('path\\to\\file.xlsx')
data=60
workbook.write_workbook(2,6,data )
workbook.save_excel()
Hawk59
  • 36
  • 6