Python Excel应用：xlrd与xlwt的应用

最近学习python，学习xlrd（excel读取）和xlwt（excel写入）的应用，参考了网上大神的博客，有问题请多多指正。

1. 安装文件 ：  pip install xlrd ；

                         pip intall xlwt

2. 准备一个 excel.xlsx 文件，放在执行py文件统计目录下，新建sheet页student，录入数据如下面excel所示

学号	姓名	性别	年龄	语文	数学	英语	体育
A001	阿大	男	8	90	100	67	78
A002	阿二	男	9	83	60	57	85
A003	阿三	女	7	87	80	76	35
A004	阿四	女	8	76	76	43	132
A005	阿五	女	9	100	98	79	24

3. xlrd 常规应用

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import xlrd
import os,sys

def change_to_py_dir():
    folder_path = os.path.abspath(os.path.dirname(sys.argv[0]))
    os.chdir(folder_path)
    return None

if __name__ == '__main__':
   try:
     change_to_py_dir()
     excel_name = 'excel.xlsx' 
     ##打开一个excel文件
     ##学习参考网址：https://www.cnblogs.com/insane-Mr-Li/p/9092619.html 跟随大神的脚步
     book = xlrd.open_workbook(excel_name)
     
     ##查看所有的sheet页面
     print(book.sheet_names())
     
     ##打开student这个sheet页面
     sheet1 = book.sheet_by_name('student') 
     sheet1 = book.sheet_by_index(0) 
     sheet1 = book.sheets()[0] 
     
     ##判断sheet页是否加载
     print(book.sheet_loaded('student'))
     
     ##sheet页行操作    
     print('表格总行数：%d'%sheet1.nrows)
     print('表格第一行(类型+数据)',sheet1.row(0))
     print('表格第一行（类型+数据）',sheet1.row_slice(0))
     print('表格第一行（数据）',sheet1.row_values(0,start_colx=0,end_colx=None))
     print('表格第一行（类型）',sheet1.row_types(0,start_colx=0,end_colx=1))
     print('表格第一行有%d个有效数据'%sheet1.row_len(0))
     
     ##sheet页列操作         
     ##数据类型 0 empty,1 string,2 number, 3 date,4 boolean, 5 error
     print('表格总行数：%d'%sheet1.ncols)
     print('表格第一列（类型+数据)',sheet1.col(0))
     print('表格第一列（类型+数据)',sheet1.col(0).encode('utf-8'))
     print('表格第一列（类型+数据）',sheet1.col_slice(0))
     print('表格第一列（数据）',sheet1.col_values(0,start_rowx=0,end_rowx=None))
     print('表格第一列（类型）',sheet1.col_types(0,start_rowx=0,end_rowx=1))
     
     ##sheet页单元格操作
     print('表格第一行第一列(类型+数据)',sheet1.cell(0,0))
     print('表格第一行第一列(类型)',sheet1.cell_type(0,0))
     print('表格第一行第一列(数据)',sheet1.cell_value(0,0))
     ##print('表格第一行第一列(数据)',sheet1.cell_xf_index(0,0))  #可能和字体有关
       
     ##元素值在excel中没有改变，但是输出的时候变了
     sheet1.put_cell(rowx=4, colx=1, ctype=2, value=99, xf_index=None) 
          
   except FileNotFoundError:
     print('EXCEL文件%s不存在，请检查python目录下是否存在该文件'%excel_name)

4. xlwt 常规应用

#!/usr/bin/env python 
# -*- coding:utf-8 -*-

import xlwt
import datetime
import os

if __name__ == '__main__':
    # 创建一个workbook 设置编码
    ## 学习参考网址;
    ## https://www.cnblogs.com/python-robot/p/9958352.html
    book = xlwt.Workbook(encoding = 'utf-8')
    sheet = book.add_sheet('test1')
    sheet1 = book.add_sheet('test2')

    ##写入单元格
    sheet.write(0,0,label = 'this is test')
    sheet1.write(0,0,label = 'this is test1')
    book.save('Excel1.xls')
    book.save('Excel2.xls')

    ##设置字体
    style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = 'Times New Roman'
    font.bold = True  # 黑体
    font.underline = True  # 下划线
    font.italic = True  # 斜体字
    style.font = font  # 设定样式
    # worksheet.write(0, 0, 'Unformatted value') # 不带样式的写入
    sheet.write(1, 0, 'Formatted value', style) # 带样式的写入
    book.save('Excel1.xls') # 保存文件

    ##设置高度和宽度
    sheet.write(2, 0, 'with and height')
    sheet.col(0).width = 10000
    tall_style = xlwt.easyxf('font:height 720')  # 36pt
    ##貌似直接设置高度没有效果
    ##参考网址 https://blog.youkuaiyun.com/weixin_42122355/article/details/83536142
    #sheet.row(2).height = 6000
    sheet.row(2).set_style(tall_style)
    book.save('Excel1.xls') # 保存文件

    ##输入一个日期到单元格
    style = xlwt.XFStyle()
    #Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, c
    style.num_format_str = 'M/D/YY'
    sheet.write(3, 0, datetime.datetime.now(),style)
    time_str = '2020/01/13 20:57:01'
    sheet.write(4, 0,  time_str)
    style.num_format_str = 'YYYY/MM/DD [h]:mm:ss'
    time = datetime.datetime.strptime(time_str,'%Y/%m/%d %H:%M:%S')
    sheet.write(5, 0,  time,style)
    book.save('Excel1.xls') # 保存文件

    ##向单元格添加公式
    sheet.write(6, 0, 2)
    sheet.write(6, 1, 3)
    formulate_sum1 = xlwt.Formula('A7 + B7')
    sheet.write(6, 2, formulate_sum1)
    formulate_sum2 = xlwt.Formula('SUM(A7,B7)')
    sheet.write(6, 3, formulate_sum2)
    formulate_multiply = xlwt.Formula('A7 * B7')
    sheet.write(6, 4, formulate_multiply)
    book.save('Excel1.xls') # 保存文件

    ##向单元格添加一个超链接
    Formula1 = xlwt.Formula('HYPERLINK("https://www.baidu.com/";"Baidu")')
    sheet.write(7, 0, Formula1)
    Formula2 = xlwt.Formula('HYPERLINK("#test2!A1";"跳转到test2 sheet")')
    sheet.write(7, 1, Formula2)
    excel2_file = os.path.join(os.getcwd(),'Excel2.xls')
    Formula3 = xlwt.Formula('HYPERLINK("[%s]%s!%s";"跳转到Excel2 test1 sheet")'%(excel2_file,'test1','A1'))
    sheet.write(7, 2, Formula3)
    book.save('Excel1.xls')

    ##合并行和列
    #print(help(sheet.write_merge))
    sheet.write_merge(8, 8, 0, 2, 'First col Merge')
    font = xlwt.Font()
    font.bold = True
    style = xlwt.XFStyle()
    sheet.write_merge(9, 10, 0, 2, 'First row Merge',style)
    book.save('Excel1.xls')

    ##设置单元格内容的对其方式:
    alignment = xlwt.Alignment()
    ## May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    ## May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER
    style = xlwt.XFStyle()
    style.alignment = alignment
    sheet.write(11, 0, 'Cell Contents', style)
    book.save('Excel1.xls')

    ##s设置边框
    borders = xlwt.Borders()
    print(dir(borders))
    ## DASHED虚线    NO_LINE没有    THIN实线
    borders.left = xlwt.Borders.DASHED
    borders.right = xlwt.Borders.DASHED
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    ## # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
    borders.left_colour = 2
    borders.right_colour = 0x10
    borders.top_colour = 0x20
    borders.bottom_colour = 0x30
    style = xlwt.XFStyle()
    style.borders = borders
    sheet.write(12, 0, 'Cell Contents', style)
    book.save('Excel1.xls')

    ## 设置背景颜色
    pattern = xlwt.Pattern()
    # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 5
    style = xlwt.XFStyle()
    style.pattern = pattern
    sheet.write(13, 0, 'Cell Contents', style)
    book.save('Excel1.xls')

  运行结果如下所示：

this is test
Formatted value
with and height
2020/1/13
2020/01/13 20:57:01
2020/01/13 36:57:01
2	3	5	5	6
Baidu	跳转到test2 sheet	跳转到Excel2 test1 sheet
First col Merge
First row Merge
Cell Contents
Cell Contents
Cell Contents