在进行日常的数据分析统计时,很多时候我们需要将不同数据写入到同一个Excel文件的不同工作簿或同一个工作簿中。本文主要介绍 Python 如何将多个 DataFrame 写入同一个 Excel 不同或同一个工作簿中。
将多个 DataFrame 写入到 Excel 的不同工作簿中
实现代码如下:
# -*- coding: utf-8 -*-import pandas as pdimport inspect class ExcelWriter: def __init__(self, filepath, student_data, performance_data): self.writer = pd.ExcelWriter(filepath) self.student_data = student_data self.performance_data = performance_data @staticmethod def save_execl(writer, sheet_name, header, data, startcol=0, startrow=0): """ :param writer: pd.ExcelWriter(filepath) 实例 :param sheet_name: sheet页 :param header: 字段表头 :param data: 数据 list :param startcol: 起始列 :param startrow: 起始行 :return: """ header = header.split(",") df = pd.DataFrame(data) df.to_excel(writer, sheet_name=sheet_name, header=header, index=False, startcol=startcol, startrow=startrow) writer.save() def student_information(self): """ 将考生信息写入'考生信息表' :param student_data: :return: """ header = "学号,姓名,性别,年龄" ExcelWriter.save_execl(self.writer,"考生信息表", header, self.student_data) def performance_information(self): """ 将考生成绩写入'成绩信息表' :param performance_data: :return: """ header = "学号,语文,数学,体育" ExcelWriter.save_execl(self.writer,"成绩信息表", header, self.performance_data) def run_all(self): for function in inspect.getmembers(self, predicate=inspect.ismethod): if function[0] not in ["__init__","run_all"] : function[1]() if __name__ == '__main__': student_data = [["9527", "张三", "男", 27]] performance_data = [["9527", 77, 88, 99]] excelWriter = ExcelWriter("info.xlsx", student_data, performance_data) excelWriter.run_all()
输出 info.xlsx 如下:
Python如何将多个 DataFrame 写入到 Excel 的相同工作簿中
实现代码如下:
# -*- coding: utf-8 -*-import pandas as pdimport inspectclass ExcelWriter: def __init__(self, filepath, student_data, performance_data): self.writer = pd.ExcelWriter(filepath) self.student_data = student_data self.performance_data = performance_data @staticmethod def save_execl(writer, sheet_name, header, data, startcol=0, startrow=0): """ :param writer: pd.ExcelWriter(filepath) 实例 :param sheet_name: sheet页 :param header: 字段表头 :param data: 数据 list :param startcol: 起始列 :param startrow: 起始行 :return: """ header = header.split(",") df = pd.DataFrame(data) df.to_excel(writer, sheet_name=sheet_name, header=header, index=False, startcol=startcol, startrow=startrow) writer.save() def student_with_performance_information(self): """ 考生成绩信息表 :param student_data: :param performance_data: :return: """ header = "学号,姓名,性别,年龄" ExcelWriter.save_execl(self.writer, "考生成绩信息表", header, self.student_data) header = "学号,语文,数学,体育" ExcelWriter.save_execl(self.writer,"考生成绩信息表", header, self.performance_data, startrow=len(self.student_data) + 2) def run_all(self): for function in inspect.getmembers(self, predicate=inspect.ismethod): if function[0] not in ["__init__","run_all"] : function[1]()if __name__ == '__main__': student_data = [["9527", "张三", "男", 27]] performance_data = [["9527", 77, 88, 99]] excelWriter = ExcelWriter("info.xlsx", student_data, performance_data) excelWriter.run_all()
输出 info.xlsx 如下: