python 写入Excel

使用xlwt 和xlrd 进行写入Excel 操作

个人使用随笔

1.数组按奇偶进行分组
2.对数组进行去头，去尾处理
3.对数组进行二维求平均值

import xlwt
import xlrd
from xlutils.copy import copy
import os

a = ['2', '1', '1', '8', 'r']
b = ['2', '9', '9', '1', "w"]
c = ['9', '5', '5', '1', "r"]
d = ['5', '8', '1', '1', "w"]
e = ['5', '9', '1', '1', "r"]
f = ['5', '8', '1', '1', "w"]
g = ['9', '2', '5', '1', "r"]
h = ['2', '7', '1', '8', "w"]
i = ['2', '3', '1', '8', "r"]
j = ['2', '6', '9', '1', "w"]
lists = [a, b, c, d, e, f, g, h, i, j]
result = ["0", "1", "2", "3", "4"]
header = ["", "tab1", "tab2", "tab3", "tab4"]

BASE_PATH_3 = os.path.dirname(
    os.path.dirname(os.path.dirname(os.path.realpath(__file__))))  # BASE_PATH_2的上一级目录BASE_PATH_3

BASE_PATH_2 = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))  # BASE_PATH_1的上一级目录BASE_PATH_2

BASE_PATH_1 = os.path.dirname(os.path.realpath(__file__))  # 获取当前文件目录BASE_PATH_1
os.chdir(BASE_PATH_1)
sheet_name = "上下滑数据详细信息"
save_path = BASE_PATH_1 + "/上下滑数据.xlsx"
new_list = []
result_1 = []
result_2 = []
listt = []
avg_1=[]
avg_2=[]

class Data_processing:


    """按次数的奇偶 分为两组"""

    def group_by(self,lists):
        time = 1
        for i in lists:
            time += 1
            if time % 2 == 1:
                result_1.append(i)
            else:
                result_2.append(i)
        return result_1,result_2

    # print(result_2)
    """排序去除最大值和最小值"""

    def bubble_sort(self,results):
        x=lambda a:a[1]
        results.soft(key=x,reverse=True)
        del results[0]
        del results[-1]
        return results

    """计算平均值"""

    def average(self, results, avg=None, new_list=None):
        if avg is None:
            avg = []
        if new_list is None:
            new_list = []
        for i in results:
            i_new = list(map(int, i[0:4]))
            new_list.append(i_new)
        for e in zip(*new_list):
            # print(e)
            ave = str(round(sum(e) / len(e), 2))
            avg.append(ave)

        return avg



    def   data_processing(self,lists):
        result_1,result_2=self.group_by(lists)
        Excel().add_things(["第一个app"])
        for i in result_1:
            Excel().add_things(i)
        Excel().add_things(["第二个app"])
        for k in result_2:
            Excel().add_things( k)

        result_1_1=self.bubble_sort(result_1)
        result_2_2=self.bubble_sort(result_2)
        Excel().add_things(["第一个app最优数据"])
        for i in result_1_1:
            Excel().add_things(i)
        Excel().add_things(["第二个app最优数据"])
        for k in result_2_2:
            Excel().add_things(k)
        avg1_1=self.average(result_1)
        avg2_2 = self.average(result_2)

        Excel().add_things(["第一个app平均值"])
        Excel().add_things(avg1_1)
        Excel().add_things(["第二个app平均值"])
        Excel().add_things(avg2_2)



class Excel:

    def make_excel(self, sheet_name, save_path):
        path_judge = os.path.exists(save_path)
        # 判断路径文件是否存在
        if path_judge == False:
            "用xlwt 创建Excel"
            self.work_book = xlwt.Workbook(encoding="utf-8")
            "创建sheet"
            self.work_book.add_sheet(sheet_name)
            "保存Excel"
            self.work_book.save(save_path)

    def add_things(self,result):
        "使用xlrd打开指定路径文件"
        self.work_book = xlrd.open_workbook(save_path)
        "获取Excel 的所有sheet 名称"
        self.all_sheet = self.work_book.sheet_names()
        "选择指定sheet"
        self.first_sheet = self.work_book.sheet_by_name(self.all_sheet[0])
        "复制新的Excel"
        self.new_workbook = copy(self.work_book)
        "获取新表的sheet"
        self.new_sheet = self.new_workbook.get_sheet(0)
        "获取sheet 已使用的行数"
        self.old_rows = self.first_sheet.nrows
        "获取sheeet 已使用的列数"
        self.old_col = self.first_sheet.ncols
        " 生成表头"
        self.set_head()
        "添加数据"
        self.add_data(result)
        "保存Excel"
        self.new_workbook.save(save_path)
        # print(self.old_rows, self.old_col)

    def set_head(self):
        """
        列：col
        行：row
        """
        "判断是否已有表头"
        if self.old_rows == 0:
            for k in header:
                self.new_sheet.write(self.old_rows, self.old_col, k)
                self.old_col += 1


    def add_data(self,result):
        "需要使列数从头数起"
        '获取到的row是从1开始计数需要加判断，python是从0计数'
        self.old_rows_1 = self.first_sheet.nrows
        if self.old_rows_1==0
	        col = 0
	        for i in result:
	            self.new_sheet.write(self.old_rows_1+1, col, i)
	            col += 1
		else：
			col = 0
	        for i in result:
	            self.new_sheet.write(self.old_rows_1+1, col, i)
	            col += 1
		
		


#
if __name__ == '__main__':
    Excel().make_excel(sheet_name, save_path)
    # Excel().add_things(header,result)
    Data_processing().data_processing(lists)