如何将两个值放在列表的一个索引中以将其存储在行+ cellvalue的excel文件中?

时间:2021-02-27 07:12:02

I want to add two values in one index of list like

我想在列表的一个索引中添加两个值,如

['18' 'PASS','19' 'FAIL','20' 'PASS','21' '' FAIL] 

and then put this value in excel sheet like 18th row put value 'PASS'.

然后把这个值放在excel表中,如第18行put值'PASS'。

3 个解决方案

#1


3  

If you want to have multiple values in "one index of a list" you can simply use a list of tuples:

如果要在“列表的一个索引”中包含多个值,则只需使用元组列表:

myTupleList= [(1,"one"),(2,"two"),(3,"three")]

If you want to store something as excel table (which is in and of itself 2 dimensional), you would need at least 3 things:

如果你想存储一些东西作为excel表(它本身就是二维的),你需要至少3件事:

myData = [(row_1,column_1,data_1), ..., (row_n,col_n,data_n)]

to enable arbitary placement inside a excel workboot sheet.

在excel workboot表中启用任意放置。

Lets create some demo data:

让我们创建一些演示数据:

header = [ (0,idx,n) for idx,n in enumerate(["number","number^2","number^3"])] 

data = [ (n, 0, n) for n in range(0,30,3)]
data += [ (n, 1, n ** 2) for n in range(0,30,3)]
data += [ (n, 2, n ** 3) for n in range(0,30,3)]

# header: [(0, 0, 'number'), (0, 1, 'number^2'), (0, 2, 'number^3')]

# data: [(0, 0, 0),    (3, 0, 3),   (6, 0, 6),   (9, 0, 9),   (12, 0, 12), (15, 0, 15), 
#        (18, 0, 18),  (21, 0, 21), (24, 0, 24), (27, 0, 27), (0, 1, 0),   (3, 1, 9), 
#        (6, 1, 36),   (9, 1, 81),  (12, 1, 144),(15, 1, 225),(18, 1, 324),(21, 1, 441), 
#        (24, 1, 576), (27, 1, 729), (0, 2, 0),  (3, 2, 27),   (6, 2, 216), (9, 2, 729), 
#        (12, 2, 1728),(15, 2, 3375),(18, 2, 5832),(21, 2, 9261),(24, 2, 13824), 
#        (27, 2, 19683)]

And then write the data into workbooks:

然后将数据写入工作簿:

writeXlsWorkbook("text.xls","computed Numbers", header, data)
writeXlsxWorkbook("text.xlsx","computed Numbers", header, data)

Writing old excel files (*.xls):

编写旧的Excel文件(* .xls):

import xlwt    
def writeXlsWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xls to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = xlwt.Workbook()
    sh = book.add_sheet(sheet)

    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.write(row , col , data)
        addToRowsNr = 1

    for row, col, data in tupleData:
        sh.write(row + addToRowsNr, col , data)

    book.save(filename)

Writing new excel files (*.xlsx):

编写新的Excel文件(* .xlsx):

import openpyxl 
def writeXlsxWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xlsx to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = openpyxl.Workbook()
    sh = book.active # get the one default sheet
    sh.title = sheet # rename it

    # sh.cell( ..) is 1-based, data is 0 based so we add one to row and col
    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.cell(row=row+1, column=col+1, value=data) # ws['B4'] = "42" would work
        addToRowsNr = row+2

    for row, col, data in tupleData:
        sh.cell(row = row + addToRowsNr, column = col + 1, value = data)

    book.save(filename)

To write your data you would supply:

要编写您的数据,您需要提供:

# zero based data, hence -1 on the row
writeXlsWorkbook("somename.xls","someSheetName",None, [(18-1,0, 'PASS'),
                          (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

writeXlsxWorkbook("somename.xlsx","someSheetName",None, [(18-1,0, 'PASS'),
                           (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

Links:

Disclaimer: I took a peek at this answer for the old style excel writing and heavily adapted it.

免责声明:我看了一下这个旧式excel写作的答案并对其进行了大量调整。

#2


0  

You can make a list of tuples

您可以列出元组列表

For example

lst = [(18, 'Pass'), (19, 'Fail')]

You can access the values as

您可以访问值

lst[1] --> (19, 'Fail')
lst[1][0] --> 19
lst[1][1] --> 'Fail'

Please Note:

  • Tuples are immutable, so cannot add new elements
  • 元组是不可变的,因此无法添加新元素

  • Merging of tuples with the + operator you can add an element and it will create a new tuple
  • 将元组与+运算符合并,您可以添加一个元素,它将创建一个新的元组

You can read more about this over here

你可以在这里阅读更多相关信息

#3


0  

Possible options are

可能的选择是

1. One string with seperator

1.一个带分隔符的字符串

>>> list = ['18 PASS', '19 FAIL', '20 PASS']

>>> list = ['18 PASS','19 FAIL','20 PASS']

>>> list[0].split()
['18', 'PASS']

>>> list[0].split()[0]
'18'

>>> list[0].split()[1]
'PASS'

>>> list = ['18|PASS', '19|FAIL', '20|PASS']

>>> list[0].split('|')
['18', 'PASS']

>>> list[0].split('|')[0]
'18'

>>> list[0].split('|')[1]
'PASS'

Here you store the information as 1 string with a separator, usually space. And whenever needed you parse it.

在这里,您将信息存储为带有分隔符的1个字符串,通常是空格。无论何时需要你解析它。

Caution: Though the separator can e any character or string, you should be extra careful that you separator should not appear in the information string.

警告:虽然分隔符可以是任何字符或字符串,但您应该格外小心分隔符不应出现在信息字符串中。

For example, lets go out of your example and take age and name

例如,让我们走出你的榜样,拿出年龄和名字

>>> list = ['48 Torvalds', '62 Rossum', '62 Grady Booch']

>>> list[0].split()[1]
'Torvalds'

>>> list[1].split()[1]
'Rossum'

>>> list[2].split()[1]
'Grady'

Ouch!! you missed the last name Booch.

哎哟!!你错过了姓Booch。

2. List of lists or list of tuples

2.列表列表或元组列表

As explained in previous answer by https://*.com/users/3287355/user3287355

如前面的回答https://*.com/users/3287355/user3287355中所述

Remember tuples are immutable, once created you cannot alter the contents.

记住元组是不可变的,一旦创建就不能改变内容。

3. Recommended to use Python Dictionary

3.建议使用Python Dictionary

For key-value pairs Python has a built-in structure called Dictionary which analogous to Map, JSON etc.

对于键值对,Python有一个名为Dictionary的内置结构,类似于Map,JSON等。

>>> dict = {'18':'PASS', '19':'FAIL', '20':'PASS'}

>>> dict.get('18')
'PASS'

>>> dict.get('19')
'FAIL'

>>> dict.get('20')
'PASS'

Caution: Beware of duplicate keys. Also you can't expect the dictionary to preserve the order of elements as you entered. However its completely based on key-value, we don't get values by index.

注意:注意重复的密钥。此外,您不能指望字典在您输入时保留元素的顺序。然而,它完全基于键值,我们不能通过索引获得值。

#1


3  

If you want to have multiple values in "one index of a list" you can simply use a list of tuples:

如果要在“列表的一个索引”中包含多个值,则只需使用元组列表:

myTupleList= [(1,"one"),(2,"two"),(3,"three")]

If you want to store something as excel table (which is in and of itself 2 dimensional), you would need at least 3 things:

如果你想存储一些东西作为excel表(它本身就是二维的),你需要至少3件事:

myData = [(row_1,column_1,data_1), ..., (row_n,col_n,data_n)]

to enable arbitary placement inside a excel workboot sheet.

在excel workboot表中启用任意放置。

Lets create some demo data:

让我们创建一些演示数据:

header = [ (0,idx,n) for idx,n in enumerate(["number","number^2","number^3"])] 

data = [ (n, 0, n) for n in range(0,30,3)]
data += [ (n, 1, n ** 2) for n in range(0,30,3)]
data += [ (n, 2, n ** 3) for n in range(0,30,3)]

# header: [(0, 0, 'number'), (0, 1, 'number^2'), (0, 2, 'number^3')]

# data: [(0, 0, 0),    (3, 0, 3),   (6, 0, 6),   (9, 0, 9),   (12, 0, 12), (15, 0, 15), 
#        (18, 0, 18),  (21, 0, 21), (24, 0, 24), (27, 0, 27), (0, 1, 0),   (3, 1, 9), 
#        (6, 1, 36),   (9, 1, 81),  (12, 1, 144),(15, 1, 225),(18, 1, 324),(21, 1, 441), 
#        (24, 1, 576), (27, 1, 729), (0, 2, 0),  (3, 2, 27),   (6, 2, 216), (9, 2, 729), 
#        (12, 2, 1728),(15, 2, 3375),(18, 2, 5832),(21, 2, 9261),(24, 2, 13824), 
#        (27, 2, 19683)]

And then write the data into workbooks:

然后将数据写入工作簿:

writeXlsWorkbook("text.xls","computed Numbers", header, data)
writeXlsxWorkbook("text.xlsx","computed Numbers", header, data)

Writing old excel files (*.xls):

编写旧的Excel文件(* .xls):

import xlwt    
def writeXlsWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xls to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = xlwt.Workbook()
    sh = book.add_sheet(sheet)

    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.write(row , col , data)
        addToRowsNr = 1

    for row, col, data in tupleData:
        sh.write(row + addToRowsNr, col , data)

    book.save(filename)

Writing new excel files (*.xlsx):

编写新的Excel文件(* .xlsx):

import openpyxl 
def writeXlsxWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xlsx to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = openpyxl.Workbook()
    sh = book.active # get the one default sheet
    sh.title = sheet # rename it

    # sh.cell( ..) is 1-based, data is 0 based so we add one to row and col
    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.cell(row=row+1, column=col+1, value=data) # ws['B4'] = "42" would work
        addToRowsNr = row+2

    for row, col, data in tupleData:
        sh.cell(row = row + addToRowsNr, column = col + 1, value = data)

    book.save(filename)

To write your data you would supply:

要编写您的数据,您需要提供:

# zero based data, hence -1 on the row
writeXlsWorkbook("somename.xls","someSheetName",None, [(18-1,0, 'PASS'),
                          (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

writeXlsxWorkbook("somename.xlsx","someSheetName",None, [(18-1,0, 'PASS'),
                           (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

Links:

Disclaimer: I took a peek at this answer for the old style excel writing and heavily adapted it.

免责声明:我看了一下这个旧式excel写作的答案并对其进行了大量调整。

#2


0  

You can make a list of tuples

您可以列出元组列表

For example

lst = [(18, 'Pass'), (19, 'Fail')]

You can access the values as

您可以访问值

lst[1] --> (19, 'Fail')
lst[1][0] --> 19
lst[1][1] --> 'Fail'

Please Note:

  • Tuples are immutable, so cannot add new elements
  • 元组是不可变的,因此无法添加新元素

  • Merging of tuples with the + operator you can add an element and it will create a new tuple
  • 将元组与+运算符合并,您可以添加一个元素,它将创建一个新的元组

You can read more about this over here

你可以在这里阅读更多相关信息

#3


0  

Possible options are

可能的选择是

1. One string with seperator

1.一个带分隔符的字符串

>>> list = ['18 PASS', '19 FAIL', '20 PASS']

>>> list = ['18 PASS','19 FAIL','20 PASS']

>>> list[0].split()
['18', 'PASS']

>>> list[0].split()[0]
'18'

>>> list[0].split()[1]
'PASS'

>>> list = ['18|PASS', '19|FAIL', '20|PASS']

>>> list[0].split('|')
['18', 'PASS']

>>> list[0].split('|')[0]
'18'

>>> list[0].split('|')[1]
'PASS'

Here you store the information as 1 string with a separator, usually space. And whenever needed you parse it.

在这里,您将信息存储为带有分隔符的1个字符串,通常是空格。无论何时需要你解析它。

Caution: Though the separator can e any character or string, you should be extra careful that you separator should not appear in the information string.

警告:虽然分隔符可以是任何字符或字符串,但您应该格外小心分隔符不应出现在信息字符串中。

For example, lets go out of your example and take age and name

例如,让我们走出你的榜样,拿出年龄和名字

>>> list = ['48 Torvalds', '62 Rossum', '62 Grady Booch']

>>> list[0].split()[1]
'Torvalds'

>>> list[1].split()[1]
'Rossum'

>>> list[2].split()[1]
'Grady'

Ouch!! you missed the last name Booch.

哎哟!!你错过了姓Booch。

2. List of lists or list of tuples

2.列表列表或元组列表

As explained in previous answer by https://*.com/users/3287355/user3287355

如前面的回答https://*.com/users/3287355/user3287355中所述

Remember tuples are immutable, once created you cannot alter the contents.

记住元组是不可变的,一旦创建就不能改变内容。

3. Recommended to use Python Dictionary

3.建议使用Python Dictionary

For key-value pairs Python has a built-in structure called Dictionary which analogous to Map, JSON etc.

对于键值对,Python有一个名为Dictionary的内置结构,类似于Map,JSON等。

>>> dict = {'18':'PASS', '19':'FAIL', '20':'PASS'}

>>> dict.get('18')
'PASS'

>>> dict.get('19')
'FAIL'

>>> dict.get('20')
'PASS'

Caution: Beware of duplicate keys. Also you can't expect the dictionary to preserve the order of elements as you entered. However its completely based on key-value, we don't get values by index.

注意:注意重复的密钥。此外,您不能指望字典在您输入时保留元素的顺序。然而,它完全基于键值,我们不能通过索引获得值。