Python - Excel: Finding the first empty row in a column

Question

working from my last question I've managed to get a good chunk of the way to get my system finished. Of course I've run across a problem.

I basically have a program that plays a game. Every correct answer adds 10 to the global variable 'points'. Then I want to add 'points' into an excel spreadsheet.

This is where I get very stuck. I'm running XLRD-0.8.0, XLUTILS-1.4.1 and XLWT-0.7.5.

Of course I've looked up different things but they don't seem to work for me.

This is a simplified version of my code:

 import pygame, pygame.font, pygame.event, string, xlwt, xlrd, xlutils, socket


 points = 0

 def Uploadpoints(wbname):

     global points 
     wb = xlrd.open_workbook(wbname)

     # CODE TO FIND FIRST EMPTY CELL IN COLUMN 1 GOES HERE

     wb.write(row,0,points)
     wb.save()

 Uploadpoints('workbook1.xls')

I thought of doing something like this but I'm not sure how I would go about it so I post pseudo-code.

Define worksheet: ws = 'sheet 1' [Done]

Define column: col = 0 [column A] [Done]

Search for first row in col that is empty: ??? [Not Done]

Define first row that is empty as row: row =????? [Not Done]

Any help would be greatly appreciated. Thanks in advance.

Do you have Excel on the machine which will be running this game? If so might as well use win32com and use Excel itself to do this if this is causing problems. It's beautifully easy with that: Columns("A").Find("", Cells(Rows.Count, "A")). — Aleksander Lidtke
Yes Excel is on the machine. I'm not familiar with win32com, what would need to be changed / done to the code then? — user3191879
Never used xlwt and xlrd, so hard to say. The general framework would stay the same, you'd just tell Excel to do the things you need it to do. Have a look here to judge it roughly: pythonexcels.com/basic-excel-driving-with-python — Aleksander Lidtke

wnnmaw wnnmaw · Accepted Answer · 2014-01-22T21:09:27

My answer to this question a few days ago is very relelvant.

Basically whats going wrong is that xlrd and xlwt are different modules with different objects. The object you read in with xlrd.open_workbook() IS NOT the same object which xlwt knows how to write to

To get around this, theres the copy function in xlutils.

from xlutils.copy import copy
import os

wb = xlrd.open_workbook(name) 

# Code to find the last open cell

wb = copy(wb)   #This is the important line!

sheet = wb.get_sheet(num)  #num is the index of the sheet you want to edit
sheet.write(whatever you want to write)

wb.save(name)

Python - Excel: Finding the first empty row in a column

1 Answers