" name="sm-site-verification"/>
侧边栏壁纸
博主头像
PySuper博主等级

千里之行,始于足下

  • 累计撰写 203 篇文章
  • 累计创建 14 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录

使用 pandas、xlrd、xlwt 读写 Excel

PySuper
2021-03-04 / 0 评论 / 0 点赞 / 10 阅读 / 9679 字
温馨提示:
所有牛逼的人都有一段苦逼的岁月。 但是你只要像SB一样去坚持,终将牛逼!!! ✊✊✊

读写Excel

读写Excel比较简单的,不管是使用pandas还是xlrd,都可以很方便的读取到Excel中的数据。

但是pandas和xlrd读取到的数据格式是不一样的,我们可以通过导入两个包中的方法,通过查看源码中的方法。

注意点

  • pandasxlrd读取之后的数据格式、方法
  • 写入数据时候的格式
  • 对于不同数据的操作,使用不同逻辑
  • 可以使用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)
0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin

评论区