import openpyxl
from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment
from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU
from openpyxl.styles import Border, Side, Font #设置字体和边框需要的模块
filePath = '1.xlsx'
wb = openpyxl.Workbook()
ws = wb.active
#表格基本设置
ws.title = '环城有限公司_2019-12-15'
ws.column_dimensions['A'].width = 13.7
ws.column_dimensions['B'].width = 9.85
ws.column_dimensions['C'].width = 9.85
ws.column_dimensions['D'].width = 9.85
ws.column_dimensions['E'].width = 9.85
ws.column_dimensions['F'].width = 9.85
ws.column_dimensions['G'].width = 12.4
ws.row_dimensions[1].height = 18
ws.row_dimensions[2].height = 18
ws.row_dimensions[3].height = 18
ws.row_dimensions[4].height = 18
font = Font(u'宋体',
size=11,
bold=False,
italic=False,
strike=False,
color='000000')
aligmentCenter = Alignment(vertical='center', wrap_text=True)
aligmentCenter1 = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
diagonal=Side(border_style='thin', color='FF000000'),
diagonal_direction=0,
outline=Side(border_style='thin', color='FF000000'),
vertical=Side(border_style='thin', color='FF000000'),
horizontal=Side(border_style='thin', color='FF000000'))
# logo图片
ws.merge_cells(start_row=1, start_column=1, end_row=4, end_column=7)
img = Image('logo.jpg')
newsize = (314, 80)
img.width, img.height = newsize
ws['A1'].alignment = aligmentCenter
p2e = pixels_to_EMU
h, w = img.height, img.width
position = XDRPoint2D(p2e(115), p2e(13))
size = XDRPositiveSize2D(p2e(w), p2e(h))
img.anchor = AbsoluteAnchor(pos=position, ext=size)
ws.add_image(img)
# 主题
ws.merge_cells(start_row=5, start_column=1, end_row=5, end_column=7)
ws.row_dimensions[5].height = 30
ws['A5'].font = font
ws['A5'].alignment = aligmentCenter
ws['A5'] = '主题Subject:报价单'
# 收件单位
ws.merge_cells(start_row=6, start_column=1, end_row=6, end_column=7)
ws.row_dimensions[6].height = 30
ws['A6'].font = font
ws['A6'].alignment = aligmentCenter
ws['A6'] = '收件单位To：南京理工大学'
# 发件单位
ws.merge_cells(start_row=7, start_column=1, end_row=7, end_column=7)
ws.row_dimensions[7].height = 30
ws['A7'].font = font
ws['A7'].alignment = aligmentCenter
ws['A7'] = '发件单位From:无锡一成化工装备有限公司'
# 联系人、电话、邮箱
ws.merge_cells(start_row=8, start_column=1, end_row=8, end_column=2)
ws.merge_cells(start_row=8, start_column=3, end_row=8, end_column=5)
ws.merge_cells(start_row=8, start_column=6, end_row=8, end_column=7)
ws.row_dimensions[8].height = 30
ws['A8'].font = font
ws['A8'].alignment = aligmentCenter
ws['A8'] = '联系人：肖琦'
ws['C8'].font = font
ws['C8'].alignment = aligmentCenter
ws['C8'] = '电话：13382892699'
ws['F8'].font = font
ws['F8'].alignment = aligmentCenter
ws['F8'] = '邮箱：natex@wycce.com'
# 日期
ws.merge_cells(start_row=9, start_column=1, end_row=9, end_column=7)
ws.row_dimensions[9].height = 30
ws['A9'].font = font
ws['A9'].alignment = aligmentCenter
ws['A9'] = '日期： 2019年12月15日'
# 表头
ws.row_dimensions[10].height = 30
ws['A10'].font = font
ws['A10'].alignment = aligmentCenter1
ws['A10'].border=border
ws['A10'] = '产品名称\nProduct'
ws['B10'].font = font
ws['B10'].alignment = aligmentCenter1
ws['B10'].border=border
ws['B10'] = '规格型号'
ws['C10'].font = font
ws['C10'].alignment = aligmentCenter1
ws['C10'].border=border
ws['C10'] = '材质'
ws['D10'].font = font
ws['D10'].alignment = aligmentCenter1
ws['D10'].border=border
ws['D10'] = '数量\n（台）'
ws['E10'].font = font
ws['E10'].alignment = aligmentCenter1
ws['E10'].border=border
ws['E10'] = '理算重量\nWeight'
ws['F10'].font = font
ws['F10'].alignment = aligmentCenter1
ws['F10'].border=border
ws['F10'] = '单价\nU/P(RMB)'
ws['G10'].font = font
ws['G10'].alignment = aligmentCenter1
ws['G10'].border=border
ws['G10'] = '总金额\nT/P(RMB)'
wb.save(filePath)
