上一篇文章写了怎么自动化写一个月报，其中有很多很好用的函数组件，都被我封装为了函数，功能很好用。下面一一介绍：

1.添加汇总函数

输入一个pandas的数据框，就会返回一个加了汇总行的数据框。

def add_summary_row(df):
    numeric_df = df.select_dtypes(include=['number'])
    summary = numeric_df.sum()
    
    for col in df.columns.difference(numeric_df.columns):
        summary[col] = None
        
    result_df = pd.concat([df, pd.DataFrame(summary).T])
    result_df.index = list(range(len(df.index))) + ['汇总']
 
    return result_df

 当然，全是数值型数据的情况下可以简化为：

df.groupby('资产类别名称').sum(numeric_only=True).T.assign(汇总=lambda x:x.sum(1)).T

---

2.锚点查找函数

例如我要找流动资产合计这一项，我可以用固定的位置来找，比如财务那边总把流动资产合计的值写在C26这个格子里面。

 但这种固定位置的查找可能有时候会有问题，比如财务那边突然需要加个标题，把资产负债表整体向下挪动了一行....那么C26这个位置就不对了。

所以绝对位置不准确，那我们就应该用相对位置，例如我发现我需要的这个值总是在‘流动资产合计’这个格子的右边两列的位置。那我先查找‘流动资产合计’这一项，然后右移动两格就是我需要的值了。

我称这种查找为锚定查找，找一个锚点，然后偏移找到自己需要的附近的值。这种相对查找的方法比绝对查找的方法出问题的可能性会小一点。

定义一个函数来进行锚点查找：

#资产负债表
wb = xlrd.open_workbook('***********2023年7月报表20230731.xls')
sheet = wb.sheet_by_index(0)   #wb.sheet_by_name('资产负债表')
def find_and_offset_xlrd(sheet, target_value, offset_row, offset_column):
    for row in range(sheet.nrows):
        for col in range(sheet.ncols):
            if str(sheet.cell(row, col).value).replace(' ','') == target_value:
                target_cell_value = sheet.cell(row + offset_row, col + offset_column).value
                return target_cell_value
资产总计 = find_and_offset_xlrd(sheet, '资产总计', 0, 2)/10000

这个函数只针对xls文件，xlsx文件可以用下面的这个更高的函数：

wb = openpyxl.load_workbook('../周报/周报.xlsx',data_only=True)
ws = wb['汇总2']

def find_and_offset_openpyxl(sheet, target_value, offset_row, offset_column, start_row=1, end_row=None, start_col=1, end_col=None):
    # If end_row or end_col is not specified, search until the last row or column
    if end_row is None:
        end_row = sheet.max_row
    if end_col is None:
        end_col = sheet.max_column

    for row in sheet.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col):
        for cell in row:
            if str(cell.value).replace(' ', '') == target_value:
                target_cell = sheet.cell(row=cell.row+offset_row, column=cell.column+offset_column)
                return target_cell.value

 使用样例：

物贸营收=find_and_offset_openpyxl(ws, '物贸',1, 0,start_col=1, end_col=6)
物贸合同=find_and_offset_openpyxl(ws, '物贸',1, 0,start_col=7, end_col=12)

它可以限制查找范围，免得重复锚点造成混乱。

（更新）

查找excel包含特定内容的单元格位置，找到锚点，然后基于锚点获取数据和修改数据：

def lookup_cell(ws,range_quyu,txt=daterange):
    cell_range = ws[range_quyu]#['P1:AF6']
    for row in cell_range:
        for cell in row:
            if cell.value and daterange in str(cell.value):
                print(f"匹配的单元格：{(cell_name:=cell.coordinate)}, 值为: {cell.value}")
    return cell_name

使用样例：

wb = openpyxl.load_workbook('周报.xlsx',data_only=True)
ws = wb['汇总1']
information_cell=lookup_cell(ws,'A1:Z80',txt=daterange)
information_row=int(re.search(r'\d+', information_cell).group())

  获取和修改函数：

def change_offset(ws,cell_name,offset_row,offset_column,new_value):
    offset_cell = ws[cell_name].offset(row=offset_row, column=offset_column)
    offset_cell.value=new_value
def get_offset(ws,cell_name,offset_row,offset_column):
    offset_cell = ws[cell_name].offset(row=offset_row, column=offset_column)
    return offset_cell.value

使用样例：（写入excel公式）

change_offset(ws,cell_name,2,0,f'=(SUMIFS(K{index1}:K{index2},M{index1}:M{index2},"*集采*"))/10000')
change_offset(ws,cell_name,3,1,f'=(SUMIFS(H{index1}:H{index2},M{index1}:M{index2},"*物贸*"))/10000')
营收=[get_offset(ws,cell_name,3,1),get_offset(ws,cell_name,2,1),get_offset(ws,cell_name,4,1)]

合并上述所有的功能的一个类：

class WorksheetHandler:
    def __init__(self, ws):
        self.ws = ws
        self.range = self.set_default_range()
        self.found_cells = None

    def set_default_range(self):
        max_row = self.ws.max_row  ; max_col = self.ws.max_column
        start_cell = 'A1' ; end_cell = f'{chr(max_col + 64)}{max_row}'
        self.range = self.ws[f'{start_cell}:{end_cell}']
        return self.range

    def set_range(self, input_range=None):
        if input_range is None:
            return self.set_default_range()
        if isinstance(input_range, list):
            start_row, start_col = input_range[0]
            end_row, end_col = input_range[1]
            start_cell = f'{chr(start_col + 65)}{start_row + 1}'
            end_cell = f'{chr(end_col + 65)}{end_row + 1}'
            self.range = self.ws[f'{start_cell}:{end_cell}']
        elif isinstance(input_range, str):
            self.range = self.ws[input_range]
        else:
            raise ValueError("无效的输入参数")
        return self.range

    def find(self, value):
        found_cells = []
        for row in self.range:
            for cell in row:
                if str(value) in str(cell.value):
                    print(f"匹配的单元格：{(cell_name:=cell.coordinate)}, 值为: {cell.value}")
                    found_cells.append(cell.coordinate)
        self.found_cells = found_cells
        return found_cells if found_cells else None


    def offset_find(self, value, row_offset, col_offset):
        found_cells = self.find(value)
        offset_cell_values=[]
        for found_cell in found_cells:
            offset_cell=self.ws[found_cell].offset(row=row_offset, column=col_offset)
            offset_cell_values.append(offset_cell.value)
        return offset_cell_values
    
    def offset_change(self, value, row_offset, col_offset,new_value=None):
        found_cells = self.find(value)
        #found_cells = self.found_cells
        for found_cell in found_cells:
            offset_cell=self.ws[found_cell].offset(row=row_offset, column=col_offset)
            offset_cell.value=new_value
            print(f"{offset_cell.coordinate}修改成功")

使用样例：

check_ws=WorksheetHandler(ws)
#check_ws.set_range()
check_ws.set_range('A1:I60')
check_ws.find('物贸')

 

---

check_ws.offset_find('物贸',1,0)

 

check_ws.offset_change('物贸',1,0,50)

 

它是直接原表上修改的，然后储存就行

wb.save('hh.xlsx')

---

 

3.修改文字段落样式函数

def set_style(paragraphs,style=u'仿宋_GB2312',size=16):
    for run in paragraphs.runs:
         = style
        run.font.size = Pt(size)
        r = run._element.rPr.rFonts
        r.set(qn("w:eastAsia"),style)

这个函数的功能是修改这个段落的字体和大小。因为我发现每次代码修改了word里面的东西后，它就会默认使用微软体文字.....使用我们需要把内容变成我们要的模板格式。我们需要这个函数，无论替换了什么内容，都需要它来变一下格式 擦屁股。

使用样例：

for i,paragraph in enumerate(doc.paragraphs):
    if '月，**公司资产总计约'in paragraph.text:
        paragraph.text =txt[0]
        print('0')   ; set_style(paragraph)

---

4.文字查找替换函数

#查找替换
def docx_replace(old_text, new_text, doc):
    for paragraph in doc.paragraphs:
        if old_text in paragraph.text:
            paragraph.text = paragraph.text.replace(old_text, new_text)
            set_style(paragraph)
docx_replace(f"截至{year}年{month-1}月", f"截至{year}年{month}月", doc)

这个函数类似于word里面的替换功能，把你要查找的文字，换为其他文字。

---

5.表格内容替换

我们对word表里面的数据也要进行修改，

直接替换里面的文字是不行的，还是因为样式会变成默认样式，和领导要求的模板不一样...

所以也需要设置一下。

定义一个表格替换函数，传入你要替换的格子，替换的文本，以及字体样式，大小，居中什么的

def set_cell_text(cell, text, font_name='仿宋_GB2312', font_size=12, alignment=WD_PARAGRAPH_ALIGNMENT.CENTER):
    cell.text = text
    for paragraph in cell.paragraphs:
        paragraph.alignment = alignment
        set_style(paragraph,font_name,font_size)

 使用样例：

table = doc.tables[0]
set_cell_text(table.cell(2, 3), str(df.loc['房屋建筑物','资产数量']), '仿宋_GB2312', 12)

这样相当于把这个表第3行第4列的格子里面的数据进行了替换，设置为仿宋体，12号，居中。