python3读写excel文件_python3对excel文件读写操作

1 importxlrd2 importxlwt3 from datetime importdate,datetime4

5 defread_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) insheet1.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 =borders68

69 returnstyle70

71

72 defwrite_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,085 while i < 2*len(education) and j

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