copy sheet

[JeffMGreg]

Hi All,

After spending all day looking for the answer and getting nowhere, I
figured it was time to figure it out for myself. Here's my solution to
the copy sheet question. Nothing fancy, and I'm sure some of you can
spruce it up... a lot. Anyway, I though that this could help someone
else out.

def copy_sheet(workbook, source_index, new_name):
'''
import xlwt
import copy

having a writable workbook you can now copy a sheet and append
to the end of the worksheets list.

workbook == book in use
source_index == index of sheet you want to copy (0 start)
new_name == name of new copied sheet
'''
# make a copy of desired sheet in memory
copied_sheet = copy.copy(workbook.get_sheet(source_index))

# append copy to worksheets list
workbook._Workbook__worksheets.append(copied_sheet)

# activate the last sheet in the workbook
workbook.set_active_sheet(source_index+1)

# rename the last sheet in the work book
workbook.get_sheet(index+1).set_name(new_name)


Hope this helps someone
Jeff

[JeffMGreg]

The previous one had an error, this one should work

import xlrd, xlwt
import copy
import xlutils.copy

def copy_sheet(workbook, source_index, new_name):
'''

workbook == book in use
source_index == index of sheet you want to copy (0 start)
new_name == name of new copied sheet
'''

# make a copy of desired sheet in memory

new_sheet = copy.copy(workbook.get_sheet(source_index))

# append copy to worksheets list

workbook._Workbook__worksheets.append(new_sheet)

# find out how many sheets are in the workbook
append_index = len(workbook._Workbook__worksheets)-1

# activate the last sheet in the workbook

workbook.set_active_sheet(append_index)

# rename the last sheet in the workbook

workbook.get_sheet(append_index).set_name(new_name)

[John Machin]

Hi Jeff,

Thanks for the effort, but the sprucing up has to start with getting
it to run; in the last line, you meant "source_index", not "index".
Result: NameError: global name 'index' is not defined

In any case, source_index + 1 is refers to the new last sheet only
when the source sheet was originally the last.

Moreover, the last line can be replaced by
copied_sheet.set_name(new_name)

Setting the active sheet doesn't belong in this method -- the caller
may want to keep the active sheet (the activeness of which is only
manifested when a user opens the saved workbook) at the default (the
first sheet).

The method would be much more useful if it were to return a reference
to the copied sheet.

Remedying all that and adding a simple case (write a1 in the first
sheet, copy the first sheet, write a2 in the 2nd sheet) gives this
code:

8<---- cut here -----------
import copy

def copy_sheet(workbook, source_index, new_name):
'''

having a writable workbook you can now copy a sheet and append
to the end of the worksheets list.

workbook == book in use
source_index == index of sheet you want to copy (0 start)
new_name == name of new copied sheet
'''
# make a copy of desired sheet in memory
copied_sheet = copy.copy(workbook.get_sheet(source_index))
# append copy to worksheets list
workbook._Workbook__worksheets.append(copied_sheet)

# rename the new sheet
copied_sheet.set_name(new_name)
return copied_sheet

if __name__ == "__main__":
import xlwt
b = xlwt.Workbook()
s0 = b.add_sheet('s0')
s0.write(0, 0, "s0!a1")
s1 = copy_sheet(b, 0, "s1")
s1.write(1, 0, "s1!a2")
b.save("copied_sheet.xls")
8<--- cut here ---

When you look at the resulting file in Excel etc, s1 looks fine, but
s0 ... hmmm. Do you really intend the original sheet to be a
doppelganger of the new sheet? Try adding a line to write a3 in the
original sheet, just before the save. Check out what happens. Hint:
consider what other function in the copy module might be more suitable
than copy.

By the way, it's the original that's the copied_sheet, not the new
sheet. Could be confusing.

Apart from all of the above, to make it qualify to be included in
xlwt, it needs to do all that Workbook.add_sheet() does (except of
course it will copy a sheet instead of making a new sheet) -- that
includes another arg, all the validity testing, and adding an entry to
the name-to-index mapping.

HTH,
John

[John Machin]

On Wed, February 16, 2011 4:15 pm, JeffMGreg wrote:
> The previous one had an error, this one should work
>
> import xlrd, xlwt
> import copy
> import xlutils.copy
>
> def copy_sheet(workbook, source_index, new_name):
> '''
> workbook == book in use
> source_index == index of sheet you want to copy (0 start)
> new_name == name of new copied sheet
> '''
>
> # make a copy of desired sheet in memory
> new_sheet = copy.copy(workbook.get_sheet(source_index))
>
> # append copy to worksheets list
> workbook._Workbook__worksheets.append(new_sheet)
>
> # find out how many sheets are in the workbook
> append_index = len(workbook._Workbook__worksheets)-1
>
> # activate the last sheet in the workbook
> workbook.set_active_sheet(append_index)
>
> # rename the last sheet in the workbook
> workbook.get_sheet(append_index).set_name(new_name)

Looks like our messages are crossing. You don't need append_index at all,
but if you persist is using it, try append_index = -1

[JeffMGreg]

Hi John,

Thanks for the reply, I really appreciate the time you put into it
and I learned a few things from reading it.

I guess I have some syntax seeping over from matlab, there you have to
select a sheet and then activate it if you want to modify it. The -1
much nicer, don't know how I missed that one.

[John Machin]

On Feb 17, 2:04 pm, JeffMGreg <jeffmg...@gmail.com> wrote:
> Hi John,
>
> Thanks for the reply,  I really appreciate the time you put into it

Not a problem. Do you intend to come back with another version?

Cheers,
John

[Jeff Gregory]

I would like to, but I'm running into a problem that I can't seem to get around in an elegant manner.  The way I'm doing it now it very ugly and would most likely degrade performance, especially for large workbooks.  I'll work on it though and let you know.

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

--
Jeff

[John Machin]

On Feb 20, 12:09 pm, Jeff Gregory <jeffmg...@gmail.com> wrote:
> I would like to, but I'm running into a problem that I can't seem to get
> around in an elegant manner.  The way I'm doing it now it very ugly and
> would most likely degrade performance, especially for large workbooks.  I'll
> work on it though and let you know.

What is the problem that appears to have only an ugly and possibly
performance degrading solution?

[Jeff Gregory]

Every time I copy a sheet, it adds it to the end of the worksheet list but then activates all the sheets in the workbook.  I try and deactivate the sheets but when I select one to modify, it reactivates all of them again.  It seems like all the copied sheets are referencing the source sheet.  I can get around this by saving and them reopening before modifying anything; I don't want to do that every time I copy a sheet though. 

Attached is my code with a test xls file. If you look at cell F6 after run you'll see they all have the same text.

I'm sure it's some really small thing I'm overlooking here but I'm stumped.

Jeff

--

You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

--
Jeff

[John Machin]

On Feb 20, 12:45 pm, Jeff Gregory <jeffmg...@gmail.com> wrote:
> Every time I copy a sheet, it adds it to the end of the worksheet list but
> then activates all the sheets in the workbook.  I try and deactivate the
> sheets but when I select one to modify, it reactivates all of them again.
>  It seems like all the copied sheets are referencing the source sheet.

You appear to have ignored what I said in a previous message:

"""
[after rewriting your code and putting in a rest case]

[John Machin]

On Jun 22, 7:30 am, EggButler <eggbut...@gmail.com> wrote:
> John, I have been working off of this conversation all week.  Please tell
> me what command is better than copy?
>

"command"??

In the context that you are quoting, "the copy module" is the Python
copy module, and its deepcopy function is much more suitable for what
the OP was trying to do.

You might like to start a new question, outlining *your* problem.

[EggButler]

OPP yeah you know me.

I have the same exact problem as the OP's problem except that I don't have as much experience.  When I first thought about posting my problem I figured it was better to keep going with this because I am having the same exact problems.  I felt bad because you had said yourself how we were ignoring your previous posts.  I am trying to pay attention to them as closely as possible.

When I use the deepcopy modufuncmand I get Excel sheets with "#Value!" errors.  Can you please post a reply to this or the other question I have posted to this group so that I don't have to keep explaining (admittedly bad explaining due to the obvious lack of pycabulary that I have) what I am trying to do.

It also looks like you helped someone else with #Value! errors with XLWT but those solutions didn't seem appropriate to my situation.

Thanks in advance for what ever help you can get me.

theEB.

[John Machin]

On Mon, June 25, 2012 6:22 am, EggButler wrote:
> OPP yeah you know me.
> I have the same exact problem as the OP's problem except that I don't have
> as much experience. When I first thought about posting my problem I
> figured it was better to keep going with this because I am having the same
> exact problems. I felt bad because you had said yourself how we were
> ignoring your previous posts. I am trying to pay attention to them as
> closely as possible.
> When I use the deepcopy modufuncmand I get Excel sheets with "#Value!"
> errors. Can you please post a reply to this or the other question I have
> posted to this group so that I don't have to keep explaining (admittedly
> bad explaining due to the obvious lack of pycabulary that I have) what I
> am
> trying to do.
> It also looks like you helped someone else with #Value! errors with XLWT
> but those solutions didn't seem appropriate to my situation.

See attached files. Turns out that deepcopy is a bit too deep ... see new
copy function. Note: input file has headings only, no data.

Other remarks:

(1) pls don't use tabs for indentation, use 4 spaces (configure your text
editor to translate tab key to 4 spaces)
(2) last heading says "Column K+M+O" but (a) this appears in col O (b)
column letters are not shown therefore a little error-prone ...

HTH
John

[EggButler]

Helps?  That kills the problem!

About that other stuff:

1) I figured that was one of those style "guidelines" and not a rule.  So I started using two character long tabs.  I will stop.

2) about that form, I was just using that because it's the simplest form that I have to fill out.  didn't really check it out that hard.  The rest of that data comes from a scale and some python calculations...it will no longer put into the excel forms.

Thanks for looking at that code and making it all look really straight forward.

(Holy Moly look at how much better that looks.)

As for the new copy_sheet function, thank you so much for that.  I will take it.  

Thanks for putting up with me and my first six weeks with Python.

theEB.