博客

这次分享三个用openpyxl写入数据的方法

• 
  
  第一个：sheet.append()这个是最省代码的方法，缺点追加数据时只能从A列的最后一行写入。
  
  

from openpyxl import *


wb =load_workbook('I:/学习资料/文本/常用模块/qq33.xlsx')
sheet = wb['qq33']
yu = [
      ['a111','b1','c1','d1'],
      ['a22','b2','c2','d2'],
      ['a3','b3','c3','d3'],
      ['a4','b4','c4','d4'],
      ['a5','b5','c5','d5'],
      ]

for i in yu:
    sheet.append(i)
    


wb.save('I:/学习资料/文本/常用模块/qq33.xlsx')

• 
  
  第二个：sheet.cell(row,col,value) 用这个方法要用到enumerate()给列表加上序列号
  
  

from openpyxl import *


wb =load_workbook('I:/学习资料/文本/常用模块/qq33.xlsx')
sheet = wb['qq33']
yu = [
      ['a111','b1','c1','d1'],
      ['a22','b2','c2','d2'],
      ['a3','b3','c3','d3'],
      ['a4','b4','c4','d4'],
      ['a5','b5','c5','d5'],
      ]


    
def cyclic_write(list,row=1,col=1):
    for y,i in enumerate(list):
        for x,n in enumerate(i):
            sheet.cell( y + col, x + row, n )
            
cyclic_write(yu,5,5)
wb.save('I:/学习资料/文本/常用模块/qq33.xlsx')

• 
  
  第三个：sheet[].vakue，用这个方法须要用openpyxl.utils.get_column_letter()方法把序列号换字母，再用字符串格式化传入参数
  
  

from openpyxl import *
from openpyxl.utils import *

wb =load_workbook('I:/学习资料/文本/常用模块/qq33.xlsx')
sheet = wb['qq33']
yu = [
      ['a111','b1','c1','d1'],
      ['a22','b2','c2','d2'],
      ['a3','b3','c3','d3'],
      ['a4','b4','c4','d4'],
      ['a5','b5','c5','d5'],
      ]


    
def cyclic_write(list,row=1,col=1):
    for y,i in enumerate(list):
        for x,n in enumerate(i):
           column = get_column_letter(y+col)
           sheet['{}'.format(column+str(x+row))].value=n
            
cyclic_write(yu,5,5)
wb.save('I:/学习资料/文本/常用模块/qq33.xlsx')