Python实现excle文件读写

实现学生成绩数据的汇总:

语文成绩.xlsx  英语成绩.xlsx 

生成:成绩汇总.xlsx

实现代码:

 excle文件读取:

# @File  : excleread.py
# @Author: yize365
# @Date  :  2019/12/16
# @Function:excle读取
# @Remarks:
from xlrd import open_workbook
class Excle_Read():
    def __init__(self,lujing):
        self.lujing=lujing

    def red_excle(self):
        data=open_workbook(self.lujing)
        #获取sheet1
        sheet1 = data.sheet_by_index(0)
        #所有行数
        nrows = sheet1.nrows
        #获取首行表头
        first_row_name_list=sheet1.row_values(0)

        #获取所有行数据存储
        info_all_list=[]
        for rownum in range(1,nrows):
            row=sheet1.row_values(rownum)
            if row:
                info_all_list.append(row)
        return first_row_name_list,info_all_list

 excle文件写入:

# @File  : exclewright.py
# @Author: yize365
# @Date  :  2019/12/16
# @Function:excle写入
# @Remarks:
from xlsxwriter import Workbook

class Excle_Write():
    def __init__(self,lujing,shouhang,allstu,list1,list2):
        self.lujing=lujing
        self.shouhuang=shouhang
        self.allstu=allstu
        self.list1=list1
        self.list2=list2

    def write_excle(self):
        #打开文件
        workbook = Workbook(self.lujing)
        #创建工作表
        worksheet=workbook.add_worksheet()
        #设置首行信息
        for i in range(len(self.shouhuang)):
            worksheet.write(0,i,self.shouhuang[i])

        for i in range(len(self.allstu)):
            worksheet.write(i+1,0,self.allstu[i][0])
            worksheet.write(i+1,1,self.allstu[i][1])
            for j in self.list1:
                if j[0] == self.allstu[i][0]:
                    #写入成绩
                    worksheet.write(i + 1, 2, j[2])
            for k in self.list2:
                if k[0] == self.allstu[i][0]:
                    #写入成绩
                    worksheet.write(i + 1, 3, k[2])

        workbook.close()

表多中学号去重:

# @File  : onlynum.py
# @Author: yize365
# @Date  :  2019/12/16
# @Function:获取唯一学号和姓名
# @Remarks:
class Only_Num():
    def __init__(self,list1,list2):
        self.list1=list1
        self.list2=list2

    def only_number(self):
        new_list=self.list1[1]+self.list2[1]
        new_list2 = []
        for i in range(len(new_list)):
            # new_list2.append(str(new_list[i][0])+','+new_list[i][1])
            #获取指定两个元素
            a=new_list[i][0:2:1]
            #去重
            if a not in new_list2:
                new_list2.append(a[0:2:1])
        return new_list2

主函数执行:

# @File  : domain.py
# @Author: yize365
# @Date  :  2019/12/16
# @Function:
# @Remarks:
from stumark.excleread import Excle_Read
from stumark.exclewright import Excle_Write
from stumark.onlynum import Only_Num
shouhang=["学号","姓名","语文","英语"]

#成绩文件路径
lujing1="student-chinese.xlsx"
lujing2="student-english.xlsx"
#生成文件的路径
lujing3="student.xlsx"

#读取excle获取的数据
list1=Excle_Read(lujing1).red_excle()
list2=Excle_Read(lujing2).red_excle()
#返回所有的学生
allstu=Only_Num(list1,list2).only_number()

#创建新表,写入数据
Excle_Write(lujing3,shouhang,allstu,list1[1],list2[1]).write_excle()