Re: [openpyxl-users] remove_sheet() not working correctly

[Charlie Clark]

Hi,

Am 13.11.2013, 20:32 Uhr, schrieb <axf...@gmail.com>:

> Hi folks, the last line of the below code is failing for me and I'm
> afraid
> I can't quite tell why.
> workbook = openpyxl.load_workbook(workbookname)
> sheets = workbook.get_sheet_names()
> totalsheets = len(sheets)
> if totalsheets!=1:
> workbooks = {}
> for y in sheets:
> if y != "DV-IDENTITY-0":
> workbooks[y] = workbook
> for x in sheets:
> if x != y:
> workbooks[y].remove_sheet(x)

Sorry, but as you can see your code has come through garbled.

> Traceback (most recent call last):
> File "csvnormalize.py", line 33, in <module>
> workbooks[y].remove_sheet(x)
> File "/usr/local/lib/python2.7/dist-packages/openpyxl/workbook.py",
> line
> 161, in remove_sheet
> self.worksheets.remove(worksheet)
> ValueError: list.remove(x): x not in list

> not quite sure what the issue is -- I'm looping through the workbook
> object
> and creating new workbook objects that only contain one sheet, for each
> sheet. should be quite simple, but I can't see the problem. Thanks!

I can't see any code there creating sheets but I don't think you are doing
quite what you think you are.

Try:

if x in workbooks[y].get_sheet_names():
workbooks[y].remove_sheet(x)

Working backwards from that might help you find the problem.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[axf...@gmail.com]

Hi,

I've attached the actual code -- the relevant part for this issue should be lines 20-34.

I'm trying to create a new workbook containing only one worksheet for each of the worksheets in the original workbook. the "print" commands are in there for sanity checking right now. I'm having a really weird issue... the output currently is:

    sheet1

    sheet1

    sheet2

    sheet3&funnyname

    sheet4

    sheet2

    sheet1

    sheet2

    sheet3&funnyname

    Traceback (most recent call last):

      File "csvnormalize.py", line 34, in <module>

        workbooks[y].remove_sheet(workbooks[y].get_sheet_by_name(x))

      File "/usr/local/lib/python2.7/dist-packages/openpyxl/workbook.py", line 161, in remove_sheet

        self.worksheets.remove(worksheet)

    ValueError: list.remove(x): x not in list

I can't understand why it's failing on sheet3&funnyname while it's looping through the second new workbook but not the first ... any idea?

[Charlie Clark]

Am 13.11.2013, 22:22 Uhr, schrieb <axf...@gmail.com>:

> Hi,
> I've attached the actual code -- the relevant part for this issue should
> be
> lines 20-34.

workbook = openpyxl.load_workbook(workbookname)
sheets = workbook.get_sheet_names()
totalsheets = len(sheets)
if totalsheets!=1:
workbooks = {}

# for y in [1:totalsheets]:

for y in sheets:
if y != "DV-IDENTITY-0":

print y
workbooks[y] = workbook

That last line assigns the workbook you've opened again and again and
again. You then proceed to remove sheets from *it*, because a new one
isn't created until you save the file. However, the list sheets is not
being updated as you remove them so you are bound to get an IndexError
sooner or later. Create new workbooks explicitly and you should be fine,
though I still find this code has too many levels to be fun to debug.

[axf...@gmail.com]

Hey Charlie,

I can rewrite the script to create new workbooks explicitly, but -- and forgive me for being stubborn -- I still don't understand why my method doesn't work. I'm deliberately assigning a new instance of the workbook each time (as a value of the workbooks{} dictionary) and removing all but one sheet from each of those instances. At no point should I be referencing a sheet that doesn't exist for a given workbook instance, as far as I can see...

[Charlie Clark]

Am 13.11.2013, 23:24 Uhr, schrieb <axf...@gmail.com>:

> Hey Charlie,

> I can rewrite the script to create new workbooks explicitly, but -- and
> forgive me for being stubborn -- I still don't understand why my method
> doesn't work. I'm deliberately assigning a new instance of the workbook

No. You are repeatedly assigning the *same* instance of the opened
workbook.

At least that's what it looks like to me and the usual cause of these
kinds of errors when you have mutables that you think are copies.
If I'm right and you force it to be a new workbook each time you're
problem will go away. If I'm wrong, then, well, ahem… ;-)

Step into the debugger and you should find that all the items
workbooks.values() are the same.

[axf...@gmail.com]

Ah, hm. I just realized that the errors I'm seeing are potentially consistent with what you're saying -- that I'm removing sheets from the same workbook and eventually getting an Index Error. But if that's the case, I have to ask: why is workbooks[y] = workbook not sufficient to create a copy of the object? I've used declarations like that in Python many times before... seems like a silly question, almost.

[Charlie Clark]

Am 13.11.2013, 23:41 Uhr, schrieb <axf...@gmail.com>:

> Ah, hm. I just realized that the errors I'm seeing *are* potentially

> consistent with what you're saying -- that I'm removing sheets from the
> same workbook and eventually getting an Index Error. But if that's the
> case, I have to ask: why is workbooks[y] = workbook not sufficient to
> create a copy of the object? I've used declarations like that in Python
> many times before... seems like a silly question, almost.

Well, if you've used it before you've been lucky. Dictionaries will
perfectly happen accept references to objects as values.

d = {}
a = [1, 2, 3]

d["a"] = a
d["b"] = a

a.pop()

assert d["a"] == [1, 2] == d["b"]

That's basically what you're doing and why you're having problems.

[axf...@gmail.com]

Thanks, Charlie. Understood now -- I appreciate your patience.

[axf...@gmail.com]

PS: the original code ended up working fine after I changed the dictionary assignment to workbooks[y] = copy.deepcopy(workbook).

[ashishra...@gmail.com]

you shoudl delete like this it will work .

work_book_ro = load_workbook( filename = filename, use_iterators=True )

    

    work_book_w = load_workbook( filename )

    

    dc_host_dictionary = create_data_center_host_dictionary (work_book_ro , hostname_text_file)

    

    print dc_host_dictionary 

    

    print work_book_ro.get_sheet_names()

    

    #Will print the all the work sheet (ws) in the work book Loaded 

    for work_sheet_name_ro in work_book_ro.get_sheet_names():

        print work_sheet_name_ro 

        

    # WILL GO IN THE ABOVE FOR LOOP        

    work_sheet_name_ro="RTP1_ACL's" # this will be commented out

    work_sheet_name_split= work_sheet_name_ro.split('_')

    work_sheet_ro = work_book_ro.get_sheet_by_name(work_sheet_name_ro)

    work_sheet_w = work_book_w.get_sheet_by_name(work_sheet_name_ro)

    

    

    

    if work_sheet_name_split[0] in dc_host_dictionary:

    #Parse Each Work Sheet as per Data_Center Name

        parse_and_replace_data_center_work_sheet ( work_sheet_ro , work_sheet_w , dc_host_dictionary[work_sheet_name_split[0]])

    else:

        work_book_w.remove_sheet(work_sheet_w)

[Charlie Clark]

Am .01.2016, 00:46 Uhr, schrieb <ashishra...@gmail.com>:

> you shoudl delete like this it will work .

No it won't.