如何从Excel单元格复制值并将其按比例粘贴到新单元格中 [英] How to copy a value from an Excel cell and paste it into a new cell at scale

问题描述

我正在尝试创建代码，以在一个单元格中搜索颜色值(例如蓝色")，如果在该单元格中找到了该值，则将该值粘贴到另一个单元格中，并重复几次一百种颜色.

I'm trying to create code that will search for a color value such as "blue" in a cell and if it is found in that cell, paste that value into another cell, and repeat this for a range of a couple hundred colors.

一旦发生这种情况，请对列中的下一个单元格再次执行相同的过程，然后粘贴到相应行中的另一个单元格中，直到在每个填充的单元格中搜索了所有颜色值为止.

Once that happens, to do the same process again for the next cell down in the column, and pasting into another cell in the corresponding row, until each populated cell has been searched for all color values.

我已经有了创建Excel工作表的代码，该工作表将提取一些预定义的值，但是我不确定如何将代码扩展为包括数百种不同的颜色.

I've got the code to create an Excel sheet that will pull a few predefined values, but I'm not sure how I will expand the code to include hundreds of different colors.

#Excel Project to fill attributes quickly.

#Importing Modules
import os, openpyxl

#defining variables
wb = openpyxl.Workbook()
sheet = wb['Sheet']

#Asking for your category
x = input('What is your category?')



#Writing header
sheet['A1'] = 'Short Description'
sheet['B1'] = 'Long Description'
sheet['C1'] = 'Item'
sheet['J1'] = 'Color'

#writing attribute values
sheet['A2'] = 'Amazing blue shiny fantastic shirt ee-9003'
sheet['A3'] = 'Red dull jacket'

#Writing values for "Color"

if 'Red' or 'red' in sheet['A2'].value:
    sheet['J2'] = 'Red'
elif 'Orange' or 'orange' in sheet['A2'].value:
    sheet['J2'] = 'Orange'
elif 'Blue' or 'blue' in sheet['A2'].value:
        sheet['J2'] = 'Blue'


if 'Red' or 'red' in sheet['A3'].value:
    sheet['J3'] = 'Red'
elif 'Blue' or 'blue' in sheet['A3'].value:
    sheet['J3'] = 'Blue'


#Saving to your desktop
os.chdir(r'E:\Desktop')
wb.save('Attributes_filled_' + str(x) + '.xlsx')

到目前为止，我希望代码第一次为我的代码生成蓝色"，而第二次为红色"，但是根据我对代码的排序方式，我会得到第一个if语句结果.

I expect the code to produce "blue" the first time, and "red" the second time for my code so far, but depending on how I order the code, I get the first if statement result.

推荐答案

if 'Red' or 'red' in sheet['A2'].value:始终被视为True，因为非空字符串被视为True. 所以实际上它与if True or 'red' in sheet['A2'].value:

if 'Red' or 'red' in sheet['A2'].value: is always considered True because non-empty strings are considered True. So in fact it's the same as if True or 'red' in sheet['A2'].value:

从文档中:

默认情况下，除非对象被其对象定义，否则其对象的类定义了返回False的__bool__()方法或返回零的__len__()方法. 1 以下是大多数内置对象被认为是错误的:

By default, an object is considered true unless its class defines either a __bool__() method that returns False or a __len__() method that returns zero, when called with the object. 1 Here are most of the built-in objects considered false:

• 常量定义为false:无"和"False".
• 任何数字类型的零:0，0.0，0j，Decimal(0)，Fraction(0, 1)
• 空序列和集合:''，()，[]，{}，set()，range(0)

• constants defined to be false: None and False.
• zero of any numeric type: 0, 0.0, 0j, Decimal(0), Fraction(0, 1)
• empty sequences and collections: '', (), [], {}, set(), range(0)

以下任何一种方法都可以解决您的问题:

any of the following would fix your problem:

if 'red' in sheet['A3'].value.lower():

或

if 'Red' in sheet['A3'].value or 'red' in sheet['A3'].value:

或

if any(color in sheet['A3'].value for color in ('Red', 'red')):

同样适用于所有if/elif条件. 另请注意，如果该字符串是较大字符串的一部分，它将仍然返回True，即 'Blue" in "I've Got The Blues"将被评估为True

Same apply for all of your if/elif conditions. Also note that if the string is part of bigger string it will still return True, i.e. 'Blue" in "I've Got The Blues" will be evaluated True

由于评论中的讨论，我将评估"替换为考虑".

Because of discussion in the comments, I replace evaluate with considered.

这篇关于如何从Excel单元格复制值并将其按比例粘贴到新单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！