Python实现Excel办公自动化_用python处理表格

为什么使用Python处理Excel

Excel已经足够流行和好用，为什么要用Python处理Excel？

• Python处理Excel的好处

1，批量操作：当要处理众多Excel文件时，例如出现重复性的手工劳动，那么使用Python就可以实现批量扫描文件、自动化进行处理，利用代码代替手工重复劳动，实现自动化，是Python第一个比Excel强大的地方

2，大型文件，当Excel文件超过几十兆、甚至上百兆时，打开文件很慢、处理文件更加慢，这时候若使用Python，会发现处理几十兆、几百兆甚至几GB都是没有问题的

3，当使用Excel进行复杂的计算时，会使用VBA，但是VBA本身是过时并且复杂的语言，Python是当前最简单且容易实现的一门语言，用Python能够处理比VBA难度更高的业务逻辑

4，Python是通用语言，不仅可以处理Excel，使用Python就可以得到很多额外的功能，例如：爬虫、发布网页的Web服务、与数据库进行连接、同时结合word和PPT进行处理、加入定时任务处理、人工智能分析等，各种额外的功能，这是Excel和VBA所不具备的

Python处理Excel的类库和开发环境

• Python处理Excel主要有三大类库

1，pandas：是Python领域非常重要的，用于数据分析和可视化的类库，在处理Excel中，90%可以利用pandas类库就可以搞掂，利用pandas就可以读取Excel、处理Excel和输出Excel，但是pandas也有缺点，就是无法做到格式类，例如Excel中合并单元、大量复杂的样式（看起来很精美）的时候，用pandas无法搞掂，此时，依然是使用pandas结合openyxl、xlwings来搞掂需求

2，openpyxl：若电脑上未安装office时，也可以使用openpyxl，这个类型可以运行在linux上，并且也可以实现操作大部分Excel格式和样式的功能，使用它配合pandas，也可以完成大部分场景的需求

3，xlwings：比openyxl更加强大，只能运行在Windows或者Mac系统，并且该系统中必须安装了office才能运行，xlwings的原理，就是基于当前系统已经安装好的office软件，来进行功能的拓展来操作Excel

• 使用pandas的时候，经常会结合其他类库，来完成更加复杂的功能

• requests, bs4：可以完成爬虫的功能

• flask：可以做网页，把表格展示在网页上

• Matplotlib：读取表格后，进行可视化

• sklearn：进行复杂的数据分析时，也可以结合机器学习Sklearn把读取的Excel数据，进行数据分析和机器学习

• Python-docx：也可以结合Python-docx类库，实现Excel和word的互通

• smtplib：也可以使用smtplib，讲Excel数据发送邮件出

• 开发环境

操作系统：使用windows, mac都可以

Python版本：系统中需要安装Python3.6以上的版本，Python2已经过期不建议使用，Python3.6以前的版本功能相对弱，最好就是采用Python3.6以上的版本

开发工具：有两个可以选择，jupyter notebook，是个网页编辑器，可以运行Python，常常用于交互性、探索性的开发；pycharm，用于成熟脚本，或者web服务的一些开发；这两个工具可以随意选择

重要类库：xlwings, pandas, matplotlib等

 关于xlwings

• xlwings的使用，必须先导入

import xlwings

 在程序中使用xlwings，必须首先创建一个app，在这个app下才能继续创建一个或多个work_book，在work_book下才能创建一个或多个work_sheet，这是xlwings的基本操作逻辑

• 创建app

相对于openpyxl，xlwings多了一步即需要创建一个app

import xlwingsxlwings_app = xlwings.App(visible=False, add_book=False)

• visible，表示xlwings在操作Excel文件时，用户是否在屏幕上可以看见，此处设置False是为了加快程序运行速度

• add_book，表示在创建Excel文件时，是否创建第二个Excel文件，建议设置为False，加快程序运行速度

• 创建工作本

import xlwingsxlwings_app = xlwings.App(visible=False, add_book=False)
Excel_file_name_xlsx = 'xlwings_范例_xlsx.xlsx'Excel_file_name_xls = 'xlwings_范例_xlsx.xls'

创建Excel范例文件的文件名，此处创建了两个文件名，一个是“.xls”文件，一个是“.xlsx”文件

• 创建Excel文件，使用add()函数

work_book_xlwings = xlwings_app.books.add()
work_book_xlwings.save(Excel_file_name_xlsx)    #  创建并保存为.xlsx文件
work_book_xlwings.save(Excel_file_name_xls)      #  创建并保存为.xls文件

• Excel文件关闭，退出app

work_book_xls.close()          #  Excel文件使用完毕要退出，释放内存work_book_xlsx.close()          #  Excel文件使用完毕要退出，释放内存xlwings_app.quit()              #  app使用完毕要退出， 释放内存

• 打开工作簿

import xlwingsxlwings_app = xlwings.APP(visible=False, add_book=False)
Excel_file_name_xlsx = 'xlwings_范例_xlsx.xlsx'Excel_file_name_xls = 'xlwings_范例_xlsx.xls'

• 用 .book.open() 函数来打开Excel文件

work_book_xlsx = xlwings_app.books.open(Excel_file_name_xlsx)  #此处打开了一个.xlsx文件work_book_xls = xlwings_app.books.open(Excel_file_name_xls)    #此处打开了一个.xls文件

• 在工作簿中创建、添加表

• 方法1，在默认位置添加一张表，默认位置，即在第一张表的前面新增添加一张表：

• et_name = '新表' work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name)work_sheet_xls = work_book_xls.sheets.add(sheet_name)

• 方法2，在指定位置添加一张表：

sheet_name_1 = 'before_sheet1'sheet_name_2 = 'after_sheet1'
work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name_1, before='Sheet1')      #  在 .xlsx文件的 Sheet1表 的 前面 增加一张表work_sheet_xlsx = work_book_xlsx.sheets.add(sheet_name_2, after='Sheet1')      #  在 .xlsx文件的 Sheet1表 的 后面 增加一张表
work_sheet_xls = work_book_xlsx.sheets.add(sheet_name_1, before='Sheet1')      #  在 .xls文件的 Sheet1表 的 前面 增加一张表work_sheet_xls = work_book_xlsx.sheets.add(sheet_name_2, after='Sheet1')      #  在 .xls文件的 Sheet1表 的 后面 增加一张表

• 打开工作簿中的工作表

• 方法1，根据工作表的名称，打开工作表：

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')

• 方法2，根据工作表的索引值，打开工作表（注意！）

work_sheet_xlsx = work_book_xlsx.sheets(1)work_sheet_xls = work_book_xls.sheets(0)

xlwings对.xlsx文件中的工作表索引值是从“1”开始的，而对.xls文件中的工作表索引值是从“0”开始的！！！

• 获取工作簿中所有工作表的名称

xlwings对这个功能的支持不是特别好，或者说不是特别方便

sheets_names_from_xlsx = work_book_xlsx.sheets    # 注意，这里的.sheets 不带()sheets_names_from_xls = work_book_xls.sheets  # 注意，这里的.sheets 不带()
new_sheets_names_from_xlsx_list = []sheets_names_from_xlsx_list = list(sheets_names_from_xlsx)
for sheet_name in sheets_names_from_xlsx_list:  sheet_name_str = str(sheet_name)  new_name_list = sheet_name_str.split(']')  sheet_name_str = new_name_list[-1][:-1]    # 利用字符串的切片，将工作簿的名字剪切出来  new_sheets_names_from_xlsx_list.append(sheet_name_str)

• sheets_names_from_xlsx或sheets_names_from_xls的返回值是一个xlwings.sheets类，需要通过代码才能转换为我们需要的类型，比如将所有工作簿的名字转换为一个字符串列表

• new_sheets_names_from_xlsx_list列表中，存储的就是这个工作簿内所有工作表的名称了

• 获取当前工作表的名称

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')
work_sheet_name_from_xlsx = work_sheet_xlsx.nameprint(work_sheet_name_from_xlsx)
work_sheet_name_from_xls = work_sheet_xls.nameprint(work_sheet_name_from_xls)
清空工作表内所有内容

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xlsx.clear_contents()  # 删除表内的所有内容数据，但保留表格的格式，例如单元格颜色、已合并的单元格
work_sheet_xlsx.clear()  # 删除表内的所有内容数据，包括单元格颜色、已合并的单元格将被差分开，但保留了单元格的行高和列宽

• 删除工作表

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xlsx.delete()  # 工作表将被彻底删除掉

• 读取单元格的内容

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')

• 读取一个单元格内的数值

cell_value_from_xlsx = work_sheet_xlsx.range(2, 3).value  #  读取work_sheet_xlsx表中，第2行第3列 的单元格的值
cell_value_from_xls = work_sheet_xls.range(2, 3).value    #  读取work_sheet_xls表中，第2行第3列 的单元格的值
# 读取连续单元格内的数值cells_value_from_xlsx = work_sheet_xlsx.range((2, 2), (3, 3)).value    #  读取work_sheet_xlsx表中，第2行第2列至第3行第3列 的单元格的值

• 写入单元格

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')

• 在一个单元格中写入数据

work_sheet_xlsx.range(2, 3).value= 'abcd'    #  在 第2行第3列 中写入‘abcd’
# 在多个单元格中写入数据test_value = [[1, 2, 3], ['a', 'b', 'c'], ('AA', 'BB', 'CC')]work_sheet_xlsx.range('B5').value = test_value              #  从‘B5’单元格开始横着写入数据，‘B5’单元格是1，‘C5’单元格是2，# ‘D5’单元格是3，‘B6’单元格是a，以此类推
work_sheet_xlsx.range(10, 2).value = test_value              #  从 第10行第2列 开始横着写入数据，单元格（10,2）是1# 单元格（10,3）是2，单元格（10,4）是3，单元格（11,2）是a

• 获取最大的行数和列数

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')
cell = work_sheet_xlsx.used_range.last_cellrows = cell.row  #  返回该单元格的 行号columns = cell.column  #  返回该单元格的 列号# cell可以是表格中非连续行数据的单元格，也就说，# 行与行之间可以存在空白行，列与列之间也可以存在空白列

• 插入公式

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')work_sheet_xls = work_book_xls.sheets('Sheet1')
work_sheet_xlsx.range(19, 2).formula = '=B3'  #  在 第19行第2列 的单元格中写入公式‘=B3’
work_sheet_xlsx.range(19, 2).value = '=B3'  #  在 第19行第2列 的单元格中写入公式‘=B3’

• 版本转换，xls格式转为xlsx格式

xls_to_xlsx_file_name = 'xls_to_xlsx.xls'new_name = xls_to_xlsx_file_name + 'x'work_book_xls_to_xlsx = xlwings_app.books.open(xls_to_xlsx_file_name)    #  打开 .xls 文件
work_book_xls_to_xlsx.save(new_name)  #  直接在原.xls文件路径下，另存为一个新的.xlsx文件
#  版本转换，xlsx格式转为xls格式xlsx_to_xls_file_name = 'xlsx_to_xls.xls'new_name = xlsx_to_xls_file_name[:-1]

• 设置单元格大小

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
work_sheet_xlsx.autofit()            #  设置整张表为 自动调整单元格大小
work_sheet_xlsx.range(2, 2).row_height = 25       # 设置第2行的行高为25
work_sheet_xlsx.range(15, 6).column_width = 40       # 设置第6列的列宽为40

• 设置单元格 字体格式

work_sheet_xlsx = work_book_xlsx.sheets('Sheet1')
test_cell = work_sheet_xlsx.range(2, 3)
test_cell.color = 255, 200, 255                 #   设置单元格的填充色为紫红色
test_cell = work_sheet_xlsx.range(12, 2)
test_cell.api.Font.ColorIndex = 3              #   设置字体的颜色

字体颜色索引见下方

test_cell.api.Font.Size = 15

#   设置字体的大小为15号
test_cell.api.Font.Bold = True                  #   设置为粗体
test_cell.api.HorizontalAlignment = -4152    #  -4108 水平居中，-4131 靠左，-4152 靠右
test_cell.api.VerticalAlignment = -4108      #  -4108 水平居中（默认），-4160 靠上，-4107 靠下， -4130 自动换行对齐
test_cell.api.NumberFormat = "@"        #  @ 为文本格式, 0.00 为小数格式，yyyy-mm-dd 为"-"连接的日期格式，0.00% 为百分数

     知道你对python感兴趣，所以给你准备了下面的资料~

 这份完整版的Python全套学习资料已经上传，朋友们如果需要可以点击链接免费领取或者滑到最后扫描二v码保证100%免费】

资源免费分享，保证100%免费！！！

需要的话可以点击这里