美文网首页
python操作excel

python操作excel

作者: 很菜呀 | 来源:发表于2023-09-04 14:09 被阅读0次

示例:

#!/usr/bin/python
# -*- coding:utf-8 -*-

import datetime
from  openpyxl import  Workbook
from openpyxl  import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string

# ? 参考链接: https://zhuanlan.zhihu.com/p/344123753

# ? 新建
wb = Workbook()  # 实例化
ws = wb.active  # 激活 worksheet
# 方式一:数据可以直接分配到单元格中(可以输入公式)
ws['A1'] = 42
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
# 方式二:可以附加行,从第一列开始附加(从最下方空白处,最左开始)(可以输入多行)
ws.append(['n1', 11, 12])
ws.append(['n2', 21, 22])
wb.save('demo.xlsx')

# ? 打开已有 xsl
wb = load_workbook('demo.xlsx')
ws = wb.active  # 激活 worksheet

# ? 创建表
ws_n3 = wb.create_sheet("x-sheet3")  # 方式一:插入到最后(default)
ws_n2 = wb.create_sheet("x-sheet2", 1)  # 方式二:插入到指定位置

# ? 选择表
print(wb.sheetnames)
ws0 = wb[wb.sheetnames[0]]
ws3 = wb["x-sheet2"]
ws4 = wb.get_sheet_by_name("x-sheet3")

# ? 单元格访问
ws = ws_n2
ws['A1'] = 'A1'  # 设置值
cell = ws['A1']
print(cell.value)
d22 = ws.cell(row=2, column=2)
d33 = ws.cell(row=3, column=3, value=10)  # 设置空值时使用默认值
for i in range(4):
    ws['A{0}'.format(i + 1)] = i + 1
    ws['B{0}'.format(i + 1)] = i + 1
    ws['C{0}'.format(i + 1)] = i + 1
    ws['D{0}'.format(i + 1)] = i + 1

# ? 取多个单元格
# %% 取出单个列
print('取出单个列')
cell_range = ws['A']  # 取出来 A 列,用 ws[1] 也可以
for cell in cell_range:
    print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx

# %% 取出多列
print('取出多列')
cell_range = ws['A:B']  # 取出来两列
for cells in cell_range:
    for cell in cells:
        print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx

# %% 取出多行多列-方法2
print('取出多行多列')
cell_range = ws['A1':'C2']   # 取出来 [A1, B1, C1], [A2, B2, C2]
for cells in cell_range:
    for cell in cells:
        print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx

# %% 取出多行多列-方法1
print('取出多行多列')
for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in  row:
        print('{0}{1}'.format(cell.column_letter, cell.row), cell.value) # cell.column, cell.col_idx

# ? 取行,列的统计数据,前者为生成器
print(len(list(ws.rows)), len(list(ws.columns)), ws.max_row, ws.max_column)

# ! 删除工作表
ws_n4 = wb.create_sheet("x-sheet4")
ws.remove(ws_n4)

# ! 部分列名称删除了,有空缺
print(get_column_letter(2), column_index_from_string('B'))

# * 其他补充:字体,对齐方式,行高,列宽,合并单元格,拆分单元格
# ! 扩展补充:生成 2D 图表, 3D图表

# 保存 xsl
wb.save('demo.xlsx')


相关文章

网友评论

      本文标题:python操作excel

      本文链接:https://www.haomeiwen.com/subject/tqqcvdtx.html