Python办公自动化【Excel数据拆分-xlrd、Excel读取数据-openpyxl、Excel写入数据-openpyxl】(二)-全面详解（学习总结---从入门到深化）

深藏阁楼爱情的钟 2024-02-17 12:54 24阅读 0赞

### ![2b905821c89a4b0984a40f0fb7732eab.gif][] ###

**目录**

Excel数据汇总

Excel数据拆分-xlrd

Excel读取数据-openpyxl

Excel写入数据-openpyxl

Excel写入样式-openpyxl

常用方法与属性

--------------------

### Excel数据汇总 ###

![fe950e31d99e43668dfbbc367a9b11f2.png][]

数据汇总是Excel中常用的功能，而在Python中使用通过遍历所有单元格即可实现

**代码**

import xlrd
    from xlutils.copy import copy
    
    
    def read_data():
      wb = xlrd.open_workbook('./base_data/data01.xlsx')
      sh = wb.sheet_by_index(0)
      fen_type = {}    # {a:110,b:300}
      count_price = []   # [1,2,3,4,5,6]
      for r in range(sh.nrows):
        count = sh.cell_value(r,3) * sh.cell_value(r,4)
        count_price.append(count)
        key = sh.cell_value(r,0)
        if fen_type.get(key):
          fen_type[key] += count
        else:
          fen_type[key] = count
      return fen_type,count_price # 各分类的总价值, 每个单品的总价值
    
    
    def save(fen,count):
      wb = xlrd.open_workbook('./base_data/data01.xlsx')
      sh_t = wb.sheet_by_index(0)
      wb2 = copy(wb)
      sh = wb2.get_sheet(0)
      for r in range(sh_t.nrows):
        sh.write(r,sh_t.ncols,count[r])
     
      sh2 = wb2.add_sheet('汇总的数据')
      for i,key in enumerate(fen.keys()):
        sh2.write(i,0,key)
        sh2.write(i,1,fen.get(key))
      wb2.save('./create_data/05_汇总数据.xlsx')
    
    
    if __name__ == "__main__":
      f,c = read_data()
      save(f,c)

> 问题  
> xlrd.biffh.XLRDError: Excel xlsx file; not supported  
> 解决方案：  
> 更换版本  
> **pip uninstall xlrd  
> pip install xlrd==1.2.0**

### Excel数据拆分-xlrd ###

![320c88a654544339ad57b2779a98c46d.png][]

**代码**

import xlrd
    from xlutils.copy import copy
    
    
    def get_data():
      wb = xlrd.open_workbook('./base_data/data01.xlsx')
      sh = wb.sheet_by_index(0)
      '''
     {
      a: [{},{},{}],
      b:[{},{},{}],
      c:[{},{},{}],
     }
     '''
      all_data = {}
      for r in range(sh.nrows):
    
        d = {'type':sh.cell_value(r,1),'name':sh.cell_value(r,2),'count':sh.cell_value(r,3),'price':
    sh.cell_value(r,4)}
        key = sh.cell_value(r,0)
        if all_data.get(key):
          all_data[key].append(d)
        else:
          all_data[key] = [d]
      return all_data
    
    
    def save(data):
      wb = xlrd.open_workbook('./base_data/data01.xlsx')
      wb2 = copy(wb)
      for key in data.keys():
        temp_sheet = wb2.add_sheet(key)
        for i, d in enumerate(data.get(key)):
        
     temp_sheet.write(i,0,d.get('type'))
        
     temp_sheet.write(i,1,d.get('name'))
        
     temp_sheet.write(i,2,d.get('count'))
        
     temp_sheet.write(i,3,d.get('price'))
      wb2.save('./create_data/06_表格的拆分.xlsx')
    
    
    if __name__ == "__main__":
      all_data = get_data()
    
     # save(all_data)
      print(all_data)

###  ###

### **Excel读取数据-openpyxl** ###

![45af8ff429b147ab8153ef70d7fbaf11.png][]

Python操作Excel的一个常用库openpyxl，openpyl是第3方模块库，可以方便的对excel执行读取与写入操作

**安装**

pip install openpyxl

**常用方法与属性**

<table> 
 <tbody> 
  <tr> 
   <td><span style="color:#0d0016;"><strong>函数名&amp;属性&nbsp;</strong></span></td> 
   <td><span style="color:#0d0016;"><strong>含义</strong></span></td> 
  </tr> 
  <tr> 
   <td>openpyxl.load_workbook(path)&nbsp;</td> 
   <td>加载Excel文件</td> 
  </tr> 
  <tr> 
   <td>workbook.active&nbsp;</td> 
   <td>激活第1个工作薄</td> 
  </tr> 
  <tr> 
   <td>workbook[name]&nbsp;</td> 
   <td>获取根据名字工作薄</td> 
  </tr> 
  <tr> 
   <td>workbook.get_sheet_by_name(name)&nbsp;</td> 
   <td>获取根据名字工作薄</td> 
  </tr> 
  <tr> 
   <td>workbook.sheetnames&nbsp;</td> 
   <td>获取所有工作薄名字</td> 
  </tr> 
  <tr> 
   <td>sheet.title&nbsp;</td> 
   <td>获取工作薄名字</td> 
  </tr> 
  <tr> 
   <td>sheet.max_row&nbsp;</td> 
   <td>获取行数</td> 
  </tr> 
  <tr> 
   <td>sheet.max_column&nbsp;</td> 
   <td>获取列数</td> 
  </tr> 
  <tr> 
   <td>sheet.cell(row,col)&nbsp;</td> 
   <td>获取指定单元格</td> 
  </tr> 
  <tr> 
   <td>sheet[cell_name]&nbsp;</td> 
   <td>获取指定单元格,例如: C2</td> 
  </tr> 
  <tr> 
   <td>sheet[col/row_name]</td> 
   <td>获取整行或整列单元格:填写num为整<br> 行,row_name为整列</td> 
  </tr> 
  <tr> 
   <td>sheet[col:col]&nbsp;</td> 
   <td>获取指定范围整行单元格</td> 
  </tr> 
  <tr> 
   <td>sheet[cell_name:cell_name]&nbsp;</td> 
   <td>获取指定范围单元格</td> 
  </tr> 
  <tr> 
   <td>sheet.iter_rows(min_row =0 , max_row =0, min_col =0,<br> max_col =0)</td> 
   <td>获取指定范围单元格</td> 
  </tr> 
  <tr> 
   <td>sheet.rows&nbsp;</td> 
   <td>获取所有行</td> 
  </tr> 
  <tr> 
   <td>sheet.columns&nbsp;</td> 
   <td>获取所有列</td> 
  </tr> 
  <tr> 
   <td>cell.value&nbsp;</td> 
   <td>获取单元格的值</td> 
  </tr> 
 </tbody> 
</table>

**代码**

# pip install openpyxl
    def open():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      # 获取工作薄
      sh1 = wb.active
      sh2 = wb['Sheet1']
      sh3 = wb.get_sheet_by_name('Sheet1')
      print(sh1 is sh2 is sh3)
    
    def show_sheets():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      print(wb.sheetnames)
      for sh in wb:
        print(sh.title)
    
    def get_one_value():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      sh1 = wb.active
      value1 = sh1.cell(2,3).value
      value2 = sh1['c2'].value
      print(value1,value2)
    
    def get_many_value():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      sh1 = wb['Sheet1']
      # 切片
      cells1 = sh1['c2':'d3']
      # print(cells1)
      # 整行，整列
      cell_row3 = sh1[3]
      cell_col3 = sh1['c']
      print(cell_row3)
      print(cell_col3)
      cell_row3_5 = sh1[3:5]
      print(cell_row3_5)
    
      # 通过迭代获取数据
      # for row in sh1.iter_rows(min_row =2 , max_row =5, max_col =3):
      #   for cell in row:
      #     print(cell.value)
      for row in sh1.iter_rows(min_row =2 , max_row =5, min_col =2, max_col =4):
        for cell in row:
          print(cell.value)
    
    def get_all_data():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      sh1 = wb['Sheet1']
      for row in sh1.rows:
        for cell in row:
          print(cell.value)
      for column in sh1.columns:
        for cell in column:
          print(cell.value)
    
    def get_num():
      from openpyxl import load_workbook
      wb = load_workbook('./base_data/data01.xlsx')
      sh1 = wb['Sheet1'] 
      print(sh1.max_row)
      print(sh1.max_column)
    
    if __name__ == "__main__":
    
      # open()
      # show_sheets()
      # get_one_value()
      # get_many_value()
      # get_all_data()
      get_num()

###  ###

### Excel写入数据-openpyxl ###

![5e37c8ae57ce4923970cb134a6394543.png][]

**常用方法与属性**

<table> 
 <tbody> 
  <tr> 
   <td style="width:427px;"><span style="color:#0d0016;"><strong>函数名&amp;属性&nbsp;</strong></span></td> 
   <td style="width:321px;"><span style="color:#0d0016;"><strong>含义</strong></span></td> 
  </tr> 
  <tr> 
   <td style="width:427px;">Workbook()&nbsp;</td> 
   <td style="width:321px;">创建Excel文件</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">workbook.create_sheet(name, &lt;num&gt; )&nbsp;</td> 
   <td style="width:321px;">在指定位置创建sheet表</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">cell.value&nbsp;</td> 
   <td style="width:321px;">获取单元格值,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">cell.font&nbsp;</td> 
   <td style="width:321px;">获取字体样式,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">cell.alignment&nbsp;</td> 
   <td style="width:321px;">获取单元格内容位置,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">openpyxl.styles.Font()&nbsp;</td> 
   <td style="width:321px;">创建字体样式</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">openpyxl.styles.Alignment()&nbsp;</td> 
   <td style="width:321px;">创建单元格内容位置样式</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">sheet.row_dimensions[num].height&nbsp;</td> 
   <td style="width:321px;">设置指定行高度</td> 
  </tr> 
  <tr> 
   <td style="width:427px;">sheet.column_dimensions[col_name].width&nbsp;</td> 
   <td style="width:321px;">设置指定列宽度</td> 
  </tr> 
 </tbody> 
</table>

**代码**

from openpyxl import Workbook
    def create_excel():
      # 创建excel
      wb = Workbook()
      # 激活第1个工作薄
      ws1 = wb.active
      # 创建工作薄
      ws2 = wb.create_sheet('数据')
      # 在指定位置创建工作薄、
       ws3 = wb.create_sheet('人员',0)
      # 保存数据
      wb.save('./create_data/09_创建excel文件.xlsx')
    
    def set_value1():
      # 创建excel
      wb = Workbook()
      # 激活第1个工作薄
      ws1 = wb.active
      # 写入数据
      ws1['A1'] = 'Hello'
      ws1['B2'] = 'Excel'
      ws1['C3'] = 'Python'
      # 保存数据
      wb.save('./create_data/10_创建excel文件_写入数据.xlsx')
    
    def set_value2():
      # 创建excel
      wb = Workbook()
      # 激活第1个工作薄
      ws1 = wb.active
      # 需要写入的数据
      data = ['Hello','Excel','Python']
      for i,d in enumerate(data):
        ws1.cell(i+1,i+1).value = d  # 注意 openpyxl里面 记数是从1开始
      # 保存数据
      wb.save('./create_data/11_创建excel文件_写入数据2.xlsx')
    
    def set_value3():
      # 创建excel
      wb = Workbook()
      # 激活第1个工作薄
      ws1 = wb.active
      # 需要写入的数据
      data = ['Hello','Excel','Python']
      # 把数据追加到工作薄
      ws1.append(data)
      # 保存数据
      wb.save('./create_data/12_创建excel文件_写入数据3.xlsx')
    
    if __name__ == '__main__':
      # create_excel()
      # set_value1()
      # set_value2()
      set_value3()

### Excel写入样式-openpyxl ###

![b3fe65e1a98b4fe5ae8fa85c3bee48e7.png][]

### **常用方法与属性**  ###

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数名&amp;属性&nbsp;</strong></td> 
   <td><strong>含义</strong></td> 
  </tr> 
  <tr> 
   <td>Workbook()&nbsp;</td> 
   <td>创建Excel文件</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.Font()&nbsp;</td> 
   <td>创建字体样式</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.Alignment()&nbsp;</td> 
   <td>创建单元格内容位置样式</td> 
  </tr> 
  <tr> 
   <td>sheet.column_dimensions[col_name].width&nbsp;</td> 
   <td>设置指定列宽度</td> 
  </tr> 
  <tr> 
   <td>sheet.row_dimensions[num].height&nbsp;</td> 
   <td>设置指定行高度</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.Border()&nbsp;</td> 
   <td>设置边框样式</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.Side()&nbsp;</td> 
   <td>设置边的样式</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.PatternFill()&nbsp;</td> 
   <td>设置背景样式</td> 
  </tr> 
  <tr> 
   <td>cell.font&nbsp;</td> 
   <td>获取字体样式,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td>cell.alignment&nbsp;</td> 
   <td>获取单元格内容位置,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td>cell.border&nbsp;</td> 
   <td>获取边框样式,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td>cell.fill&nbsp;</td> 
   <td>获取背景样式,也可直接赋值</td> 
  </tr> 
  <tr> 
   <td>openpyxl.styles.colors&nbsp;</td> 
   <td>设置颜色</td> 
  </tr> 
 </tbody> 
</table>

**代码  
字体样式与内容位置样式代码**

from openpyxl import Workbook
    from openpyxl.styles import
    Font,colors,Alignment
    
    def set_font():
    
     '''
     设置字体样式
     '''
      wb = Workbook()
      ws = wb.active
      ws['A1'] = 'Hello'
      ws['B2'] = 'Python'
      ws['C3'] = 'Excel'
      # 创建一个字体样式对象
      ft = Font(name='微软雅黑',size=30,italic=True,bold=True,color=colors.BLUE)
      ft2 = Font(name='微软雅黑',size=30,italic=True,bold=True,color='CDAD00')
      # 将字体的样式应用到单元格
      ws['B2'].font = ft
      ws['C3'].font = ft2
      # 保存数据
      wb.save('./create_data/13_openpyxl_字体样式.xlsx')
    
    def set_aligment():
     '''
    设置内容对齐方式
     '''
      wb = Workbook()
      ws = wb.active
      ws['A1'] = 'Hello'
      ws['B2'] = 'Python'
      ws['C3'] = 'Excel'
      # 创建一个对齐方式对象
      aligment = Alignment(horizontal='left',vertical='top')
      aligment2 = Alignment(horizontal='right',vertical='bottom')
      # 设置单元格的宽与高
      ws.row_dimensions[1].height = 50
      ws.column_dimensions['A'].width = 50
      ws['B2'].alignment = aligment
      ws['C3'].alignment = aligment2
      # 保存数据
      wb.save('./create_data/14_openpyxl_位置样式.xlsx')
    
    if __name__ =='__main__':
      # set_font()
      set_aligment()

**边框样式与背景样式代码**

from openpyxl import Workbook
    from openpyxl.styles import
    Border,Side,colors,PatternFill
    
    def set_border():
      '''
     设置边框样式
     '''
      wb = Workbook()
      ws = wb.active
      ws['A1'] = 'Hello'
      ws['B2'] = 'Python'
      ws['C3'] = 'Excel'
      # 创建一个线的样式对象
      side = Side(color=colors.BLUE,style='medium')
      side2 = Side(color='00008000',style='double')
      # 创建边框样式对象
      border = Border(left=side,right=side2,top=side,bottom =side2)
      # 将边框样式应用到单元格
      ws['B2'].border = border
      # 保存数据
      wb.save('./create_data/15_openpyxl_边框样式.xlsx')
    
    def set_pattern():
      '''
     设置背景颜色
     '''
      wb = Workbook()
      ws = wb.active
      ws['A1'] = 'Hello'
      ws['B2'] = 'Python'
      ws['C3'] = 'Excel'
      # 创建背景的样式对象
      pattern = PatternFill('solid',fgColor=colors.BLUE)
      # 将背景颜色样式应用单元格， 不要用pattern属性
      ws['B2'].fill = pattern # AttributeError: 'Cell' object has no attribute 'pattern'
      # 保存数据
      wb.save('./create_data/16_openpyxl_背景样式.xlsx')
    
    if __name__ =='__main__':
      # set_border()
      set_pattern()

[2b905821c89a4b0984a40f0fb7732eab.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/0f355462bdd9489288e300f3ca9fa142.png
[fe950e31d99e43668dfbbc367a9b11f2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/614018596fc84636a0e851bb75cff3bf.png
[320c88a654544339ad57b2779a98c46d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/164bdd262bd0405d8913dd1963fdbaf8.png
[45af8ff429b147ab8153ef70d7fbaf11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/d5a88e92fb874811b8a8a87a7380e2f6.png
[5e37c8ae57ce4923970cb134a6394543.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/101aba196d794108b57d678ebb0d4cd0.png
[b3fe65e1a98b4fe5ae8fa85c3bee48e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/3819c90e47cf4ee3b5ca7a64017eda6d.png