python处理excel文件

python处理excel文件可以使用openpyxl库。

精简版：

>>> import openpyxl #使用openpyxl库

#如果没有工作簿，可以创建：
>>> wb=openpyxl.Workbook() #此时工作簿中只创建一个工作表
>>> wb.sheetnames #查看工作表名字
['Sheet']
>>> wb.get_sheet_names() #或用.get_sheet_names()，为弃用的函数，警告

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function get_sheet_names (Use wb.sheetnames).
['Sheet']
>>> ws=wb.active
>>> ws.title #查看工作表标题
'Sheet'
>>> ws.title='NewTitle' #更改工作表标题
>>> ws.title
'NewTitle'

#如果已有工作簿，可以打开：
>>> wb=openpyxl.load_workbook('NewExcelFile.xlsx') #载入原有excel文件
>>> wb=openpyxl.load_workbook('NewExcelFile.xlsx',data_only=True) #设置data_only=True可以只读取数值而非公式
>>> ws=wb.active #默认激活第一个工作表
>>> ws.title #查看工作表标题
'Sheet1'

#创建新的工作表：
>>> wb.create_sheet()
<Worksheet "Sheet1">
>>> wb.get_sheet_names()
['Sheet', 'Sheet1']
>>> wb.create_sheet(index=0,title='1stSheet') #创建新工作表1stSheet并放在最前面
<Worksheet "1stSheet">
>>> wb.get_sheet_names()
['1stSheet', 'Sheet', 'Sheet1']
>>> wb.create_sheet(index=2,title='2ndSheet') #创建新工作表1stSheet并放在第3个
<Worksheet "2ndSheet">
>>> wb.get_sheet_names()
['1stSheet', 'Sheet', '2ndSheet', 'Sheet1']

#删除工作表
>>> wb.remove_sheet(wb.get_sheet_by_name('1stSheet')) #方法1，用弃用的函数删除工作表弹出警告

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function remove_sheet (Use wb.remove(worksheet) or del wb

[sheetname]).
>>> wb.remove_sheet(wb.get_sheet_by_name('Sheet1')) #方法2
>>> wb.get_sheet_names()
['Sheet', '2ndSheet']
>>> wb.remove_sheet(wb['Sheet']) #方法3
>>> wb.get_sheet_names()
['2ndSheet']
>>> del wb['2ndSheet'] #方法4
>>> wb.get_sheet_names() #工作表完全删除
[]
>>> wb.create_sheet() #创建一个工作表
<Worksheet "Sheet">

#写入数据、公式
>>> ws=wb.get_sheet_by_name('Sheet')
#方法1：
>>> ws['F6']="Writing new Value!" #F6单元格写入
>>> ws["f6"].value #单元格索引字母大小写均可
'Writing new Value!'
>>> ws["F6"]=500
>>> ws["F7"]=800
>>> ws["D3"]='=SUM(F6:F7)' #单元格写入公式
#方法2：
>>> ws.cell(1,2).value=2 #B1单元格cell()方式赋值。方法2。
>>> ws.cell(1,2).value #用cell().value查看值
2

#行列遍历
方法1：ws.max_row获得表格的最大行数，取得遍历次数，使用for循环遍历：
>>>for row in range(2, ws.max_row + 1): # 一般第一行是表头，所以从2开始，range()不含右边界
    for cell in row:
        print(cell)
方法2：openpyxl中提供了行列生成器（ws.rows和ws.columns），这两个生成器里面存储了每一行（列）的数

据，每一行由一个tuple包裹，便于对行列进行遍历:
>>>for row in ws.rows:
    for cell in row:
        print(cell)
由于ws.rows或ws.columns是生成器类型，不能直接调用，使用时往往将其转化为list类型，然后索引遍历获得

某一行（列）的内容:
>>>for cell in list(ws.rows)[0]: #打印第一行的内容
    print(cell.value)
方法3：使用sheet[行列值: 行列值]来对给定单元格范围进行遍历:
>>>for area_date in sheet[‘A1’:’H8’]: #打印A1到H8范围内的内容
    for cell in area_date:
        print(cell.value)

#插入与删除行、列：
ws.insert_rows(n) # 在第n行插入一行
ws.insert_cols(m,n) # 从第m列开始插入n列
ws.delete_cols(m, n) # 从第m列开始，删除n列
ws.delete_rows(n) # 删除第n行

#设置行高、列宽：
>>> ws['F6'] = 'Tall row'
>>> ws['D7'] = 'Wide column'
>>> ws.row_dimensions[3].height = 65 #设置第3行行高为65
>>> ws.column_dimensions['F'].width = 25 #设置F列宽为25
>>> mywb.save('Heightandwidth.xlsx')

#保存或另存为
>>> wb.save('E://desktop/NewExcelFile.xlsx') ##在桌面将excel文件保存为NewExcelFile.xlsx

详细：

1.创建和保存excel文件，查看工作表标题：

>>> import openpyxl #引用openpyxl库
>>> mywb=openpyxl.Workbook() #此时只创建一个工作表
>>> mywb.get_sheet_names() #.get_sheet_names()为弃用的函数，警告

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function get_sheet_names (Use wb.sheetnames).
['Sheet']
>>> mywb.sheetnames() #不该加括号
Traceback (most recent call last):
  File "<pyshell#3>", line 1, in <module>
    mywb.sheetnames()
TypeError: 'list' object is not callable
>>> mywb.sheetnames #新函数
['Sheet']
>>> sheet=mywb.active
>>> sheet.title #查看工作表标题
'Sheet'
>>> sheet.title='MyNewTitle' #更改工作表标题
>>> mywb.sheetnames
['MyNewTitle']
>>> mywb.save('NewExcelFile.xlsx') #在根目录下将excel文件保存为NewExcelFile.xlsx
>>> mywb.save('E://desktop/NewExcelFile.xlsx') ##在桌面将excel文件保存为NewExcelFile.xlsx

保存文件时磁盘符（C、D、E、F等，大小写均可） 后面必须跟冒号，斜线或反斜线可添加任意多个或0个，两文件夹之间至少有一个斜线或反斜线，可无文件名，此时只有后缀。文件格式保存为.xlsx或.xls均可

2.已有excel文件另存：

>>> import openpyxl
>>> mywb=openpyxl.load_workbook('NewExcelFile.xlsx') #载入原有excel文件
>>> sheet=mywb.active
>>> sheet.title='Working on Save as' #修改工作表标题
>>> mywb.save('example_NewExcelFile.xlsx') #在根目录下另存为'example_NewExcelFile.xlsx'

3.在excel中创建和删除工作表：

>>> import openpyxl
>>> mywb=openpyxl.Workbook()
>>> mywb.get_sheet_names()
['Sheet']
>>> mywb.create_sheet() #创建新工作表
<Worksheet "Sheet1">
>>> mywb.get_sheet_names()
['Sheet', 'Sheet1']
>>> mywb.create_sheet(index=0,title='1stSheet') #创建新工作表1stSheet并放在最前面
<Worksheet "1stSheet">
>>> mywb.get_sheet_names()
['1stSheet', 'Sheet', 'Sheet1']
>>> mywb.create_sheet(index=2,title='2ndSheet') #创建新工作表1stSheet并放在第3个
<Worksheet "2ndSheet">
>>> mywb.get_sheet_names()
['1stSheet', 'Sheet', '2ndSheet', 'Sheet1']


>>> mywb.remove_sheet(mywb.get_sheet_by_name('1stSheet')) #用弃用的函数删除工作表弹出警告

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).

Warning (from warnings module):
  File "__main__", line 1
DeprecationWarning: Call to deprecated function remove_sheet (Use wb.remove(worksheet) or del wb[sheetname]).
>>> mywb.remove_sheet(mywb.get_sheet_by_name('Sheet1'))
>>> mywb.get_sheet_names()
['Sheet', '2ndSheet']
>>> mywb.remove_sheet(mywb['1stSheet']) #删除不存在的工作表会报错
Traceback (most recent call last):
  File "<pyshell#39>", line 1, in <module>
    mywb.remove_sheet(mywb['1stSheet'])
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\workbook\workbook.py", line 278, in __getitem__
    raise KeyError("Worksheet {0} does not exist.".format(key))
KeyError: 'Worksheet 1stSheet does not exist.'
>>> mywb.remove_sheet(mywb['Sheet'])
>>> mywb.get_sheet_names()
['2ndSheet']
>>> del mywb['2ndSheet'] #新语法删除工作表
>>> mywb.get_sheet_names() #工作表完全删除
[]

4.单元格写入数据、公式：

>>> import openpyxl
>>> mywb=openpyxl.Workbook()
>>> mysheet=mywb.get_sheet_by_name('Sheet')
>>> mysheet['F6']="Writing new Value!" #F6单元格写入
>>> mysheet["f6"].value #单元格索引字母大小写均可
'Writing new Value!'

>>> import openpyxl
>>> mywb=openpyxl.Workbook()
>>> mysheet=mywb.active
>>> mysheet["F6"]=500
>>> mysheet["F7"]=800
>>> sheet["D3"]='=SUM(F6:F7)' #单元格写入公式
>>> mywb.save('Applyingformula.xlsx')

当需要用变量表示单元格时， 可以使用ws.cell(row,column)方式引用，其中列号也使用数字表示，列号的字母数字转换函数如下：

col_number=openpyxl.utils.column_index_from_string(char)

col_char=openpyxl.unils.get_column_letter(number)

>>> from openpyxl.utils import*
>>> col_number=column_index_from_string('AAA')
>>> col_number
703
>>> col_char=get_column_letter(100)
>>> col_char
'CV'

>>> from openpyxl import load_workbook
>>> wb=load_workbook('NewExcelFile.xlsx')
>>> wb
<openpyxl.workbook.workbook.Workbook object at 0x0000003A6F53C668>
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>
>>> ws=wb[wb.sheetnames[0]]
>>> ws
<Worksheet "MyNewTitle">
>>> type(ws)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> ws['A1']
<Cell 'MyNewTitle'.A1>
>>> type(ws['A1'])
<class 'openpyxl.cell.cell.Cell'>
>>> ws['A1']=1 #A1单元格赋值
>>> ws['A1'].value
1
>>> ws.cell(1,1)
<Cell 'MyNewTitle'.A1>
>>> ws.cell(1,2).value=2 #B1单元格cell()方式赋值
>>> ws.cell(1,2).value #用cell().value查看值
2

5.合并和取消合并单元格：

>>> import openpyxl
>>> mywb=openpyxl.Workbook()
>>> mysheet=mywb.active
>>> mysheet.merge_cells('B2:D3') #B2:D3合并，前者为左上角单元格，后者为右下角单元格，合并后的单元格写入时用左上角的单元格
>>> mysheet['A1']='cells merged together.'
>>> mysheet.merge_cells('F6:F7') #F6:F7合并
>>> mysheet['G5']='Two merged cells.'
>>> mywb.save('Mergingcells.xlsx')

>>> import openpyxl
>>> mywb = openpyxl.load_workbook('Mergingcells.xlsx')
>>> mysheet = mywb.active
>>> mysheet.unmerge_cells('B2:D3') #单元格取消合并
>>> mysheet.unmerge_cells('F6:F7')
>>> mywb.save('unmerged.xlsx') 

后者为右下角单元格，合并后的单元格写入时用左上角的单元格。 

6.设置行高列宽：

>>> import openpyxl
>>> mywb = openpyxl.Workbook()
>>> mysheet = mywb.active
>>> mysheet['F6'] = 'Tall row'
>>> mysheet['D7'] = 'Wide column'
>>> mysheet.row_dimensions[3].height = 65 #设置第3行行高为65
>>> mysheet.column_dimensions['F'].width = 25 #设置F列宽为25
>>> mywb.save('Heightandwidth.xlsx')

默认行高为12.75 points，1 point等于1/72 inch，行高范围为0-409.

列宽范围为0-255，可以是整数或浮点数。

行高列宽设为0时，被隐藏。

7.插入与删除行、列：

ws.insert_rows(n) # 在第n行插入一行
ws.insert_cols(m,n) # 从第m列开始插入n列
ws.delete_cols(m, n) # 从第m列开始，删除n列
ws.delete_rows(n) # 删除第n行

上述两种方式删除行（列）后，下（后）面的表格自动上（前）移。

8.行列遍历：

行列遍历有两种方式：

1.ws.max_row获得表格的最大行数，取得遍历次数，使用for循环遍历：

>>>for row in range(2, ws.max_row + 1): # 一般第一行是表头，所以从2开始，range()不含右边界
    for cell in row:
        print(cell)

2.openpyxl中提供了行列生成器（ws.rows和ws.columns），这两个生成器里面存储了每一行（列）的数据，每一行由一个tuple包裹，便于对行列进行遍:

>>>for row in ws.rows:
    for cell in row:
        print(cell)

由于ws.rows或ws.columns是生成器类型，不能直接调用，使用时往往将其转化为list类型，然后索引遍历获得某一行（列）的内容:

>>>for cell in list(ws.rows)[0]: #打印第一行的内容
    print(cell.value)

此外，还可以使用sheet[行列值: 行列值]来对给定单元格范围进行遍历:

>>>for area_date in sheet[‘A1’:’H8’]: #打印A1到H8范围内的内容
    for cell in area_date:
        print(cell.value)

9.设置字体：

>>> import openpyxl
>>> from openpyxl.styles import Font, Style
>>> mywb = openpyxl.Workbook()
>>> mysheet = mywb.get_sheet_by_name('Sheet')
>>> italic32Font = Font(size=32, italic=True)
>>> sobj = Style(font=italic32Font)
>>> mysheet['F6'].style = sobj #设置F6单元格样式为字号32，斜体
>>> mysheet['F6'] = 'Applying Styles!'
>>> mywb.save('Appliedstyle.xlsx')

>>> import openpyxl
>>> from openpyxl.styles import Font, Style
>>> mywb = openpyxl.Workbook()
>>> mysheet = mywb.get_sheet_by_name('Sheet')

>>> firstFontObj = Font(name='Arial', bold=True) #斜体和加粗均为布尔类型
>>> firstStyleObj = Style(font=firstFontObj)
>>> mysheet['F6'].style/firstStyleObj
>>> mysheet['F6'] = 'Bold Arial'

>>> secondFontObj = Font(size=32, italic=True)
>>> secondStyleObj = Style(font=secondFontObj)
>>> mysheet['D7'].style/secondStyleObj
>>> mysheet['D7'] = '32 pt Italic'
>>> mywb.save('ApplicationofStyles.xlsx')

10.获取最大行、列数

import openpyxl
wb=openpyxl.load_workbook('xxx.xlsx')
sheet=wb.active
max_row=sheet.max_row
max_column=sheet.max_column

译自：

Python Excel Writer - Python Excel http://www.pythonexcel.com/python-excel-writer.php

参考：

Python利用openpyxl处理Excel文件（单元格及行列具体操作）

Python-Excel获取最大行与列