我想用 Python 嘗試一些新的東西。這是關于一個 excel 檔案，我想從中插入某些條目到另一個 excel 檔案中。如您所見，我想將包含顏色名稱的條目插入到括號中的另一個檔案中，并在其前面寫下翻譯的顏色名稱。你知道我該怎么做嗎？我將不勝感激任何答案。
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')
uj5u.com熱心網友回復：
建議按以下順序進行處理：
- 使用 pd.read_excel(DF) https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html將您當前的作業表讀入熊貓
- 調整您的輸入以考慮大寫和額外的空格
- 使用字典作為翻譯器而不是條件https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.replace.html
- 使用 openpyxl dataframe_to_rows 來調節 pandas 資料框以匯出到作業簿中。
這是一個簡化的作業示例：
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')
uj5u.com熱心網友回復：
請使用您的檔案檢查下面的代碼。我用你最能從我的代碼中理解的方法做了你想要做的事情。我知道這不是最有效的方法，但因為您需要代碼方面的幫助。所以，這里是：
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')
你可以比較它們，看看有什么變化。主要錯誤出現在條件陳述句中，當您撰寫一個名為 blue 的未知變數代替 cell.value 時。如果需要更多幫助，請隨時與我聯系。另外，如果有人可以改進此代碼，也請隨時這樣做。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/319088.html