python读写excel

写的一个帮同事处理excel的小程序，需要把一个总表按照店铺名称拆开成7个表。各个商铺表中商品编号中开头是8的需要去掉8，开头是DS的不用管，开头是其他数字的也不用管。然后将商品编号相同的合并起来（卖给顾客同种商品也许优惠程度合出售价格不同，所以同种商品可能有多行），相同编号的商品只保留一行，每行对应的结算价格和售出数量是该店铺所有同编号商品的总和，求出平均价格。如果该店铺的商品列表行数超过500，就再建一个新的excel表。

程序涉及到python对excel的读写操作，将对象按照某个属性排序，以及具有相同属性的对象的合并等。

<pre name="code" class="python">import xlrd
import xlwt
import math
import operator

class store:
    def __init__(self,name,goodsnum,final,salnum):
        self.name=name
        self.goodsnum=goodsnum
        self.final=final
        self.salnum=salnum

wbs = [xlwt.Workbook()] * 10
sheetWrites = [xlwt.Workbook().add_sheet("sheet")] * 10

workbook=xlrd.open_workbook("F:/excel/tmpdb.xls")
sheets=workbook.sheet_names();
worksheet=workbook.sheet_by_name(sheets[0])

global t
t=0

shops = [1]*worksheet.nrows
shopds=[]

listt=[]
listj=[]
listr=[]
listy=[]
listc=[]
listbc=[]
listg=[]
listtd=[]
listjd=[]
listrd=[]
listyd=[]
listcd=[]
listbcd=[]
listgd=[]

def addsame(list):#将一个店铺中商品编号相同的商品汇总起来的方法
    a=0#记录初次出现的下标
    for i in range(0,len(list)):
        if a==i==0:
            list[a].final = list[i].final
            list[a].salnum = list[i].salnum
            #print(listt[i].goodsnum,"\t",listt[i].final,'\t',listt[i].salnum)
        else:
            if list[i].goodsnum == list[i-1].goodsnum:
                list[a].final=list[a].final+list[i].final
                list[a].salnum=list[a].salnum+list[i].salnum
                list[i].final=0
                list[i].salnum=0
            else:
                a = i

def sheet_Shops(sheetname,wbname,listname,path):#写入excel的方法
    global t
    t=0
    for obj in listname:
        if obj.salnum>0:
            obj.final=("%.2f" % obj.final)
            sheetname.write(t, 0, obj.name)
            sheetname.write(t, 1, obj.goodsnum)
            sheetname.write(t, 2, obj.final)
            sheetname.write(t, 3, obj.salnum)
            price=float(obj.final)/float(obj.salnum)
            sheetname.write(t, 4, "%.2f" % price)
            t = t + 1
            wbname.save(path)
        else:
            pass

def sheet_ShopDs(sheetname,wbname,listname,path):#商品号带DS写入excel的方法
    global t
    for obj in listname:
        if obj.salnum>0:
            obj.final=("%.2f" % obj.final)
            sheetname.write(t, 0, obj.name)
            sheetname.write(t, 1, 'DS'+str(obj.goodsnum))
            sheetname.write(t, 2, obj.final)
            sheetname.write(t, 3, obj.salnum)
            price = float(obj.final) / float(obj.salnum)
            sheetname.write(t, 4, "%.2f" % price)
            t = t + 1
            wbname.save(path)
        else:
            pass
def readexcel():
    global shops
    global shopds
    for i in range(0,worksheet.nrows):#读取出excel表格中所有的数据
        row=worksheet.row(i)
        shops[i]= store(worksheet.cell_value(i, 0), worksheet.cell_value(i, 3), worksheet.cell_value(i, 8),
                        worksheet.cell_value(i, 9))
        if "DS" in shops[i].goodsnum:#包含DS的放入shopds数组中
            shopds.append(shops[i])
        elif shops[i].goodsnum[0]=="8" :#开头是8的截取字符串，去掉开头的8，转换成数字
            #print(shops[i].goodsnum)
            shops[i].goodsnum = int(shops[i].goodsnum[-6:])
            #print(shops[i].goodsnum)
        elif shops[i].goodsnum[0]=="1":
            shops[i].goodsnum = int(shops[i].goodsnum)#转换成数字
    shops.remove(shops[0])#去掉表头

def sorted():
    global shops
    global shopds
    print("所有店铺的个数",len(shops))
    shops=list(set(shops).difference(shopds))#将商品编号有DS的过滤出来，取差集
    print("不含DS的个数",len(shops))
    cmpfun = operator.attrgetter('goodsnum') #按照商品编号排序进行排序
    shops.sort(key=cmpfun)
    shopds.sort(key=cmpfun)  # 按照商品编号排序
    for obj in shops:#将shops里的商品按照店铺名分别放入不同列表
        if obj.name=="AAAAAAAA":
            listt.append(obj)
        elif obj.name=="BBBBBBBBBB":
            listj.append(obj)
        elif obj.name=="CCCCCCCCCCCCC":
            listr.append(obj)
        elif obj.name=="DDDDDDDDDDDDDDDDD":
            listy.append(obj)
        elif obj.name=="EEEEEEEEEEEEEE":
            listc.append(obj)
        elif obj.name=="FFFFFFFFFFFFFFFFff":
            listbc.append(obj)
        else:
            listg.append(obj)
    for i in range(0,len(shopds)):#遍历DS开头的
        #print(shopds[i].name,"\t",shopds[i].goodsnum)
        shopds[i].goodsnum=int(shopds[i].goodsnum[2:])#将DS开头的去掉DS，剩下数字
    for obj in shopds:#将商品编号含有DS的商品按照店铺名称，分别放入不同的列表
        if obj.name=="AAAAAAAAAAAAAAAAA":
            listtd.append(obj)
        elif obj.name=="BBBBBBBBBBBBBB":
            listjd.append(obj)
        elif obj.name=="CCCCCCCCCCCCCCC":
            listrd.append(obj)
        elif obj.name=="DDDDDDDDDDDDDDDD":
            listy.append(obj)
        elif obj.name=="EEEEEEEEEEEEEEEEEE":
            listcd.append(obj)
        elif obj.name=="FFFFFFFFFFFFFFFF":
            listbcd.append(obj)
        else:
            listgd.append(obj)

def addexcel(sheet,wb,lists,listds,path):
    addsame(lists)
    sheet_Shops(sheet, wb, lists,path)
    addsame(listds)
    sheet_ShopDs(sheet, wb, listds, path)
def insertexcel():
    for i in range(0, 7):
        wbs[i] = xlwt.Workbook()
        sheetWrites[i] = wbs[i].add_sheet("sheet")
    addexcel(sheetWrites[0], wbs[0], listt, listtd, "F:/excel/AAAAAAAA.xls")
    addexcel(sheetWrites[1], wbs[1], listj, listjd, "F:/excel/BBBBBBBBBBB.xls")
    addexcel(sheetWrites[2], wbs[2], listr, listrd, "F:/excel/CCCCCCCCCCCC.xls")
    addexcel(sheetWrites[3], wbs[3], listy, listyd, "F:/excel/DDDDDDDDDDD.xls")
    addexcel(sheetWrites[4], wbs[4], listc, listcd, "F:/excel/EEEEEEEEEEEE.xls")
    addexcel(sheetWrites[5], wbs[5], listbc, listbcd, "F:/excel/FFFFFFFFFFFFFFFF.xls")
    addexcel(sheetWrites[6], wbs[6], listg, listgd, "F:/excel/GGGGGGGGGGGGG.xls")

def paging(path):
    #path="F:/excel/tmpdb.xls"
    workbook = xlrd.open_workbook(path)
    sheets = workbook.sheet_names();
    worksheet = workbook.sheet_by_name(sheets[0])
    wbs = [xlwt.Workbook()] * 10
    sheetWrites = [xlwt.Workbook().add_sheet("sheet")] * 10
    if worksheet.nrows>500:
        wb = xlwt.Workbook()
        goods=[1]*worksheet.nrows
        for i in range(0,worksheet.nrows):
            goods[i] = store(worksheet.cell_value(i, 0), worksheet.cell_value(i, 1), worksheet.cell_value(i, 2),
                             worksheet.cell_value(i, 3))
        wbnum=math.floor(len(goods) / 500)

        for i in range(0,wbnum):
            wbs[i]=xlwt.Workbook()
            sheetWrites[i]=wbs[i].add_sheet("sheet")
            t=0
            path = path[:14] + str(i+2) + ".xls"
            print(path)
            #print(i)
            if i < wbnum-1:
                for j in range(500*(i+1),500*(i+2)):
                    sheetWrites[i].write(t, 0, goods[j].name)
                    sheetWrites[i].write(t, 1, goods[j].goodsnum)
                    sheetWrites[i].write(t, 2, goods[j].final)
                    sheetWrites[i].write(t, 3, goods[j].salnum)
                    t=t+1
                    wbs[i].save(path)
            elif i==wbnum-1:
                for j in range(500*wbnum,len(goods)):
                    sheetWrites[i].write(t, 0, goods[j].name)
                    sheetWrites[i].write(t, 1, goods[j].goodsnum)
                    sheetWrites[i].write(t, 2, goods[j].final)
                    sheetWrites[i].write(t, 3, goods[j].salnum)
                    t = t + 1
                    wbs[i].save(path)
    else:
        pass
def cut():
    paging("F:/excel/AAAAAAAAAAAA.xls")
    paging("F:/excel/BBBBBBBBBBBBB.xls")
    paging("F:/excel/CCCCCCCCCCC.xls")
    paging("F:/excel/DDDDDDDDDDD.xls")
    paging("F:/excel/EEEEEEEEEEEEE.xls")
    paging("F:/excel/FFFFFFFFFFFF.xls")
    paging("F:/excel/GGGGGGGGGGGG.xls")

readexcel()
sorted()
insertexcel()
cut()