How to remove spaces at the beginning and end of an XLS output

Question

I have the code below, for him to open a spreadsheet on XLS and show me the header. however I would like the left and right spaces to be removed and the internal spaces to be replaced by underline there is some function in the xlrd do that?

   import xlrd

    def xlread(arq_xls):

        xls = xlrd.open_workbook(arq_xls)
        # Pega a primeira planilha do arquivo
        plan = xls.sheets()[0]

        # Para i de zero ao numero de linhas da planilha
        for i in xrange(plan.nrows):
            # Le os valores nas linhas da planilha
            yield plan.row_values(0)

    for linha in xlread("teste.xls"):
        print linha

Example of Saida;

[u'Cod.Lab.', u'Laudo', u'Cliente', u'Fazenda', u'Talh\xe3o', u'Profundidade', u'Descri\xe7\xe3o', u' pH          ', u'pH', u' pH           ', u'CE', u'MO ', u'P resina', u'S-SO4', u'K (res)', u'Na', u'Ca', u'Mg', u'Al'

As you can see where it has ~ it is not figured out and in "pH" there are several spaces.

Answer 1

To remove the whitespace before and after the text, you can use the .strip().

To print the special characters, you can put the .encode('utf-8').

To put everything in Lower case, put a .lower() at the end of the sentence.

To swap the various spaces in the middle of the string for a single _, you can make a .split() to separate all words within the string and then join them with the "_".join().

Putting the four together would look like this:

print "_".join(celula.encode('utf-8').strip().lower().split())

Whereas celula is an iteration of each element of linha.

Besides you have a logic error in this excerpt:

yield plan.row_values(0)

Where instead of 0 should be i.

Putting everything back into your code would look like this:

import xlrd


def xlread(arq_xls):

    xls = xlrd.open_workbook(arq_xls)
    # Pega a primeira planilha do arquivo
    plan = xls.sheets()[0]

    # Para i de zero ao numero de linhas da planilha
    for i in xrange(plan.nrows):
        # Le os valores nas linhas da planilha
        yield plan.row_values(i)

for linha in xlread("teste.xlsx"):
    for celula in linha:
        print "_".join(celula.encode('utf-8').strip().lower().split())

Answer 2

I believe that spaces can be easily removed as follows:

for linha in xlread("teste.xls"):
    for item in linha:
        print item.strip()

As for the special characters I think you should leave so, when they are read from the list they should return to their original format.