一次excel表格操作

最近在一家公司上班，发现公司的操作过于落后了，忍不住给他们写了个脚本，统计订单。

import xlwt as xw
import xlrd as xr
from pandas.core.dtypes.inference import is_number

xlsx_file_path = input("请输入送货单模版文件（带上路径）比如E:/filename.xlsx  ：")
xls_file_path = input("请输入将要保存的出库信息表路径，比如E:/ ：")
xls_sheet_name = input("请输入出库日期，比如0902等 ：")

excel_obj = xr.open_workbook(xlsx_file_path)
excel_sheet_names = excel_obj.sheet_names()
excel_sheets = excel_obj.sheets()

account_datas = []
for excel_sheet in excel_sheets:
    if excel_sheet.nrows > 3:
        if(excel_sheet.row_values(3)[0] == "送货单"):
            account_data = {}
            #获取用户名
            name_row_value = excel_sheet.row_values(4)
            if(name_row_value[0] == "客户名称："):
                account_data['name'] = name_row_value[1]
            #获取客户地址
            address_row_value = excel_sheet.row_values(5)
            if(address_row_value[0] == "客户地址"):
                account_data['address'] = address_row_value[1]
            #获取客户代码和送货时间
            code_row_value = excel_sheet.row_values(6)
            if(code_row_value[0] == "客户代码："):
                account_data['code'] = code_row_value[1]

                #获取送货时间
                str_code_row_value = ''.join(map(str,code_row_value))
                list_split_date_value = str_code_row_value.split("送货日期：")
                if(len(list_split_date_value) >= 2):
                    account_data['date'] = list_split_date_value[1]
                else:
                    account_data['date'] = ""



            #获取客户详细订单
            order_header_row_value = excel_sheet.row_values(7)
            flag = 0
            account_data['order'] =[]
            order_data_start = 0
            header_name = []
            tmp_count = 0
            if(order_header_row_value[0] == "序号"):
                for orv in order_header_row_value:
                    if(orv == "商品名称"):
                        flag = 1
                        order_data_start = tmp_count
                    if(flag == 1):
                        header_name.append(orv)
                    if(orv == "备注"):
                        break
                    if(flag == 0):
                        tmp_count = tmp_count + 1
                    else:
                        tmp_count = tmp_count + 1
            else:
                continue
            order_data_row_num = 8
            tmp_order_data_start = order_data_start
            while 1:
                #读取具体订单商品数据
                order_data_detail_row = excel_sheet.row_values(order_data_row_num)
                if(is_number(order_data_detail_row[0]) == 0):
                    break
                account_order = {}
                for hn in header_name:
                    account_order[hn] = order_data_detail_row[tmp_order_data_start]
                    tmp_order_data_start = tmp_order_data_start + 1
                #如果表单无实收重量则按发送重量
                if(('实收重量' in header_name) == 0):
                    account_order['实收重量'] = account_order['送货重量']
                if(account_order['送货重量'] != '' and float(account_order['送货重量'])*1000 > 0):
                    account_data['order'].append(account_order)
                order_data_row_num = order_data_row_num + 1
                tmp_order_data_start = order_data_start
            if(len(account_data['order']) > 0):
                account_datas.append(account_data)
        else:
            print(excel_sheet.name + ":不是一张单据")

    else:
        print(excel_sheet.name + ":不是一张单据")

#写入销售明细表
write_excel_obj = ""
write_excel_sheet_name = xls_sheet_name
write_excel_head_name = "广东xxxx有限公司"

write_excel_obj = xw.Workbook(encoding = 'utf-8')
write_excel_obj_worksheet = write_excel_obj.add_sheet(write_excel_sheet_name)
#设置单元格列宽
write_excel_obj_worksheet.col(0).width = 11*500
write_excel_obj_worksheet.col(1).width = 22*500
write_excel_obj_worksheet.col(2).width = 23*500
write_excel_obj_worksheet.col(3).width = 12*500
write_excel_obj_worksheet.col(4).width = 12*500
write_excel_obj_worksheet.col(5).width = 12*500
write_excel_obj_worksheet.col(6).width = 12*500
write_excel_obj_worksheet.col(7).width = 12*500
write_excel_obj_worksheet.col(8).width = 18*500

#创建新的单元格样式

def create_style( font_height, bold, is_currency = False, is_date = False):
    #设置单元格格式
    style = xw.XFStyle()
    font = xw.Font()
    font.name = u"宋体"
    font.height = font_height
    font.bold = bold
    style.font = font
    alignment = xw.Alignment()
    alignment.horz = xw.Alignment.HORZ_CENTER
    alignment.vert = xw.Alignment.VERT_CENTER
    style.alignment = alignment
    style.alignment.wrap = 1
    borders = xw.Borders()
    borders.left = xw.Borders.NO_LINE
    borders.right = xw.Borders.THIN
    borders.top = xw.Borders.NO_LINE
    borders.bottom = xw.Borders.THIN
    borders.right_colour = 0x40
    borders.left_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style.borders = borders
    if(is_currency):
        style.num_format_str = '"￥"#,##0.00'
    if(is_date):
        style.num_format_str = 'yyyy-mm-dd'

    return style

style_header = create_style(
    16*20,True,False,False
)

style_header2 = create_style(
    22*20,True,False,False
)

style_normal = create_style(
    16*20,False,False,False
)

style_date = create_style(
    16*20,True,False,True
)

style_currency = create_style(
    16*20,True,True,False
)

style_currency2 = create_style(
    16*20,False,True,False
)
write_excel_obj_worksheet.write_merge(0,0,0,8, write_excel_head_name, style_header2)

write_excel_obj_worksheet_title = ['日期','客户名称','商品名称','单位','送货重量','实收重量','单价','金额','备注']
#写入标头
cur_col = 0
for weow in write_excel_obj_worksheet_title:

    write_excel_obj_worksheet.write(1,cur_col,weow, style_header)
    cur_col = cur_col + 1
#写入具体数据
cur_row = 2
for account_data in account_datas:
    for od in account_data['order']:
        for title, value in od.items():
            if((title in write_excel_obj_worksheet_title) == 0):
                continue
            title_col = write_excel_obj_worksheet_title.index(title)
            if(title_col >= 0):
                if(title == "金额"):
                    Fn = 'F' + str(cur_row+1)
                    Gn = 'G' + str(cur_row+1)
                    write_excel_obj_worksheet.write(cur_row,title_col,xw.Formula(Fn + '*' + Gn),style_currency)
                elif(title == '单价'):
                    write_excel_obj_worksheet.write(cur_row,title_col,value,style_currency2)
                else:
                    write_excel_obj_worksheet.write(cur_row,title_col,value,style_normal)
            else:
                continue
        date_col = write_excel_obj_worksheet_title.index('日期')
        write_excel_obj_worksheet.write(cur_row,date_col,account_data['date'],style_date)
        name_col = write_excel_obj_worksheet_title.index("客户名称")
        write_excel_obj_worksheet.write(cur_row,name_col,account_data['name'],style_normal)
        write_excel_obj_worksheet.height_mismatch = True
        write_excel_obj_worksheet.row(cur_row).height = 18*1000
        cur_row = cur_row + 1
    print(account_data['name'] + "写入完毕")
E3 = "E3"
En = "E" + str(cur_row)
H3 = "H3"
Hn = "H" + str(cur_row)
total_name_index = write_excel_obj_worksheet_title.index('单位')
total_value_index= total_name_index + 1
write_excel_obj_worksheet.write(cur_row,total_name_index,'总出货量：',style_header)
write_excel_obj_worksheet.write(cur_row,total_value_index,xw.Formula('SUM('+E3+':'+En+')'),style_normal)
#总额
total_price_index = write_excel_obj_worksheet_title.index('单价')
total_p_v_index= total_price_index + 1
write_excel_obj_worksheet.write(cur_row,total_price_index,'总额：',style_header)
write_excel_obj_worksheet.write(cur_row,total_p_v_index,xw.Formula('SUM('+H3+':'+Hn+')'),style_currency2)

print("全部写入完毕")

file_name ="出库信息表" + xls_sheet_name + ".xls"
#生成xlsx文件
write_excel_obj.save(xls_file_path + "/" + file_name)