代码
# coding=utf-8
import pandas as pd
import openpyxl
import pymssql
from openpyxl import load_workbook
a3s = ["通用", "天猫", "淘宝", "京东", "拼多多", "唯品会", "抖音", "驿氪", "sku表"]
sheets = ["商品属性-通用", "商品属性-天猫", "商品属性-淘宝", "商品属性-京东", "商品属性-拼多多", "商品属性-唯品会",
"商品属性-抖音", "商品属性-驿氪", "sku表"]
a_ = "DO"
num = 2 # 待修改
fNames = ['中筒袜','保暖上装','保暖套装','保暖裤','吊带背心T恤','女三角裤','女安全裤','少女文胸','平角裤','文胸','文胸套装','短袜','美腿袜','睡衣家居服套装','休闲鞋','手链','男士包袋-单肩斜挎包','腰带','项链','保暖上装','保暖套装','保暖裤','吊带背心T恤','棉袜','男三角裤','男平角内裤','包头拖']
a1s = ['女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-女内衣','女装-家居服','女鞋-低帮鞋','服饰配件饰品流行首饰','服饰配件饰品流行首饰','服饰配件饰品流行首饰','服饰配件饰品流行首饰','男装-男内衣','男装-男内衣','男装-男内衣','男装-男内衣','男装-男内衣','男装-男内衣','男装-男内衣','男鞋']
def main():
selectNum = num
checkNum = selectNum
a = a_
a1Num = 0
for a2 in fNames:
a1 = a1s[a1Num]
a1Num += 1
print("正在打印"+a1+"—" + a2)
excelFile = 'excel03/'+a1+'-' + a2 + '.xlsx'
# 多选编号
data_path = "data.txt"
dict_path = "dict.txt"
manyselect = []
# ======================== 数据库
server = '***'
database = '***'
username = 'demo'
password = 'demo'
# 连接到数据库
conn = pymssql.connect(server=server, user=username, password=password, database=database,
tds_version="7.0") # 最后一个参数不是必须的
# 创建游标对象
cursor = conn.cursor()
# +========================
pa2 = a2
file_path = excelFile # 替换为你的 Excel 文件路径 #换页要改
for sheet, a3 in zip(sheets, a3s):
try:
df = pd.read_excel(file_path, sheet_name=sheet, header=0, nrows=1) # 读取第一行数据
except Exception as e: # 捕获所有异常
print(f"Error reading sheet '{sheet}': {e}")
continue # 发生异常时继续下一个循环
# 获取有多选框的列
poist = read_with_dropdown(excelFile, sheet)
cListNum = -1
# print(poist)
for index, column in enumerate(df.columns):
if index == 0:
continue
if index == 1 and sheet != "sku表":
continue
# 多项 写入文件
if (number_to_excel_column(index + 1) in poist):
# 如果是多选 +1
cListNum = cListNum + 1
# 获取选项列表
cList = retList(file_path, sheet, cListNum)
# 打印多选框列表 f"{number:02d}"
para2 = ""
if (selectNum < 10):
para2 = a + "0" + str(selectNum)
else:
para2 = a + str(selectNum)
n = 1
for c in cList:
# 字典表
s = '%02d' % n
# ===================== 插入数据
# 字典表
sql = "INSERT INTO sal_po_typeBase04 (category, categoryName, code, name, orderseq) VALUES (%s, %s, %s, %s, %d)"
params = (para2, column, para2 + s, para2 + s, str(n)) # 替换成实际的参数值
try:
# 执行 SQL 插入操作
cursor.execute(sql, params)
# 提交事务
conn.commit()
except Exception as e:
print(f'插入数据时出错：{str(e)}')
# 如果出错，回滚事务
conn.rollback()
# =====================上面为字典表
# ===================== 插入数据 数据表
# 字典表
para3 = para2 + s
sql = "INSERT INTO sal_po_typeData04 (stype,category,attributes,column_Name,column_type,column_content,remarks) VALUES (%s, %s, %s, %s, %s, %s, %s)"
params = (a1, pa2, a3, column, 2, para3, "") # 替换成实际的参数值
try:
# 执行 SQL 插入操作
cursor.execute(sql, params)
# 提交事务
conn.commit()
except Exception as e:
print(f'插入数据时出错：{str(e)}')
# 如果出错，回滚事务
conn.rollback()
# =====================
n = n + 1
if len(cList) < 1:
# ===================== 插入数据 数据表
# 字典表
para3 = para2 + s
sql = "INSERT INTO sal_po_typeData04 (stype,category,attributes,column_Name,column_type,column_content,remarks) VALUES (%s, %s, %s, %s, %s, %s, %s)"
params = (a1, pa2, a3, column, 2, "", "") # 替换成实际的参数值
try:
# 执行 SQL 插入操作
cursor.execute(sql, params)
# 提交事务
conn.commit()
except Exception as e:
print(f'插入数据时出错：{str(e)}')
# 如果出错，回滚事务
conn.rollback()
# =====================
selectNum = selectNum + 1
if selectNum > 99:
selectNum = 1
if a[1] == 'Z':
a = chr(ord(a[0]) + 1) + 'A'
else:
a = a[0] + chr(ord(a[1]) + 1)
# 单项v
else:
# ===================== 插入数据 数据表
# 字典表
sql = "INSERT INTO sal_po_typeData04 (stype,category,attributes,column_Name,column_type,column_content,remarks) VALUES (%s, %s, %s, %s, %s, %s, %s)"
params = (a1, pa2, a3, column, 1, "", "") # 替换成实际的参数值
try:
# 执行 SQL 插入操作
cursor.execute(sql, params)
# 提交事务
conn.commit()
except Exception as e:
print(f'插入数据时出错：{str(e)}')
# 如果出错，回滚事务
conn.rollback()
# =====================
if selectNum != checkNum:
print("此刻编号 a 的值是 ：" + a)
print("此刻编号 num 的值是 ：" + str(selectNum))
def colums(filename):
tcolums = []
# 获取通用的所有选项
file_path = filename # 替换为你的 Excel 文件路径 #换页要改
df = pd.read_excel(file_path, sheet_name='商品属性-通用', header=0, nrows=1) # 读取第一行数据
for index, column in enumerate(df.columns):
if index == 0 | index == 1:
continue
tcolums.append(column)
return tcolums
def number_to_excel_column(n):
result = ""
while n > 0:
n -= 1
result = chr(n % 26 + ord('A')) + result
n //= 26
return result
def read_with_dropdown(book_name, sheet_name):
poist = []
# 读取excel
wb = openpyxl.load_workbook(book_name)
# 读取sheet表
ws = wb[sheet_name]
# 读取excel指定单元格数据
# data = ws["A1":"G5"]
# 获取内容存在下拉选的框数据
validations = ws.data_validations.dataValidation
# 遍历存在下拉选的单元格
for validation in validations:
# 获取下拉框中的所有选择值
cell = validation.sqref
result = validation.formula1
#下拉选内容：result
s = str(cell)
poist.append(s.split(":")[0][:-1])
return poist
def convert_to_number(letter, columnA=0):
"""
字母列号转数字
columnA: 你希望A列是第几列(0 or 1)? 默认0
return: int
"""
ab = '_ABCDEFGHIJKLMNOPQRSTUVWXYZ'
letter0 = letter.upper()
w = 0
for _ in letter0:
w *= 26
w += ab.find(_)
return w - 1 + columnA
# 解析具名区域字符串
# named_range_string = '商品属性值!$Q$1:$Q$40'
def retList(fileName, sheet, column):
file_Path = fileName
wb = load_workbook(file_Path)
ws = wb[sheet]
if len(ws.data_validations.dataValidation) <= column:
return "1"
#判断是否存在
try:
b2 = ws.data_validations.dataValidation[column].formula1
attr_text = wb.defined_names[b2].attr_text
except KeyError as e:
return ""
print(f"Key not found: {e}")
sheet_name, cell_range = attr_text.split('!')
start_cell, end_cell = cell_range.split(':')
start_col = (start_cell).split("$")[1]
# 获取工作表
ws = wb[sheet_name]
col = ws[start_col]
cs = []
for c in col:
if c.value is not None:
cs.append(c.value)
return cs
if __name__ == "__main__":
main()
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
