python对于excel的操作

python可以通过开源框架openpyxl来对excel进行读写操作。

基本概念：

对于excel，其中的主要用到的元素如下：

WorkBook：工作簿

WorkSheet：sheet表

Cell：单元格

style：样式

Border:框线

Font：字体

Alignment：对齐
...
hello world的demo：

from openpyxl import Workbook

#一开始新建 是存储在内存中
wb = Workbook()
# 获取表
sheet = wb.active
# 写入相关的单元格数据
sheet['A1'] = 'hello'
sheet['B1'] = 'world'

# 文件落盘
wb.save("hello.xlsx")

设置相关样式的方法：

from openpyxl import Workbook

#  一开始整个excel都是只存在于内存之中
from openpyxl.cell import Cell

from openpyxl.styles import Font, Alignment, Side, Border

# 创建文字的样式
font_ = Font(
    size=14,
    italic=True,
    color='ff0000',
    bold=False,
    strike=None
)

centerAlign = Alignment(horizontal='center', vertical='center')
# 设置线条
side = Side(style='thin', color='ff0000')
# 框线模型信息
allBorder = Border(left=side, top=side, right=side, bottom=side)  # 设置四边线条形状与颜色

workbook = Workbook()
sheet = workbook.active

# 设置字体
sheet['A1'] = "九九乘法表"
sheet['A1'].font = font_
sheet['A1'].alignment = centerAlign
sheet['A1'].border = allBorder

# 合并单元格
sheet.merge_cells("A1:I1")

# 打印乘法表
column_value = 65
for i in range(1, 10):
    column_name = chr(column_value)
    column_value += 1

    for j in range(i, 10):
        sheet["%s%d" % (column_name, j + 1)] = "%d * %d = %d" % (i, j, i * j)

#  最后需要进行数据的落盘操作   写出到磁盘中
workbook.save(filename="hello_world.xlsx")

 一些样式的设置代码

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

# 字体
font = Font(name='Calibri',
            size=11,
            bold=False,
            italic=False,
            vertAlign=None,
            underline='none',
            strike=False,
            color='FF000000')
# 背景填充色
fill = PatternFill(fill_type=None,
                   start_color='FFFFFFFF',
                   end_color='FF000000')

# 框线的线的定义
side = Side(border_style=None,
     color='FF000000')

# 框线
border = Border(left=Side(border_style=None,
                          color='FF000000'),
                right=Side(border_style=None,
                           color='FF000000'),
                top=Side(border_style=None,
                         color='FF000000'),
                bottom=Side(border_style=None,
                            color='FF000000'),
                diagonal=Side(border_style=None,
                              color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,
                             color='FF000000'),
                vertical=Side(border_style=None,
                              color='FF000000'),
                horizontal=Side(border_style=None,
                                color='FF000000')
                )
# 对齐方式
alignment = Alignment(horizontal='general',
                      vertical='bottom',
                      text_rotation=0,
                      wrap_text=False,
                      shrink_to_fit=False,
                      indent=0)
number_format = 'General'

# 是否允许编辑
protection = Protection(locked=True,
                        hidden=False)

 

excel中绘制一个图

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
treeData = [["Type", "Leaf Color", "Height"], ["Maple", "Red", 549], ["Oak", "Green", 783], ["Pine", "Green", 1204]]

for row in treeData:
    ws.append(row)

# 条形图
chart = BarChart()
# 条形展示的方向  bar 是左右方向的堆砌的  col 是下上堆砌的
chart.type = "col"
# 图的名字
chart.title = "Tree Height"
# y轴显示
chart.y_axis.title = 'Height (cm)'
# x轴的显示
chart.x_axis.title = 'Tree Type'
chart.legend = None
# 数据取值 （关联excel的相关的列）
data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3)
# 种类的取值 （关联excel的相关的列）
categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1)
chart.add_data(data)
chart.set_categories(categories)
# 最后需要将图绘制到具体的sheet的具体的那个单元格中
ws.add_chart(chart, "E1")

wb.save("h.xlsx")

一个读取excel的栗子

from openpyxl import open
# 打开读取excel
ws = open("h.xlsx")
sheet = ws.active
# 缓存所有的excel的行列信息
rows = []
# 赋值
for row in sheet.iter_rows():
    rows.append(row)
# 读取并打印
for i in range(len(rows)):
    for j in range(len(rows[i])):
        print(rows[i][j].value, end="\t")
    print()
# 关闭文件
ws.close()

其他的高级用法 包括抽象一个更加简单的框架 可以进行相关的定制开发!