Python Forum
P3, openpyxl, csv to xlsx, cell is not number, problem with colorize
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
P3, openpyxl, csv to xlsx, cell is not number, problem with colorize
#1
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!
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to append a value to specific excel cell using openpyxl hobbyist 0 559 Mar-05-2021, 07:14 PM
Last Post: hobbyist
  Python3 doesn't populate xlsx file with openpyxl Auldyin75 2 447 Feb-16-2021, 12:00 PM
Last Post: Auldyin75
  How can I speed up my openpyxl program reading Excel .xlsx files? deac33 0 988 May-04-2020, 08:02 PM
Last Post: deac33
  openpyxl problem Sheeper 2 1,571 Mar-14-2020, 06:54 AM
Last Post: buran
  openpyxl, if value in cell then change format genderbee 1 2,189 Nov-05-2019, 01:37 PM
Last Post: genderbee
  Need to copy column of cell values from one workbook to another with openpyxl curranjohn46 3 5,682 Oct-12-2019, 10:57 PM
Last Post: curranjohn46
  Openpyxl - When save existing xlsx sheet, images/drawing does not get saved shubhamjainj 2 3,937 Apr-16-2019, 07:09 AM
Last Post: shubhamjainj
  problem with complex number jodrickcolina 1 825 Apr-13-2019, 06:59 PM
Last Post: Yoriz
  Getting error while loading excel(.xlsx) file using openpyxl module shubhamjainj 1 5,335 Mar-01-2019, 01:05 PM
Last Post: buran
  Problem with Polish characters in xlsx file Mikser 4 1,630 Nov-18-2018, 06:22 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020