P3, openpyxl, csv to xlsx, cell is not number, problem with colorize

P3, openpyxl, csv to xlsx, cell is not number, problem with colorize - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: P3, openpyxl, csv to xlsx, cell is not number, problem with colorize (/thread-30000.html)

P3, openpyxl, csv to xlsx, cell is not number, problem with colorize - genderbee - Sep-29-2020

Hello,
I am using P3 and openpyxl module for convert CSV to XLSX. But numbers are converted with ' at the beginning. Why?
There is problem then when I colorize cells by content (numbers).

Script for convert csv to xlsx.

import os
import glob
import csv
import openpyxl # from https://pythonhosted.org/openpyxl/ or PyPI (e.g. via pip)
import sys

Ifile=sys.argv[1]

for csvfile in glob.glob(os.path.join('.', Ifile)):
    wb = openpyxl.Workbook()
    ws = wb.active
    with open(csvfile, 'rt') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

Result.

[Image: Screenshot_2020-09-29_12-55-52.png]

Script for colorize.

from openpyxl import load_workbook
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles import PatternFill
import sys

Ifile=sys.argv[1]

wb = load_workbook(Ifile)
ws = wb.active

for row in ws.iter_rows(len("A")):
    for cell in row:
        if cell.value == 'Titulka':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ff33", fill_type = "solid")
        elif cell.value == '1':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '2':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '3':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '4':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '5':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '6':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '7':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '8':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '9':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '10':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")
        elif cell.value == '11':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '12':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '13':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '14':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '15':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '16':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '17':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '18':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '19':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '20':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")
        elif cell.value == '21':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '22':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '23':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '24':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '25':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '26':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '27':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '28':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '29':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '30':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")
        elif cell.value == '31':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '32':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '33':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '34':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '35':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '36':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '37':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '38':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '39':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '40':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")
        elif cell.value == '41':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '42':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '43':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '44':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '45':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '46':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '47':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '48':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '49':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '50':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")
        elif cell.value == '51':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff33ff", fill_type = "solid")
        elif cell.value == '52':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="33ffff", fill_type = "solid")
        elif cell.value == '53':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ffff55", fill_type = "solid")
        elif cell.value == '54':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff55ff", fill_type = "solid")
        elif cell.value == '55':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="55ffff", fill_type = "solid")
        elif cell.value == '56':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="ff5599", fill_type = "solid")
        elif cell.value == '57':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="889988", fill_type = "solid")
        elif cell.value == '58':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="99ff88", fill_type = "solid")
        elif cell.value == '59':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="77ffdd", fill_type = "solid")
        elif cell.value == '60':
            ws.cell(row=cell.row, column=1).fill = PatternFill(fgColor="dddd99", fill_type = "solid")

wb.save(Ifile + '.xlsx')

Result.

[Image: Screenshot_2020-09-29_12-56-13.png]

Thanks!

RE: P3, openpyxl, csv to xlsx, cell is not number, problem with colorize - Larz60+ - Sep-29-2020

There is a wrapper around what appears to do the conversion here: https://github.com/russlamb/file_convert
no documentation, but does have examples.
I can't vouch for it as I have never used it, but it's worth a try.