将excel中某列数据中，含有指定字符串的记录取出，并开始计算数据，生成新的excel表
Python 一大重要的功能，就是可处理大量数据，那分不开的即是使用Excel表格了，这里我做下学习之后的总结，望对我，及广大同仁们是一个帮助
Python处理Excel数据需要用到2个库：xlwt和xlrd。xlwt库负责将数据导入生成Excel表格文件，而xlrd库则负责将Excel表格中的数据取出来。xlrd库读取Excel的数据也是轻轻松松，先来看下实现代码
原表格简要说明：
# coding = utf-8 # 将excel中某列数据中，含有指定字符串的记录取出，并生成用这个字符串命名的excel文件 import xlrd, xlwt import os, sys # 按项目 导出到新的excel文件 def export_all_excel(): root_dir = '/Users/zhaojs/Downloads' for root, dirs, files in os.walk(root_dir): for file in files: file_xls = os.path.join(root_dir, file) if file_xls.endswith('.xls'): wb = xlrd.open_workbook(file_xls) # 获取列表 sheet = wb.sheet_by_index(0) # 创建写入文件 workbook = xlwt.Workbook(encoding="utf-8") # 总行数 total_rows = sheet.nrows # 总列数 total_cols = sheet.ncols print("行数 : %d 列数 : %d" % (total_rows, total_cols)) # 按项目简称小写 创建sheet 对象 worksheet = workbook.add_sheet('全部') new_data = [ ['作业号', 'CPU/GPU', '项目', '场景文件', '渲染软件', '帧', '提交时间', '完成时间', '层名', '提交账户', '平台', '内存（GB）', '余额消费（RMB）', '完成帧数', '总帧数', '消耗时间（机时)', '实际消费（RMB）', '差值'] ] loop_res = [] for i in range(10, total_rows - 1): # 去除最后一行总计 # 取出第i行 第 13 列的数据 平台名称 # platform_name = sheet.cell_value(i, 13) excel_filename = 'all_projects.xls' outputs = sheet.row_values(i) # 将列表内的值转成字符串 data = [str(z) for z in outputs] if data == '\n': pass else: tmp = ['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''] # 作业号 tmp[0] = data[1] # CPU/GPU || 实际消费 if data[13] == 'gpu': tmp[1] = 'GPU' sum_real_consumption = round(float(data[20]) * 4, 3) tmp[16] = str(sum_real_consumption) else: tmp[1] = 'CPU' sum_real_consumption = round(float(data[20]) * 1.3, 3) tmp[16] = str(sum_real_consumption) # 项目 tmp[2] = data[4][0:3] # 场景文件 tmp[3] = data[4] # 渲染软件 tmp[4] = data[5] # 帧 tmp[5] = data[6] # 提交时间 tmp[6] = data[7] # 完成时间 tmp[7] = data[8] # 层名 tmp[8] = data[10] # 提交账户 tmp[9] = data[12] # 平台 tmp[10] = data[13] # 内存（GB） tmp[11] = data[14] # 余额消费（RMB） tmp[12] = data[15] # 完成帧数 tmp[13] = data[18] # 总帧数 tmp[14] = data[19] # 消耗时间（机时) tmp[15] = data[20] # 差值 计算差值 实际消费 - 余额消费 difference_value = 0 if (sum_real_consumption - float(data[15])) < 0 else int( sum_real_consumption - float(data[15])) tmp[17] = difference_value loop_res.append(tmp) # 形成带表头的列表集 new_data.append(tmp) # 计算汇总数据 sum_prices = 0 # 总消费 sum_finish_frames = 0 # 总完成帧数 sum_frames = 0 # 总帧数之和 sum_times = 0 # 机时之和 sum_real_consumption = 0 # 实际消费 difference_value = 0 # 差值 z = 0 for res in loop_res: # sys.exit() sum_prices += float(res[12]) sum_finish_frames += int(float(res[13])) sum_frames += int(float(res[14])) sum_times += float(res[15]) sum_real_consumption += float(res[16]) difference_value += float(res[17]) z = z + 1 tmp_data = ['', '', '', '', '', '', '', '', '', '', '', '', round(sum_prices, 3), sum_finish_frames, sum_frames, round(sum_times, 3), sum_real_consumption, difference_value] # 合成最终列表 new_data.append(tmp_data) i = 0 for data in new_data: for j in range(len(data)): worksheet.write(i, j, data[j]) i = i + 1 workbook.save('./' + excel_filename) if __name__ == "__main__": # Common_Excel().abc(123) export_all_excel()
总结一下，分为一下几步：
- 首先通过
xlrd库的open_workbook方法打开Excel文件(我这里的test.xls，是已存在数据的excel表格) - 然后通过
sheet_by_index方法获取表 - 然后分别获取表的行数和列数，便于后面循环遍历
- 根据列数和行数，循环遍历，根据某列单元格内的数据，即通过
cell_value方法获取每个单元格中的数据，匹配到我手写的字符串，如符合，则取出该行所有数据，再次循环该行数据，将其写入到新的sheet中
工作表的相关操作
获取一个工作表,有多种方式
# 通过索引
sheet1 = wb.sheets()[0]
sheet1 = wb.sheet_by_index(0)
# 通过名字
sheet1 = wb.sheet_by_name('test')
# 获取所有表名sheet_names = wb.sheet_names()
获取某一行或某一列的所有数据
# 获取行中所有数据，返回结果是一个列表
tabs = sheet1.row_values(rowx=0, start_colx=0, end_colx=None)
# 返回一行一共有多少数据
len_value = sheet1.row_len(rowx=0)
row_values的三个参数分别是：行号、开始的列和结束的列，其中结束的列为None表示获取从开始列到最后的所有数据
类似的还有获取某一列的数据
cols = sheet1.col_values(colx=0, start_rowx=0, end_rowx=None) 处理时间数据
时间数据比较特殊，没发直接通过上面的cell_value方法获取。需要先转换为时间戳，然后再格式化成我们想要的格式。
比如要获取Excel表格中，格式为2019/8/13 20:46:35的时间数据
# 获取时间 time_value = sheet1.cell_value(3, 0) # 获取时间戳 time_stamp = int(xlrd.xldate.xldate_as_datetime(time_value, 0).timestamp()) print(time_stamp) # 格式化日期 time_rel = time.strftime("%Y/%m/%d", time.localtime(time_stamp)) print(time_rel)
基本也是三步走：
- 通过
cell_value方法获取时间值 - 然后通过
xldate_as_datetime方法获取时间戳 - 然后格式化一下