import xlrd from xlrd import open_workbook from xlutils.copy import copy from openpyxl import load_workbook import random import string def read_excel(): # 打开文件 workbook = xlrd.open_workbook(r'订单导入模板.xls') # 获取所有sheet print(workbook.sheet_names()) sheet2_name = workbook.sheet_names()[0] print(sheet2_name) # 根据sheet索引或者名称获取sheet内容 sheet1 = workbook.sheet_by_index(0) print('sheet1==',sheet1) print(sheet1.row_values(0)) #sheet2 = workbook.sheet_by_name('Sheet1') # sheet的名称，行数，列数 #print(sheet1.name, sheet2.nrows, sheet2.ncols) # 获取整行和整列的值（数组） #rows = sheet2.row_values(3) # 获取第四行内容 #cols = sheet2.col_values(2) # 获取第三列内容 #print(rows) #print(cols) # print(sheet2.cell(1, 0).value) # print(sheet2.cell_value(1, 0)) # print(sheet2.row(1)[0].value) # 获取单元格内容的数据类型 print(sheet1.cell(0, 0).ctype) # 获取（2,1）单元格的类型，2行第1列 print(sheet1.cell(0, 1).value) print(sheet1.cell(0, 1).ctype, sheet1.cell(0, 2).value) # 获取数据类型和里面的值 # print(sheet1.cell(1, 2).ctype, sheet1.cell(1, 2).value) # 直接获取日期数据，会变成其他数据格式 # print(sheet1.cell(1, 3).ctype, sheet1.cell(1, 3).value) # 数据类型为0，值为空，就没有任何数据 def write_excel(): # 读取一个excel文件 rd_excel = open_workbook(r'订单导入模板.xls') # 将xlrd的对象转化为xlwt的对象 new_excel = copy(rd_excel) # 获得要操作的sheet table = new_excel.get_sheet(0) print(table) # 准备要输入的内容 values = ['小明','小李','Lily','Toms','小王'] # xlwt对象的写方法，参数分别是行、列、值 table.write(1, 0, values[0]) table.write(2, 0, values[1]) table.write(3, 0, values[2]) table.write(4, 0, values[3]) table.write(5, 0, values[4]) # xlwt对象的保存方法，这时便覆盖掉了原来的excel new_excel.save(r'订单导入模板1.xls') def read_xlsx(): pass """ 导入订单创建数据1500条 """ def write_to_xlsx(): wb = load_workbook(r'订单导入模板.xlsx') # 生成一个已存在的wookbook对象 wb1 = wb.active # 激活sheet i = 2 nember = 123456 code = 1001 name = '青汽' begin_name= "北京" end_name = "云南" while i <=1500: wb1.cell(i, 1, nember) wb1.cell(i, 2, code) wb1.cell(i, 3, name) wb1.cell(i, 4, begin_name) wb1.cell(i, 5, end_name) nember += 1 code += 1 i +=1 wb.save(r'订单导入模板.xlsx') # 保存 """ 发车计划导入模板1500条 """ def write_to_xlsx2(): wb = load_workbook(r'发车计划导入模板.xlsx') # 生成一个已存在的wookbook对象 wb1 = wb.active # 激活sheet i = 2 banxian_name = '班线' agent = "青汽" go_tme = "2019/01/09 17:33:00" vehicle = "B7T5T8" phone = "18;185" first_station = "北京" ending_station = "西藏" v = "1800" while i <=1500: wb1.cell(i, 1, banxian_name + str(i)) wb1.cell(i, 2, agent) wb1.cell(i, 3, go_tme) wb1.cell(i, 4, vehicle) wb1.cell(i, 5, phone) wb1.cell(i,6,first_station) wb1.cell(i,7,ending_station) wb1.cell(i,8,v) i +=1 wb.save(r'发车计划导入模板.xlsx') # 保存 def random_str(): ran_str = '班线'.join(random.sample(string.ascii_letters + string.digits, 2)) print(ran_str) #read_excel() #write_excel() #write_to_xlsx() #random_str() write_to_xlsx2()
注意:pyhton中操作xls和xlsx是不一样滴