用openpyxl写入excel表格

from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.utils import get_column_letter
import random
from openpyxl.styles import Color, Fill, Font, Alignment, PatternFill, Border, Side
from openpyxl.cell import Cell
import datetime
import string

# 新建一个workbook
wb = Workbook()
# 第一个sheet是ws
ws = wb.worksheets[0]
# 水平居中, 垂直居中
alignment_style = Alignment(horizontal='center', vertical='center')
ws['A1'].font = Font(size=16, bold=True)
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
# 设置ws的名称
ws.title = 'sheet1'
# 合并单元格 合并第一行的前2列
x = 1

# 定义Border边框样式
left, right, top, bottom = [Side(style='thin', color='000000')]*4
border_style = Border(left=left, right=right, top=top, bottom=bottom)

ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=14)
ws.cell(row=1, column=1).value = '库存药剂信息概览报表'

# 合并单元格

while x < 6:
ws.merge_cells(start_row=2, end_row=2, start_column=x, end_column=x+1)
x += 2
ws.merge_cells(start_row=2, end_row=2, start_column=7, end_column=14)

# 第2行写入值
now_time = datetime.datetime.now().strftime('%Y%m%d %H%M')
# 格式化时间为中文年月日
now_time = now_time[:4] + '年' + now_time[4:6] + '月' + now_time[6:8] + '日' + now_time[8:11] + '时' + now_time[11:13] + '分'
ws.cell(row=2, column=1).value = '报表导出时间：{}'.format(now_time)
ws.cell(row=2, column=3).value = '后台系统版本：Light_OS_Back_ver1.04'
ws.cell(row=2, column=5).value = '终端系统版本:Light_OS_Front_ver1.06'
ws.cell(row=2, column=7).value = '报表导出位置：后台'
# 遍历取1, 3, 5, 7为第二行添加样式
for x in range(1, 8, 2):
    ws.cell(row=2, column=x).font = Font(size=9)
    # 水平对齐 垂直对齐
    ws.cell(row=2, column=x).alignment = alignment_style
    if x < 7:
        # 边框样式
        ws.cell(row=2, column=x).border = border_style
        ws.cell(row=2, column=x + 1).border = border_style
    else:
        # 因为第2行第7-14列是合并的单元格 所以需要循环添加边框样式
        while x < 15:
            ws.cell(row=2, column=x).border = border_style
            x += 1


# 第三行的值
data_list = ['药剂类别', '纯度', 'CAS码', '重点监管', '当前库存总量', '当前在库数量', '当前借出数量', '库存价值(元)',
           '季度历史库存总量', '季度消耗总量', '季度消耗价值', '年度历史存库量', '年度消耗总量', '年度消耗价值']

for data in range(1, len(data_list) + 1):
    # 第三行写入值
    ws.cell(row=3, column=data).value = data_list[data - 1]
    # 设置文本水平居中, 垂直居中
    ws.cell(row=3, column=data).alignment = alignment_style
    #  设置字体加粗
    ws.cell(row=3, column=data).font = Font(bold=True, size=9)
    # 背景颜色
    ws.cell(row=3, column=data).fill = PatternFill(fill_type='solid', fgColor='EE9A49')
    # 边框样式
    ws.cell(row=3, column=data).border = border_style

# 设置列宽
# 生成前14个大写字母  ascii_uppercase生成所有大写字母
upper_string = string.ascii_uppercase[:15]
for col in upper_string:
    ws.column_dimensions[col].width = 20

# 设置行高
ws.row_dimensions[1].height = 35
ws.row_dimensions[2].height = 25
ws.row_dimensions[3].height = 28
ws.row_dimensions[4].height = 18
for data in range(1, len(data_list) + 1):
    ws.cell(row=4, column=data).value = random.randint(1, 20)
    # 设置文本水平居中, 垂直居中
    ws.cell(row=4, column=data).alignment = alignment_style
    # 设置边框样式
    ws.cell(row=4, column=data).border = border_style
    # 设置字体大小
    ws.cell(row=4, column=data).font = Font(size=9)



wb.save('库存药剂信息概览报表{}.xlsx'.format(random.randint(1, 20)))


# alignment = Alignment(
#     horizontal = 'general', # 水平：常规
#     vertical = 'bottom',   # 垂直：底部对齐
#     text_rotation = 0,   # 文本方向：0度
#     wrap_text = False,   # 自动换行
#     shrink_to_fit = False,  # 缩小字体填充
#     indent = 0 # 缩进0
#   )

最后编辑于：2018.10.10 16:37:34