将python列表导出到Excel列

debugcn 发表于 Dev

维克多·盖纳（Victor Gaina）

from collections import Counter
import pandas as pd
import string
import xlwt
from xlwt import Workbook
wb=Workbook()
sheet2=wb.add_sheet('Sheet2')
sheet2 = wb.add_sheet("Sheet 2", cell_overwrite_ok=True)
sheet2.title="FINAL RESULTS"
df=pd.read_excel("Book2.xlsx", sheet_name=0)
df=df.astype('object')
df.info()
df_c1=df['Signal']
df_c2=df['DCS number']
list1_with_letters=list(df_c1)
list2_with_letters=list(df_c2)
new_list1=[]
new_list2=[]
def duplicates(lst, item):
        return [i for i, x in enumerate(lst) if x == item]
#stripping the characters for COMOS list
for x in list1_with_letters:
        x=str(x)
        new_x=''.join(filter(str.isdigit, x))
        new_list1.append(new_x)
#stripping the characters for DCS list
for y in list2_with_letters:
        y=str(y)
        new_y=''.join(filter(str.isdigit, y))
        new_list2.append(new_y)

new_list1 = list(filter(None, new_list1))
seen = set()
#we take out the duplicates of the COMOS list 
new_list1_in_order= []
for item in new_list1:
    if item not in seen:
        seen.add(item)
        new_list1_in_order.append(item)

for elem1 in new_list1_in_order: #loop through COMOS list
    index_duplicates_DCS=duplicates(new_list2,elem1)
    matched= [list2_with_letters[i] for i in index_duplicates_DCS]
    matched=str(matched)
    elem1_str=str(elem1) #convert the found element from new_list 2 into a string type
    print(elem1_str+ "-->"+ matched)
   #CODE WORKS UP TO HERE
size_matched=len(matched)
size_new_list1_in_order=len(new_list1_in_order)
for x in range(size_new_list1_in_order):
        for y in range(size_matched):
                sheet2.write(x,y,matched[y])


wb.save('sample_book.xls')

如果直到#CODE WORKS为止都运行代码，您将获得以下示例输出：

690205-->['AAH690205', 'AHH690205', 'LI690205', 'TDX690205']
690206-->['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206']

我现在想做的就是将这些数据打印到像这样的Excel工作表中：

Column1 Column 2
690205  AAH690205
        AHH690205
        LI690205 
        TDX690205
690206  AAH690206
        LI690206
        TAHH690206
        THH690206
        TI690206
and so on and so forth

我意识到代码编写得不好（第一次编码），但是有人可以帮我实现#CODE在这里工作了之后的内容

gmdev

为此，我使用了字典来组织信息。

690205-->['AAH690205', 'AHH690205', 'LI690205', 'TDX690205']
690206-->['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206']

箭头前面的数字在key字典的每个成员中用作：

69020
690206

在每个列表中的号码存储为value每个key字典中。为了解释这一点，这是我的字典的样子：

columns = {
    690205 : ['AAH690205', 'AHH690205', 'LI690205', 'TDX690205'],
    690206 : ['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206'],
    }

写入每个key在其适当的点，我使用的将被设置到的所述长度的可变value先前的key。在values我刚将它们存储在一个写的要容易得多list，并逐一查看该list。

import xlwt 
from xlwt import Workbook 

wb = Workbook() 

sheet = wb.add_sheet('Sheet 1', cell_overwrite_ok=True) 

# write columns that will always be there
sheet.write(0, 0, 'Column 1') 
sheet.write(0, 1, 'Column 2')

columns = {
    690205 : ['AAH690205', 'AHH690205', 'LI690205', 'TDX690205'],
    690206 : ['AAH690206', 'AHH690206', 'LI690206', 'TAHH690206', 'THH690206', 'TI690206', 'TNHH690206'],
    }


# key_list is used to store each key in order
key_list = []

for key in columns:
    key_list.append(key)


# key_index needs to start at one to prevent overriding of the column names (Column 1 and Column 2)
# key_index will be used to place each key in their correct spot
key_index = 1

for key in key_list:
    # writes the key at the correct key_index
    sheet.write(key_index, 0, key)
    # gets the length of the value for the key
    key_value_length = len(columns[key])
    # adds key_value_length to key_index to put the next key at the correct place
    key_index += key_value_length


# values_list is used to store all of the values of each key in order
value_list = []

for values in columns.values():
    for value in values:
        value_list.append(value)
        # getting index number of the value in the value_list
        index = value_list.index(value)
        # have to add one to the index because the indexes for the values will start at 1, not 0. This prevents overriding of the cell 'Column 2'
        sheet.write(index+1 , 1 ,value)

wb.save('examplesheet.xls')

输出：