python读取excel文件
1、pandas 读excel练习
因为要真理一些excel中的工作内容,所以想用python脚本完成一下。
# -*- coding : UTF-8 -*-
# @file : read_excel.py
# @Time : 2022/1/14 0014 23:44
# @Author : wmz
import pandas as pd
if __name__ == "__main__":
excel_path = r"E:\学习笔记\xxxx.xlsx"
df = pd.read_excel(excel_path, sheet_name='daily updates 1', usecols=[0,1,2,3,4,5])
data = df.values
print(data)
2、测试程序:
# -*- coding : UTF-8 -*-
# @file : read_excel.py
# @Time : 2022-01-15 20:55
# @Author : wmz
import pandas as pd
import os
def getFiles(path, suffix):
return [os.path.join(root, file) for root, dirs, files in os.walk(path) for file in files if file.endswith(suffix)]
if __name__ == "__main__":
excel_path = r"D:\Work\xxx\2021年\xxx.xlsx"
df = pd.read_excel(excel_path, sheet_name='daily updates 1', usecols=[0,1,2,3,4,5])
data = df.values
print(data)
print("*"*20)
# 行数 (不包含表头,且一下均如此)
print(len(df.index.values))
# 行索引
print(df.index.values)
# 列数
print(len(df.columns.values))
# 列索引
print(df.columns.values)
print("*" * 20)
# 表示第0行数据
data = df.loc[0].values
print('第0行数据: \n', data)
# 读取多行数据(这里是第1行和第2行)
data = df.loc[[1, 2]].values
print('第1行和第2行数据: \n', data)
print("*" * 20)
# 读第1列数据
data = df.iloc[:, 1].values
print('第1列数据: \n', data)
# 读取多列数据(这里是第1列和第2列)
data = df.iloc[:, [1, 2]].values
print('第1列和第2列数据: \n', data)
print("*" * 20)
# 读取指定单元格数据(这里是第1行第一列数据)
data = df.iloc[1, 2]
print(data)
# 读取多行多列数据(第1,2行1,2列的数据)
data = df.iloc[[1, 2], [1, 2]].values
print(data)
3、实际使用
# -*- coding : UTF-8 -*-
# @file : weekly_report.py
# @Time : 2022-01-15 21:33
# @Author : wmz
import pandas as pd
import os
import numpy as np
def getFiles(path, suffix):
return [os.path.join(root, file) for root, dirs, files in os.walk(path) for file in files if file.endswith(suffix)]
if __name__ == "__main__":
# 按行读可以避免出现合并行,按列读可以保证按天前进
excel_path = r"D:\Work\周报\2021年"
output_file = "weekly.txt"
excel_list = getFiles(excel_path, ".xlsx")
# df = pd.read_excel(excel_file, sheet_name='daily updates 1')
# 周报表格存储:第一行是标题,获取数据的实际第一行是第二行,索引0
# 周报的data索引2,4行分别对应上午和下午,索引列1至7对应周一至周日
# 索引行6列1对应 总结
# 读取多行多列数据(第1,2行1,2列的数据)
file_lineinfo = open(output_file, 'w', encoding='utf-8')
weeks = ["MON", "TUE", "WEN", "THU", "FRI", "SAT", "SUN"]
for file in excel_list:
filename = file.split("\\")[-1].split(".")[0]
print(filename)
line_info = [filename, "\n"]
file_lineinfo.writelines(line_info)
df = pd.read_excel(file, sheet_name='daily updates 1')
cols = len(df.columns.values)
print(cols)
for col in range(1, cols):
# 日期
line_info = [weeks[col - 1], "\n"]
file_lineinfo.writelines(line_info)
data = df.iloc[2, [col]].values
print(data)
if str(data) == "[nan]":
continue
line_info = data
file_lineinfo.writelines(line_info)
line_info = ["\n"]
file_lineinfo.writelines(line_info)
data = df.iloc[4, [col]].values
print(data)
line_info = data
file_lineinfo.writelines(line_info)
line_info = ["\n"]
file_lineinfo.writelines(line_info)
# 总结
line_info = ["总结", "\n"]
file_lineinfo.writelines(line_info)
data = df.iloc[6, 1]
print(data)
if str(data) == "nan":
continue
line_info = data
file_lineinfo.writelines(line_info)
file_lineinfo.close()