Python使用openpyxl模块控制excel-xlsx文件

打开文件-选择Sheet/创建Sheet-循环rows以及columns

Excel文件

xlxs文件内部含有sheet表

• 导入模块

• 打开文件

  from openpyxl import load_workbook
  
  #打开文件
  wb = load_workbook('files/p1.xlsx')
  
  #显示所有sheet表
  print(wb.sheetnames)

读取

• 读取sheet表名称

  print(wb.sheetnames)

• 选择sheet表

  • 名称

  • 索引

    # #选取sheetname，方法1
    # sheet = wb['数据导出']
    
    #选取sheetname，方法2
    sheet = wb.worksheets[0]

• 读取单元格

  • 单个读取

    • sheet_object.cell(1,1)
    • sheet_object[’A1’]

  • 循环sheel读取

    worksheet默认等于wb

    • wb
    • worksheets
    • sheetnames

    #循环每一份sheet，然后获取第一行，第一列
    #方式一
     for i in wb.sheetnames:
         data = wb[i]
         print(data.cell(1,1).value)
    
    #方式二
    for i in wb.worksheets:
        print(i.cell(1,1).value)

单元格对象包含很多信息

• 读取单元格信息

  • 字体 font
  • 样式 style
  • 值 value
  • 排列方式 alignment

  #循环每个sheel表的1，1,获取值，样式，排列方式，字体
  for i in wb:
      data = i.cell(1,1)
      print(data.value,data.font,data.alignment,data.style)

• 获取行

  • 获取一行数据

    • sheet_object[1]

    #获取sheet1表第一行数据
    sheet_line1 = sheet1[1]
    #循环确认一行中，每一个单元格的内容
    for i in sheet_line1:
        print(i.value)

  • 获取所有行数据

  • sheet_object.rows

    • 循环获取每一行内容

    #获取所有行数据
    sheet_all_row = sheet1.rows
    
    #循环每一行
    for i in sheet_all_row:
        #循环每一行的内容
        for j in i:
            print(j.value,end='')
        print()
    

• 获取列

• 获取第N列

  • sheet_object[’A’]

  #获取A列
  sheet_A_columbs = sheet1['A']
  
  for i in sheet_A_columbs:
      print(i.value)

• 获取所有列

  • sheet_object.columns

  #获取所有列
  all_column = sheet1.columns
  
  #循环所有列
  for i in all_column:
      #循环一列中的单元格
      for j in i:
          print(j.value,end='')
      print()

补充

• 单元格合并

当单元格被合并，类型为‘MergedCell’，而不是‘cell’,返回的值为None

(<Cell 'Sheet1'.A1>, <MergedCell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)
(<MergedCell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>)

print(sheet3['A1'].value)
#用户信息
print(sheet3['B1'].value)
#None

写入

文件

• 修改文件

  #修改文件
  #引入load_workbook函数
  from openpyxl import load_workbook
  #打开文件啊
  wb = load_workbook('files/p1.xlsx')
  
  #确定sheet表
  sheet3 = wb.worksheets[3]
  
  #确定单元格值
  cell_b4 = sheet3['B4']
  cell_b4.value = 123
  
  wb.save('files/p1n.xlsx')

• 新建文件

  from openpyxl import workbook
  
  #新建文件
  #引入workbook函数
  wb = workbook.Workbook()
  
  #确定默认sheet
  #sheet_1n = wb.worksheets[0]
  sheet_1n = wb['Sheet']
  
  #创建新值
  cell_A1 = sheet_1n['A1']
  cell_A1.value = 123
  
  wb.save('files/p2n.xlsx')

sheet

• 修改名称 sheet_object.title = 123

  #修改sheet名称
  # #选择sheet
  # sheet = wb['数据导出']
  # #修改sheet名字
  # sheet.title = '数据置换'

• 创建sheet file_object.create_sheet(’name’,1)

  wb.create_sheet('第一',1)

• 修改tab颜色(rgb) sheet_object.properties.tabColor = ‘rgb颜色’

  sheet_1.sheet_properties.tabColor = '00FF00'

• 修改默认打开sheet表 file_object.active

  wb1.active = 3

• 拷贝sheet表new_sheet_object = file_object.copy_worksheet(old_sheet_object)

  # old_sheet = wb1.create_sheet('拷贝',5)
  # new_sheet = wb1.copy_worksheet(old_sheet)

• 删除 del sheet_object

  # del wb1['新拷贝']
  # wb1.save('files/p1n1.xlsx')
  

单元格

• 获取单元格(并修改值)

  • 获取一个

    • sheet_object.cell(1,1)
    • sheet_objectl[’A1’]

  • 获取多个

    • sheet_objectl[’B2’:’C3’]

导入模块

from openpyxl.styles import Alignment,Border,Side,Font,PatternFill(背景色),GradientFill(渐变背景色)

• 对齐方式

  • cell_object.alignment()

    • horizontal，水平对齐方式：general，left，right，center,fill,justify(两端对齐), centerContinuous(跨选区居中，不合并(区别于合并居中),将第一个单元格覆盖整个选择区域)，distributed(分散对齐,文字扩大间隔向两边靠拢)
    • vertical 垂直对齐 top,center,bottom,justify,distributed
    • text_rotation 旋转角度
    • warp_text 是否自动换行

  cell.alignment(horizontal = 'center',vertical = 'center',text_rotaion = 45,wrap_text = True)

• 边框

  边框样式

  dashDot，dashDotDot，dashed，dotted，double，hair，medium，mediumDashDOt，mediumDashDotDot，mediumDashed，slantDashDot，thick，thin

  • cell_object.border = Border()

    Border(
    top = Side(style='thin',color = 'FFB6C1'),
    bottom = Side(style='thin',color = 'FFB6C1'),
    left = Side(style='thin',color = 'FFB6C1'),
    right = Side(style='thin',color = 'FFB6C1'),
    diagonal = Side(style='thin',color = 'FFB6C1'), #对角线
    diagonalUP = True, #左上右下
    #diagonalDown = True #左下右上，一起用就是x号
    )

• 字体

  cell_object.font = Font()

  cell.fon = Font(name = '微软雅黑',size = 45,color = 'FFB6C1',underline = 'single') #single下划线

• 背景填充

  • 单色

    cell_object.fill = PatternFill(’solid’,fgColor=’99ccff’) #纹路填充

    cell.fill = PatternFill('solid',fgColor = '99ccff')

  • 渐变色

    cell_object.fill = GradientFill(’linear’,stop=(’FFFFFF’,’99ccff’,’00000F’)) #坡度填充

    cell.fill = GradientFill('linear',stop = (’FFFFFF’,’99ccff’,’00000F’))

• 单元格尺寸

  • sheet_object.row_dimensions[1].height = 50 #高度

  • sheet_object.column_dimensions[1].width = 100 #宽度

    from openpyxl import load_workbook,workbook
    from openpyxl.utils import get_column_letter
    
    #循环列，循环行，获取需要的1或者A
    #sheet_object.max_row为最大行数
    #sheet_object.max_row,最大列数
    for i in range(1,sheet_1.max_row+1):
        sheet_1.row_dimensions[i].height = 50
    #get_column_letter(j)，将列数转换为AB
    for j in range(1,sheet_1.max_column+1):
        data = get_column_letter(j)
        sheet_1.column_dimensions[data].width = 100

• 合并单元格

  sheet_object.merge_cells(’B2:D8’)

   sheet_1.merge_cells('B2:D8')
   sheet_1.merge_cells(start_row = 2,start_column = 2,end_row = 8,end_column = 4)

• 写入公式

  sheet_object[’B1’] = '合集'
  sheet_object[’B2’] = 'B2*C2'
  sheet_object[’B2’] = '=SUM(B3,C3)'

• 删除

  sheet_object.delete_rows()

  sheet_object.delete_cols()

  sheet_1.delete_rows(idx = 5,amount = 10) #从第五行往后删除10行
  sheet_1.delete_cols(idx = 5,amount = 10) #从第五列往后删除10列

• 插入

  sheet_object.insert_rows(idx=5,amount=10)

  sheet_object.insert_cols(idx=5,amount=10)

  sheet_1.insert_rows(idx=5,amount=10)
  sheet_1.insert_cols(idx=5,amount=10)

• 循环写入

  • 选取分区
  • 循环rows

  for i in sheet_4.iter_rows(min_row=2, max_row=4,min_col=2,max_col=2):
      for j in i:
          j.value = 'weixin1'
  
  sheet_choice_zone = sheet_4['B1:C4']
  for i in sheet_choice_zone:
      for j in i:
          j.value = 'weixin'

• 选区移动

  • sheet_object.move_range()

  sheet_choice_zone1 = sheet_4['B1:C4']
  sheet_4.move_range('B1:C4',rows=-1,cols=3,translate=True) 
  #row整为向下，负为向上，cols负向左，正向右
  
  #translate为公式跟随移动
  

• 打印区域

  • sheet_object.print_area = ‘A1:D200’

  sheet_1print_area = ‘A1:D200’

• 设置打印页眉(页面布局-工作表)

  • sheet_object.print_title_rows()
  • sheet_object.print_title_cols()

  sheet_object.print_title_rows('1:1')
  sheet_object.print_title_cols('A:D')
  

• 分页符物理设置()
• enumerate，双数据索引