读写Excel
读写Excel比较简单的，不管是使用pandas还是xlrd，都可以很方便的读取到Excel中的数据。
但是pandas和xlrd读取到的数据格式是不一样的，我们可以通过导入两个包中的方法，通过查看源码中的方法。
注意点
pandas和xlrd读取之后的数据格式、方法- 写入数据时候的格式
- 对于不同数据的操作，使用不同逻辑
- 可以使用
numpy来处理一些稍复杂的计算
代码
import os
import re
import time
import numpy as np
import pandas as pd
import xlrd
import xlwt
class ReadExcel(object):
def __init__(self, save_name):
alignment = xlwt.Alignment()
alignment.horz = 0x02
alignment.vert = 0x01
self.style = xlwt.XFStyle()
self.style1 = xlwt.XFStyle()
self.style2 = xlwt.XFStyle()
self.style.alignment = self.style1.alignment = self.style2.alignment = alignment
font1 = xlwt.Font()
font1.colour_index = 3
self.style1.font = font1
font2 = xlwt.Font()
font2.colour_index = 2
self.style2.font = font2
self.save_name = save_name
self.workbook = xlwt.Workbook(encoding='utf-8')
self.dir_path = os.path.split(os.path.realpath(__file__))[0]
self.main()
def read(self):
xls = xlrd.open_workbook(file_path)
table = xls.sheet_by_name('Sheet1')
items = []
for i in range(table.nrows):
rows = table.row_values(i)
if len(rows[1]) > 0:
try:
info_ = re.findall("(\w+) (\d+) ([A-Z]+) @ (.*)", rows[3])[0]
time_ = rows[1].replace("\xa0\xa0", " ").replace("/", "-")
money = rows[5]
except:
info_ = re.findall("(\w+) (\d+) ([A-Z]+) @ (.*)", rows[1])[0]
time_ = rows[0].replace("\xa0\xa0", " ").replace("/", "-")
money = rows[2]
items.append({
"time": time_,
"mode": info_[0],
"numb": info_[1],
"name": info_[2],
"price": info_[3],
"money": money
})
return items
def save_data(self):
items = self.read()
items_name = sorted(items, key=lambda i: i["time"])
new_items_name = sorted(items_name, key=lambda i: i["name"])
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet("Sheet1")
worksheet.write(0, 0, label="time")
worksheet.write(0, 1, label="mode")
worksheet.write(0, 2, label="numb")
worksheet.write(0, 3, label="name")
worksheet.write(0, 4, label="price")
worksheet.write(0, 5, label="money")
row = 1
for result in new_items_name:
worksheet.write(row, 0, label=result["time"])
worksheet.write(row, 1, label=result["mode"])
worksheet.write(row, 3, label=result["name"])
worksheet.write(row, 4, label=result["price"])
worksheet.write(row, 5, label=result["money"])
worksheet.write(row, 2, label=int(result["numb"] if result["mode"] == "Bought" else 0 - int(result["numb"])))
row += 1
workbook.save('result.xlsx')
def parse_list_(self, index_, list_, items, sum_numb):
for data_ in list_[index_:]:
sum_numb += data_["numb"]
if sum_numb == 0:
soul_ = list_.index(data_)
items.append(list_[index_:soul_ + 1])
if soul_ + 1 < len(list_):
self.parse_list_(soul_ + 1, list_, items, sum_numb)
return items
def save_result(self):
data = pd.read_excel("result.xlsx").groupby(["name"])
worksheet = self.workbook.add_sheet("Sheet1")
worksheet.col(2).width = 20 * 256
worksheet.col(3).width = 20 * 256
worksheet.col(4).width = 20 * 256
worksheet.write(0, 0, label="Name")
worksheet.write(0, 1, label="Times")
worksheet.write(0, 2, label="Buy")
worksheet.write(0, 3, label="Sell")
worksheet.write(0, 4, label="GIN")
row = 1
for i in data:
data_dict = i[1].T.to_dict() # 单个买卖信息
data_list = [value for key, value in data_dict.items()]
items = self.parse_list_(0, data_list, [], 0)
if items:
p = 1
for item in items:
# 对时间加权
buy_time_ = np.average([int(time.mktime(time.strptime(i["time"], "%d-%m-%Y %H:%M:%S"))) for i in item if i["mode"] == "Bought"], weights=[i["numb"] for i in item if i["mode"] == "Bought"])
sold_time_ = np.average([int(time.mktime(time.strptime(i["time"], "%d-%m-%Y %H:%M:%S"))) for i in item if i["mode"] == "Sold"], weights=[i["numb"] for i in item if i["mode"] == "Sold"])
buy_time = time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(buy_time_))
sold_time = time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(sold_time_))
worksheet.write(row, 0, item[0]["name"])
worksheet.write(row, 1, p)
worksheet.write(row, 2, buy_time)
worksheet.write(row, 3, sold_time)
worksheet.write(row, 4, "%.2f" % sum(i["money"] for i in item))
p += 1
row += 1
# print(item[0]["name"], buy_time, item[-1]["time"], "%.2f" % (sum(i["money"] for i in item)))
else:
# pprint(data_list[0]["name"])
worksheet.write(row, 0, data_list[0]["name"])
if data_list[0]["mode"] == "Bought":
buy_time_ = np.average([int(time.mktime(time.strptime(i["time"], "%d-%m-%Y %H:%M:%S"))) for i in data_list if i["mode"] == "Bought"], weights=[i["numb"] for i in data_list if i["mode"] == "Bought"])
buy_time = time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(buy_time_))
worksheet.write(row, 1, 1)
worksheet.write(row, 2, buy_time)
worksheet.write(row, 4, "No Sold")
elif data_list[0]["mode"] == "Sold":
sold_time_ = np.average([int(time.mktime(time.strptime(i["time"], "%d-%m-%Y %H:%M:%S"))) for i in data_list if i["mode"] == "Sold"], weights=[i["numb"] for i in data_list if i["mode"] == "Sold"])
sold_time = time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(sold_time_))
worksheet.write(row, 1, 1)
worksheet.write(row, 3, sold_time)
worksheet.write(row, 4, "No Bought")
row += 1
self.workbook.save('result.xlsx')
def change_style(self):
"""合并相同单元格"""
table = xlrd.open_workbook("result.xlsx").sheet_by_name('Sheet1')
name_list = table.col_values(0)
data_col = [table.col_values(i) for i in range(table.ncols)]
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet("Sheet1")
worksheet.col(2).width = 20 * 256
worksheet.col(3).width = 20 * 256
worksheet.col(4).width = 15 * 256
for col in range(1, len(data_col)):
for row in range(len(data_col[0])):
style_ = self.style
try:
if col == 4:
if float(data_col[col][row]) > 0:
style_ = self.style2
elif float(data_col[col][row]) < 0:
style_ = self.style1
except Exception as e:
pass
worksheet.write(row, col, data_col[col][row], style_)
a = {}
for i in name_list:
if name_list.count(i) > 1:
a[i] = name_list.count(i)
elif name_list.count(i) == 1:
worksheet.write(name_list.index(i), 0, data_col[0][name_list.index(i)], self.style)
for key, value in a.items():
start_ = name_list.index(key, 1)
end_ = start_ + value - 1
worksheet.write_merge(start_, end_, 0, 0, key, self.style)
workbook.save(self.save_name)
def main(self):
self.save_data()
self.save_result()
self.change_style()
if __name__ == '__main__':
"""
终端运行：python main.py
在当前文件夹中生成 result.xlsx
"""
file_path = "test_1.xlsx"
save_name = "result.xlsx"
ReadExcel(save_name)
评论区