import pymssql
import pandas as pd
from openpyxl import Workbook,load_workbook
import os

sap = pymssql.connect(
        host = '192.168.1.246',
        user = 'mymonitor',
        password = 'bfdl123456@',
        database = 'BF_CS'
    )

if sap:
    print("Connect 成功")
else:
    print("链接出错")

sql = "SELECT T0.[ItemCode] ,T1.[ItemName] AS 物料名称, T1.[FrgnName] AS 型号, FLOOR(T0.[OnHand]) AS 现存量,T1.[SalUnitMsr] AS 单位,T1.[MinLevel] AS 最小库存量 FROM OITW T0   INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]  INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] WHERE T0.[OnHand] != '0' AND T0.[ItemCode] not in ('x-001','x-002','x-003','x-004','x-0006','1000000','B0100212001001')  AND T2.[WhsCode] = '04';"
# 创建游标
cur = sap.cursor()
cur.execute(sql)
row = cur.fetchall()

print(len(row))
for i in range(0,len(row)):
    print(row[i])

pathExcel = 'C:/Users/18041/Desktop/text/shujuku/铜排车间.xlsx'
osExcel = os.path.exists(pathExcel)
if osExcel:
    wb = load_workbook(pathExcel)
    ws = wb.get_sheet_by_name('铜排车间')
else:
    wb = Workbook()
    ws = wb.active
    ws.title = '铜排车间'

ws['A1'] = '物料编码'
ws['B1'] = '物料名称'
ws['C1'] = '物料规格'
ws['D1'] = '数量'

for line in row:
    ws.append(line)

wb.save("C:/Users/18041/Desktop/text/shujuku/铜排车间.xlsx")
wb.close()