# -*- coding:utf-8 -*-

import xlrd, xlsxwriter

# 待合并excel
allxls = ["D:\\excelcs\\***.xlsx"]
allxls1 = ["D:\\excelcs\\***.xlsx"]
allxls2 = ["D:\\excelcs\\***.xlsx"]
#print(allxls[0:2])
# 目标excel
end_xls = "D:\\excelcs\\***.xlsx"


def open_xls(file):
    try:
        fh = xlrd.open_workbook(file)
        return fh
    except Exception as e:
        print("打开文件错误：" + e)


# 根据excel名以及第几个标签信息就可以得到具体标签的内容
def get_file_value(filename, sheetnum):
    rvalue = []
    fh = open_xls(filename)
    sheet = fh.sheets()[sheetnum]
    row_num = sheet.nrows
    for rownum in range(0, row_num):
        rvalue.append(sheet.row_values(rownum))
    return rvalue

###获取第一个excel的sheet个数以及名字作为标准
#获取excel的个数以及名字作为标准
first_file_fh = open_xls(allxls[0])
first_file_fh1 = open_xls(allxls1[0])
first_file_fh2 = open_xls(allxls2[0])
first_file_sheet = first_file_fh.sheets()
first_file_sheet1 = first_file_fh1.sheets()
first_file_sheet2 = first_file_fh2.sheets()
first_file_sheet_num = len(first_file_sheet)
#print(first_file_sheet_num)
sheet_name = []
sheet_name1 = []
sheet_name2 = []
for sheetname in first_file_sheet:
    sheet_name.append(sheetname.name)
for sheetname in first_file_sheet1:
    sheet_name.append(sheetname.name)
for sheetname in first_file_sheet2:
    sheet_name.append(sheetname.name)
#print("sheet_name:", sheet_name)
# 定义一个目标excel
endxls = xlsxwriter.Workbook(end_xls)

all_sheet_value = []
all_sheet_value1 = []
all_sheet_value2 = []
# 把所有内容都放到列表all_sheet_value中
#for file_name in allxls:
#for sheet_num in range(0, first_file_sheet_num):
#    all_sheet_value.append([])
#    for file_name in allxls:
#    #for sheet_num in range(0, first_file_sheet_num):
#        print("正在读取" + file_name + "的第" + str(sheet_num + 1) + "个标签...")
#        file_value = get_file_value(file_name, sheet_num)
#        all_sheet_value[sheet_num].append(file_value)
#        print("file_value:", file_value)

for file_name in allxls:
    all_sheet_value.append([])
    for sheet_num in range(0, first_file_sheet_num):
        print("正在读取" + file_name + "的第" + str(sheet_num + 1) + "个标签...")
        file_value = get_file_value(file_name, sheet_num)
        all_sheet_value[sheet_num].append(file_value)

for file_name in allxls1:
    all_sheet_value1.append([])
    for sheet_num in range(0, first_file_sheet_num):
        print("正在读取" + file_name + "的第" + str(sheet_num + 1) + "个标签...")
        file_value = get_file_value(file_name, sheet_num)
        all_sheet_value1[sheet_num].append(file_value)

for file_name in allxls2:
    all_sheet_value2.append([])
    for sheet_num in range(0, first_file_sheet_num):
        print("正在读取" + file_name + "的第" + str(sheet_num + 1) + "个标签...")
        file_value = get_file_value(file_name, sheet_num)
        all_sheet_value2[sheet_num].append(file_value)
        #print("file_value:", file_value)
#print(all_sheet_value[0])
print("*************************")
print("*************************")
print("*************************")
#print(all_sheet_value[1])
all_sheet_value3 = all_sheet_value + all_sheet_value1 + all_sheet_value2
print("########################")
print("########################")
print("########################")
print(all_sheet_value3)
num = -1
sheet_index = -1
#print(sheet_name)
#print(all_sheet_value[value_index])
# 将列表all_sheet_value的内容写入目标excel
for sheet in (all_sheet_value3):
    sheet_index += 1
    end_xls_sheet = endxls.add_worksheet(sheet_name[sheet_index])
    num += 1
    num1 = -1
    for sheet1 in sheet:
        for sheet2 in sheet1:
            print(type(sheet2))
            num1 += 1
            num2 = -1
            for sheet3 in sheet2:
                num2 += 1
                # 在第num1行的第num2列写入sheet3的内容
                end_xls_sheet.write(num1, num2, sheet3)

endxls.close()