#coding:utf-8
# import os
import win32com.client as win32
import shutil
import openpyxl
import os
import win32com.client as win32
import shutil
import openpyxl
import time
import time
#####part one
def makedir():
mydir = 'bak' # 基本逻辑就是分两种情况，文件夹有和无，有时，为了避免里面数据污染，删了重建一个；没有则是正常建一个就好
if os.path.exists(mydir) == 0:
os.mkdir(mydir)
else:
shutil.rmtree('bak')
os.mkdir(mydir)
def movedir():
file = []
path = os.getcwd() # 转换前程序所在文件目录
path_bak = os.getcwd() + r'/bak/'
pathdir = os.listdir(path)
# print(pathdir)
for s in pathdir:
newdir = os.path.join(path, s)
if os.path.isfile(newdir):
os.chdir(path_bak)
pathdir_bak = os.listdir(path_bak)
# print(pathdir_bak)
if os.path.splitext(newdir)[1] == ".xls":
t = os.path.split(s)[1] + r'x'
if os.path.exists(t):
# print(os.path.exists(os.path.split(s)[1]))
break
else:
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(newdir)
# print(newdir)
path_dir = os.path.split(newdir)[0]
path_file = os.path.split(newdir)[1]
# print(path_dir)
# print(path_file)
wb.SaveAs(path_dir + '/bak/' + path_file + 'x',
FileFormat=51) # FileFormat = 51 is for .xlsx extension
wb.Close() # FileFormat = 56 is for .xls extension
excel.Application.Quit()
elif os.path.splitext(newdir)[1] == ".xlsx":
file.append(newdir)
elif os.path.isdir(newdir):
continue
for i in range(len(file)):
pcm_file = file[i]
path_0 = os.path.split(file[i])[0]
path_1 = os.path.split(file[i])[1]
oldname = path_0 + '/' + path_1
newname = path_0 + r'/bak/' + path_1
shutil.copyfile(oldname, newname)
# time.sleep(5) #暂停10秒
#####第二步将文件目录切换至bak文件夹中，，切片u v w
file_deal = []
path_deal = os.getcwd() # 转换前程序所在文件目录
pathdir_deal = os.listdir(path_deal)
for u in pathdir_bak:
newdir_deal = os.path.join(path_deal, u)
file_deal.append(newdir_deal)
# print(file_deal)
for j in range(len(file_deal)):
pcm_file_deal = file_deal[j]
path_0_deal = os.path.split(file_deal[j])[0] # 文件所在目录
path_1_deal = os.path.split(file_deal[j])[1] # 文件名称带后缀
# print(pcm_file_deal)
# print(path_0_deal)
# print(path_1_deal)
wb = openpyxl.load_workbook(path_1_deal)
allSheets = wb.get_sheet_names()
for i in range(len(allSheets)):
sheet = wb.get_sheet_by_name(allSheets[i])
for row in sheet.iter_rows():
for cell in row:
if str(str(cell.value).replace(' ', '')).find("资产负债表") != -1:
sheet_selected = wb.get_sheet_by_name(allSheets[i])
# print(sheet_selected)
# print(path_1_deal)
print(path_0_deal)
fn['资产负债表'] = path_1_deal
# print(fn_fuzhaibiao)
ws['资产负债表'] = wb.get_sheet_by_name(sheet_selected.title)
# print(type(ws['资产负债表']))
continue
elif str(str(cell.value).replace(' ', '')).find("利润表") != -1 or str(
str(cell.value).replace(' ', '')).find("损益表") != -1:
sheet_selected = wb.get_sheet_by_name(allSheets[i])
# print(sheet_selected)
# print(path_1_deal)
#path['利润表'] = path_0_deal
fn['利润表'] = path_1_deal
ws['利润表'] = wb.get_sheet_by_name(sheet_selected.title)
continue
elif str(str(cell.value).replace(' ', '')).find("一般纳税人适用") != -1 or str(
str(cell.value).replace(' ', '')).find("小规模纳税人适用") != -1:
sheet_selected = wb.get_sheet_by_name(allSheets[i])
# print(sheet_selected)
# print(path_1_deal)
# path['纳税申报表'] = path_0_deal
fn['纳税申报表'] = path_1_deal
print(path_1_deal)
ws['纳税申报表'] = wb.get_sheet_by_name(sheet_selected.title)
continue
elif str(str(cell.value).replace(' ', '')).find("***子表开始") != -1:
sheet_selected = wb.get_sheet_by_name(allSheets[i])
# print(sheet_selected)
# print(path_1_deal)
# path['财务状况表'] = path_0_deal
fn['财务状况表'] = path_1_deal
# print(fn_fuzhaibiao)
ws['财务状况表'] = wb.get_sheet_by_name(sheet_selected.title)
# print(type(ws['资产负债表']))
continue
print(fn)
print(ws)
################part two
def getdata(fn_source,ws_source,date_row, date_column):
fn = fn_source
wb = openpyxl.load_workbook(fn)
row_result = 0
column_result = 0
result = 0
for row in ws_source.iter_rows():
for cell in row:
for i in range(len(date_row)):
#print(cell.value)
#print(date_row[i])
#print(str(str(cell.value).replace(' ', '')).find(date_row[i]))
#print(cell.value)
#print(len(str(cell.value).replace(' ', '')))
#print(len(str(cell.value).replace(' ', '')))
#print(len(date_row[i]))
if str(str(cell.value).replace(' ', '')).find(date_row[i]) != -1 :
#print(len(str(cell.value).replace(' ', '')))
#print(len(date_row[i]))
#print(str(str(cell.value).replace(' ', '')).find(date_row[i]))
row_result = cell.row
column_result = cell.column + 3
#print(cell.value)
#print(cell.row)
#print(row_result)
break
if row_result == 0 or column_result == 0:
result = 0
else:
if ws_source.cell(row = row_result , column = column_result).value != None:
#print(ws_source.cell(row=row_result, column=column_result).value)
result = ws_source.cell(row=row_result, column=column_result).value
wb.close()
return result
def writedata(fn_source,ws_source,date_row, date_column,data_write):
fn = fn_source
wb = openpyxl.load_workbook(fn)
row_result = 0
column_result = 0
result = 0
for row in ws_source.iter_rows():
for cell in row:
for i in range(len(date_row)):
if str(str(cell.value).replace(' ', '')).find(date_row[i]) != -1 :
row_result = cell.row
column_result = cell.column + 3
print(column_result)
break
ws_source.cell(row=row_result, column=column_result).value = data_write
print(ws_source.cell(row=row_result, column=column_result).value)
wb.save('ERP模板样例-(E103)财务状况.xlsx')
wb.close()
def getdata_call():
print("hell")
# 资产负债表
print(getdata(fn['资产负债表'],ws['资产负债表'], ['存货'], ['年初余额', '年初数']))
def writedata_call():
###写入操作
#年初存货
writedata(fn['财务状况表'],ws['财务状况表'],['一、年初存货'],['本年'],12321)
##############################part three
if __name__ == '__main__':
path = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}
fn = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}
ws = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}
makedir()
movedir()
print(path)
print(fn)
print(ws)
getdata_call()
writedata_call()
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
