这个代码是需要自己先建立一个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)