10分钟学会用Python轻松玩转Excel

pip install openpyxl
复制代码

pip install -i https://pypi.douban.com/simple openpyxl
复制代码

>>> from openpyxl import load_workbook
>>> wb = load_workbook('获奖名单.xlsx')
复制代码

>>> from openpyxl import Workbook
>>> wb1 = Workbook()
复制代码

>>> wb.create_sheet('new_sheet', 0)
<Worksheet "new_sheet">
复制代码

>>> wb.sheetnames
['new_sheet', '一等奖', '二等奖']
复制代码

>>> [sheet.title for sheet in wb]
['new_sheet', '一等奖', '二等奖']
复制代码

>>> wb['二等奖']
<Worksheet "二等奖">
复制代码

>>> wb.active
<Worksheet "一等奖">
复制代码

>>> sheet1 = wb['一等奖']
>>> sheet1.max_column
7
>>> sheet1.max_row
6
复制代码

>>> sheet1['D3']
<Cell '一等奖'.D3>
>>> sheet1.cell(row=3, column=4)
<Cell '一等奖'.D3>
复制代码

>>> sheet1.cell(3, 4).value
'基于Spark、Python的医护人员信息抽取与管理'
>>> sheet1.cell(3, 4, value='Python')
<Cell '一等奖'.D3>
>>> sheet1.cell(3, 4).value
'Python'
复制代码

>>> sheet1['D3'].value
'Python'
>>> sheet1['D3'].coordinate
'D3'
>>> sheet1['D3'].row
3
>>> sheet1['D3'].column
4
复制代码

>>> sheet1['A1':'B2']
((<Cell '一等奖'.A1>,
  <Cell '一等奖'.B1>),
 (<Cell '一等奖'.A2>,
  <Cell '一等奖'.B2>))
复制代码

>>> sheet1['D']
(<Cell '一等奖'.D1>,
    ...
 <Cell '一等奖'.D6>)
复制代码

>>> sheet1['B:C']
((<Cell '一等奖'.B1>,
    ...
  <Cell '一等奖'.B6>),
 (<Cell '一等奖'.C1>,
    ...
  <Cell '一等奖'.C6>))
复制代码

>>> sheet1[3]
(<Cell '一等奖'.A3>,
 <Cell '一等奖'.B3>,
    ...
 <Cell '一等奖'.F3>,
 <Cell '一等奖'.G3>)
复制代码

>>> sheet1[2:3]
((<Cell '一等奖'.A2>,
    ...
  <Cell '一等奖'.G2>),
 (<Cell '一等奖'.A3>,
    ...
  <Cell '一等奖'.G3>))
复制代码

>>> for row in  sheet1.iter_rows(min_row=2, max_row=3, min_col=2, max_col=3):
        for cell in  row:
            print(cell.coordinate)
B2
C2
B3
C3
复制代码

>>> for col in  sheet1.iter_cols(min_row=2, max_row=3, min_col=2, max_col=3):
        for cell in col:
            print(cell.coordinate)
B2
B3
C2
C3
复制代码

>>> tuple(sheet1.rows)
((<Cell '一等奖'.A1>,
  ...
  <Cell '一等奖'.G1>),
  ...
  ...
 (<Cell '一等奖'.A6>,
  ...
  <Cell '一等奖'.G6>))
复制代码

>>> tuple(sheet1.columns)
((<Cell '一等奖'.A1>,
  ...
  <Cell '一等奖'.A6>),
  ...
  ...
 (<Cell '一等奖'.G1>, 
  ...
  <Cell '一等奖'.G6>))
复制代码

>>> for row_index in range(2, sheet1.max_row + 1):
        sheet1.cell(row_index, 8).value = len(sheet1.cell(row_index, 6).value.split(','))
复制代码

>>> sheet1['H7'] = '=SUM(H1:H6)'
复制代码

>>> sheet1.append([str(n) for n in range(6)])
复制代码

>>> sheet1.append({'A':'1','C':'3'})
复制代码

>>> sheet1.insert_rows(idx=2, amount=2)
复制代码

>>> wb.remove(wb['new_sheet'])
复制代码

>>> wb.save('获奖名单V1.xlsx')
复制代码

>>> from openpyxl.styles import Font
>>> new_font = Font(name='微软雅黑', size=20, color='3333CC', bold=True)
>>> sheet1['B2'].font = new_font
复制代码

>>> from openpyxl.styles import PatternFill, colors
>>> sheet1["A2"].fill = PatternFill("solid", fgColor=colors.BLUE)
>>> sheet1["A3"].fill = PatternFill("solid", fgColor='FF66CC')
复制代码

>>> from openpyxl.styles import Alignment
>>> sheet1['D2'].alignment = Alignment(horizontal='center', vertical='center')
复制代码

>>> sheet1.row_dimensions[2].height = 40
>>> sheet1.column_dimensions['C'].width = 20
复制代码

>>> sheet.merge_cells('A1:C3')
复制代码

>>> sheet.unmerge_cells('A1:C3')
复制代码