Appending a Sheet

[JohnC]

I was able to copy a sheet and change the header data in it.

 

Now I want to append another sheet to the bottom of it.

 

Can someone give me an idea of how to script that?

 

from xlrd import open_workbook,cellname
from xlutils.copy import copy

rb = open_workbook('D:\\PythonData\\ ... in.xls')
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)

ws.write(0,0,'CLIENTNAME')
ws.write(0,1,'PHONE')
ws.write(0,2,'ADDRESS')
ws.write(0,3,'CITY')
ws.write(0,4,'ZIP')

 

wb.save('D:\\PythonData\\ ... out.xls')

[Chris Withers]

Hi John,

On 30/05/2012 16:14, JohnC wrote:
> wb = copy(rb)

At this point you just have an xlwt WorkBook object, so you'd use the
normal xlwt apis:

ws2 = wb.add_sheet('My New Sheet')

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[JohnC]

thank you for your suggestion, but does this not just add another sheet to the book with the my other data in it?

 

i am actually looking to append the data from the second sheet after the data of the first sheet, all on one sheet.

 

am I looking for something impossible?

 

John

[John Machin]

On Tuesday, June 5, 2012 6:51:42 AM UTC+10, JohnC wrote:

thank you for your suggestion, but does this not just add another sheet to the book with the my other data in it?

That was a not-unreasonable interpretation of your "I want to append another sheet to the bottom of it." Evidently you mean "append the contents of some other sheet to the end of the original sheet".

Try this:

offset = rs.nrows
for rowx in xrange(other.nrows):
    for colx in xrange(other.ncols):
        cell = other.cell(rowx, colx)
        ws.write(rowx+offset, colx, cell.value, some_style)

By the way, where do you get  "other" from?

 

[JohnC]

John Machin,

Thank you for your piece of code.  I feel I am almost there and probably inexperience is keeping from achieving my goal.

 

I am able to make your code work until I add my variable to the ws.write() statement.  ws.write(rowx+offset,colx,bdat) with bdat being the cell data from the second sheet.

 

If I replace bdat with 'text', it works great.

 

can you see what is missing?  here is my complete code.

--------------------------------------------------------------------------------

from xlrd import open_workbook,cellname
from xlwt import easyxf
from xlutils.copy import copy

rba = open_workbook('D:\\PythonData\\PythonScripts\\CCC\\TestFile_A.xls')
rbb = open_workbook('D:\\PythonData\\PythonScripts\\CCC\\TestFile_B.xls')
shta = rba.sheet_by_index(0)
shtb = rbb.sheet_by_index(0)
wb = copy(rba)
ws = wb.get_sheet(0)

offset = shta.nrows
for rowx in xrange(shtb.nrows):
    for colx in xrange(shtb.ncols):
        bdat = shtb.cell(rowx,colx)
        print bdat
        ws.write(rowx+offset,colx,bdat)

wb.save('D:\\PythonData\\PythonScripts\\CCC\\TestFileOut_2.xls')

----------------------------------------------------------------------------------------

 

Thank you for your help.

 

JohnC

 

 

On Wednesday, May 30, 2012 8:14:22 AM UTC-7, JohnC wrote:

[Adrian Klaver]

On 06/07/2012 03:23 PM, JohnC wrote:
> John Machin,
> Thank you for your piece of code. I feel I am almost there and probably
> inexperience is keeping from achieving my goal.
> I am able to make your code work until I add my variable to the
> ws.write() statement. ws.write(rowx+offset,colx,bdat) with bdat being
> the cell data from the second sheet.
> If I replace bdat with 'text', it works great.
> can you see what is missing? here is my complete code.
> --------------------------------------------------------------------------------
> from xlrd import open_workbook,cellname
> from xlwt import easyxf
> from xlutils.copy import copy
> rba = open_workbook('D:\\PythonData\\PythonScripts\\CCC\\TestFile_A.xls')
> rbb = open_workbook('D:\\PythonData\\PythonScripts\\CCC\\TestFile_B.xls')
> shta = rba.sheet_by_index(0)
> shtb = rbb.sheet_by_index(0)
> wb = copy(rba)
> ws = wb.get_sheet(0)
> offset = shta.nrows
> for rowx in xrange(shtb.nrows):
> for colx in xrange(shtb.ncols):
> bdat = shtb.cell(rowx,colx)

The above is getting you a reference to the cell at that location, not the
cell value, which I presume is what you want.

From here:
https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html#sheet.Sheet-class

I would say you want:

cell_value(rowx, colx) [#]
Value of the cell in the given row and column.

> print bdat
> ws.write(rowx+offset,colx,bdat)
> wb.save('D:\\PythonData\\PythonScripts\\CCC\\TestFileOut_2.xls')
> ----------------------------------------------------------------------------------------
> Thank you for your help.
> JohnC
>

--
Adrian Klaver
adrian...@gmail.com

[John Machin]

On Jun 8, 8:23 am, JohnC <culbe...@saccounty.net> wrote:
> John Machin,
> Thank you for your piece of code.  I feel I am almost there and probably
> inexperience is keeping from achieving my goal.
>
> I am able to make your code work until I add my variable to the ws.write()
> statement.  ws.write(rowx+offset,colx,bdat) with bdat being the cell data
> from the second sheet.
>
> If I replace bdat with 'text', it works great.

bdat is a Cell instance, 'text' is a str object ... you shouldn't
expect them to behave similarly

> can you see what is missing?  :

>         bdat = shtb.cell(rowx,colx)
>         print bdat

Surely that didn't print what you expected ...

>         ws.write(rowx+offset,colx,bdat)

Re-read my code and compare it to yours:

[JohnC]

OK .. I found the issue, comparing your code to mine.  Made the correction and it works great now.

 

Thank you very much for the help and the schooling.  I appreciate it greatly.

 

John C

 

On Wednesday, May 30, 2012 8:14:22 AM UTC-7, JohnC wrote: