使用Openpyxl创建多个自定义电子表格 [英] Using Openpyxl to create multiple custom spreadsheets
问题描述
我正在尝试根据我们拥有的较大电子表格中的条件为我的办公室创建一系列电子表格.可以很容易地手工完成，但是由于我是编程新手，所以我认为这是一个有趣的练习，看看我是否可以使用Openpyxl模块使用python来实现.
I'm trying to create a series of spreadsheets for my office based on criteria from a larger spreadsheet we have. This could very easily be done by hand, but since I am new to programming, I thought this could be a fun exercise to see if I could do it with python using the Openpyxl module.
基本问题:我有一个人们要旅行的国家/地区的列表，并且在行中列出了人们的姓名和其他数据，包括每个人要旅行的国家/地区的名称.每个人要去的国家/地区在第一栏中.对于每个国家/地区，我想制作一个单独的工作簿，以获取前往该国家/地区的每个人的数据行.出于我办公室的其他目的，每个列表都应另存为一个.xlsx文件，而不仅仅是一个工作簿中的新表.
Basic problem: I have a list of countries people are traveling to, and I have rows with people's names and other data, including the name of the country each person is traveling to. The country each person is going to is in the first column. For each country, I want to make a separate workbook that grabs the row of data for each person going to that country. For other purposes in my office, each list should be saved as a separate .xlsx file, not simply a new sheet all in one workbook.
我对编程非常陌生，但是到目前为止，这是我正在尝试的方法:
I am very new to programming, but here is how I'm trying to do it so far:
import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
def create_roster(Country_name):
roster_book = Workbook()
ws2 = roster_book.active
for row in cell_rows:
if row[0] == Country_name:
ws2.append(row)
roster_book.save('C:\\....\\{}.xlsx'.format(Country_name))
countries = ('Au','Brazil','Costa R','Cuba','France','Germ','Ghana','Greece','Hungary','Italy','Japan','Korea','Peru','Romania','S.A.','Switz','Thai')
wb = load_workbook('C:\\....\\Big_list.xlsx', data_only=True)
ws = wb.active
cell_rows = []
for row in ws.values:
cell_rows.append(tuple(row))
for country in countries:
create_roster(country)
当我运行此代码时，出现一条从未出现过的错误消息，并说:未处理的异常:输入的输入值<类'xml.etree.ElementTree.Element'>，预期的字符串或Element无效."
When I run this code, an error message I have never got before appears and says, "Exception Unhandled: got invalid input value of type < class 'xml.etree.ElementTree.Element'>, expected string or Element."
到目前为止，搜索该错误消息对我来说没有任何帮助.
A search for this error message has so far not been fruitful for me.
更奇怪的是，没有像往常一样显示错误源于该行的行号(我使用的是Visual Studio 2019)，而是IDE模块显示文本"Frame not in module.当前堆栈"在已加载的模块中找不到框架.无法在此位置显示源."
What is more peculiar is that I am not being shown the line number the error is stemming from as usual (I am using Visual Studio 2019), and instead the IDE module displays the text "Frame not in module. The current stack frame was not found in a loaded module. Source cannot be shown for this location."
关于此错误的其他相关信息是，在我尝试创建所有这些.xlsx文件的文件夹中，列表中的第一个文件Au.xlsx出现在我尝试将其保存的文件夹中至.但是，当我尝试使用Excel打开此文件时，它是不可读的.
Another bit of relevant info on this error is that in the folder where I am attempting to create all these .xlsx files, the first one in my list, Au.xlsx, appears in the folder I am trying to save them all to. But when I try to open this file using Excel, it is unreadable.
我无法确定我尝试执行此操作的方式出了什么问题.这是尝试创建多个自定义.xlsx文件的错误方法吗?这里的任何见解或建议将不胜感激.
I cannot tell what is wrong with the way I am trying to do this. Is this the wrong way to go about trying to create multiple custom .xlsx files? Any insight or advice here would be much appreciated.
推荐答案
df = pd.read_excel("Filename.xlsx")
#make a list of all unique country names
# or you can just read that specific cloumn and remove duplicate and pass it into a list
list = [All unique country names]
for i in list:
# apply str func to I/P for String Comparsion
i = str(i)
leng = len(i)
try:
#make slice of dataframe containing only country = i
df1= df[df.iloc[:, column_number_of_countries_column].str[:leng]== i]
except:
# pass if country does not exists
continue
#save df slice to excel sheet with name as Country
df1.to_excel(i+".xlsx", index= False)
print(" Sheet {} saved successfully!".format(i))
print("File Saved!")
这篇关于使用Openpyxl创建多个自定义电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
