1 import xlrd
  2 import xlwt
  3 from datetime import date,datetime
  4 
  5 def read_excel():
  6     """读取excel"""
  7 
  8     # 打开文件
  9     workbook = xlrd.open_workbook(r"D:\python_file\request_files\excelfile.xlsx", formatting_info=False)
 10     # 获取所有的sheet
 11     print("所有的工作表：",workbook.sheet_names())
 12     sheet1 = workbook.sheet_names()[0]
 13 
 14     # 根据sheet索引或者名称获取sheet内容
 15     sheet1 = workbook.sheet_by_index(0)
 16     sheet1 = workbook.sheet_by_name("Sheet1")
 17 
 18     # 打印出所有合并的单元格
 19     print(sheet1.merged_cells)
 20     for (row,row_range,col,col_range) in sheet1.merged_cells:
 21         print(sheet1.cell_value(row,col))
 22 
 23     # sheet1的名称、行数、列数
 24     print("工作表名称：%s，行数：%d，列数：%d" % (sheet1.name, sheet1.nrows, sheet1.ncols))
 25 
 26     # 获取整行和整列的值
 27     row = sheet1.row_values(1)
 28     col = sheet1.col_values(4)
 29     print("第2行的值：%s" % row)
 30     print("第5列的值：%s" % col)
 31 
 32     # 获取单元格的内容
 33     print("第一行第一列：%s" % sheet1.cell(0,0).value)
 34     print("第一行第二列：%s" % sheet1.cell_value(0,1))
 35     print("第一行第三列：%s" % sheet1.row(0)[2])
 36 
 37     # 获取单元格内容的数据类型
 38     # 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
 39     print("第二行第三列的数据类型：%s" % sheet1.cell(3,2).ctype)
 40 
 41     # 判断ctype类型是否等于data，如果等于，则用时间格式处理
 42     if sheet1.cell(3,2).ctype == 3:
 43         data_value = xlrd.xldate_as_tuple(sheet1.cell_value(3, 2),workbook.datemode)
 44         print(data_value)
 45         print(date(*data_value[:3]))
 46         print(date(*data_value[:3]).strftime("%Y\%m\%d"))
 47 
 48 
 49 def set_style(name,height,bold=False):
 50     """设置单元格样式"""
 51     style = xlwt.XFStyle()    # 初始化样式
 52 
 53     font = xlwt.Font()    # 为样式创建字体
 54     font.name = name    # 设置字体名字对应系统内字体
 55     font.bold = bold    # 是否加粗
 56     font.color_index = 5    # 设置字体颜色
 57     font.height = height    # 设置字体大小
 58 
 59     # 设置边框的大小
 60     borders = xlwt.Borders()
 61     borders.left = 6
 62     borders.right = 6
 63     borders.top = 6
 64     borders.bottom = 6
 65 
 66     style.font = font    # 为样式设置字体
 67     style.borders = borders
 68 
 69     return style
 70 
 71 
 72 def write_excel():
 73     """写入excel"""
 74 
 75     writeexcel = xlwt.Workbook()    # 创建工作表
 76     sheet1 = writeexcel.add_sheet(u"Sheet1", cell_overwrite_ok = True)    # 创建sheet
 77 
 78     row0 = ["编号", "姓名", "性别", "年龄", "生日", "学历"]
 79     num = [1, 2, 3, 4, 5, 6, 7, 8]
 80     column0 = ["a1", "a2", "a3", "a4", "a5", "a6", "a7", "a8"]
 81     education = ["小学", "初中", "高中", "大学"]
 82 
 83     # 生成合并单元格
 84     i,j = 1,0
 85     while i < 2*len(education) and j < len(education):
 86         sheet1.write_merge(i, i+1, 5, 5, education[j], set_style("Arial", 200, True))
 87         i += 2
 88         j += 1
 89 
 90     # 生成第一行
 91     for i in range(0, 6):
 92         sheet1.write(0, i, row0[i])
 93 
 94     # 生成前两列
 95     for i in range(1, 9):
 96         sheet1.write(i, 0, i)
 97         sheet1.write(i, 1, "a1")
 98 
 99     # 添加超链接
100     n = "HYPERLINK"
101     sheet1.write_merge(9,9,0,5,xlwt.Formula(n + \'("https://www.baidu.com")\'))
102 
103     # 保存文件
104     writeexcel.save("demo.xls")
105 
106 
107 if __name__ == "__main__":
108     read_excel()
109     write_excel()