Retrieve first empty column and row using xlwings

Question

I am looking for a way to find the first empty column and the row. As a part of my use case, I am trying to find out H3 (to add current date) and then H4 and H5 (to add my daily metrics) [screenshot attached]. I have tried below with xlwings.

import xlwings as xw 
from xlwings import Range, constants 
wb = xw.Book(r"path to xlsx") 
sht1 = wb.sheets['Sheet1'] 
sht1.range('G3').value = current_date
sht1.range('G4').value = 5678 
sht1.range('G5').value = 1234 
wb.save(r"path to xlsx") 

The issue is I have hardcoded the column and row references in the script. I want H3, H4 and H5 to find out dynamically through xlwings and update the metrics programmatically. Can someone guide me on this?

Answer 1

You can do this by finding the last column of the data used. Here are two options to get this data:

1. Using SpecialCells(11), which is a VBA function accessed through the .api, information about this can be found here.
2. Using .end("right"), the equivalent of ctrl + right in Excel.

Option 1 would work well if there is no other data in the spreadsheet, so the last cell in the sheet would be the correct column. This is convenient and doesn't require knowledge of the starting cell (in this case B3).

Option 2 would be preferred for spreadsheets where other data may be on the sheet, so the last cell will not necessarily be in the last column of your desired data. This option does, however, require no missing columns as moving the last right-most cell in the group of cells would therefore not strictly be the last column of the data.

An alternative could be to import all the data to Python as a pd.DataFrame, then append an additional column and return. If you need to append many columns of data, this would probably be more efficient (especially if you already have a DataFrame of the data you are pasting to Excel).

The last_col is an integer, as this is most easily manipulated (such as increasing by 1). Therefore, the range has also be modified to make use of this, instead of using A1 style (e.g. range("A1")), a tuple is used of format (row_num, col_num) (e.g. range((row_num, col_num))).

import xlwings as xw            
import datetime as dt

current_date = dt.date.today().strftime("%d-%b-%y")
wb = xw.Book(r"path to xlsx")
sht1 = wb.sheets['Sheet1'] 

# options 1: last column in the sheet through SpecialCells
last_col = sht1.range("A1").api.SpecialCells(11).Column
# option 2: starting at cell B3, the first in the date headers, move to the right (like ctrl+right in Excel)
last_col = sht1.range("B3").end("right").column

# paste new values
sht1.range((3, last_col+1)).value = current_date
sht1.range((4, last_col+1)).value = 5678
sht1.range((5, last_col+1)).value = 1234


wb.save(r"path to xlsx")