Format cells as General format

[igorbar...@gmail.com]

Hello;

I'm new in Python and i'm using openpyxl to convert a txt file to an xlsx file.

I try to use Numbers Format but I don't really understand how to do =/

The excel file is being created correctly but with all the cells as "Text" format, and I need to convert to 'General'

Can anyone help me?

Here is my code

import sys

import openpyxl

delimitador, caminho, destino = sys.argv[1], sys.argv[2], sys.argv[3]  //"Delimitador" I use to Splite the collums // "Caminho" is my txt file //"Destino" is where my xlsx file will be placed

xls = openpyxl.Workbook()

sheet = xls.active

txt = open(caminho)

i = 1

for linha in txt:

    coluna = linha.split(delimitador)

    ii = 1

    for celula in coluna:

        cell = sheet.cell(row=i, column=ii)

        cell.value = celula.decode('latin-1').encode('utf-8')

        ii += 1

    i += 1

xls.save(destino)

txt.close()

Since now, Thanks o/

[Charlie Clark]

Am .06.2016, 18:25 Uhr, schrieb <igorbar...@gmail.com>:

> Hello;
>
> I'm new in Python and i'm using openpyxl to convert a txt file to an xlsx
> file. I try to use Numbers Format but I don't really understand how to
> do =/

I assume this is a continuation of the StackOverflow question
http://stackoverflow.com/questions/37734197/openpyxl-convert-one-column-to-numbers

> The excel file is being created correctly but with all the cells as
> "Text"
> format, and I need to convert to 'General'
> Can anyone help me?
>
> Here is my code
>
> import sys
> import openpyxl
>
> delimitador, caminho, destino = sys.argv[1], sys.argv[2], sys.argv[3]
> //"Delimitador" I use to Splite the collums // "Caminho" is my txt file
> //"Destino" is where my xlsx file will be placed

xls = openpyxl.Workbook()
sheet = xls.active

with open(caminho) as txt:

for linha in txt:
coluna = linha.split(delimitador)

sheet.append(float(val) for val in coluna)

This assumes that every value in your line is a number.

If you need to convert the text encoding then use the codecs module:

with codecs.open(caminho, 'latin-1') as txt:
…

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Igor Barboza]

Tnks Charlie

[Igor Barboza]

Guys I need some help again =(

here is my current code... as I read the txt file, i need to put the values as Int/Float or str...Int is ok, as well srt, but I can't solve a problem with some float...

366351.77 or -6000.00 for example still being inserted as a str in the sheet... =((

I don't know if the problem is when i'm reading the content of the txt file or something like this...

import sys

import openpyxl

delimitador, caminho, destino = sys.argv[1], sys.argv[2], sys.argv[3]

xls = openpyxl.Workbook()

sheet = xls.active

sheet.column_dimensions['A']

txt = open(caminho)

def is_float(s):

    result = False

    if s.count(".") == 1:

        if s.replace(".", "").isdigit():

            result = True

    return result

    

i = 1

for linha in txt:

    coluna = linha.split(delimitador)

    ii = 1

    for celula in coluna:

        cell = sheet.cell(row=i, column=ii)

        if is_float(celula):

#           cell.value = float(celula.decode('latin-1').encode('utf-8'))

            cell.value = 'float'            

        elif celula.isdigit():

            cell.value = int(celula.decode('latin-1').encode('utf-8'))

        else:

            cell.value = celula.decode('latin-1').encode('utf-8')

        ii += 1

    i += 1

xls.save(destino)

txt.close()

[Charlie Clark]

Am .07.2016, 23:41 Uhr, schrieb Igor Barboza <igorbar...@gmail.com>:

> Guys I need some help again =(
>
> here is my current code... as I read the txt file, i need to put the
> values
> as Int/Float or str...Int is ok, as well srt, but I can't solve a problem
> with some float...
>
> 366351.77 or -6000.00 for example still being inserted as a str in the
> sheet... =((
>
> I don't know if the problem is when i'm reading the content of the txt
> file
> or something like this...
>
>
> import sys
> import openpyxl
>
> delimitador, caminho, destino = sys.argv[1], sys.argv[2], sys.argv[3]
> xls = openpyxl.Workbook()
> sheet = xls.active
> sheet.column_dimensions['A']
> txt = open(caminho)
>
>
> def is_float(s):
> result = False
> if s.count(".") == 1:
> if s.replace(".", "").isdigit():
> result = True
> return result

Normally just try converting to an int and a float if that fails works
best.

> i = 1
> for linha in txt:
> coluna = linha.split(delimitador)
> ii = 1
> for celula in coluna:
> cell = sheet.cell(row=i, column=ii)
> if is_float(celula):
> # cell.value = float(celula.decode('latin-1').encode('utf-8'))
> cell.value = 'float'
> elif celula.isdigit():
> cell.value = int(celula.decode('latin-1').encode('utf-8'))

This conversion makes no sense at all.

> else:
> cell.value = celula.decode('latin-1').encode('utf-8')
> ii += 1
> i += 1
> xls.save(destino)
> txt.close()
>

[Igor Barboza]

Hello Charlie,

Yeah, for me it is ok create all value as Str, the problem is that the User wants all the numbers as a number because she needs to do some tasks with it,

and if I just read the txt and put in the cell all the values are write as a text...

Em sexta-feira, 10 de junho de 2016 13:25:45 UTC-3, Igor Barboza escreveu:

[Charlie Clark]

Am .07.2016, 13:01 Uhr, schrieb Igor Barboza <igorbar...@gmail.com>:

> Yeah, for me it is ok create all value as Str, the problem is that the
> User
> wants all the numbers as a number because she needs to do some tasks with
> it,
> and if I just read the txt and put in the cell all the values are write
> as
> a text...

This e-mail is a bit of a non-sequitur and it is not clear what you want
to do. Converting numbers from strings to ints or floats is easy.

def text_to_num(text):
try:
return int(text)
except ValueError:
try:
return float(text)
except ValueError:
return text

Use the codecs module to open your source file but only if you do not have
ascii text and you know the encoding.

Charlie

[Igor Barboza]

Hi Guys, 

First of all tks so much Charlie for all, your help.

Here is my final code, now The values as Number(Values, Accountings, Index..) are being correctly added as a number in the Worksheet, and the others value as (Date, Description...) are being added as String

Em sexta-feira, 10 de junho de 2016 13:25:45 UTC-3, Igor Barboza escreveu: