python操作excel

python操作excel，这里记录一下

前言

我使用的是openpyxl，安装命令：pip3 install openpyxl

读excel

按行读取（读取所有数据）

按行读取，一行为一组（tuple），然后放在一个list中

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
minrow=ws.min_row #最小行
maxrow=ws.max_row #最大行
mincol=ws.min_column #最小列
maxcol=ws.max_column #最大列
datas = []
for i in range(minrow,maxrow+1):
    cells = []
    for j in range(mincol,maxcol+1):
        cell=ws.cell(row=i,column=j).value
        cells.append(cell)
    datas.append(cells)
print(datas)

按列读取（读取所有数据）

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
minrow=ws.min_row #最小行
maxrow=ws.max_row #最大行
mincol=ws.min_column #最小列
maxcol=ws.max_column #最大列
datas = []
for i in range(mincol,maxcol+1):
    cells = []
    for j in range(minrow,maxrow+1):
        cell=ws.cell(row=j,column=i).value
        cells.append(cell)
    datas.append(cells)
print(datas)

读取某一行的所有数据

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
mincol=ws.min_column #最小列
maxcol=ws.max_column #最大列
datas = []
row = 2
for i in range(mincol,maxcol+1):
    cell = ws.cell(row=row, column=i).value
    datas.append(cell)
print(datas)

读取某一列的所有数据

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
minrow=ws.min_row #最小行
maxrow=ws.max_row #最大行
datas = []
column = 1
for i in range(minrow,maxrow+1):
    cell = ws.cell(row=i, column=column).value
    datas.append(cell)
print(datas)

读取某个坐标的数据

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
datas = []
cell = ws.cell(row=1, column=1).value
print(cell)

写excel

批量按行写入

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active

datas = [
    ["账号","密码"],
    [1,2],
    [3,4]
]
for row in datas:
    ws.append(row)
wb.save(excelPath)

单独写入某一行

在第3行，第3列中插入值为666

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

excelPath = "1.xlsx"
wb = load_workbook(excelPath)
ws = wb.active
value = "6666"
ws.cell(row=3,column=3,value=value)
wb.save(excelPath)