问题描述
我有一个看起来像这样的列表. (数据来自多个xlsx文件):
I have a list that looks something like this. (The data is from several xlsx files):
[['A B 10', 2, 'A B 10', 3, 1, AC], ['A B 104', 3, 'A B 104', 2, -1, 'AC']]
[['D B 126', 3, 'D B 126', 2, -1, 'EFG 1'], ['D B 15', 3, 'D B 15', 2, -1, 'EFG 1']
[]
[]
[['D B 544', 2, 'D B 544', 1, -1, 'EFG 11'], ['D B 152', 3, 'D B 152', 2, -1, 'EFG 11'], ['D B 682', 3, 'D B 682', 2, -1, 'EFG 11']
我想将此信息放入一个新的xlsx文件中，但首先我需要将数据分类为行和列.我希望每个子列表中的所有第一个字符串都添加到第一列中，第二列中的数字添加到此类中.因此，这些列将是逗号符号所在的位置.我也不希望列表具有子列表，因此所有内容都应在同一列表中.像这样:
I want to put this information into a new xlsx file, but first I need to sort the data into rows and columns. I want all of the first strings in each sub-lists to be added to the first column, the numbers in the second column etc. So the columns is to be where the comma signs are. I also do not want the list to have sub lists, so everything should be in the same list. Something like this:
['A B 10', 2, 'A B 10', 3, 1, AC,
'A B 104', 3, 'A B 104', 2, -1, 'AC'
'D B 126', 3, 'D B 126', 2, -1, 'EFG 1'
'D B 15', 3, 'D B 15', 2, -1, 'EFG 1'
'D B 544', 2, 'D B 544', 1, -1, 'EFG 11'
'D B 152', 3, 'D B 152', 2, -1, 'EFG 11'
'D B 682', 3, 'D B 682', 2, -1, 'EFG 11']
这是我在代码中走了多远:
This is how far I have come on my code:
import pandas as pd
from openpyxl import Workbook, load_workbook
import glob
from openpyxl.utils.dataframe import dataframe_to_rows
numbers = []
os.chdir(r'C:Myfolder')
files = glob.glob('*.xlsx')
print(files)
for file in files: #Getting the data from xlsx files and to the numbers-list
df = pd.read_excel(file)
m = (df.iloc[:,4] - df.iloc[:,1]) != 0
pos = [0,1,3,4,6,7]
numbers = (df.loc[m, df.columns[pos]].values.tolist())
print(numbers)
excel_input = load_workbook(rapp) #Going from using pandas and dataframes to working with openpyxl (Just because I am not that familiar with pandas).
ws = excel_input.active
for r in dataframe_to_rows(df, index=True, header=True):
ws.append(r)
else:
pass
col1 = [] #Creating open lists to put the data into columns
col2 = []
col4 = []
col5 = []
col7 = []
col8 = []
mainlist = []
try:
for row in numbers: #Putting the data into the columns lists
col1.append(ws.cell(row=row, column=1).value) #This is wrong, and is throwing the error
col2.append(ws.cell(row=row, column=2).value) #Wrong
col4.append(ws.cell(row=row, column=4).value) #Wrong
col5.append(ws.cell(row=row, column=5).value) #Wrong
col7.append(ws.cell(row=row, column=7).value) #Wrong
col8.append(ws.cell(row=row, column=8).value) #Wrong
except AttributeError:
logging.error('Something is wrong')
finally:
columns = zip(col1, col2, col4, col5, col7, col8) #Zipping the lists
for column in columns:
mainlist.append(column)
有人知道如何做到这一点而不出现错误吗?我已经在代码中注释了错误所在.
Does anyone know how to do this without getting the error? I have commented in the code where the mistake is.
通过使用@stovfl的方法，我能够将某些数据放入xlsx文件中，但是由于我的列表是列表的列表，因此只有最后一个列表被添加到了我的xlsx文件中.我使用了以下代码:
By using @stovfl's method I was able to put some of the data into the xlsx file, but as my list is a list of list, only the last list was added to my xlsx file.I used this code:
from openpyxl import load_workbook
report = load_workbook(r"C:\Myworkbook.xlsx")
ws = report.create_sheet('My sheet')
for _list in numbers:
for row_data in _list:
ws.append([row_data])
print(row_data)
report.save(r"C\Myworkbook.xlsx")
打印:
A B 10
2
A B 10
3
1
AC
xlsx文件中的输出:
The output in the xlsx file:
问题是仅添加了最后一个列表，而不是整个列表，我希望输出看起来像这样:
The problem is only the last list is added, not the entire list of lists and I wanted the output to look like this:
推荐答案
第一 for loop是您的for file in files:.
我将下面的代码更新为此.
The First for loop is your for file in files:.
I updated my Code below to this.
-
例如，使用
openpyxl编写列表列表的解决方案
Solution writing your List of Lists using
openpyxl, for instance
newWorkbook = False
newWorksheet = False
if newWorkbook:
from openpyxl import Workbook
wb = Workbook()
# Select First Worksheet
ws = wb.worksheets[0]
else:
from openpyxl import load_workbook
wb = load_workbook("mySortData.xlsx")
if newWorksheet:
# Create a New Worksheet in this Workbook
ws = wb.create_chartsheet('Sheet 2')
else:
# Select a Worksheet by Name in this Workbook
ws = wb['Sheet']
for file in files: # Getting the data from xlsx files and to the numbers-list
df = pd.read_excel(file)
m = (df.iloc[:, 4] - df.iloc[:, 1]) != 0
pos = [0, 1, 3, 4, 6, 7]
numbers = (df.loc[m, df.columns[pos]].values.tolist())
# numbers is a List[Row Data] of List[Columns]
# Iterate List of Row Data
for row_data in numbers:
ws.append(row_data)
wb.save("mySortData.xlsx")
解决方案将您的列表列表直接写到CSV，例如:
Solution writing your List of Lists direct to CSV, for instance:
# Data == List of List
data = [[['A B 10', 2, 'A B 10', 3, 1, 'AC'], ['A B 104', 3, 'A B 104', 2, -1, 'AC']],
[['D B 126', 3, 'D B 126', 2, -1, 'EFG 1'], ['D B 15', 3, 'D B 15', 2, -1, 'EFG 1']],
[],
[],
[['D B 544', 2, 'D B 544', 1, -1, 'EFG 11'], ['D B 152', 3, 'D B 152', 2, -1, 'EFG 11'], ['D B 682', 3, 'D B 682', 2, -1, 'EFG 11']],
]
import csv
# Write to File
with open('Output.csv', 'w') as csv_file:
writer = csv.writer(csv_file)
for _list in data:
for row_data in _list:
writer.writerow(row_data)
A B 10,2,A B 10,3,1,AC
A B 104,3,A B 104,2,-1,AC
D B 126,3,D B 126,2,-1,EFG 1
D B 15,3,D B 15,2,-1,EFG 1
D B 544,2,D B 544,1,-1,EFG 11
D B 152,3,D B 152,2,-1,EFG 11
D B 682,3,D B 682,2,-1,EFG 11
使用Python测试:3.4.2-openpyxl:2.4.1
Tested with Python: 3.4.2 - openpyxl: 2.4.1
这篇关于将列表中的数据排序到xlsx文件的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！