python处理Excel数据（记录）

python处理Excel数据（记录） 本代码均参考于网上代码 txt文档替换内容

def updateFile(file,old_str,new_str):
    file_data = ""
    with open(file, "r", encoding="utf-8") as f:
        for line in f:
            if old_str in line:
                line = line.replace(old_str,new_str)
            file_data += line
    with open(file,"w",encoding="utf-8") as f:
        f.write(file_data)
def updateOneLine(file):
    file_data = ""
    with open(file, 'r', encoding="utf-8") as r:
        lines = r.readlines()
    with open(file, "w", encoding="utf-8") as f:
        for l in lines:
            if 'DecaRangeRTLS' not in l:
                f.write(l)
            else:
                f.write("time id distance real_distance serial_number number n")
for i in range(1,325):
    flie_name=str(i)+".异常.txt"
    print(flie_name)
    updateOneLine(flie_name)
    updateFile(flie_name, "T:", "")
    updateFile(flie_name, ":RR:0:", " ")
    updateFile(flie_name, ":", " ")

Excel表格合成（汇总数据使用）

import os # 导入模块
import pandas as pd


def combine_Excel(dir):
    file_name_li = os.listdir(dir)

    # 遍历出每个文件名
    for file_name in file_name_li:
        # 将文件夹绝对路径 与 文件名进行拼接
        file_path_li = os.path.join(dir, file_name)
        print(file_path_li)

    # 定义文件名集合
    # all_file_name = set()
    all_file_name = []
    # 定义数据列表
    all_data_li = []

    # 遍历出每个文件名
    for file_name in file_name_li:
        # 将文件夹绝对路径 与 文件名进行拼接
        file_path_li = os.path.join(dir, file_name)
        # 读取 excel 表格数据
        all_data = pd.read_excel(file_path_li, sheet_name=None)
        # 将数据添加到数据列表中
        all_data_li.append(all_data)
        # 将工作表名添加到文件夹集合中
        for name in all_data:
            all_file_name.append(name)

    # print(all_data_li)
    # all_file_name.sort()
    new_all_file_name = []
    for x in all_file_name:
        if x not in new_all_file_name:
            new_all_file_name.append(x)
    print(new_all_file_name)
    # 创建工作簿
    writer = pd.ExcelWriter("all_data.xlsx")

    # 遍历每个工作表名
    for sheet_name in all_file_name:
        data_li = []
        # 遍历数据
        for data in all_data_li:
            # 获取同名数据并添加到data_li中
            try:
                n_rows = data_li.append(data[sheet_name])
            except:
                print("此表格无此表！")
                continue
        # 将同名数据进行拼接
        group_data = pd.concat(data_li)
        # 保存到writer工作簿中，并指定工作表名为sheet_name
        group_data.to_excel(writer, sheet_name=sheet_name)
    # 千万莫忘记，保存工作簿
    writer.save()

if __name__ == '__main__':
    dir = r'C:UsersdellDesktopTest_EXCelExcel'
    combine_Excel(dir)
    pass

Excel统一时间格式

# coding=utf-8

import xlrd
import codecs
import datetime


def clear_date_xlsx(file,sheet,num):
    # 加载Excel数据，处理数据
    data = xlrd.open_workbook(file)  # 读取工作表
    table = data.sheet_by_name(sheet)  # 读取当前sheet表对象
    rows = table.nrows  # 获取行数
    print('一共有{}行数据，开始清洗数据'.format(rows))

    for i in range(1, rows):
        MyTime = str(table.row_values(i)[num])
        today=" "
        if "44" in MyTime:
            delta=datetime.timedelta(days=float(MyTime))
            today = datetime.datetime.strptime('1899-12-30', '%Y-%m-%d') + delta
            today=datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "/" in MyTime:
            today = datetime.datetime.strptime(MyTime, '%m/%d/%Y')
            today = datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "日"in MyTime:
            today = datetime.datetime.strptime(MyTime, '%Y年%m月%d日')
            today = datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "年" in MyTime:
            today = datetime.datetime.strptime(MyTime, '%Y年%m月')
            today = datetime.datetime.strftime(today, '%Y.%m')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "，" in MyTime:
            today = MyTime.replace("，", ".")
        else:
            today=MyTime

        # MyTime = MyTime.replace("年", ".").replace("月","").replace("，", ".")
        output = ('{}n').format(today)
        f = codecs.open('清洗后的数据.xls', 'a+')
        f.write(output)
        f.close()

if __name__ == '__main__':
    clear_date_xlsx('all_data.xlsx','sheet',6)