Python与Excel--04打开Excel整理字体与大小

1.需求：

1.1 背景：日常工作需要发报告，报告是由多人汇总而来

1.2 目标：为避免每人填写后字体类型与大小不统一导致的观感不适，遂开发此脚本专门用于统一字体

1.3 模板部分如下：

 

2.安装openpyxl

#导入openpyxl
pip install openpyxl

 3.获取与脚本同目录下的report.xlsx

#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl import load_workbook
wb=load_workbook('report.xlsx')
print(type(wb))

4.获取表sheet1

#!/usr/bin/env python
import openpyxl
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
print(sheet1)

5.获取单元格

#!/usr/bin/env python
import openpyxl
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
B9=sheet1['B9'].value
print(B9)

结果同上，利用了表sheet1的cell方法

#!/usr/bin/env python
import openpyxl
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
B9=sheet1.cell(row=9,column=2).value
print(B9)

6.修改单元格(中文加上第二行)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
sheet1['B9']='工程名称'
wb.save('report1.xlsx')

7.改变B9的文字字体

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Font
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
italic24Font=Font(size=24,italic=True)
sheet1['B9'].font=italic24Font
sheet1['B9']='工程名称'
wb.save('report1.xlsx')

8.改变B9的行宽20和列高30

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Font
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
italic24Font=Font(size=24,italic=True)
sheet1['B9'].font=italic24Font
sheet1['B9']='工程名称'
sheet1.row_dimensions[9].height=30
sheet1.column_dimensions['B'].width=20
wb.save('report1.xlsx')

9.合并的单元格只需要修改这一合并单元左上角的单元格B3的值

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Font
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
italic24Font=Font(size=24,italic=True)
sheet1['B3'].font=italic24Font
sheet1['B3']='工程名称'
sheet1.row_dimensions[3].height=50
sheet1.column_dimensions['B'].width=30
wb.save('report1.xlsx')

10.合并单元格

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Font
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
sheet1.merge_cells('B1:N1')
wb.save('report1.xlsx')

11.添加边框

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Font,Border,Side
wb=openpyxl.load_workbook('report.xlsx')
bd = Side(style='thick', color="000000")
border = Border(left=bd, top=bd, right=bd, bottom=bd)
sheet1=wb.worksheets[0]
sheet1.merge_cells('B1:N1')
sheet1['B1'].border=border
sheet1['C1'].border=border
wb.save('report1.xlsx')

 12打印当前日期

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl,time,datetime
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
sheet1['B4']='XX日报'+time.strftime('%Y-%m-%d')
wb.save('report1.xlsx')

 13改变单元格颜色

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl
import openpyxl.styles as sty
wb=openpyxl.load_workbook('report.xlsx')
sheet1=wb.worksheets[0]
sheet1.cell(row=3,column=2).fill=sty.fills.PatternFill(fill_type='solid',fgColor="00b0f0")
wb.save('report1.xlsx')