pandans导出Excel并将数据保存到不同的Sheet表中
数据存在mongodb中,按照类别导出到Excel文件,问题是想把同一类的数据放到一个sheet表中,最后只导出到一个excel文件中
# coding=utf-8
import pandas as pd
def export_to_excel_bycas():
client = pymongo.MongoClient('localhost', 27017)
db = client['****']
coll = db["***"]
#之前看到的都收
writer = pd.ExcelWriter("H:\\2019.xls")
cas_list = ["29411012","30042090",
"29419090",
"29413020",
"29359000",
"29339900",
"29419057",
"29419040",
"29322090",
"29395900",
"29349990",
"29419055",
"29419010"]
# 列名称
columns = ['COMMODITY', 'G3_Company', 'G3_Company_EName', 'G3_CompanyShortName',
'G3_Country', 'G3_CountryCode', 'HS_CODE', 'MONTHS', 'PRICE', 'VALUE_USD']
for cas in cas_list:
data = pd.DataFrame(
list(coll.find({"HS_CODE": cas}, {"_id": 0, "PARADATE": 0})))
# 保存到一个sheet表中
data.to_excel(writer, sheet_name="sheet_{0}".format(cas), header=columns, index=False)
# 保存整个excel文件
writer.save()
if __name__ == '__main__':
export_to_excel_bycas()