Read certain columns of xls file in Python with XLRD

Question

I am a beginner in language and I am trying to display the contents of a spreadsheet on the front end with Python. With this code I am displaying the result of the columns name, surname, city and note, constant in the spreadsheet, and in that order.

def exibir(request):      
    workbook = xlrd.open_workbook("teste.xlsx", encoding_override="cp1252")
    worksheet = workbook.sheet_by_index(0)
    dados = []
    
    for row_num in range(worksheet.nrows):
        if row_num == 0:
            continue
        row = worksheet.row_values(row_num)
        usuario = Usuario(row[0], row[1], row[2], float(row[3]))
        dados.append(usuario)
    return render(request, 'lista.html', {'lista': dados})

Now I want to make me receive the file and display the result of 3 specific columns of the spreadsheet, selected by the column name (name, city and email). I cannot select by index because each sheet can vary the number of columns, since the names do not vary. Can anyone help me?

Answer 1

Opa,

I believe you’re looking for the method col_values. See the documentation.

col_values(colx, start_rowx=0, end_rowx=None)

Returns a Slice of the values of the Cells in the Given column.

In your case, it would be something like:

workbook = xlrd.open_workbook("teste.xlsx", encoding_override="cp1252")
worksheet = workbook.sheet_by_index(0)
columnValues = sheet.col_values(columnIndex)  # columnIndex é o índice da coluna. Começando em 0 para a coluna A.

If you have a header line, you can do something like:

columnValues = sheet.col_values(columnIndex, 1)

I hope it helps.