前言
之前写过一篇通过python提取excel 中数据并转换为json格式数据(python 读取excel并输出为json),然后有位小伙伴留言说自己有一个更为复杂的excel需要取数,存在多个sheet需要进行合并取数。之前的这篇文章并不能满足,于是我在之前的基础上进行对应的改造,通过pandas这个利器来处理数据。
excel取数工具类
还是使用之前的openpyxl来取数,相比之前文章有些微的变化就是添加了指定读取那个sheets。当然如果你想通过pandas去取excel中的数据也是可以OK的。
from openpyxl import load_workbook
# 文件路径
file = "D:\demo.xlsx"
# 工具类,用于读取excele中的值
class ExcelUtils:
def __init__(self, i):
self.file = file
self.wb = load_workbook(self.file)
sheets = self.wb.get_sheet_names()
self.ws = self.wb[sheets[i]]
# 行数
def get_rows(self):
rows = self.ws.max_row
return rows
# 列数
def get_clos(self):
clo = self.ws.max_column
return clo
# 获取所有sheet
def get_sheets(self):
sheets = self.wb.get_sheet_names()
return sheets
def get_sheets_num(self):
sheets_num = len(self.wb.get_sheet_names())
return sheets_num
# 获取值
def get_cell_value(self, row, column):
cell_value = self.ws.cell(row=row, column=column).value
return cell_value
将数据取出
比如我们这里有一个excel表格,现将这个数据取出来并转换为dict
# 将excel中的值转换为dict
def to_dict(sheet):
excel_utils = ExcelUtils(sheet)
excel_list = []
clo = excel_utils.get_clos()
# 遍历excel中的值存入字典中
for j in range(2, excel_utils.get_rows() + 1):
excel_dict = {}
for i in range(1, clo + 1):
dict_key = excel_utils.get_cell_value(1, i)
dict_value = excel_utils.get_cell_value(j, i)
excel_dict[dict_key] = dict_value
excel_list.append(excel_dict)
return excel_list
# 如果存在多个sheet,则遍历所有的sheet,转换为dict
def sheet_json():
excel_utils = ExcelUtils(0)
list_json = []
sheets_num = excel_utils.get_sheets_num()
for i in range(0, sheets_num):
json_data = to_dict(i)
list_json.append(json_data)
return list_json
取出后的数据
pandas
通过上述步骤转换为dict 后,我们先用pands将其转换为二维数组,是不是和我们的excel显示的数据一样了。(这里你也可以通过pandas提供的方法直接通过sheet name直接取数据)
最后我们通过pandas的merge方法去合并ID相同的数据,然后将数据重新输出到一个新的excel
if __name__ == "__main__":
compare_json = sheet_json()
# 使用pandas进行数据处理
# 先将第一个sheet中的值转为DataFrame
data = pd.DataFrame(compare_json[0])
for i in range(1, 3):
# 从第二个sheet开始遍历
df = pd.DataFrame(compare_json[i])
# 循环将sheet中的值merge到上一个sheet中
data = pd.merge(data, df, on='ID')
data.to_excel("D:\demo_pands.xlsx")