Extracting a sheet and adding it to a new workbook

[refp16]

Hello,

I want to extract a sheet from one workbook and insert it in a new
excel file (I need an exact copy of the sheet).

I thought I could use something like this:

from xlrd import open_workbook, cellname
book = open_workbook(r'F:\MyDocuments\tut_python\Libro1.xls')
sheet = book.sheet_by_name('Hoja1')

import xlwt
wbk = xlwt.Workbook()
wbk_sheet = wbk.add_sheet(sheet)
wbk.save(r'F:\MyDocuments\tut_python\test.xls')

but add.sheet() only adds a new blank sheet to the recently created
workbook.

I tried something in this post
http://groups.google.com/group/python-excel/browse_thread/thread/ecdd8980f297b2f8/c6f1f563f9c46394?lnk=gst&q=import+sheet+object#c6f1f563f9c46394
but was unsuccessful.

Can this be done with xlrd and xlwt? How would it be?

Thanks in advance.

[John Machin]

On Jan 22, 7:24 am, refp16 <ref...@gmail.com> wrote:
> Hello,
>
> I want to extract a sheet from one workbook and insert it in a new
> excel file (I need an exact copy of the sheet).
>
> I thought I could use something like this:
>
> from xlrd import open_workbook, cellname
> book = open_workbook(r'F:\MyDocuments\tut_python\Libro1.xls')
> sheet = book.sheet_by_name('Hoja1')
>
> import xlwt
> wbk = xlwt.Workbook()
> wbk_sheet = wbk.add_sheet(sheet)

The argument should be the NAME of a new worksheet. I'm highly
surprised that this didn't throw an exception.

> wbk.save(r'F:\MyDocuments\tut_python\test.xls')
>
> but add.sheet() only adds a new blank sheet to the recently created
> workbook.
>

> I tried something in this posthttp://groups.google.com/group/python-excel/browse_thread/thread/ecdd...
> but was unsuccessful.

What is "something"?

>
> Can this be done with xlrd and xlwt? How would it be?

Get the tutorial that you can access via http://www.python-excel.org,
and read the sections on xlwt, xlutils.copy, and xlutils.filter.

HTH

John

[refp16]

On Jan 21, 5:07 pm, John Machin <sjmac...@lexicon.net> wrote:
> On Jan 22, 7:24 am, refp16 <ref...@gmail.com> wrote:
>
> > Hello,
>
> > I want to extract a sheet from one workbook and insert it in a new
> > excel file (I need an exact copy of the sheet).
>
> > I thought I could use something like this:
>
> > from xlrd import open_workbook, cellname
> > book = open_workbook(r'F:\MyDocuments\tut_python\Libro1.xls')
> > sheet = book.sheet_by_name('Hoja1')
>
> > import xlwt
> > wbk = xlwt.Workbook()
> > wbk_sheet = wbk.add_sheet(sheet)
>
> The argument should be the NAME of a new worksheet. I'm highly
> surprised that this didn't throw an exception.

Excatly, I' m aware it is the NAME (it is acknowledged three lines
below). I just put that code to show that I wanted to be able to do
something like that. "something like that" refers to giving the
add_sheet() method a sheet object for it to append.

> > wbk.save(r'F:\MyDocuments\tut_python\test.xls')
>
> > but add.sheet() only adds a new blank sheet to the recently created
> > workbook.
>
> > I tried something in this posthttp://groups.google.com/group/python-excel/browse_thread/thread/ecdd...
> > but was unsuccessful.
>
> What is "something"?

Sorry for that useless "something". What I did was create a
my_add_sheet() method in the source code (xlwt\Workbook.py, aprox.
line 300) where it is given the possibility of managing not only
NAMEs, but also SHEETs. (At least that is what I thought I was doing.
I confess to not understanding completely what the original poster was
up to but the problem seemed related.) I do not have the new code with
me, so I cannot show it to you for the moment. For some reason I can`t
access the original post either. Sorry.

>
>
>
> > Can this be done with xlrd and xlwt? How would it be?
>

> Get the tutorial that you can access viahttp://www.python-excel.org,

> and read the sections on xlwt, xlutils.copy, and xlutils.filter.
>

I have. Did it before posting but not closely enough. I will try
again.

The problem has a very simple logic to it. I just want to take a sheet
and insert it in a new workbook. I just don't know if this can be done
using a SHEET object (I did not find anything in the manual at the
time). That is, without using commands for copying rows (or columns),
changing the formats, etc.

Thanks.

> HTH
>
> John

[Chris Withers]

On 22/01/2011 17:06, refp16 wrote:
>
> The problem has a very simple logic to it.

From your perspective, perhaps. You're ignoring how .xls stores
formatting...

> I just want to take a sheet
> and insert it in a new workbook. I just don't know if this can be done
> using a SHEET object (I did not find anything in the manual at the
> time). That is, without using commands for copying rows (or columns),
> changing the formats, etc.

You want xlutils.filter, with a filter that excludes all but the sheet
you want...

cheers,

Chris

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

[refp16]

Hello,

Some weeks ago I resolved this writing the following code:

import xlrd
import xlwt
from xlutils.copy import copy


# COPYING EXCEL SHEETS FROM DIFFERENT SOURCE FILES (.XLS) USING xlrd,
xlwt

# Parameters to fiddle with
# Source parameters
startYear = 1997
endYear = 1998
path = r'D:\My Documents\tut_python'
fileNames = [r'\COUVEN' + str(year)[2:] + 'CP.xls' for year \
in range(startYear,
endYear + 1)]
pathNames = [path + aFile for aFile in fileNames]
sourceSheetName = 'Hoja2' # Must be the same name in all files
# Target parameters
targetPath = r'D:\My Documents\tut_python'
targetFile = r'\test.xls'

# Create the target workbook
wb = xlwt.Workbook()

# Create target worksheets and copy source data
for aFile, aPath in zip(fileNames, pathNames):
# Create the target worksheet and name it
ws = wb.add_sheet(aFile[1:-6])
# Open the source book and sheet
book = xlrd.open_workbook(aPath, on_demand=True,
formatting_info=True)
# The source sheet name must be the same on all files
sheet = book.sheet_by_name(sourceSheetName)
##print sheet.cell_xf_index(0,0)
# Get number of rows and columns that contain data
numRow = sheet.nrows
numCol = sheet.ncols

for row in xrange(numRow):
# Get all the rows in the sheet (each rows is a list)
rowList = sheet.row_values(row)
for col in xrange(numCol):
# Get all the values in each list
oneValue = rowList[col]
# Copy the values to target worksheet
ws.write(row, col, oneValue)

# Save the target workbook to file
wb.save(targetPath + targetFile)

This is not how I wanted to solve it but it worked at the time. I was
able to copy the data in the sheets but of course not the formats
(even though I wanted to - I tried some things here but none worked).

Anyway, I just recently saw the thread on "copy sheet" and the code
there is much closer to what I wanted.

Kudos on these modules. I really like the speed with which they do
their work.

Thanks.