使用Python选择特定的Excel输入并将其保存到另一个Excel文件中?

Question

我想尝试一些新的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')

Answer 1

建议按以下顺序处理:

1. 使用pd.read_excel(DF) https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html将当前工作表读入pandas
2. 调整您的输入以考虑大小写和额外的空格
3. 使用字典代替条件句https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.replace.html
4. 使用openpyxl dataframe_to_rows来限制熊猫数据框导出到工作簿。

下面是一个简化的工作示例:

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')

Answer 2

请检查以下代码与您的文件。我做了你想做的事情，你可以从我的代码中最好地理解。我知道这不是最有效的方法，但是当您需要代码帮助时。这里是:

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 ProjectsOpenpyxlmybook.xlsx')

你可以比较它们，看看有什么改变。主要的错误是当您在cell.value的位置编写一个名为blue的未知变量时的条件。如果需要更多的帮助，请随时联系我。此外，如果有人可以改进这段代码，请随时这样做。