刷到利用：Python快速处理表格的AD

忘是亡心i 2023-02-20 07:13 3阅读 0赞

**Python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel**

### **Part 1xlrd** ###

**匆匆之中，准备如下，简单一表，测试功能：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 1][]

**直接脚本，文件如下：**

![2020062414554241.png][]

**① 导入模块：xlrd。**

cmd命令下：pip install xlrd就OK了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 2][]

**② 加载表格**

import xlrd,os
    # 加载xls格式的Excel文件对象，原理都是在xlrd中创建Book对象
    work_book = xlrd.open_workbook('frozen.xls')
    # work_book = xlrd.book.open_workbook_xls('frozen.xls')

**③ 获取sheet，页签数量**

# 1、sheet页签
    # sheet页签数量
    print(work_book.nsheets)
    # 获取工作簿中所有sheet表对象
    sheets = work_book.sheets()
    print(sheets)
    # 获取工作簿所有sheet表对象名称
    sheets_name = work_book.sheet_names()
    print(sheets_name)
    os.system("pause");

![20200624161242262.png][]

**④获取sheet**

# 2、获取sheet对象
    # 按照索引
    sheet_1 = work_book.sheet_by_index(0)
    print(sheet_1)
    # 按sheet表名，名称分大小写
    sheet_2 = work_book.sheet_by_name('Frozen')
    print(sheet_2)

![20200624161458991.png][]

**⑤获取单元格**

# 单元格数据类型：单元格值
    cell_0 = sheet_1.cell(0,0)
    print(cell_0)
    # 单纯单元格值
    cell_0_value = sheet_1.cell_value(0,0)
    print(cell_0_value)
    # 单纯单元格类型
    cell_0_type = sheet_1.cell_type(0,0)
    print(cell_0_type) 
    os.system("pause");

![20200624162002394.png][]

**⑥ 获取一行，表格属性**

# 当前sheet，总行数
    row_sum = sheet_1.nrows
    print(row_sum)
    # 获取sheet表某一行长度
    row_len = sheet_1.row_len(0)
    print(row_len)
    # 获取sheet表某一行所有数据类型及值
    row_0 = sheet_1.row(0)
    print(row_0)
    os.system("pause");

![20200624162215391.png][]

**⑦ 获取某一行**

# 对象数据类型、值，可指定开始结束列
    row_0_s = sheet_1.row_slice(0,0,3)
    print(row_0_s)
    # 获取sheet表对象某一行数据类型,返回一个数组对象
    row_0_type = sheet_1.row_types(0)
    print(row_0_type)
    # 获取sheet表对象某一行数据值
    row_0_value = sheet_1.row_values(0)
    print(row_0_value)
    row_1_value = sheet_1.row_values(1)
    print(row_1_value)
    row_2_value = sheet_1.row_values(2)
    print(row_2_value)
    row_3_value = sheet_1.row_values(3)
    print(row_3_value)
    os.system("pause");

![20200624163239296.png][]

**⑧获取列对象**

# 获取列对象
    rows = sheet_1.get_rows()
    print(rows)
    for row in rows:
        print(row)
    # 获取sheet表有效列数
    col_sum = sheet_1.ncols
    print(col_sum)
    # 获取某一列的值
    col_0_value = sheet_1.col_values(0)
    print(col_0_value)
    # 获取某一列的数据类型
    col_0_type = sheet_1.col_types(0)
    print(col_0_type)
    os.system("pause");

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 3][]

**一个完整的demo**

import xlrd,os
    # 加载xls格式的Excel文件对象，原理都是在xlrd中创建Book对象
    work_book = xlrd.open_workbook('frozen.xls')
    # work_book = xlrd.book.open_workbook_xls('frozen.xls')
    # 1、sheet页签
    # sheet页签数量
    print(work_book.nsheets)
    # 获取工作簿中所有sheet表对象
    sheets = work_book.sheets()
    print(sheets)
    # 获取工作簿所有sheet表对象名称
    sheets_name = work_book.sheet_names()
    print(sheets_name)
    os.system("pause");
    
    # 2、获取sheet对象
    # 按照索引
    sheet_1 = work_book.sheet_by_index(0)
    print(sheet_1)
    # 按sheet表名，名称分大小写
    sheet_2 = work_book.sheet_by_name('Frozen')
    print(sheet_2)
    os.system("pause");
    
    # 3、获取单元格对象
    # 单元格数据类型：单元格值
    cell_0 = sheet_1.cell(0,0)
    print(cell_0)
    # 单纯单元格值
    cell_0_value = sheet_1.cell_value(0,0)
    print(cell_0_value)
    # 单纯单元格类型
    cell_0_type = sheet_1.cell_type(0,0)
    print(cell_0_type) 
    os.system("pause");
    
    # 4、获取sheet表对象有效行数
    row_sum = sheet_1.nrows
    print(row_sum)
    # 获取sheet表某一行长度
    row_len = sheet_1.row_len(0)
    print(row_len)
    # 获取sheet表某一行所有数据类型及值
    row_0 = sheet_1.row(0)
    print(row_0)
    os.system("pause");
    
    
    # 5、获取某一行
    # 对象数据类型、值，可指定开始结束列
    row_0_s = sheet_1.row_slice(0,0,3)
    print(row_0_s)
    # 获取sheet表对象某一行数据类型,返回一个数组对象
    row_0_type = sheet_1.row_types(0)
    print(row_0_type)
    # 获取sheet表对象某一行数据值
    row_0_value = sheet_1.row_values(0)
    print(row_0_value)
    row_1_value = sheet_1.row_values(1)
    print(row_1_value)
    row_2_value = sheet_1.row_values(2)
    print(row_2_value)
    row_3_value = sheet_1.row_values(3)
    print(row_3_value)
    os.system("pause");
    
    # 6、获得列对象
    rows = sheet_1.get_rows()
    print(rows)
    for row in rows:
        print(row)
    # 获取sheet表有效列数
    col_sum = sheet_1.ncols
    print(col_sum)
    # 获取某一列的值
    col_0_value = sheet_1.col_values(0)
    print(col_0_value)
    # 获取某一列的数据类型
    col_0_type = sheet_1.col_types(0)
    print(col_0_type)
    os.system("pause");

### **Part 2 xlwt** ###

安装xlwt：cmd命令下输入：pip3 install xlwt

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 4][]

**小demo**

# -*- coding:cp936 -*-
    import xlwt,os
    from datetime import datetime
    
    style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
        num_format_str='#,##0.00')
    style1 = xlwt.easyxf(num_format_str='YYYY-MM-DD')
    wb = xlwt.Workbook(encoding = 'utf-8')
    ws = wb.add_sheet('A Test Sheet')
    str1 = "爱到尽头，覆水难收。爱悠悠恨悠悠！"
    ws.write(0, 0, 1234.56, style0)
    ws.write(1, 0, datetime.now(), style1)
    ws.write(2, 0, 1)
    ws.write(2, 1, 1)
    ws.write(2, 2, xlwt.Formula("A3+B3"))
    ws.write(3, 0, str1)
    os.system("pause");
    wb.save('redant.xls')

**【注意】**

在Windows下编码cp936是可以写入成功的，但是在Linux中只能够用utf-8。

为什么使用：编码cp936呢？笔者刚刚接触，还未可知。

![20200624172524501.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 5][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200624150626338.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200624150646583.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70
[2020062414554241.png]: https://img-blog.csdnimg.cn/2020062414554241.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020062415265613.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70
[20200624161242262.png]: https://img-blog.csdnimg.cn/20200624161242262.png
[20200624161458991.png]: https://img-blog.csdnimg.cn/20200624161458991.png
[20200624162002394.png]: https://img-blog.csdnimg.cn/20200624162002394.png
[20200624162215391.png]: https://img-blog.csdnimg.cn/20200624162215391.png
[20200624163239296.png]: https://img-blog.csdnimg.cn/20200624163239296.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200624163611797.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200624165730570.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70
[20200624172524501.png]: https://img-blog.csdnimg.cn/20200624172524501.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200624172549815.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjg3ODA5,size_16,color_FFFFFF,t_70