财务季报

import os
import win32com.client as win32
import shutil
import openpyxl
import docx



#####第一步先降原文件夹中的xlsx文件直接拷贝至bak文件夹中，切片r s t

str01_row    = 0
str01_column = 0



mydir = 'bak'
if os.path.exists(mydir):
    shutil.rmtree('bak')


os.mkdir(mydir)



str01 = ''


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)




#####第二步将文件目录切换至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)
                    fn = path_1_deal
                    ws = wb.get_sheet_by_name(sheet_selected.title)
                    break




########### 资产负债表     1年初存货

########### 资产负债表     1年初存货


for row in ws.iter_rows():
    for cell in row:
        if str(str(cell.value).replace(' ', '')).find("总计") != -1:
            str01_row = cell.row
            print(cell.row)
            print(str01_row)
        else:
            ws_str01 = 0

            break
for row in ws.iter_rows():
    for cell in row:
        if str(str(cell.value).replace(' ', '')).find("上年同期") != -1 :
            str01_column = cell.column
            print(str01_column)

            break

if str01_row == 0 or str01_column == 0:
    str01 = ''
else:
    if ws.cell(row=str01_row, column=str01_column).value != None:
        str01 =   str(ws_fuzhaibiao.cell(row=nianchucunhuo_row, column=nianchucunhuo_column).value).replace(',', '')


str = '济南市财务季报上报单位'

print(str01)
wdoc = docx.Document()
ptr = wdoc.add_paragraph(str)
wdoc.save('财务季报.docx')