Python自动化小技巧19——复制excel表放入word

案例背景

依旧是工作需要，文员工作就是这样，写各种报告，周报月报季报半年报。报告需要数据，数据在excel里面，每天都是复制粘贴....贼重复和枯燥，主要还费时间费神。

能自动化的尽量用代码去简化吧。比如我每次周报写在excel里面，然后又要复制到word里面去，还要排版什么的很麻烦。

流程都是固定的，所以代码来封装。下面来看看我这个案例。

---

明确要求

数据肯定是放在excel里面的，先填好，然后按照模板文字套，表里面很多sheet大概是这样：

 

 汇总1和汇总2就是我们填入每周的数据，然后写到后面具体的每周的周报里面去。每周的周报文字都是有模板的。

例如我的文字模板：

（ 打码是怕信息泄露）

下面还有别的栏目，本周工作总结，工作计划什么的，都是一样，在代码里面字符串进行模板化套用，然后对着固定的格子修改就行。

因为不是从0开始写周报，是复制上一期的周报然后把这周的内容进行替换，不用代码手工写周报也是这么一个操作流程。我们用代码只是为了让这个重复的的流程简化而已。

---

代码实现

excel套模板

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt 
import seaborn as sns

plt.rcParams ['font.sans-serif'] ='SimHei'               #显示中文
plt.rcParams ['axes.unicode_minus']=False               #显示负号
import re
import openpyxl
information_row=28
datetime='8.7-8.13'
row_n=17           #周会材料要填的行

初始化一些参数，比如你数据放在excel表的哪一行，这周报日期是从哪一天到哪一天，还有周会材料写在表的第几行。 

读取excel一行的数据，这一行的数据就是本周的实际数据，我等下套模板里面去改的数据

wb = openpyxl.load_workbook('./周报/周报.xlsx',data_only=True)
ws = wb['汇总1']
rows = ws.iter_rows(min_row=information_row, max_row=information_row, min_col=2, max_col=25,values_only=True)
# 遍历每一行，并获取每个单元格的值
for row in rows:
    result1=list(row)
len(result1)

 24个数据，我把他们都拆卡命名：

(合同1,合同2,合同3,市场1,市场2,市场3,营业1,营业2,营业3,利润1,利润2,利润3,投资1,投资2,外营收1,外营收2,
外营收3,外利润1,外利润2,外利润3,应收账1,应收账2,应收账回1,应收账回2)=result1

套入模板：

text=f'''一、生产经营情况：本周及本年投资、营收、净利润、合同签约额、销售签约额、应收账款、应收账款回款额等关键指标增速、目标达成率等。
1、合同承接额：本周完成合同承接额{合同1:.3f}亿元，全年累计完成合同承接额{合同2:.2f}亿元，占年度目标15亿元的{合同3:.1f}%。
2、市场化业务承接额：本周完成集团外合同承接额{市场1:.2f}亿元，全年累计完成合同承接额{市场2:.2f}亿元，占年度目标15亿元的{市场3:.1f}%。
3、营业收入：本周完成营收{营业1:.2f}亿元，全年累计完成营收{营业2:.2f}亿元，占年度目标15亿元的{营业3:.2f}%。
4、净利润：本周实现净利润{利润1:.1f}万元，全年累计实现净利润{利润2:.1f}万元，占年度目标0.32亿元的{利润3:.1f}%。
5、投资额：本周完成投资额{投资1:.1f}亿元，全年累计完成投资额{投资2:.1f}亿元。
6、集团外营收：本周完成营收{外营收1*10000:.2f}万元，全年累计完成集团外营收{外营收2:.2f}亿元，占年度目标2亿元的{外营收3:.1f}%。
7、集团外净利润：本周完成{外利润1:.2f}万元，全年累计实现集团外净利润{外利润2:.2f}万元，占年度目标300万元的{外利润3:.1f}%。
8、应收账款：本周新增应收账款额{应收账1:.1f}万元，全年累计应收账款{应收账2:.1f}万元。
9、应收账款回款额：本周应收账款回款额{应收账回1:.1f}万元，全年累计应收账款回款额{应收账回2:.1f}万元。\n'''

把上一周的sheet周报读进来，然后复制一下为这周，修改sheet名称为本周时间，修改内容：

wb2 = openpyxl.load_workbook('./周报/周报.xlsx')
ws1 = wb2['7.10-7.16']
ws2 = wb2.copy_worksheet(ws1)
ws2.title = datetime
# 写入值到指定单元格
ws2.cell(row=3, column=2, value=text)

把第一行的日期也改一下：

ws2['A1'].value
a,b,c,d=ws2.title.replace('-','.').split('.')
ws2.cell(row=1, column=1, value=f'周重点工作事项通报表\n（2023年{a}月{b}日-{c}月{d}日）')

后面需要改的也进行一定的修改：

重点工作='''四、重点工作进展及亮点：本周开展的重点工作及亮点。   
项目保供：1、确料保供工作。
          2、保证各项材料供应。
市场开拓：1.完有限公司走访。
          2.完成贵编制工作。
          3.完成划分。
生产经营：1.接营公司认可；
          2.当枝行预验收；
          3.汉江目投标。'''
ws2.cell(row=6, column=2, value=重点工作)

内容我删了一部分，防止信息泄露。然后保存就行了。

# 保存 Excel 文件
wb2.save('周报.xlsx')

---

excel一个sheet复制到word里面去

我们刚刚这个表wb2是这周的周报，已经写入了excel里面了，但是我们还需要单独出一个word版，所以我们需要对这个表原封不动的放入word里面。

下面是我的代码，原封不动是很难的，因为样式，字体，表格高宽根本没办法挪过去的时候保持一致，只能按照下面这个代码思路来，先在word里面生产一个行和列数和wb2一样的表格，然后再遍历每一个格子填进去。

from docx.enum.section import WD_ORIENT
from docx import Document
from docx.shared import Cm, Pt
from docx.enum.table import WD_TABLE_ALIGNMENT, WD_ALIGN_VERTICAL
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.oxml import OxmlElement
from docx.oxml.ns import qn

# 创建 Word 文档和表格对象
doc = Document()
doc.styles['Normal'].font.name = '仿宋_GB2312'
doc.styles['Normal'].font.size = Pt(11)
table = doc.add_table(rows=ws2.max_row, cols=ws2.max_column)
# 将 Excel 表格复制到 Word 表格中
for i, row in enumerate(ws2.iter_rows()):
    for j, cell in enumerate(row):
        table.cell(i, j).text = str(cell.value)

我这里还设置了页面方向和页边距，表格框线：

# 设置页面方向为横向
section = doc.sections[0]
new_width, new_height = section.page_height, section.page_width
section.orientation, section.page_width, section.page_height = \
    WD_ORIENT.LANDSCAPE, new_width, new_height
#页边距
section.top_margin = Cm(0.5)
section.bottom_margin = Cm(0.5)
section.left_margin = Cm(1.25)
section.right_margin = Cm(1.25)

# 设置表格框线
for row in table.rows:
    for cell in row.cells:
        for paragraph in cell.paragraphs:
            for run in paragraph.runs:
                run.font.name = '仿宋_GB2312'
                run.font.size = Pt(11)
                r = run._element.rPr.rFonts
                r.set(qn("w:eastAsia"),"仿宋_GB2312")
                tc = cell._element
                tcPr = tc.get_or_add_tcPr()
                tcBorders = OxmlElement('w:tcBorders')
                top = OxmlElement('w:top')
                top.set(qn('w:val'), 'single')
                top.set(qn('w:sz'), '4')
                tcBorders.append(top)
                bottom = OxmlElement('w:bottom')
                bottom.set(qn('w:val'), 'single')
                bottom.set(qn('w:sz'), '4')
                tcBorders.append(bottom)
                left = OxmlElement('w:left')
                left.set(qn('w:val'), 'single')
                left.set(qn('w:sz'), '4')
                tcBorders.append(left)
                right = OxmlElement('w:right')
                right.set(qn('w:val'), 'single')
                right.set(qn('w:sz'), '4')
                tcBorders.append(right)
                tcPr.append(tcBorders)

我上面代码其实设置了字体样式和大小的，但是我发现无效....只能继续使用我们自定义的样式函数了：

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

然后我发现格子的高宽都是默认值，有的单元格还没合并，有的空白行不需要，所以我进行了下面的操作。（这些操作不是必要的，看你的报告需要什么格式就进行调整，可以模仿）

首先设置表格的高和宽：

#doc = Document('周test.docx')
table = doc.tables[0]
for cell in table.columns[0].cells:
    cell.width = Cm(1.95)
for cell in table.columns[1].cells:
    cell.width = Cm(23.5)

 然后设置第一行，也是标题进行合并居中，设置字体为黑体小三。

# Merge the first two cells in the first row 
cell_1 = table.cell(0, 0)
cell_2 = table.cell(0, 1)
cell_1.merge(cell_2)
cell_1.text = cell_1.text.replace('None', '').replace('\n', '')
# 设置字体
for paragraph in cell_1.paragraphs:
    paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
    set_style(paragraph,u'黑体',16)

 对我们的第二行，表头进行居中设置，段前断后0行距，黑体11号大小

# 居中，单倍行距段前段后0，设置样式
for cell in table.row_cells(1):
    for paragraph in cell.paragraphs:
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        paragraph.paragraph_format.space_before = Pt(0)
        paragraph.paragraph_format.space_after = Pt(0)
        set_style(paragraph,u'黑体',11)
        #for run in paragraph.runs:
#             run.bold = False
#             r = run._element.rPr.rFonts
#             r.set(qn("w:eastAsia"),"黑体")  

 对我们最左边的一列，从第三行开始进行单元格合并，然后去空白，垂直水平，每一段都水平居中，设置字体样式和大小

            
#合并，去掉none，居中，设置字体
merged_cell = table.cell(2, 0).merge(table.cell(7, 0))
merged_cell.text = merged_cell.text.replace('None', '').replace('\n', '')
merged_cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER

for paragraph in merged_cell.paragraphs:
    paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
    set_style(paragraph,u"仿宋_GB2312",11)

 然后删除中间这两个里面的换行符，也就是空白行，设置一下样式，然后保存，就完成啦

#去空白
for cell in [table.cell(3, 1),table.cell(4, 1),table.cell(7, 1)]:
    cell.text = cell.text.replace('\n', '')
    for paragraph in cell.paragraphs:
        set_style(paragraph,u"仿宋_GB2312",11)
doc.save('周报.docx')

 

---

还没结束....领导还说这个工作计划和工作任务都要写入另外一个材料表，后面还有一个周例会材料，说白了就是把16行的内容复制到17行去，然后修改为本周的实际情况，我发现openpyxl在这里会出问题，会把前面的写好的样式都改了....所以这里使用了 xlwings库。可以不用看，我放上去是给个演示。

import xlwings as xw
book = xw.Book('./周报/周例会材料(**公司）.xlsx')
sheet = book.sheets[0]

# 从第15行复制值到第16行，列从 A 到 I
for col in ["A", "B", "C", "D", "E", "F", "G", "H", "I"]:
    cell_to_copy = f"{col}{row_n-1}"
    destination_cell = f"{col}{row_n}"
    sheet.range(destination_cell).value = sheet.range(cell_to_copy).value

#要改的内容
营收情况=f'本周完成营收{营业1*10000:.0f}万元，全年累计完成营收{营业2*10000:.0f}万元'
重点工作1=重点工作.split('\n', 1)[1].replace(' ', '')
重点工作1=re.sub(r'\n(\d)', r'\1', 重点工作1)
近期计划1=近期计划.split('\n', 1)[1].replace(' ', '')
近期计划1=re.sub(r'\n(\d)', r'\1', 近期计划1).replace('其他内容', '其他工作')

#修改
sheet.range(f'C{row_n}').value = 营收情况
sheet.range(f'F{row_n}').value = 重点工作1
sheet.range(f'G{row_n}').value = 近期计划1
sheet.range(f'I{row_n}').value = datetime

#设置字体样式
sheet.range(f'F{row_n}').api.Font.Bold = False
sheet.range(f'G{row_n}').api.Font.Bold = False  #cell.api.Font.Name = '宋体'   cell.api.Font.Size = 10

#保存
book.save('周例会材料(远大公司）.xlsx')
book.close()