Copy worksheets with formulas and cell format properties

[Sammie]

Hi All:

I am trying to learn the basics of xlrd, xlwt and xlutils.
From the codes in one of the examples discussed in this group:

# ***************** codes ****************
import xlrd
import xlutils.copy

filename = 'readfile.xls'
rb = xlrd.open_workbook(filename, on_demand=True)
ws = xlutils.copy.copy(rb)

#pick first sheet
shw = ws.get_sheet(1)

#some processing here (writing to shw), for example:
shw.write(1, 1, 1)
# ... more writing ...

#saving file
ws.save('writefile.xls')

#end of code *******************************

The above codes do NOT copy sheet formulas and cell format properties,
such as font and background colors, font types, border types (line
widths and types) etc.

How do I make the change of the above codes to copy them?

I want to change the above codes to copy the first sheets of several
workbooks in the current directory and copy them into one output
workbook and save also into the current directory.
Any suggestions?

Thanks and I appreciate any help.
-Sammie

[Sammie]

Hi all again:

I also tried to do the copy of multiple worksheets into a single
workbook using COM, but don't know enough VBA to complete:

# ***************** codes ****************
import win32com.client
import win32com.client.dynamic
import os
import glob


XLApp = win32com.client.dynamic.Dispatch('Excel.Application')
cdir = os.getcwd()
print 'Current Working Directory: %s' %cdir
os.chdir(cdir)

xlFiles = glob.glob(cdir + "\\*.xls") # a list of all *.xls files

for xlfile in xlFiles:
XLWorkbook = XLApp.Workbooks.Open(xlfile)
XLApp.Visible = -1
XLWorksheets = XLWorkbook.Worksheets
???

#end of code *******************************

If I use COM to do the worksheet copies, the sheet properties (colors,
border styles, font styles etc) are retained.
Want to find out how to do this function two different ways, in COM
and in xlutils. Can anyone give me some advices?

Thanks

-Sammie

====================================================

[John Machin]

On 19/07/2009 4:28 AM, Sammie wrote:
> Hi All:
>
> I am trying to learn the basics of xlrd, xlwt and xlutils.
>>From the codes in one of the examples discussed in this group:

[snip]

>
> The above codes do NOT copy sheet formulas and cell format properties,
> such as font and background colors, font types, border types (line
> widths and types) etc.

... and the code wasn't trying to do any of that.

xlrd doesn't support the extraction of formulas at the moment. When it
does ...

>
> How do I make the change of the above codes to copy them?

... you'd be wanting to use xlutils instead of reinventing large slabs
of it.

>
> I want to change the above codes to copy the first sheets of several
> workbooks in the current directory and copy them into one output
> workbook and save also into the current directory.
> Any suggestions?

General suggestion: work through (or at least read) the tutorial at
http://www.python-excel.org/

About your particular problem: see response to your next message.

Cheers,
John

[John Machin]

On 19/07/2009 5:22 AM, Sammie wrote:
> Hi all again:
>
> I also tried to do the copy of multiple worksheets into a single
> workbook using COM, but don't know enough VBA to complete:
>
> # ***************** codes ****************
> import win32com.client
> import win32com.client.dynamic
> import os
> import glob
>
>
> XLApp = win32com.client.dynamic.Dispatch('Excel.Application')
> cdir = os.getcwd()
> print 'Current Working Directory: %s' %cdir
> os.chdir(cdir)
>
> xlFiles = glob.glob(cdir + "\\*.xls") # a list of all *.xls files
>
> for xlfile in xlFiles:
> XLWorkbook = XLApp.Workbooks.Open(xlfile)
> XLApp.Visible = -1
> XLWorksheets = XLWorkbook.Worksheets
> ???
>
> #end of code *******************************
>
> If I use COM to do the worksheet copies, the sheet properties (colors,
> border styles, font styles etc) are retained.
> Want to find out how to do this function two different ways, in COM
> and in xlutils. Can anyone give me some advices?

1. At the moment, COM is the only possibility.
2. Google is your friend e.g.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=829
3. Don't top-post.
4. Quote only the relevant parts of any message that you are replying
to, especially when it's your own.

Cheers,
John