第三节、Python操作Excel

目录

前言

一、使用openpyxl库操作excel

二、使用xlrd、xlwt读写Excel

---

前言

Excel无论是在生活中还是在机器学习中，许多数据都是存储在Excel中，借助Excel的统计分析能力，能让数据清洗和平常繁琐的工作自动化处理。Excel的结构如下：

一、使用openpyxl库操作excel

1、Excel的读取：

import openpyxl as xl
excel01 = xl.load_workbook("Excel文件路径") # 加载已经存在的Excel文件
excel02 = xl.Workbook()                    # 创建新的Excel

2、获取Excel的sheet页：

sheet01 = excel01.worksheets[0]   # 通过索引获取sheet页
sheet01 = excel01['sheet01']      # 通过sheet页名称获取

3、创建sheet页方法：

 #创建Sheet页，index是sheet页的索引，title是sheet页的名称
excel02.create_sheet(index=0,title='sheet01') 
excel02.create_sheet(index=1,title='sheet02')

4、获取单元格数据：

sheet01['A1'].value  # 根据sheet页中单元格的索引进行获取


# 便利sheet页中所有数据
import openpyxl
excel = openpyxl.load_workbook("文件路径")
sheet = excel.worksheet[0]
for rows in sheet.iter_rows():
    for cell in rows:
        print(cell.coordinate, cell.value)

 5、修改单元格数据：

# 单元格赋值三种方式
sheet01['A1']= "test"
sheet01.cell(1,1).value="test"
sheet01.call(1,1,"test")

6、保存Excel

excel.save("save.xlsx")

7、Excel设置字体样式

from openpyxl.styles import Font, PartternFill, Border, Side, Alignment
# 设置字体
font = Font(name='微软雅黑', size=10, bold=False, italic=False, vertAlign=None,
underline='none', strike=False, color="FF0000")
# 设置单元格样式
sheet['A1'].font = font
sheet.cell(1,1).font = font
 

8、Excel设置填充样式

# 设置单元格样式
fill = PatternFill(fill_type='darkUp', start_color='FFFF00', end_color='FF0000')
sheet['A1'].fill = fill
sheet.cell(1,1).fill = fill

9、边框样式：

# 设置单元格边框样式和颜色
border = Border(left=Side(border_style='dashDotDot', color='9932cc'),
                right=Side(border_style='dashDotDot',color='121212'),
                top=Side(border_style='dashDotDot',color='FFEEFF'),
                bottom=Silde(border_style='dashDotDot',color='AAABBB'))
sheet['B2'].border = border
sheet.cell(2,2).border = border

10、文字对齐样式：

# 设置单元格文字对齐方式
alignment = Alignment(horiznontal='center',
                      vertical='center',
                      text_rotation=0,
                      indent=0)
sheet.cell(3,3).alignment = alignment

11、链接公式

# 设置，点击"百度一下"跳转到百度首页
sheet.cell(6,6).value='HYPERLINK("%S", "%S")' % ("www.baidu.com","百度一下")
# sheet链接跳转到指定目标
sheet.cell(9,9).value='HYPERLINK("%S", "%S")' % ("#A1","跳转到A1")

12、计算公式

sheet['A1']='=SUM(B1:E1)'   # 求和计算B1到E1的值，输出到A1

13、单元格合并与拆分

sheet.merge_cells('A1:E1')    # 合并A1到E1单元格
sheet.unmerge_cells('A1:E1')  # 拆分A1到E1单元格

二、使用xlrd、xlwt读写Excel

  xlrd2.01版本以后，不支持xlsx的读写，建议是用1.2.0版本或者使用openpyxl

1、打开Excel:

import xlrd
import xlwt
# 2.0.1 版本不支持xlsx文件加载
read_excel01 = xlrd.open_workbook("file_path")

2、获取excel的sheet页：

# 通过索引获取sheet页
sheet01 = read_excel01.sheet_by_index(0)
# 通过sheet页的Name获取
sheet02 = read_excel01.sheet_by_name("sheet01")

3、获取Excel的行数和列数：

# 获取sheet页的最大行数
rows = sheet01.nrows
# 获取sheet页的最大列数
cols = sheet01.ncols

4、获取某个单元格的值：

# 获取第一行第二列的值。cell(行，列)
value01 = sheet01.cell(1,2).value

5、给打开的Excel添加sheet页

# 打开一个写的Excel
write_book = xlwt.WorkBook()
# 添加一个名为“test”的sheet页
write_sheet = write_book.add_sheet_("test")

6、将数据写入Excel

# 将结果写道第三行第五列，write(行，列，值)
write_sheet.write(3,5,"value01")

7、保存Excel

# 保存Excel
write_book.save("file_name_path")