number of columns in xlwt worksheet

Question

I can't seem to find a way to return the value of the number of columns in a worksheet in xlwt.Workbook(). The idea is to take a wad of .xls files in a directory and combine them into one. One problem I am having is changing the column position when writing the next file. this is what i'm working with thus far:

import xlwt, xlrd, os

def cbc(rd_sheet, wt_sheet, rlo=0, rhi=None,
rshift=0, clo=0, chi=None, cshift = 0):
    if rhi is None: rhi = rd_sheet.nrows
    if chi is None: chi = 2#only first two cols needed
    for row_index in xrange(rlo, rhi):
        for col_index in xrange(clo, chi):
            cell = rd_sheet.cell(row_index, col_index)
            wt_sheet.write(row_index + rshift, col_index + cshift, cell.value)

Dir = '/home/gerg/Desktop/ex_files'
ext = '.xls'
list_xls = [file for file in os.listdir(Dir) if file.endswith(ext)]
files = [Dir + '/%s' % n for n in list_xls]
output = '/home/gerg/Desktop/ex_files/copy_test.xls'
wbook = xlwt.Workbook()
wsheet = wbook.add_sheet('Summary', cell_overwrite_ok=True)#overwrite just for the repeated testing

for XLS in files:
    rbook = xlrd.open_workbook(XLS)
    rsheet = rbook.sheet_by_index(0)
    cbc(rsheet, wsheet, cshift = 0)

wbook.save(output)

list_xls returns:

['file2.xls', 'file3.xls', 'file1.xls', 'copy_test.xls']

files returns:

['/home/gerg/Desktop/ex_files/file2.xls', '/home/gerg/Desktop/ex_files/file3.xls', '/home/gerg/Desktop/ex_files/file1.xls', '/home/gerg/Desktop/ex_files/copy_test.xls']

My question is how to scoot each file written into xlwt.workbook over by 2 each time. This code gives me the first file saved to .../copy_test.xls. Is there a problem with the file listing as well? I have a feeling there may be. This is Python2.6 and I bounce between windows and linux.

Thank you for your help, GM

How is this related to ArcGIS (or GIS at all)? It looks like a Python/Excel problem: this means you might get quicker (and more) advice migrating the question to SO. — whuber, May 19 '12 at 22:27
Full question is: How to make table summaries of all fields in a features class and output them into a single XLS using python as an arcscript tool and by an automated python script on a daily, weekly basis? I've gotten thus far with this question. — gm70560, May 22 '12 at 13:34

score 1 · Accepted Answer · answered May 20 '12 at 23:20

You are using only the first two columns in each input spreadsheet. You don't need "the number of columns in a worksheet in xlwt.Workbook()". You already have the cshift mechanism in your code, but you are not using it. All you need to do is change the loop in your outer block, like this:

for file_index, file_name in enumerate(files):
    rbook = xlrd.open_workbook(file_name)
    rsheet = rbook.sheet_by_index(0)
    cbc(rsheet, wsheet, chi = 2, cshift = file_index * 2)

For generality, change the line
if chi is None: chi = 2
in your function to
if chi is None: chi = rsheet.ncols
and pass chi=2 in as an arg as I have done in the above code.

I don't understand your rationale for overriding the overwrite check ... surely in your application, overwriting an existing cell value is incorrect?

You say "This code gives me the first file saved to .../copy_test.xls". First in input order is file2.xls. The code that you have shown is overwriting previous input and will give you the LAST file (in input order) , not the first ... perhaps you are mistaken. Note: The last input file 'copy_test.xls' is quite likely be a previous OUTPUT file; perhaps your output file should be put in a separate folder.

Enumerate! Thank you for helping this idiot. The cshift wasn't working with the for loop and using enumerate makes things happy. I owe you a one up. — gm70560, May 22 '12 at 13:36

number of columns in xlwt worksheet

1 Answers1