python:openpyxl实现表格内容的读取和精准写入

---

需求

再想想，是读取一份表格内容对应数据填入到另一份表格数据，实现精准写入，还有一个注意点就是表格公司不能破坏，写入的数据可以自动被表格内的公式计算

---

思路

对于表格的操作，我第一个想到的就是pandas，但是对于这个情景仔细一下想，精准写入会不会破坏表格内的公式，能实现很精准的写入吗，我感觉应该会很麻烦。这里就需要引入另一个模块openpyxl，也是一个非常强大的表格处理，网上有很多文章的介绍

首先那些是变量和不变量，2个文件肯定是不变，变量是读取表格日期，y1,y2,写入表格的sheet1，sheet2(也就是读取好多表格写入一个表格中多个sheet表)，数据的存储我首先想到的就是字典，他的键值对形式可以取值非常方便

代码实现

from openpyxl import load_workbook
from xlrd import xldate_as_datetime, xldate_as_tuple
import re

class HandleExecl(object):

    def __init__(self,date,file,sheet_name,write_file):
        self.date = date # 日期
        self.file = file # 读取文件
        self.sheet_name = sheet_name #写入文件sheet名
        self.write_file = write_file # 写入文件


    def read_execl(self):
        """
        读表
        :return: list
        """
        wb = load_workbook(self.file, data_only=True) #  data_only=True计算表格公式的结果
        ws = wb.active
        row_dict = {}
        count_row = 2
        # 行判断 （构成字典，key是日期，value日期所在的行数）
        for row in ws.iter_rows(max_row=34):
            try:
                date_time = xldate_as_datetime(row[0].value,0).strftime('%m/%d').replace('/','月') + "日"
                count_row += 1
                row_dict[date_time] = count_row
            except:
                pass
        # print(row_dict)
        row_num = row_dict[self.date]
        # 列判断 （也是构成字典，key是列名y3,value是一个区间列表，表示y3在多少列到多少列）
        count_column = 1
        column_list = []
        for column in ws.iter_cols():
            if count_column > 2:
                    column_list.append(str(column[0].value) + "-" + str(count_column))
            count_column += 1
        column_list = [column_list[i:i + 16] for i in range(0, len(column_list), 16)]
        column_dict = {}
        for j in column_list:
            if len(j) == 16:
                column_dict[j[0].split('-')[0]] = [j[0].split('-')[1],j[-1].split('-')[1]]
        # 查值 （行和列都确定好了，单元格内容也能动态表示，也是构成字典，最后组成列表）
        result_list = []
        for key in column_dict:
            dicts = {
                'shop_name': key
            }
            dicts["data"] = {}
            for i in range(int(column_dict[key][0]), int(column_dict[key][1])+1):

                dicts["data"][ws.cell(2, i).value] = ws.cell(row_num, i).value
            result_list.append(dicts)

        wb.close()

        return result_list

    def read_info(self):
        """
        写入表格店名对应行数
        :return: dict
        """
        wb = load_workbook(self.write_file)
        # 判断写入文件中哪一个sheet表 （确定写入表格日期是在哪一行，构成字典，key是日期，value日期所在的行数）
        if self.sheet_name == "sheet1":
            ws = wb[self.sheet_name]
            count_row = 5
            row_dicts = {}
            for row in ws.iter_rows():
                try:
                    # 关键字判断
                    if "sheet1" in str(row[1].value) and "=" not in str(row[1].value):
                        row_dicts[row[1].value] = count_row
                        count_row += 1
                except:
                    pass
            wb.close()
            return row_dicts
        elif self.sheet_name == "sheet2":
            ws = wb[self.sheet_name]
            count_row = 5
            row_dicts = {}
            for row in ws.iter_rows():
                try:
                    if "sheet2" in str(row[1].value) and "=" not in str(row[1].value):
                        row_dicts[row[1].value] = count_row
                        count_row += 1
                except:
                    pass
            wb.close()
            return row_dicts
        elif self.sheet_name == "sheet3":
            ws = wb[self.sheet_name]
            count_row = 5
            row_dicts = {}
            for row in ws.iter_rows():
                try:
                    if "sheet3" in str(row[1].value) and "=" not in str(row[1].value):
                        row_dicts[row[1].value] = count_row
                        count_row += 1
                except:
                    pass
            wb.close()
            return row_dicts

    def write_execl(self):
        """
        写入表格
        :return:
        """
        wb = load_workbook(self.write_file)
        ws = wb[self.sheet_name]
        # 接收2个方法返回的值
        data_list = self.read_execl() 
        write_dict = self.read_info() 
        # print(data_list)
        # print(write_dict)
        # 字典的遍历，取值操作
        for i in write_dict.keys():
            shop_name = i
            row = write_dict[shop_name]
            for j in data_list:
                if shop_name == j["shop_name"]:
                    # print(shop_name,row,j["data"]["转化数"])
                    # 异常判断，可能读取表格内容为控制，一定要是float，确保它能参与表格公式的计算
                    try:
                        ws.cell(row, 6, value=float(j["data"]["转化数"]))
                        ws.cell(row, 7, value=float(j["data"]["预计实际成交笔数"]))
                        ws.cell(row, 8, value=float(j["data"]["净利润目标"]))
                        ws.cell(row, 10, value=float(j["data"]["整体花费"]))
                        ws.cell(row, 11, value=float(j["data"]["实际成交金额"]))
                        ws.cell(row, 12, value=float(j["data"]["店铺预估净利润"]))
                    except:
                        pass
        wb.save(self.write_file)
        print("完成")
    
    # 方法跟上面一样，不一一介绍了
    def read_all_data(self):
        """
        合计
        :return:
        """
        wb = load_workbook(file,data_only=True)
        ws = wb.active
        count_row = 0
        dicts = {}
        # 行判断
        for row in ws.iter_rows():
            count_row += 1
            if row[0].value == "合计":
                dicts[row[0].value] = count_row
        row_num = dicts["合计"]
        # print(row_num)
        # 列判断
        count_column = 1
        column_list = []
        for column in ws.iter_cols():
            if count_column > 2:
                column_list.append(str(column[0].value) + "-" + str(count_column))
            count_column += 1
        column_list = [column_list[i:i + 16] for i in range(0, len(column_list), 16)]
        column_dict = {}
        for j in column_list:
            if len(j) == 16:
                column_dict[j[0].split('-')[0]] = [j[0].split('-')[1], j[-1].split('-')[1]]
        # 查值
        result_list = []
        for key in column_dict:
            dicts = {
                'shop_name': key
            }
            dicts["data"] = {}
            for i in range(int(column_dict[key][0]), int(column_dict[key][1]) + 1):
                dicts["data"][ws.cell(2, i).value] = ws.cell(row_num, i).value
            result_list.append(dicts)

        wb.close()
        return result_list

    def write_all_data(self):
        """
        总计写入
        :return:
        """
        wb = load_workbook(self.write_file)
        ws = wb[self.sheet_name]
        data_list = self.read_all_data()
        write_dict = self.read_info()
        # print(data_list)
        # print(write_dict)
        for i in write_dict.keys():
            shop_name = i
            row = write_dict[shop_name]
            for j in data_list:
                if shop_name == j["shop_name"]:
                    # print(shop_name,row,j["data"]["转化数"])
                    try:
                        ws.cell(row, 15, value=float(j["data"]["转化数"]))
                        ws.cell(row, 16, value=float(j["data"]["预计实际成交笔数"]))
                        ws.cell(row, 17, value=float(j["data"]["净利润目标"]))
                        ws.cell(row, 19, value=float(j["data"]["整体花费"]))
                        ws.cell(row, 20, value=float(j["data"]["实际成交金额"]))
                        ws.cell(row, 21, value=float(j["data"]["店铺预估净利润"]))
                    except:
                        pass
        wb.save(self.write_file)
        print("完成")


    def change_date(self,date):
        """
        日期转化
        :param date:
        :return:
        """
        return xldate_as_datetime(date,0).strftime('%m/%d').replace('/','月') + "日"



if __name__ == '__main__':
    date = "12月02日"
    file = "读取文件.xlsx"
    sheet_name = "sheet1"
    write_file = "写入文件.xlsx"
    obj = HandleExecl(date,file,sheet_name,write_file)
    obj.write_execl()
    obj.write_all_data()

总结

通过这次的小需求，再一次体会了字典的方便，Python每一个模块的强大，用对往往事半功倍。上述只是我用到的方法，如有其它好的方法，一起学习交流