# 需要进入的第三方库
# pip install pypiwin32
# openpyxl
# shutil
# pyinstaller
# 更新了单元格中数值不填写的bug
# 不填写的数据，默认取值为空
# coding:utf-8
import os
import win32com.client as win32
import shutil
import openpyxl
# import time
#####part one
def makedir():
mydir = 'bak' # 基本逻辑就是分两种情况，文件夹有和无，有时，为了避免里面数据污染，删了重建一个；没有则是正常建一个就好
if os.path.exists(mydir) == 0:
os.mkdir(mydir)
else:
shutil.rmtree('bak')
if os.path.exists(mydir) == 0:
os.mkdir(mydir)
def movedir():
path = os.getcwd() # 转换前程序所在文件目录
path_bak = os.getcwd() + r'/bak/'
pathdir_bak = os.listdir(path_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
def getbaklist(path_deal):
pathdir_deal = os.listdir(path_deal)
# print(pathdir_deal)
path_bak = os.getcwd()
pathdir_bak = os.listdir(path_bak)
# print(pathdir_bak)
for u in pathdir_bak:
newdir_deal = os.path.join(path_deal, u)
file_deal.append(newdir_deal)
# print(file_deal)
# print(file_deal)
# print(file_deal)
def getbooksheet(path, filename):
# print( path + r'/' + filename )
wb = openpyxl.load_workbook(path + r'/' + filename)
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['资产负债表'] = allSheets[i]
# print(type(ws['资产负债表']))
# print(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['利润表'] = allSheets[i]
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['纳税申报表'] = allSheets[i]
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['财务状况表'])
ws['财务状况表'] = allSheets[i]
# print(type(ws['资产负债表']))
continue
wb.close()
'''
def getbooksheet():
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:
#print(cell.value)
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['资产负债表'] = allSheets[i]
#print(type(ws['资产负债表']))
#print(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['利润表'] = allSheets[i]
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['纳税申报表'] = allSheets[i]
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['财务状况表'])
ws['财务状况表'] = allSheets[i]
# print(type(ws['资产负债表']))
continue
'''
def getdata(path, filename, worksheet, data_row, data_column):
wb = openpyxl.load_workbook(path + r'/' + filename)
ws = wb.get_sheet_by_name(worksheet)
getdata_result = 0
row_result = 0
row_column = 0
column_result = 0
column_temp = 0
stop_row = 0
stop_column = 0
for row in ws.iter_rows():
for cell in row:
if stop_row == 1:
break
else:
for i in range(len(data_row)):
if str(str(cell.value).replace(' ', '')).find(data_row[i]) != -1:
# print(data_row[i])
row_result = cell.row
print(row_result)
column_temp = cell.column
stop_row = 1
break
for row in ws.iter_rows():
for cell in row:
if stop_column == 1:
break
else:
for i in range(len(data_column)):
if str(str(cell.value).replace(' ', '')).find(data_column[i]) != -1 and cell.column > column_temp:
# print(data_column[i])
column_result = cell.column
print(column_result)
stop_column = 1
break
# print(row_result)
# print(column_result)
if row_result == 0 or column_result == 0:
getdata_result = 0
else:
if ws.cell(row=row_result, column=column_result).value != None:
# print(ws_source.cell(row=row_result, column=column_result).value)
getdata_result = ws.cell(row=row_result, column=column_result).value
print(getdata_result)
wb.close()
return getdata_result
def writedata(path, filename, worksheet, data_row, data_column, data_write):
# print( path + r'/' + filename )
wb = openpyxl.load_workbook(path + r'/' + filename)
# print(wb)
ws = wb.get_sheet_by_name(worksheet)
row_result = 0
row_column = 0
column_result = 0
column_temp = 0
stop_row = 0
stop_column = 0
for row in ws.iter_rows():
for cell in row:
if stop_row == 1:
break
else:
for i in range(len(data_row)):
if str(str(cell.value).replace(' ', '')).find(data_row[i]) != -1:
# print(data_row[i])
row_result = cell.row
print(row_result)
column_temp = cell.column
stop_row = 1
break
for row in ws.iter_rows():
for cell in row:
if stop_column == 1:
break
else:
for i in range(len(data_column)):
if str(str(cell.value).replace(' ', '')).find(data_column[i]) != -1 and cell.column > column_temp:
# print(data_column[i])
column_result = cell.column
print(column_result)
stop_column = 1
break
# print(data_write)
if row_result == 0 or column_result == 0:
print("未获取需要写入的行或列号")
else:
print(len(str(data_write)))
if len(str(data_write)) == 0:
data_write = 0
ws.cell(row=row_result, column=column_result).value = round(data_write / 1000, 0)
else:
ws.cell(row=row_result, column=column_result).value = round(data_write / 1000, 0)
# print(data_write)
# print(data_write)
wb.save(filename)
wb.close
def writedata_especial(path, filename, worksheet, data_row, data_row_especial, data_column, data_write):
# print( path + r'/' + filename )
wb = openpyxl.load_workbook(path + r'/' + filename)
# print(wb)
ws = wb.get_sheet_by_name(worksheet)
row_result = 0
row_column = 0
column_result = 0
column_temp = 0
stop_row = 0
stop_column = 0
for row in ws.iter_rows():
for cell in row:
if stop_row == 1:
break
else:
for i in range(len(data_row)):
if str(str(cell.value).replace(' ', '')).find(data_row[i]) != -1 and str(
str(cell.value).replace(' ', '')).find(data_row_especial[0]) == -1:
# print(data_row[i])
row_result = cell.row
print(row_result)
column_temp = cell.column
stop_row = 1
break
for row in ws.iter_rows():
for cell in row:
if stop_column == 1:
break
else:
for i in range(len(data_column)):
if str(str(cell.value).replace(' ', '')).find(data_column[i]) != -1 and cell.column > column_temp:
# print(data_column[i])
column_result = cell.column
print(column_result)
stop_column = 1
break
# print(data_write)
if row_result == 0 or column_result == 0:
print("未获取需要写入的行或列号")
else:
print(len(str(data_write)))
if len(str(data_write)) == 0:
data_write = 0
ws.cell(row=row_result, column=column_result).value = round(data_write / 1000, 0)
else:
ws.cell(row=row_result, column=column_result).value = round(data_write / 1000, 0)
# print(data_write)
wb.save(filename)
wb.close
if __name__ == '__main__':
file = []
file_deal = []
fn = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}
ws = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}
makedir()
movedir()
getbaklist(os.getcwd())
for i in range(len(file_deal)):
pcm_file_deal = file_deal[i]
path_0_deal = os.path.split(file_deal[i])[0]
path_1_deal = os.path.split(file_deal[i])[1]
# print(path_0_deal)
# print(path_1_deal)
getbooksheet(path_0_deal, path_1_deal)
# print(fn)
# print(ws)
###第一部分：全都是资产负债表，准备批量
writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['年初存货'], ['1-本季', '本年'],
getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['存货'], ['年初余额', '年初数', '期初余额']))
# 季报/年报---流动资产合计
writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['流动资产合计'], ['1-本季', '本年'],
getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['流动资产合计'], ['期末余额', '期末数']))
一键式统计10.1测试如何小循环内批量处理数据
最新推荐文章于 2025-10-30 15:43:57 发布
该脚本主要用于批量处理Excel文件，首先创建备份目录，然后将原始Excel文件转换为.xlsx格式，并搜索特定工作表，如'资产负债表'、'利润表'等。在找到相关工作表后，脚本会读取并写入特定单元格数据，例如从'资产负债表'获取年初存货数据并写入'财务状况表'。此外，它还能处理流动资产合计等关键指标的季度和年度数据。
扫一扫
949
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
