import shutil
import os

from openpyxl import load_workbook
from xlutils.copy import copy
import win32com.client as win32
import xlrd


def copyfiles3p3k():
    rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\外3p3k\\"
    sourcefile=rootdir+"\\aa.xls"
    # listdir=os.listdir(rootdir)
    for fileindex in range(14,41,2):
        filename="3p3k"+str(fileindex)+"k.xls"
        # print(filename)
        file_full_name=rootdir+filename
        print(file_full_name)
        shutil.copyfile(sourcefile,file_full_name)

def copyfiles3p4k():
    rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\内3p4k\\"
    sourcefile=rootdir+"\\aa.xls"
    # listdir=os.listdir(rootdir)
    for fileindex in range(14,41,2):
        filename="3p4k"+str(fileindex)+"k.xls"
        # print(filename)
        file_full_name=rootdir+filename
        print(file_full_name)
        shutil.copyfile(sourcefile,file_full_name)

def copyfiles6p3k():
    rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\外6p3k\\"
    sourcefile=rootdir+"\\aa.xls"
    # listdir=os.listdir(rootdir)
    for fileindex in range(14,41,2):
        filename="6p3k"+str(fileindex)+"k.xls"
        # print(filename)
        file_full_name=rootdir+filename
        print(file_full_name)
        shutil.copyfile(sourcefile,file_full_name)

def copyfiles6p4k():
    rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\外6p4k\\"
    sourcefile=rootdir+"\\aa.xls"
    # listdir=os.listdir(rootdir)
    for fileindex in range(14,41,2):
        filename="6p4k"+str(fileindex)+"k.xls"
        # print(filename)
        file_full_name=rootdir+filename
        print(file_full_name)
        shutil.copyfile(sourcefile,file_full_name)

def copyfiles6p4knei():
    rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\内6p4k\\"
    sourcefile=rootdir+"\\aa.xls"
    # listdir=os.listdir(rootdir)
    for fileindex in range(14,41,2):
        filename="6p4k"+str(fileindex)+"k.xls"
        # print(filename)
        file_full_name=rootdir+filename
        print(file_full_name)
        shutil.copyfile(sourcefile,file_full_name)

def read_and_modify_cell(rootdir,prefilename,sheetname,lastname,worksheet_read,rowcount):
    # cell(行，列)所有单元格从0开始
    wfn = int(worksheet_read.cell(9, 4).value + 1)#这个是双层4K内走廊
    # wfn = int(worksheet_read.cell(8,4).value+1)#这个是双层4K外走廊
    # print(wfn)#这个是得到板房K数
    # 要写入的文件，找到表
    path=rootdir+prefilename+str(wfn)+lastname
    print(path)
    rb = xlrd.open_workbook(path)
    wb = copy(rb)
    ws = wb.get_sheet(0)
    # for i in range(2, rowcount):
    #     for j in range(0, 5, 2):
            # print(str(worksheet_read.cell(i, j).value))
            # ws.write(i, j, label=worksheet_read.cell(i, j).value)
    # print(worksheet_read.cell(13, 8).value+":"+worksheet_read.cell(23, 8).value)
    ws.write(13, 8, label=worksheet_read.cell(13, 8).value)
    ws.write(22, 8, label=worksheet_read.cell(22, 8).value)
    wb.save(path)
    # exit()

def modify_bbxls():
    readfilename = 'F:\\数据备份20211108\\材料模版\\bb4p.xlsx'
    savefilename='F:\\数据备份20211108\\材料模版\\bb4p.xls'
    # 打开文件
    workbook_read_write=load_workbook(readfilename)
    # 得到要修改的表
    worksheet_read_write=workbook_read_write.get_sheet_by_name('计算')
    # 修改单元格
    for fileindex in range(14, 41, 2):
        # print(fileindex)
        worksheet_read_write['b3']=str(fileindex)
        # 写入模板单元格
        workbook_read_write.save(readfilename)
        # 把要读取的xlsx改成xls
        if(os.path.isfile(savefilename)):
            os.remove(savefilename)
        excel=win32.gencache.EnsureDispatch('excel.application')
        pro=excel.Workbooks.Open(readfilename)
        pro.SaveAs(savefilename,FileFormat=56)
        pro.Close(True)
        excel.Application.Quit()
        # 得到板房模板
        sheetname=''
        sheettype='6p4k'
        zoulang=0
        rootdir="F:\\数据备份20211108\\材料模版\\back\\4异形内外走廊钢构\\内6p4k\\"
        lastname='k.xls'
        rowcount=0
        if sheettype=='3p3k':
            rowcount=29
            sheetname='3p3k外材料';
        elif sheettype=='3p4k':
            if zoulang==0:
                rowcount=27
                sheetname = '3p4k内材料';
            else:
                rowcount = 30
                sheetname = '3p4k外材料';
        elif sheettype=='6p3k':
            rowcount=44
            sheetname = '6p3k外材料';
        elif sheettype=='6p4k':
            if zoulang == 0:
                rowcount = 43
                sheetname = '6p4k内材料';
            else:
                rowcount = 45
                sheetname = '6p4k外材料';

        worksheet_read=xlrd.open_workbook(savefilename).sheet_by_name(sheetname)
        read_and_modify_cell(rootdir,sheettype,sheetname,lastname,worksheet_read,rowcount)


if __name__ == '__main__':
    print('PyCharm')
    modify_bbxls()
    # copyfiles3p4k()
    # copyfiles6p3k()
    # copyfiles6p4k()
    # copyfiles3p3k()
    # copyfiles6p4knei()