Python操作EXCEL合集

#移动指定文件至指定位置--按TXT文档内容进行移动
import shutil
import os
TXT=r'E:\CZZ\4. 银行函证（年度）\3. 2023年期末函证资料\6 银行函证\新建文本文档.txt'
Old_Place='E:\\2023年期末函证资料\\6 银行函证\\2023期末银行询证函\\'
New_Place='E:\\2023年期末函证资料\\6 银行函证\\'
if __name__ == '__main__':
    file_object = open(TXT,encoding='utf-8')
    try:
        for line in file_object:
            # print(line)
            shutil.move(Old_Place+line.rstrip('\n')+'.pdf',New_Place)
    finally:
        file_object.close()
print('ok')



#获取工作簿下的工作表名称
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open(r'E:\1 如家报表--久其\1 月报\如家月报2311.xlsx')
worksheet = workbook.sheets 
lists = []
for i in worksheet:
    sheet_name = i.name
    lists.append(sheet_name)
    print(sheet_name)
#print(lists)
workbook.close()
app.quit()



#获取文件夹下的工作簿名称
from pathlib import Path
folder_path = Path(r'E:\3. 2023年期末函证资料\6 银行函证\20240322补发函证')
#file_list = folder_path.glob('*.xls*')  # 获取文件夹下所有工作簿的文件路径
file_list = folder_path.glob('*.pdf*')  # 获取文件夹下所有工作簿的文件路径
lists = []  # 创建一个空的列表，用于存储提取的工作簿名
for i in file_list:  # 遍历已获取的文件路径
    file_name = i.name.replace('.pdf','')  # 提取工作簿的文件名
    lists.append(file_name)  # 将已提取的文件名添加到列表中
    print(file_name.replace('_',' '))
#print(lists)  # 打印出已提取的文件名列表
print(len(lists))#获取该文件夹下的工作簿个数



#工作簿中同名工作表数据复制粘贴--久其决算模板
import xlwings as xw
import xlrd
def number_to_letter(number):
    if 1 <= number <= 26:
        return chr(number + 64)
    if 27 <= number <=52:
        return str('A')+str(chr(number + 38))
    else:
        return None
app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open(r'E:\5 WPS在线文档\BTG-2023年报附注--更新.xlsx')
workbook2 = app.books.open(r'E:\6. 年报\5 WPS在线文档\决算2023--0227.xlsx')
worksheet1 = workbook1.sheets# 获取工作簿中的所有工作表
worksheet2 = workbook2.sheets
lists1 = []
lists2 = []
for i in worksheet1:
    sheet_name1 = i.name
    lists1.append(sheet_name1)
    #print(sheet_name1)
for j in worksheet2:
    sheet_name2 = j.name
    lists2.append(sheet_name2)
    #print(sheet_name2)
print(len(lists1),len(lists2))
for a in lists2:
    sht = workbook2.sheets[a]
    info = sht.used_range
    rows_count=info.last_cell.row#有数据的行数
    cols_count=info.last_cell.column#有数据的列数
    Col = number_to_letter(cols_count)
    #source_JS=workbook2.sheets[a].range('A1:'+str(Col)+str(rows_count)).options(ndim=2).value #不带格式复制粘贴
    #workbook1.sheets[a].range('A2:'+str(Col)+str(rows_count+1)).value = source_JS
    #source_sheet.range.copy(destination_sheet.range)#带格式复制粘贴
    workbook2.sheets[a].range('A2:'+str(Col)+str(rows_count+1)).copy(workbook1.sheets[a].range('A1:'+str(Col)+str(rows_count)))
    print(a,rows_count,cols_count,"ok")
workbook1.save()
workbook2.save()
workbook1.close()
workbook2.close()
app.quit()



#多工作簿批量汇总至单工作簿中
import xlwings as xw
import xlrd
from pathlib import Path
#修改工作簿中工作表名称
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('E:\\6. 年报\\7 JIO查询模版check\\0229')
file_list = folder_path.glob('*.xls*')
for i in file_list:
    workbook = app.books.open(i)
    worksheet = workbook.sheets
    for j in worksheet:
        j.name = i.name.rstrip(".XLS")
        print(j.name)
    workbook.save()
    workbook.close()
app.quit()
print("ok")
#数字转为字母
def number_to_letter(number):
    if 1 <= number <= 26:
        return chr(number + 64)
    if 27 <= number <=52:
        return str('A')+str(chr(number + 38))
    else:
        return None
#目标文件夹
app = xw.App(visible=False, add_book=False)
folder_path = Path(r'E:\CZZ\6. 年报\7 JIO查询模版check\0229')
file_list = folder_path.glob('*.xls*')
#目标工作簿
workbook1 = app.books.open(r'E:\6. 年报\7 JIO查询模版check\2023年决算查询模板-AII.xlsx')
worksheet1 = workbook1.sheets# 获取工作簿中的所有工作表
for j in file_list:
    a = j.name.rstrip(".XLS")#删除字符串中的指定文本
    for i in worksheet1:
        if i.name == a :
            workbook2 = app.books.open(j)
            print(a)
            sht = workbook2.sheets[a]
            info = sht.used_range
            rows_count=info.last_cell.row#有数据的行数
            cols_count=info.last_cell.column#有数据的列数
            Col = number_to_letter(cols_count)
            #source_JS=workbook2.sheets[a].range('A1:'+str(Col)+str(rows_count)).options(ndim=2).value #不带格式复制粘贴
            #workbook1.sheets[a].range('A2:'+str(Col)+str(rows_count+1)).value = source_JS
            #source_sheet.range.copy(destination_sheet.range)#带格式复制粘贴
            workbook2.sheets[a].range('A1:'+str(Col)+str(rows_count)).copy(workbook1.sheets[a].range('A2:'+str(Col)+str(rows_count+1)))
            print(a,rows_count,cols_count,"ok")
            workbook2.save()
            workbook2.close()
workbook1.save()
workbook1.close()
app.quit()
app.kill()
print("完成")



#转换工作簿格式
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('E:\\6. 年报\\8 两版数据check\\')
file_list = folder_path.glob('*.xls')
for i in file_list:
    new_file_path = str(i.with_suffix('.xlsx')) # 将文件路径中的扩展名替换为“.xls”
    # SaveAs()函数不能识别pathlib模块创建的路径对象，所以用str()函数将路径对象转换成字符串
    workbook = app.books.open(i)
    workbook.api.SaveAs(new_file_path, FileFormat=51) # 将打开的工作簿另存为“.xlsx”格式
    #workbook.api.SaveAs(new_file_path, FileFormat=56) # 将打开的工作簿另存为“.xls”格式
    workbook.close()
app.quit()
print("OK")



#两个Excel表格数据比对
import warnings
warnings.filterwarnings('ignore')#忽视警告级别的报错
from openpyxl.styles import PatternFill 
from openpyxl.styles import colors 
from openpyxl.styles import Font
import openpyxl as pxl
# 下面使用openpyxl模块的load_workbook函数读取到Excel文件对象，并提取两个Excel文件中'Sheet1'工作表作为源数据。
workbook_1 = pxl.load_workbook(r'E:\6. 年报\8 两版数据check\测试1.xlsx')
workbook_2 = pxl.load_workbook(r'E:\6. 年报\8 两版数据check\测试2.xlsx')
# 提取一个工作簿中所有工作表的名称
import pandas as pd
file_path = r'E:\CZZ\6. 年报\8 两版数据check\测试1.xlsx'
data = pd.read_excel(file_path, sheet_name=None) # 字典的键为工作表的名称，字典的值为对应的工作表数据
worksheet_name = list(data.keys())
for a in worksheet_name:
    workbook_1_sheet_1 = workbook_1[a]
    workbook_2_sheet_1 = workbook_2[a]
    if workbook_1_sheet_1.max_row > workbook_2_sheet_1.max_row:   # 提取两个工作表中的最大行和最大列，这样即使两个表的行数和列数不一致也能完全找出不同的单元格数据。
        max_row = workbook_1_sheet_1.max_row
    else:
        max_row = workbook_2_sheet_1.max_row
    if workbook_1_sheet_1.max_column > workbook_2_sheet_1.max_column:  # 确定最大列
        max_column = workbook_1_sheet_1.max_column
    else:
        max_column = workbook_2_sheet_1.max_column
    for i in range(1, (max_row + 1)): # 使用for循环的方式分别遍历行数据和列数据，然后判断对应单元格的数据值是否相等，若是不相等则打上标记。
        for j in range(1, (max_column + 1)):
            cell_1 = workbook_1_sheet_1.cell(i, j)
            cell_2 = workbook_2_sheet_1.cell(i, j)
            if cell_1.value != cell_2.value:
                print(a,i,j)
                cell_1.fill = PatternFill("solid", fgColor='FFFF00')
                cell_1.font = Font(color=colors.BLACK, bold=True)
                cell_2.fill = PatternFill("solid", fgColor='FFFF00')
                cell_2.font = Font(color=colors.BLACK, bold=True)
workbook_1.save(r'E:\6. 年报\8 两版数据check\测试1-data3.xlsx')#保存为新工作簿
workbook_2.save(r'E:\6. 年报\8 两版数据check\测试2-data4.xlsx')
print("OK")



#合并工作表并转置
import xlrd
import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook
import pandas.io.formats.excel
pd.io.formats.excel.ExcelFormatter.header_style = None #不要格式
import warnings
warnings.filterwarnings('ignore') #忽略警告级别的提示
from pathlib import Path
folder_path = Path(r'D:\TB2309')
file_list = folder_path.glob('*.xls*')  # 获取文件夹下所有工作簿的文件路径
for j in file_list:  # 遍历已获取的文件路径
    excel_name = j #选取工作簿
    wb = xlrd.open_workbook(excel_name) #打开工作簿
    sheets = wb.sheet_names() #获取工作簿下的所有工作表名称
    alldata = DataFrame()#空表
    for i in range(len(sheets)):
        df = pd.read_excel(excel_name, sheet_name=i)
        alldata = pd.concat([alldata,df],axis=1) #合并各工作表，concat()括号内哪个在前合并时哪个在前
    alldata=alldata.T #数据转置
    #将结果写入源文件，并不覆盖源文件内容
    writer = pd.ExcelWriter(excel_name,engine='openpyxl',mode='a')#ExcelWriter的参数mode='a'，模式改为新增，非写入（'w'）
    book = load_workbook(excel_name)
    alldata.to_excel(excel_writer=writer,sheet_name="转置",header=False)
    writer.close()
    book = load_workbook(excel_name)
    worksheet = book['转置']
    worksheet.freeze_panes ='E4'
    book.move_sheet("转置",-len(sheets))
    book.save(excel_name)
    book.close()
print("完成")




#提取PDF表格数据
import pdfplumber
from openpyxl import Workbook
class PDF(object):
    def __init__(self, file_path):
        self.pdf_path = file_path
        # 读取pdf文件
        try:
            self.pdf_info = pdfplumber.open(self.pdf_path)
            print('读取文件完成！')
        except Exception as e:
            print('读取文件失败：', e)
    # 打印pdf的基本信息、返回字典，作者、创建时间、修改时间/总页数
    def get_pdf(self):
        pdf_info = self.pdf_info.metadata
        pdf_page = len(self.pdf_info.pages)
        print('pdf共%s页' % pdf_page)
        print("pdf文件基本信息：\n", pdf_info)
        self.close_pdf()
    # 提取表格数据,并保存到excel中
    def get_table(self):
        wb = Workbook()  # 实例化一个工作簿对象
        ws = wb.active  # 获取第一个sheet
        con = 0
        try:
            # 获取每一页的表格中的文字，返回table、row、cell格式：[[[row1],[row2]]]
            for page in self.pdf_info.pages:
                for table in page.extract_tables():
                    for row in table:
                        # 对每个单元格的字符进行简单清洗处理
                        row_list = [cell.replace('\n', ' ') if cell else '' for cell in row]
                        ws.append(row_list)  # 写入数据
                con += 1
                print('---------------分割线,第%s页---------------' % con)
        except Exception as e:
            print('报错：', e)
        finally:
            wb.save('\\'.join(self.pdf_path.split('\\')[:-1]) + '\pdf_excel.xlsx')
            print('写入完成！')
            self.close_pdf()
    # 关闭文件
    def close_pdf(self):
        self.pdf_info.close()
if __name__ == "__main__":
    file_path = input('请输入pdf文件路径：')
    pdf_info = PDF(file_path)
    # pdf_info.get_pdf() # 打印pdf基础信息
    pdf_info.get_table() # 提取pdf表格数据并保存到excel中,文件保存到跟pdf同一文件路径下



#提取指定页码的指定表格数据
import pdfplumber
import xlwings as xw
file = pdfplumber.open(r"E:\年报\10 商誉核对\如家商誉摘要扫描.pdf")
#pages[0]为第一页
tb = file.pages[2].extract_tables()
#通过Excel显示数据，tb[0]为该页第一个表格
xw.view(tb[0],sheet=None,table=True)