python和excel笔记

shenshuai

wb = openpyxl.Workbook()    #创建一个新的excel文件

ws = wb.active      #获取工作表

wb = openpyxl.load_workbook(r"文件名称路径") #这个和可以打开指定的excel文件, wb是一个变量，可以改成任何

wb.get_sheet_names() 和 wb.sheetnames    #这两个函数可以调用excel文件里面的所有的工作表

ws = wb["工作表名称"]   #这个函数可以调用指定的工作表

nws = wb.create_sheet(index = 0, title = "工作表名称")    #这个函数可以创建一个新的工作表，并且给他命名， index意思是你想把这张工作表放在什么位置，0是第一位

wb.remove_sheet(wb.get_sheet_by_name("工作表名称"))     #remove_sheet()可以删除工作表，但是()里不能直接输入工作表的名字，需要输入工作表的对象，所以这里就需要写wb.get_sheet_by_name("工作表名称")来指定那个一个工作表是你想删除掉的

c = ws['A2'].value      #可以获取单元格里的内容是什么

c.row   #可以获取横列位置

c.colum  #可以获取竖列位置

c.coordinate #可以获取单元格位置

d = c.offset(2, 0)   #这个意思是以c作为一个位置向下移动2个，

openpyxl.cell.cell.get_column_letter(输入你想查找竖列的数字)   #这个函数可以找见竖列字母在第几位，比如openpyxl.cell.cell.get_column_letter(496)   就会显示'SB'

openpyxl.cell.cell.column_index_from_string(想要查找竖列的字母) #这个函数可以找间竖列排在第几位，比如openpyxl.cell.cell.colunm_index_from_string('SB')   就会显示496

import pandas as pd
import os
from openpyxl import load_workbook
import datetime
import easygui as gui
address = os.getcwd()

def find(name,name2):
    for root, dirs, files in os.walk(address):
        if name in files and name2 in files:
            df1 = pd.read_excel(os.path.join(root,name))
            df2 = pd.read_excel(os.path.join(root,name2))
            
            #df1 = df1.drop(df1.columns[[1,2,3,4,5,6]], axis = 1)
            df2 = df2.drop(df2.columns[[2,3]], axis = 1)

            horizontal_stack = pd.concat([df1, df2], axis = 1)
            horizontal_stack.insert(8, '单品编号', 0)
            horizontal_stack.insert(9, '单品库存：Ontraio', 0)
            horizontal_stack.insert(10, '单品库存：Memphis', 0)
            horizontal_stack.insert(11, '单品库存：Kansas City', 0)
            horizontal_stack.insert(12, '组合库存：Ontario', 0)
            horizontal_stack.insert(13, '组合库存：Memphis', 0)
            horizontal_stack.insert(14, '组合库存：Kansas City', 0)
            horizontal_stack.insert(15, 'SFP', 0)
            horizontal_stack.insert(21, '单品SFP', 0)
            #num_inserted_row = 7
            #for i in range(num_inserted_row):
                #horizontal_stack.insert(8, 'new_col%s' % i, 0)
            
            export_excel = horizontal_stack.to_excel(address + '\\test_Inventory.xlsx', index = None, header = True)
try:
    s = gui.enterbox(image = 'PA.png', msg ='enter first excel file name: ', title ='Paramount Automotive')
    s2 = gui.enterbox(image = 'PA.png', msg = 'enter second excel file name: ', title = 'Paramount Automotive')
    find(s + '.xlsx',s2 + '.xlsx')
except:
    None
    
wb = load_workbook(address + '\\test_Inventory.xlsx')
ws = wb.active
wb2 = load_workbook(address + '\\Not SFP.xlsx')
ws2 = wb2.active


num = 1
while 1:
    cell = ws.cell(row=num, column=1).value
    if cell:
        num = num +1
    else:
        break
        exit()
        
num2 = 1
for row in range (1, ws2.max_row):
    if(ws2.cell(row,1).value is None):
        break
    else:
        num2 = num2 + 1
        
for h in range (num):
    ws['I%d' % (h+1)].value = '=IF(ISERROR(FIND(" : ",H%d)),H%d,RIGHT(H%d,LEN(H%d)-FIND(" : ",H%d)-2))' % (h + 1, h + 1, h + 1, h + 1, h + 1)
ws['I1'].value = '单品编号'
for j in range (num):
    ws['J%d' % (j+1)].value = '=VLOOKUP($I%d,$Q:$X, 3,0)' % (j + 1)
ws['J1'].value = '单品库存：Ontraio'
for k in range (num):
    ws['K%d' % (k + 1)].value = '=VLOOKUP($I%d,$Q:$X, 4,0)' % (k + 1)
ws['K1'].value = '单品库存：Memphis'
for l in range (num):
    ws['L%d' % (l + 1)].value = '=VLOOKUP($I%d,$Q:$X, 5,0)' % (l + 1)
ws['L1'].value = '单品库存：Kansas City'
for m in range(num):
    ws['M%d' % (m + 1)].value = '=MINIFS(J:J,$A:$A,$A%d)' % (m + 1)
ws['M1'].value = '组合库存：Ontario'
for n in range(num):
    ws['N%d' % (n + 1)].value = '=MINIFS(K:K,$A:$A,$A%d)' % (n + 1)
ws['N1'].value = '组合库存：Memphis'
for o in range(num):
    ws['O%d' % (o + 1)].value = '=MINIFS(L:L,$A:$A,$A%d)' % (o + 1)
ws['O1'].value = '组合库存：Kansas City'

for p in range(num):
    ws['P%d' % (p + 1)].value = '=IF(AND(M%d>=5,N%d>=5,O%d>=5),"Seller Fulfilled Prime - Complete",IF(AND(M%d>=5,N%d>=5,O%d<5),"Seller Fulfilled Prime - Ontario + Memphis",IF(AND(M%d>=5,N%d<5,O%d>=5),"Seller Fulfilled Prime - Ontario + Kansas City",IF(AND(M%d<5,N%d>=5,O%d>=5),"Seller Fulfilled Prime - Kansas City + Memphis",IF(AND(M%d<5,N%d<5,O%d<5),"Default Amazon Template",IF(AND(M%d>=5,N%d<5,O%d<5),"Seller Fulfilled Prime - Ontario",IF(AND(M%d<5,N%d>=5,O%d<5),"Seller Fulfilled Prime - Memphis",IF(AND(M%d<5,N%d<5,O%d>=5),"Seller Fulfilled Prime - Kansas City",0))))))))' % (p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1,p + 1)
ws['P1'].value = 'SFP'

for v in range(ws.max_row):
    ws['v%s' % (v + 1)].value = '=IF(AND(S%d>=5,T%d>=5,U%d>=5),"Seller Fulfilled Prime - Complete",IF(AND(S%d>=5,T%d>=5,U%d<5),"Seller Fulfilled Prime - Ontario + Memphis",IF(AND(S%d>=5,T%d<5,U%d>=5),"Seller Fulfilled Prime - Ontario + Kansas City",IF(AND(S%d<5,T%d>=5,U%d>=5),"Seller Fulfilled Prime - Kansas City + Memphis",IF(AND(S%d<5,T%d<5,U%d<5),"Default Amazon Template",IF(AND(S%d>=5,T%d<5,U%d<5),"Seller Fulfilled Prime - Ontario",IF(AND(S%d<5,T%d>=5,U%d<5),"Seller Fulfilled Prime - Memphis",IF(AND(S%d<5,T%d<5,U%d>=5),"Seller Fulfilled Prime - Kansas City",0))))))))' % (v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1,v + 1)
ws['V1'].value = '单品SFP'

ws3 =wb.create_sheet('Not SFP')
sheet = wb['Not SFP']
for a in range (num2):
    sheet['A%d' % (a + 1)].value = ws2['A%d' % (a + 1)].value
for b in range (num2):
    sheet['B%d' % (b + 1)].value = ws2['B%d' % (b + 1)].value
    
for w in range (ws.max_row):
    ws['W%d' % (w + 1)].value = "=VLOOKUP(Q%d,'Not SFP'!$A:$B,2,0)" % (w + 1)
    
savefile = wb.save(address + '\\test_Inventory.xlsx')
gui.msgbox('Done!!')

大大东

shenshuai

大大东 发表于 2019-10-25 10:16

谢谢老哥支持哈，我怕自己忘记了，都写下来了哈

studentzy