python操作Excel

一、读取Excel中的数据：

import xlrd

# excle文件对象
book = xlrd.open_workbook('income.xlsx')
# print(f'包含表单数量：{book.nsheets}')
# print(f'表单的名称分别为：{book.sheet_names()}')

# 表单对象
# 表单索引从0开始，获取第一个表单对象
s1 = book.sheet_by_index(0)
# 获取名为2018的表单对象
# book.sheet_by_name('2018')
# 获取所有的表单对象，返回一个列表
# book.sheets()

# print(f"表单名：{s1.name} ")
# print(f"表单索引：{s1.number}")
# print(f"表单行数：{s1.nrows}")
# print(f"表单列数：{s1.ncols}")

# row 行数（行数从0开始）  colx 列数（列数从0开始）
# print(f"单元格A1内容是: {s1.cell_value(rowx=0, colx=0)}")

# row_values获取指定行所有的数据，返回列表
# print(f'第2行的内容是：{s1.row_values(rowx=1)}')

# col_values获取指定列所有的数据，返回列表
# print(f'第2列的内容是(从第二个数据开始)：{s1.col_values(colx=1, start_rowx=1)}')

# 求第一个表单所有月份的收入总和
incomes = s1.col_values(colx=1, start_rowx=1)
# print(f'2018年收入总和为：{sum(incomes)}')

# 去掉包含星号的月份总和
to_sub_incomes = 0
# 先找到第一列月份数据，返回月份的列表
monthes = s1.col_values(colx=0)
# enumerate函数会将monthes转换成（索引，元素）组成的迭代器对象
for row,month in enumerate(monthes):
    # print(row,month)
    if type(month) is str and month.endswith('*'):
        income = s1.cell_value(row, 1)
        # print(month, income)
        to_sub_incomes += income
print(f'2018年的真实收入为：{int(sum(incomes)-to_sub_incomes)}')

　　利用for循环求所有年份的总真实收入：

import xlrd

book = xlrd.open_workbook("income.xlsx")

# 得到所有sheet对象
sheets = book.sheets()

income_all_years = 0
for sheet in sheets:
    # 收入在第2列
    incomes = sheet.col_values(colx=1,start_rowx=1)
    # 去掉包含星号的月份收入
    to_sub_incomes = 0
    # 月份在第1列
    monthes = sheet.col_values(colx=0)

    for row,month in enumerate(monthes):
        if type(month) is str and month.endswith('*'):
            income = sheet.cell_value(row,1)
            print(month,income)
            to_sub_incomes += income

    actualIncome = int(sum(incomes) - to_sub_incomes)
    print(f"{sheet.name}年真实收入为: {actualIncome}")
    income_all_years += actualIncome

print(f'全部收入为{income_all_years}')

二、新建excel文件，写入数据：

import openpyxl

# 创建一个Excel workbook对象
book = openpyxl.Workbook()

# 创建时，会自动产生一个sheet，通过active获取
sh = book.active

# 修改当前 sheet 标题为工资表
sh.title = '工资表'

# 保存文件
# book.save('信息.xlsx')

# 增加一个名为 '年龄表' 的sheet,放在最后
sh1 = book.create_sheet('年龄表—最后')

# 增加一个sheet,放在最前
sh2 = book.create_sheet('年龄表—最前', 0)

# 增加一个 sheet，指定为第二个表单
sh3 = book.create_sheet('年龄表2', 1)

# 根据名称获取某个sheet对象
sh_num = book['工资表']

# 给第一个单元格写入内容
sh_num['A1'] = '张三'

# 获取某个单元格内容
print(sh_num["A1"].value)

# 根据行号列号，给第一个单元格写入内容
# 注意和xlrd不同，是从1开始
# sh_num.cell(2,2, value='12345')\
sh_num.cell(2, 2).value = '12345'

# 根据行号列号， 获取某个单元格内容
print(sh_num.cell(2, 2).value)

# 最后一定要保存文件
book.save('信息.xlsx')

　　遍历字典写入数据：

import openpyxl

name_ages = {
    '张飞':38,
    '赵云':27,
    '许诸':36,
    '典韦':38,
    '关羽':39,
    '黄忠':49,
    '徐晃':43,
    '马超':23
}

# 创建一个Excel workbook对象
book = openpyxl.Workbook()

# 创建时，会自动长身一个sheeet,通过active获取
sh = book.active

sh.title = '年龄表'

# 写标题栏
sh['A1'] = '姓名'
sh['B1'] = '年龄'

i = 2
for name_age in name_ages:
    sh.cell(i, 1).value = name_age
    sh.cell(i ,2).value = name_ages[name_age]
    i += 1

# 保存文件
book.save('信息表.xlsx')

三、修改数据：

import openpyxl

# 加载excel文件
wb = openpyxl.load_workbook('income.xlsx')

# 得到sheet对象
sheet = wb['2017']

sheet['A1'] = '修改一下'

# 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

# 在第2行的位置插入1行
sheet.insert_rows(2)

# 在第3行的位置插入3行
sheet.insert_rows(3, 3)

# 在第2列的位置插入1列
sheet.insert_cols(2)

# 在第2列的位置插入3列
sheet.insert_cols(2, 3)

# 指定不同的文件名，可以另存为别的文件
wb.save('income-1.xlsx')

# 在第2行的位置删除1行
sheet.delete_rows(2)

# 在第2行的位置删除3行
sheet.delete_rows(2, 3)

# 在第2列的位置删除1列
sheet.delete_cols(2)

# 在第2列的位置删除3列
sheet.delete_cols(2,3)

　　修改表格文字颜色、字体和大小：

import openpyxl
from openpyxl.styles import Font, colors

# 加载文件得到sheet对象
wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 指定单元格字体颜色，
sheet['A1'].font = Font(color=colors.RED,  # 使用预置的颜色常量
                        size=15,    # 设定文字大小
                        bold=True,  # 设定为粗体
                        italic=True  # 设定为斜体
                        )

# 也可以使用RGB数字表示的颜色
sheet['B1'].font = Font(color='981818')

# 指定整行 字体风格
font_rows = Font(color='BD6C6C')
for y in range(1, 100):  # 从第1列到100列
    sheet.cell(3, y).font = font_rows

# 指定整列 字体风格
font_cols = Font(bold=True)
for y in range(1, 100):  # 从第1行到100行
    sheet.cell(y, 2).font = font_cols

# 保存文件
wb.save('income-2.xlsx')

　　修改背景颜色：

import openpyxl
# 导入Font对象 和 colors 颜色常量
from openpyxl.styles import PatternFill

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 指定 某个单元格背景色
sheet['A1'].fill = PatternFill("solid", "E39191")

# 指定 整行 背景色， 这里指定的是第2行
fill = PatternFill("solid", "E39191")
for y in range(1, 100): # 第 1 到 100 列
    sheet.cell(2, y).fill = fill

# 指定 整列 背景色，略

wb.save('income-3.xlsx')

　　插入图片：

import openpyxl
from openpyxl.drawing.image import Image

wb = openpyxl.load_workbook('income.xlsx')
sheet = wb['2018']

# 在第1行，第4列 的位置插入图片
sheet.add_image(Image('1.png'), 'D1')

# 指定不同的文件名，可以另存为别的文件
wb.save('income-4.xlsx')

# 如果运行提示：ImportError: You must install Pillow to fetch image objects
# 安装Pillow库即可