批量给文件改后缀名


爬取的文件全部后缀为xlsx，结果有的文件打不开，原因是这些文件是xls格式，因此将xlsx统一改为xls

import os

root = r'D:\bondtest'

with os.scandir(r'D:\bondtest') as files:
    for file in files:
        if file.name.endswith('.xlsx'):
            src = file.name
            dst = file.name.split('.')[0] + ".xls"
            try:
                os.chdir(root)  # os.rename默认查找的路径是该程序所在的文件夹，如果查找的文件与程序不在一个文件夹，需要指定绝对路径
                os.rename(src, dst)  # 否则报错： [WinError 2] 系统找不到指定的文件
            except Exception as e:
                print(e)
                print("rename[%s] failed" % (src))
            else:
                print("rename[%s] success" % (src))
            print("END")

View Code

 

批量将xls转成xlsx

openpyxl不支持xls文件的打开，因此批量将xls文件打开后统一保存成xlsx格式

import os
import os.path
import win32com.client as win32  # pip install pywin32

## 根目录
rootdir = r'D:\bondtest'
# 三个参数：父目录；所有文件夹名（不含路径）；所有文件名
for parent, dirnames, filenames in os.walk(rootdir):
    for fn in filenames:
        filedir = os.path.join(parent, fn)
        print(filedir)

        excel = win32.gencache.EnsureDispatch('Excel.Application')
        wb = excel.Workbooks.Open(filedir)
        # xlsx: FileFormat=51
        # xls:  FileFormat=56,
        # 后缀名的大小写不通配，需按实际修改：xls，或XLS
        wb.SaveAs(filedir.replace('xls', 'xlsx'), FileFormat=51)  # 我这里原文件是大写
        wb.Close()                                 
        excel.Application.Quit()

View Code

 

批量将同一类型表格导入到一张总表中，按时间作分页

type_list = ['0101', '0102', '0201', '0203', '0300', '0301', '0401']
root = 'D:\\bondtest\\'

for type in type_list:
    path = root + "汇总" + type + ".xlsx"
    wbt = Workbook()
    wbt.save(path)
    with os.scandir(root) as files:
        for file in files:
            if file.name[6:10] == type:
                print(file.name)
                wb = load_workbook(file.name)
                wb2 = load_workbook(path)
                ws2 = wb2.create_sheet(file.name[:6])
                sheet1 = wb['page']
                sheet2 = wb2[ws2.title]
                for i,row in enumerate(sheet1.iter_rows()):
                    for j,cell in enumerate(row):
                        sheet2.cell(row=i+1, column=j+1, value=cell.value)
                wb.close()
                wb2.save(path)#保存数据
                wb2.close()

View Code