python处理excel文件

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

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 "
              
                ", line 1, in 
                
                  
    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() #创建新工作表

              
                
>>> mywb.get_sheet_names()
['Sheet', 'Sheet1']
>>> mywb.create_sheet(index=0,title='1stSheet') #创建新工作表1stSheet并放在最前面

                
                  
>>> mywb.get_sheet_names()
['1stSheet', 'Sheet', 'Sheet1']
>>> mywb.create_sheet(index=2,title='2ndSheet') #创建新工作表1stSheet并放在第3个

                  
                    
>>> 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 "
                    
                      ", line 1, in 
                      
                        
    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

              
                
>>> type(wb)

                
                  
>>> ws=wb[wb.sheetnames[0]]
>>> ws

                  
                    
>>> type(ws)

                    
                      
>>> ws['A1']

                      
                        
>>> type(ws['A1'])

                        
                          
>>> ws['A1']=1 #A1单元格赋值
>>> ws['A1'].value
1
>>> ws.cell(1,1)

                          
                            
>>> 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')
            
          

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

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