Openpyxl一些简单的用法

这个代码是需要自己先建立一个excel。然后导入数据

from openpyxl import load_workbook

#按照一个格子输入进去

workbook = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

sheet['A1'] = '你好啊'

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

然后要用data 子集输出的话，要弄一个子集目录。

就是里面要加个数据集

如果是row的话:

from openpyxl import load_workbook

workbook = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

data = [

    ['张三',1],

    ['李四',2],

    ['王五',3],

    ['赵六',4]

]

for row in data:

    sheet.append(row)

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

导出的结果就是一个竖列

然后子集结构变换一下：

from openpyxl import load_workbook

workbook = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

data = [

    ['张三',1,4,5,6,7]

]

for row in data:

    sheet.append(row)

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

就是横排结构。

插入空的一行：

from openpyxl import load_workbook

workbook = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

                        #只是插入空的一列column,row也可以改成row

sheet.insert_rows(idx=2)

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

然后这个sheet.insert_rows(idx=3)

from openpyxl import load_workbook

workbok = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

sheet.insert_cols(idx=2,amount=3) #在第二行插入三列

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

添加多三列：

就amount=3就可以在第二列添加三行。

from openpyxl import load_workbook

workbok = load_workbook(r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

sheet.insert_cols(idx=2,amount=3) #在第二行插入三列

workbook.save(r'C:\Users\yjiang3\Desktop\11.xlsx')

openpyxl 额外的一些小的tips：

获取sheet名字：

from openpyxl import load_workbook

workbook = load_workbook(filename='.xlsx')

print(workbook.sheetnames)

dimension的数据size是多少：

有了名字，就可以看dimension了。

from openpyxl import load_workbook

workbook = load_workbook(filename=r'C:\Users\yjiang3\Desktop\11.xlsx')

print(workbook.sheetnames)

sheet = workbook['张三'] #获取sheet的名字

print(sheet.dimensions) #dimension的意思是数据size有多少

然后开始导出他的具体一个个格子是多少的数值

from openpyxl import load_workbook

workbook = load_workbook(filename=r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

cell1 = sheet['A1']

print(cell1.value)

cell2 = sheet['B2']

print(cell2.value)

行列坐标：

#行列坐标

from openpyxl import load_workbook

workbook = load_workbook(filename=r'C:\Users\yjiang3\Desktop\11.xlsx')

sheet = workbook.active

cell = sheet['A1']

print(cell.row, cell.column, cell.coordinate)

巴特西

Openpyxl一些简单的用法

最新文章

热门文章