制作简易出库明细

import xlwings as xw
# from win32com.client import Dispatch
# xl = xw._xlwindows.COMRetryObjectWrapper(Dispatch("Ket.Application"))
# impl = xw._xlwindows.App(visible=False, add_book=False, xl=xl)

app = xw.App(visible=False, add_book=False)
workbook = app.books.open("出入库台账表.xlsx")
worksheet1 = workbook.sheets["出库登记表"]
worksheet2 = workbook.sheets["入库登记表"]
worksheet3 = workbook.sheets["高配库房"]
worksheet4 = workbook.sheets["热修库房"]
worksheet5 = workbook.sheets["机修间库房"]
worksheet6 = workbook.sheets["端台库房"]
worksheet7 = workbook.sheets["电机库房"]
print('请如实填写设备台账保存，后期审计审核重要依据。')
bm = str(input('请输入物料编码：'))
SL = int(input('请输入数量：'))
lyr = str(input('备件领用人:'))
#填写
cell1 = worksheet1.range('C1').expand('down').last_cell
cell = cell1.offset(row_offset=1)
sj = cell.offset(column_offset=6)
cell98 = cell.offset(column_offset=-1)
cell97 = cell.offset(column_offset=-2)

worksheet1.range(cell).value = bm
#数量填写
cell2 = worksheet1.range('F1').expand('down').last_cell
cell3 = cell2.offset(row_offset=1)
worksheet1.range(cell3).value = SL
#读取时间，自动填写
sj.number_format = 'yyyy"年"m"月"d"日"'
sj.formula = "=NOW()"#时间

if a == 8:
    b = input('请输入厂领导姓名：')
    sj.offset(column_offset=2).value = b
elif a == 1:
    c = str(input('请说明紧急领用理由：'))
    b = list1[a]
    sj.offset(column_offset=2).value = b
    sj.offset(column_offset=3).value = c
else:
    b = list1[a]
    sj.offset(column_offset=2).value = b

#包含物料编码的单元格区域
gpk = worksheet3.range('C2').expand('down')
jxj = worksheet5.range('C2').expand('down')
rxk = worksheet4.range('C2').expand('down')
dt = worksheet6.range('C2').expand('down')
djk = worksheet7.range('C2').expand('down')

#循环高配库物料编码
for r in gpk.rows:
    gpk_bm = str(worksheet3.range(r).value)
    if gpk_bm == bm:
        gpk1 = r.offset(column_offset=1)#高配台账物料名称定位
        cell4 = cell.offset(column_offset=1)#出库登记表物料名称定位
        gpk1.copy(cell4)#复制物料名称
        #规格型号
        gpk2 = r.offset(column_offset=2)
        cell5 = cell.offset(column_offset=2)
        gpk2.copy(cell5)

        gpk3 = int(r.offset(column_offset=4).value)
        gpk4 = gpk3 - SL #物料数量减出
        r.offset(column_offset=4).value = gpk4
        worksheet1.range(cell97).value = "高配库房"
        gpk99 = r.offset(column_offset=-1)
        gpk99.copy(cell98)

        print('高配库房出库成功，如果库房不正确请在《出入库台账表.xlsx》中核实，谢谢配合。')
        break

for x in rxk.rows:
    rxk_bm = str(worksheet4.range(x).value)
    if rxk_bm == bm:
        rxk1 = x.offset(column_offset=1)  # 高配台账物料名称定位
        cell4 = cell.offset(column_offset=1)  # 出库登记表物料名称定位
        rxk1.copy(cell4)  # 复制物料名称
        # 规格型号
        rxk2 = x.offset(column_offset=2)
        cell5 = cell.offset(column_offset=2)
        rxk2.copy(cell5)

        rxk3 = int(x.offset(column_offset=4).value)
        rxk4 = rxk3 - SL  # 物料数量减出
        x.offset(column_offset=4).value = rxk4
        worksheet1.range(cell97).value = "热修库房"
        rxk5 = x.offset(column_offset=1)  # 高配台账存放位置定位
        cell97.copy(rxk5)  # 复制高配台账存放位置定位
        print('热修库房出库成功，如果库房不正确请在《出入库台账表.xlsx》中核实，谢谢配合。')
        break

for j in jxj.rows:
    jxj_bm = str(worksheet5.range(j).value)
    if jxj_bm == bm:
        jxj1 = j.offset(column_offset=1)  # 高配台账物料名称定位
        cell4 = cell.offset(column_offset=1)  # 出库登记表物料名称定位
        jxj1.copy(cell4)  # 复制物料名称
        # 规格型号
        jxj2 = j.offset(column_offset=2)
        cell5 = cell.offset(column_offset=2)
        jxj2.copy(cell5)

        jxj3 = int(j.offset(column_offset=4).value)
        jxj4 = jxj3 - SL  # 物料数量减出
        j.offset(column_offset=4).value = jxj4
        worksheet1.range(cell97).value = "机修间库房"
        jxj5 = j.offset(column_offset=1)  # 高配台账存放位置定位
        cell97.copy(jxj5)  # 复制高配台账存放位置定位
        print('机修间库房出库成功，如果库房不正确请在《出入库台账表.xlsx》中核实，谢谢配合。')
        break

for d in dt.rows:
    dt_bm = str(worksheet6.range(d).value)
    if dt_bm == bm:
        dt1 = d.offset(column_offset=1)  # 高配台账物料名称定位
        cell4 = cell.offset(column_offset=1)  # 出库登记表物料名称定位
        dt1.copy(cell4)  # 复制物料名称
        # 规格型号
        dt2 = d.offset(column_offset=2)
        cell5 = cell.offset(column_offset=2)
        dt2.copy(cell5)

        dt3 = int(d.offset(column_offset=4).value)
        dt4 = dt3 - SL  # 物料数量减出
        d.offset(column_offset=4).value = dt4
        worksheet1.range(cell97).value = "端台库房"
        dt5 = d.offset(column_offset=1)  # 高配台账存放位置定位
        cell97.copy(dt5)  # 复制高配台账存放位置定位
        print('端台库房出库成功，如果库房不正确请在《出入库台账表.xlsx》中核实，谢谢配合。')
        break
#循环dianji物料编码
for m in djk.rows:
    djk = str(worksheet7.range(m).value)
    if djk == bm:
        djk1 = m.offset(column_offset=1)#DIANJI台账物料名称定位
        cell.offset(column_offset=1).value = '电机'
        #规格型号
        cell5 = cell.offset(column_offset=2)
        djk1.copy(cell5)

        djk3 = int(m.offset(column_offset=6).value)
        gpk4 = djk3 - SL #物料数量减出
        m.offset(column_offset=4).value = gpk4
        worksheet1.range(cell97).value = "热修库房"
        gpk99 = m.offset(column_offset=-1) #存放位置
        gpk99.copy(cell98)

        print('电机出库成功，如果库房不正确请在《出入库台账表.xlsx》中核实，谢谢配合。')
        break


workbook.save()#保存
workbook.close()#关闭
app.quit()#退出Excel程序