how to find the next available column in an excel spreadsheet.

[bd62]

I want to output the results of a Python script to the next available column (one without any data in it) of a specified row in an excel spreadsheet (XLS). Below is the portion of my code to do that:

col_test = 1
col_num = open_workbook(subnet)
sheet = col_num.sheet_by_index(0)
cell = sheet.cell(1,col_test)
cell.ctype == XL_CELL_TEXT
 
while cell.ctype == True:
   col_test = col_test + 1
   cell = sheet.cell(1,col_test)
   print cell.ctype == XL_CELL_TEXT
 
column = cul_test

The results I get are below:

True
True
True
True
True
True
True
True
True

Traceback (most recent call last):
File "/Users/myname/Desktop/TEST_Subnet_Pin g.py", line 48, in <module>
cell = sheet.cell(1,co l_test)
File "/Library/Frameworks/Python.framewor k/Versions/2.6/lib/python2.6/site-packages/xlrd/sheet.py", line 255, in cell
self._cell_type s[rowx][colx],
IndexError: array index out of range


It is correctly starting at column 1, checking to see if theres data there and if so it moves to the next column of that row and checks. When it gets to column 10, a column without any data, I get the IndexError. What I want is the cul_test number so that I can tell my script to output the results in whatever column number cul_test is. How can I set this up so that it doesn't give me any errors when it gets to a column that has absolutely no data in it at all? My expectation was that the WHILE would quit after finding a column with no data and I would have that cul_test number. I am not a programmer so this is all pretty new to me. It's probably something simple but I can not figure out what. Any help would be greatly appreciated and if you need more info please let me know. Thanks!

[Glenton]

Hi

I haven't really used python with excel before, but can tell you a basic problem with your code (By the way, you'd probably have got answers sooner if you used code tags, because it's much easier for people to read!)

Thought 1: I could be way off here, since I'm not familiar with the functions you're using, but is it possible that cell.ctype is always true, and so you're moving all way across your spreadsheet until you run out of columns?

Try run it again, but change the print line to:
print col_test, cell.ctype
and post back.

Thought 2: If it's "working" in the sense that it's finding the first blank column and returning an error at cell = sheet.cell(1,co l_test), then there's probably another way to do it.

[bvdet]

I have played with xlrd a few times. It's great for reading an Excel spreadsheet, but you cannot write to an Excel spreadsheet. You could write all the data to a CSV file, then import the file back into Excel, or you could use win32com.client .Dispatch("Exce l.Application") . Example using win32com:

import win32com.client
xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible=0
xlWb = xlApp.Workbooks.Open(r"D:\SDS2_7.0\macro\win32com\Read.xls")
print xlApp.Worksheets("Sheet1").Name
xlApp.Worksheets("Sheet2").Range("B1").Value = "green"
sheet = xlApp.Worksheets("Sheet3")
sheet.Range("C3").Value = "money"
sheet.Range("D4").Value = 9999
print sheet.Range("C3").Value
print sheet.Range("D4").Value
xlWb.Close(SaveChanges=1)
xlApp.Quit()

Use xlrd.sheet.Shee t object method rows() and row_values to read the data. Example:

>>> import xlrd
>>> wb = xlrd.open_workbook('workbook.xls')
>>> sheet1 = wb.sheet_by_index(6)
>>> sheet1.nrows
68
>>> sheet1.row_values(13)
[u'GARAGE L1', '', u'POUR 1', u'L1', 40196.0, u'Embeds - Vault', u'Detail D03 ?', 21.0, 40175.0, '', u'None Shown', '', '', '', '']
>>> len(sheet1.row_values(13))
15
>>> sheet1
<xlrd.sheet.Sheet object at 0x00CA7610>
>>> 

[bd62]

Thanks Glenton. Code tags, I will remember that.

cell.ctype will return False when it checks a cell that has no data in it PROVIDING there's data somewhere within the same column, just on a different row, than the cell resides. i hope that makes sense. where I get the error is when it hits a column that has absolutely no data in any of it's rows.

I agree with your second thought. I'm certain there's another way to do it.

[bvdet]

If you would use sheet_object.ro w_values(row_nu mber) you would know exactly how may columns are occupied and avoid the error. See my example code above. You could also directly read the number of columns like this:

wb = xlrd.open_workbook('workbook.xls')
sheet1 = wb.sheet_by_index(1)
numCols = sheet1.ncols

Then you know which column is unoccupied.

[bd62]

Yes thanks bvdet. Took me a little while to test it and respond from your previous post but sheet_object.ro w_values(row_nu mber) worked perfectly. That's exactly what I needed. Thanks for your help