I would like to try something new with Python. It's about an excel file from which I want to insert certain entries into another excel file. As you can see, I want to insert the entries that contain a colour name into another file in brackets and write the colour name translated in front of it. Do you have any idea how I can do this? I would be grateful for any answer.
import openpyxl
from openpyxl import Workbook, load_workbook
book = openpyxl.load_workbook('datei.xlsx')
valuesK = []
sheet = book.get_sheet_by_name(Sheet1)
vK = sheet['G1': 'G2259']
for row in vK:
for cell in row:
if blue in cell:
valuesK.append('Blau (' cell.value ')')
elif red in cell:
valuesK.append('Rot (' cell.value ')')
elif grey in cell:
valuesK.append('Grau (' cell.value ')')
elif black in cell:
valuesK.append('Schwarz (' cell.value ')')
elif white in cell:
valuesK.append('Weiß (' cell.value ')')
elif offwhite in cell:
valuesK.append('Elfenbein (' cell.value ')')
elif brown in cell:
valuesK.append('Braun (' cell.value ')')
elif beige in cell:
valuesK.append('Beige (' cell.value ')')
elif pink in cell:
valuesK.append('Pink (' cell.value ')')
elif yellow in cell:
valuesK.append('Gelb (' cell.value ')')
elif orange in cell:
valuesK.append('Orange (' cell.value ')')
elif green in cell:
valuesK.append('Grün (' cell.value ')')
elif turquoise in cell:
valuesK.append('Türkis (' cell.value ')')
elif purple in cell:
valuesK.append('Violett (' cell.value ')')
elif gold in cell:
valuesK.append('Gold (' cell.value ')')
elif silver in cell:
valuesK.append('Orange (' cell.value ')')
else:
valuesK.append('Multicolour (' cell.value ')')
i = 1
result =1
wb = Workbook()
ws = wb.active
filename='dosya'
i = 0
for i in valuesK:
j = 1
ws['A' str(j)] = i
int(j)
wb.save('datei2.xlsx')
CodePudding user response：
Recommend approaching this in the following order:
- read your current worksheet into pandas using pd.read_excel(DF) https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html
- condition your inputs to account for capitalization and extra spaces
- use a dictionary as your translator instead of the conditionals https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.replace.html
- use openpyxl dataframe_to_rows to condition the pandas data frame for export into a workbook.
Here is a simplified working example:
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
# create a function to deal capitalization and extra spaces
def condition(x):
x = x.strip().lower()
return x
# use a dictionary for your translator
color_translator_dict = {
'red': 'rot',
'blue': 'blau',
'white': 'weiss'
}
# read the original excel file using pandas read_excel
colorDF = pd.read_excel('colors.xlsx', header=None)
# condition all cells to deal with upper case and extra spaces
colorDF = colorDF.applymap(condition)
# use dictionary pandas dictionary replace to translate
colorDF = colorDF.replace(color_translator_dict)
# output to workbook using openpyxl
# create new workbook
wb = Workbook()
ws = wb.active
# use openpyxl's dataframe_to_rows to condition pandas DF
rows = dataframe_to_rows(colorDF)
# iterate through the rows and apply to new workbook
for row_id, row in enumerate(rows, 1):
for column_id, value in enumerate(row, 1):
if value == 0 or value is None:
pass
else:
if column_id == 1:
# this is the pandas index
pass
else:
# compensate for the counting difference between dataframes and
# Excel sheets
column = column_id - 1
row = row_id - 2
print(f'{column_id=}, {row_id=}: {value=}')
ws.cell(row=row, column=column, value=value)
# save the new workbook
wb.save('output_wb.xlsx')
CodePudding user response：
Please check the code below with your file. I did what you are trying to do with the approach you can best understand from my code. I know it is not the most efficient way to do it, but as you need help with your code. So, here it is:
import openpyxl
from openpyxl import Workbook, load_workbook
#add your path to file
book = openpyxl.load_workbook(r'e:/Python Projects/Openpyxl/colordata.xlsx')
valuesK = []
sheet = book.get_sheet_by_name('Sheet1')
vK = sheet['G1': 'G80']
#Increase cells from G80 to how many you want
wb = Workbook()
ws = wb.active
filename = 'translated'
def color_translator():
for row in vK:
for cell in row:
try:
color_value = (str.lower(cell.value))
print(cell.value)
if color_value == 'blue':
valuesK.append('Blau (' cell.value ')')
elif color_value == 'red':
valuesK.append('Rot (' cell.value ')')
elif color_value == 'grey':
valuesK.append('Grau (' cell.value ')')
elif color_value == 'black':
valuesK.append('Schwarz (' cell.value ')')
elif color_value == 'white':
valuesK.append('Weiß (' cell.value ')')
elif color_value == 'offwhite':
valuesK.append('Elfenbein (' cell.value ')')
elif color_value == 'brown':
valuesK.append('Braun (' cell.value ')')
elif color_value == 'beige':
valuesK.append('Beige (' cell.value ')')
elif color_value == 'pink':
valuesK.append('Pink (' cell.value ')')
elif color_value == 'yellow':
valuesK.append('Gelb (' cell.value ')')
elif color_value == 'orange':
valuesK.append('Orange (' cell.value ')')
elif color_value == 'green':
valuesK.append('Grün (' cell.value ')')
elif color_value == 'turquoise':
valuesK.append('Türkis (' cell.value ')')
elif color_value == 'purple':
valuesK.append('Violett (' cell.value ')')
elif color_value == 'gold':
valuesK.append('Gold (' cell.value ')')
elif color_value == 'orange':
valuesK.append('Orange (' cell.value ')')
else:
valuesK.append('Multicolour (' str(cell.value) ')')
except TypeError as e:
pass
print(valuesK)
j = 1
for value in valuesK:
ws['A' str(j)] = value
j = 1
#launch Function from HERE#
color_translator()
print('file_Saved')
#Set path where you want your excel file
wb.save(filename = 'E:\Python Projects\Openpyxl\mybook.xlsx')
You can compare them both and see what has been changed. The main mistakes were in the conditionals when you were writing an unknown variable named blue in place of cell.value. Please feel free to contact me if more help is needed. Also, if someone can improve this code please feel free to do that as well.