python学习笔记22 excel汇总

需求：根据数电发票生成汇总数据

要点：

1.如何获取最后一行行号。

2.空值value =None

3.数据存储用了双list[[]]的形式，定位通过enumerate()拆分后用list.index()实现。

4.长数据要数值化，否则excel会变成科学计数，round(float(),2)

代码1

import os
import re
import time
import datetime
import xlwings as  xw

def choosefile(path):

    result=[]
    for dir,folder,file in os.walk(path):
        for f in file:
            if f.endswith('.xls') or f.endswith ('.xlsx'):
                result.append(os.path.join(dir,f))

    print(result)
    return result

def transfile(listpath):
    excelapp = xw.App(visible=False, add_book=False)
    # 打开excel
    excel = excelapp.books.open(listpath[0])
    # 获取sheets
    sheet = excel.sheets
    sheetname=[]
    for i in sheet:
        sheetname.append(i.name)
    sht=sheet['Sheet1']

    # 获取最后一行的行号
    last_row = sht.used_range.rows[-1].row
    #print(last_row)
    #第一行行号 sht.used_range.rows[0].row
    print(str(sht.used_range.rows[-1].row))
    #开始处理汇总数据
    # 有数据要处理就新建sheet
    if sht.range("A7").value!=None and "汇总123" not in sheetname:
        excel.sheets.add("汇总123")
        sht2=excel.sheets('汇总123')
        sht2.range("A1").value='购方名称'
        sht2.range("B1").value = '数电票号码'
        sht2.range("C1").value = '数量'
        sht2.range("D1").value = '合计金额（不含税）'
        sht2.range("E1").value = '合计税额'
        sht2.range("F1").value = '合计金额（含税）'
        sht2.range('A:B').column_width=40
        sht2.range('d:f').column_width=20

    total=[]
    cpname=[]
    # 按需补齐客户名称
    for i in range(7, last_row - 1):
        if  sht.range("G"+str(i)).value==None:
            sht.range("G" + str(i)).value =  sht.range("G"+str(i-1)).value
        #print(i)

    # 按需要设定范围
    for i in range(7,last_row-1):
        if sht.range("G"+str(i)).value not in cpname and sht.range("G"+str(i)).value!=None:
            total.append([sht.range("G"+str(i)).value,'\''+str(sht.range("E"+str(i)).value),sht.range("Y"+str(i)).value,sht.range("L"+str(i)).value,sht.range("M"+str(i)).value,sht.range("K"+str(i)).value])
            cpname.append(sht.range("G"+str(i)).value)
        elif sht.range("G"+str(i)).value!=None :

            # 遍历双重列表
            for j, list in enumerate(total):
                # 在当前子列表中查找元素
                # print(i,sub_list)
                if sht.range("G" + str(i)).value in list:
                    # 发票号
                    if sht.range("E" + str(i)).value[-8:] not in total[j][1]:
                        total[j][1] += '/' + sht.range("E" + str(i)).value[-8:]
                    # 数量
                    total[j][2] += sht.range("Y" + str(i)).value
                    # 金额不含税
                    total[j][3] += sht.range("L" + str(i)).value
                    # 税额
                    total[j][4] += sht.range("M" + str(i)).value
                    # 金额含税
                    total[j][5] += sht.range("K" + str(i)).value

                    # print( total[j][1])
                    break

        # print(total)
        # 数据更新
        l = 2
        for k in total:
            sht2 = excel.sheets('汇总123')
            sht2.range("A" + str(l)).value = k[0]
            sht2.range("B" + str(l)).value = str(k[1])
            sht2.range("C" + str(l)).value = round(float(k[2]), 3)
            sht2.range("D" + str(l)).value = round(float(k[3]), 2)
            sht2.range("E" + str(l)).value = round(float(k[4]), 2)
            sht2.range("F" + str(l)).value = round(float(k[5]), 2)
            l += 1

    excel.save()
    excel.close()
    excelapp.quit()




path=r'C:\Users\d\Desktop\1'
listpath=choosefile(path)
transfile(listpath)

代码2

import os
import re
import time
import datetime
import xlwings as  xw

def choosefile(path):

    result=[]
    for dir,folder,file in os.walk(path):
        for f in file:
            if f.endswith('.xls') or f.endswith ('.xlsx'):
                result.append(os.path.join(dir,f))

    print(result)
    return result

def transfile(listpath):
    excelapp = xw.App(visible=False, add_book=False)
    # 打开excel
    excel = excelapp.books.open(listpath[0])
    # 获取sheets
    sheet = excel.sheets
    sheetname=[]
    for i in sheet:
        sheetname.append(i.name)
    sht=sheet['Sheet1']

    # 获取最后一行的行号
    last_row = sht.used_range.rows[-1].row
    #print(last_row)
    #第一行行号 sht.used_range.rows[0].row
    print(str(sht.used_range.rows[-1].row))
    #开始处理汇总数据
    # 有数据要处理就新建sheet
    if sht.range("A7").value!=None and "汇总123" not in sheetname:
        excel.sheets.add("汇总123")
        sht2=excel.sheets('汇总123')
        sht2.range("A1").value='购方名称'
        sht2.range("B1").value = '数电票号码'
        sht2.range("C1").value = '数量（不一定准确）'
        sht2.range("D1").value = '合计金额（不含税）'
        sht2.range("E1").value = '合计税额'
        sht2.range("F1").value = '合计金额（含税）'
        sht2.range('A:B').column_width=40
        sht2.range('c:f').column_width=20

    total=[]
    cpname=[]

    # 按需要设定范围
    for i in range(7,last_row-1):
        print(i)
        if sht.range("G"+str(i)).value not in cpname and sht.range("G"+str(i)).value!=None:

            total.append([sht.range("G"+str(i)).value,'\''+str(sht.range("E"+str(i)).value),sht.range("Y"+str(i)).value,sht.range("L"+str(i)).value,sht.range("M"+str(i)).value,sht.range("K"+str(i)).value])
            cpname.append(sht.range("G"+str(i)).value)
        elif sht.range("G"+str(i)).value!=None :

            # 遍历双重列表
            for j, list in enumerate(total):
                # 在当前子列表中查找元素
                    #print(i,sub_list)
                    if sht.range("G"+str(i)).value in list:
                        #发票号
                        total[j][1]+='\\'+sht.range("E"+str(i)).value[-8:]
                        #数量
                        total[j][2] +=  sht.range("Y" + str(i)).value
                        #金额不含税
                        total[j][3] +=  sht.range("L" + str(i)).value
                        #税额
                        total[j][4] +=  sht.range("M" + str(i)).value
                        #金额含税
                        total[j][5] +=  sht.range("K" + str(i)).value


                        #print( total[j][1])
                        break

    #print(total)
    #数据更新
    l=2
    for k in total:
        sht2 = excel.sheets('汇总123')
        sht2.range("A"+str(l)).value=k[0]
        sht2.range("B"+str(l)).value = str(k[1])
        sht2.range("C"+str(l)).value = round(float(k[2]),3)
        sht2.range("D"+str(l)).value = round(float(k[3]),2)
        sht2.range("E"+str(l)).value = round(float(k[4]),2)
        sht2.range("F"+str(l)).value = round(float(k[5]),2)
        l+=1
    excel.save()
    excel.close()
    excelapp.quit()




path=r'C:\Users\d\Desktop\1'
listpath=choosefile(path)
transfile(listpath)