将excel中某列数据中,含有指定字符串的记录取出,并生成用这个字符串命名的txt文件

时间:2023-03-08 16:40:22
Python 一大重要的功能,就是可处理大量数据,那分不开的即是使用Excel表格了,这里我做下学习之后的总结,望对我,及广大同仁们是一个帮助
Python处理Excel数据需要用到2个库:xlwtxlrdxlwt库负责将数据导入生成Excel表格文件,而 xlrd库则负责将Excel表格中的数据取出来。 xlrd 库读取Excel的数据也是轻轻松松,先来看下实现代码
原表格简要说明:
将excel中某列数据中,含有指定字符串的记录取出,并生成用这个字符串命名的txt文件

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

总结一下,分为一下几步:
  1. 首先通过xlrd库的open_workbook方法打开Excel文件(我这里的test.xls,是已存在数据的excel表格)
  2. 然后通过sheet_by_index方法获取表
  3. 然后分别获取表的行数和列数,便于后面循环遍历
  4. 根据列数和行数,循环遍历,根据某列单元格内的数据,即通过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)

基本也是三步走:

  1. 通过cell_value方法获取时间值
  2. 然后通过xldate_as_datetime方法获取时间戳
  3. 然后格式化一下