Python读写txt, xlsx文件实践

#！/usr/bin/env python3
#-*-coding: UTF-8 -*-
#Author 守望的距离

import time
import base64
import openpyxl
import binascii

from openpyxl import workbook

def decode_base64(dataIn):
    tempData = base64.b64decode(dataIn)
    bs = binascii.hexlify(bytearray(tempData))
    return bytes.decode(bs, encoding='utf-8').lower()

def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    for i in range(len(index)):
        for j in range(len(value[i])):
            sheet.cell(row = i+1, column = j+1, value = str(value[i][j]))
    workbook.save(path)
    print('xlsx表格写入成功')

def read_excel_xlsx(path, sheet_name):
    workbook = openpyxl.load_workbook(path)
    sheet = workbook[sheet_name]
    x1_list2 = []
    for row in sheet.rows:
        x1_list1 = []
        for cell in row:
            x1_list1.append(cell.value)
        x1_list2.append(x1_list1)
    x1_list2.pop(0)
    return x1_list2

with open(r'D:\SEIDTool\IN\eseidFile.txt', 'r') as f1:
    read_data1 = f1.read()
    f1.close()

arr1 = read_data1.split('\n')
if arr1[-1] == '':
    arr1.pop(-1)

with open(r'D:\SEIDTool\IN\secretFile.txt', 'r') as f2:
    read_data2 = f2.read()
    f2.close()

arr2 = read_data2.split('\n')
if arr2[-1] == '':
    arr2.pop(-1)
path = r'D:\SEIDTool\NFC卡片ID导出模板.xlsx'
read_x1_list = read_excel_xlsx(path, 'NFC卡片导出')

arrTxt = [['' for col in range(int(len(arr1)/len(read_x1_list)) + 2)] for row in range(len(read_x1_list))]]

for i in range(len(arrTxt)):
    for j in range(10):
        arrTxt[i][0] = read_x1_list[i][0]
        arrTxt[i][1] = read_x1_list[i][1]
        arrTxt[i][ j + 2] = decode_base64(arr1[j].split(':')[2].replace('"', '').replace('}',''))


ts1 = time.strftime('%Y%m%d%H%M%S')

file2Create1 = "D:\\SEIDTool\\OUT\\" + 'eseid_secret_' + ts1 + '.txt'
fo1 = open(file2Create1, 'a')

for i in range(0, len(arrTxt)):
    txt_Value = ''
    fo1.write(arr1[i])
    for j in range(len(arrTxt[0])):
        txt_Value += arrTxt[i][j] + ','
    txt_Value = txt_Value.strip(',')
    fo1.write(txt_Value + '\n')
fo1.close()
print('NFC Card.txt写入成功！')

book_name_xlsx = 'D:\\SEIDTool\\0814\\NFC\\OUT\\' + 'NFC密钥索引引导模板' + ts1 + '.xlsx'
sheet_name_xlsx = 'Sheet1'

value_list = [['title1', 'title2', 'title3']]
for i in range(len(arrTxt)):
    row_list = []
    row_list.append(arrTxt[i][0])
    row_list.append(arrTxt[i][1])    
    keyIdx_list = []
    for j1 in range(int(len(arr1)/len(arrTxt))):
        keyIdx_list.append('{"num":"%d","id":%s"}' %(j1, arr1[j1 + i*10].split(',')[0].replace('{"id:','').replace('"','')))
    keyIdx_value = '"['
    for k1 in range(len(keyIdx_list)):
        keyIdx_value += keyIdx_list[k1] + ','
    keyIdx_value = keyIdx_value.strip(',') + ']"'
    row_list.append(keyIdx_value)
    value_list.append(row_list)

write_excel_xlsx(book_name_xlsx, sheet_name_xlsx, value_list)
#print(read_excel_xlsx(book_name_xlsx, sheet_name_xlsx))