Python 操作office 封装
#coding=gbk
__author__ = 'libo'
import os
import time
import datetime
import traceback
from win32com.client import DispatchEx, constants, pythoncom
WORD_NAME = 'result.docx' #默认的文件名称
EXCEL_NAME = 'result.xlsx' #默认的文件名称
ISOTIMEFORMAT = 'seop60_report%Y-%m-%d-%H_%M_%S'
class Enum:
def __init__(self):
pass
xlColumnClustered = 51
class word:
def __init__(self, office_dir, name=WORD_NAME):
pythoncom.CoInitialize()
office_dir = office_dir.replace('/', '\\')
if office_dir[-1] != '\\':
office_dir += '\\'
self.office_dir = office_dir #word保存的文件夹
if not os.path.exists(office_dir):
os.makedirs(office_dir)
self.name = self.office_dir + time.strftime(ISOTIMEFORMAT, time.localtime()) + WORD_NAME #最终保存的文件名
self.__app = DispatchEx('Word.Application') # 挂载word程序
self.__app.Visible = False
self.__app.DisplayAlerts = False
self.__doc = self.__app.Documents.Add() # 创建新的文档
self.__doc.SaveAs(self.name)
self.__excel = excel(self.office_dir)
def append_str(self, value, size=12, bold=0, alignment=0):
"""
添加文本到文档
"""
self.to_end()
self.__doc_range.InsertBefore(value+'\n')
self.__doc_range.Select()
self.__doc_range.style = -1
self.__doc_range.Style.Font.Bold = bold
self.__doc_range.Style.Font.Size = size
self.__doc_range.ParagraphFormat.Alignment = alignment
self.__doc.Save()
def append_head_big(self, value):
"""
添加文本到文档
"""
self.to_end()
self.__doc_range.InsertBefore(value)
self.__doc_range.Select()
self.__doc_range.style = -2
self.__doc_range.ParagraphFormat.Alignment = 1
self.__doc_range.InsertAfter('\n')
self.__doc.Save()
def append_head(self, value):
"""
添加文本到文档
"""
self.to_end()
self.__doc_range.InsertBefore(value)
self.__doc_range.Select()
self.__doc_range.style = -3
self.__doc_range.ParagraphFormat.Alignment = 1
self.__doc_range.InsertAfter('\n')
self.__doc.Save()
def append_head_small(self, value):
"""
添加文本到文档
"""
self.to_end()
self.__doc_range.InsertBefore(value)
self.__doc_range.Select()
self.__doc_range.style = -4
self.__doc_range.ParagraphFormat.Alignment = 0
self.__doc_range.InsertAfter('\n')
self.__doc.Save()
def append_head_small_title(self, value):
"""
添加文本到文档
"""
self.to_end()
self.__doc_range.InsertBefore(value)
self.__doc_range.Select()
self.__doc_range.style = -5
self.__doc_range.ParagraphFormat.Alignment = 0
self.__doc_range.InsertAfter('\n')
self.__doc.Save()
def append_table(self, dyadic_array, x_len, y_len):
if len(dyadic_array) == 0:
return False
if x_len * y_len == 0:
return False
self.to_end()
tab = self.__doc.Tables.Add(self.__doc_range, x_len, y_len)
tab.Style = "网格型" # 显示表格边框
for y_index in range(1, y_len + 1):
for x_index in range(1, x_len + 1):
tab.Cell(x_index, y_index).Range.Text = str(dyadic_array[x_index - 1][y_index - 1])
tab.Cell(x_index, y_index).Range.Font.Size = 10
self.__doc.Save()
return True
def append_chart(self, dyadic_array, x_len, y_len, chart_type, title, show_label=False):
if len(dyadic_array) == 0:
return False
if x_len * y_len == 0:
return False
self.append_head_small_title(title)
self.to_end()
self.__excel.insert_chart(dyadic_array, x_len, y_len, chart_type, title, show_label)
self.__doc_range.Paste()
#self.append_str('\n')
self.__doc.Save()
return True
def append_scatter(self, dyadic_array, x_len, y_len, title, x_axis='', y_axis=''):
if len(dyadic_array) == 0:
return False
if x_len * y_len == 0:
return False
self.append_head_small_title(title)
self.to_end()
self.__excel.insert_Scatter(dyadic_array, x_len, y_len, title, x_axis, y_axis)
self.__doc_range.Paste()
self.append_str('\n')
self.__doc.Save()
return True
def to_end(self):
"""
移动到文档末尾
"""
self.__doc_range = self.__doc.Range()
self.__doc_range = self.__doc.Range(self.__doc_range.End - 1, self.__doc_range.End - 1)
self.__doc_range.Select()
self.__doc_range.style = -1
self.__doc_range.Style.Font.Bold = 0
self.__doc_range.Style.Font.Size = 12
self.__doc_range.ParagraphFormat.Alignment = 0
def un_init(self):
try:
if self.__doc:
self.__doc.Close()
if self.__app:
self.__app.Quit()
self.__app = None
except Exception:
self.__app.Quit()
finally:
self.__excel.un_init()
class excel:
def __init__(self, office_dir, name=EXCEL_NAME):
pythoncom.CoInitialize()
office_dir = office_dir.replace('/', '\\')
if office_dir[-1] != '\\':
office_dir += '\\'
self.office_dir = office_dir #EXCEL保存的文件夹
if not os.path.exists(office_dir):
os.makedirs(office_dir)
self.name = self.office_dir + time.strftime(ISOTIMEFORMAT, time.localtime()) + EXCEL_NAME #最终保存的文件名
self.__app = DispatchEx('Excel.Application') # 挂载word程序
self.__app.Visible = 0
self.__app.DisplayAlerts = 0
self.__book = self.__app.Workbooks.Add() # 创建新的文档
self.__book.SaveAs(self.name)
def insert_chart(self, dyadic_array, x_len, y_len, chart_type, title, show_label=False, x_axis='', y_axis=''):
if len(dyadic_array) == 0:
return False
if x_len * y_len == 0:
return False
title = unicode(title, 'gbk').replace('*', '')
sheet = self.__book.Worksheets.Add() # 增加新表，新表为第4个表，相当与wBook.Worksheets(4)
sheet.Name = title
print '_c_' + str(datetime.datetime.now())
for y_index in range(1, y_len + 1):
for x_index in range(1, x_len + 1):
sheet.Cells(x_index, y_index).Value = dyadic_array[x_index - 1][y_index - 1]
print '_d_' + str(datetime.datetime.now())
ra = sheet.Range(sheet.Cells(1, 1), sheet.Cells(x_len, y_len))
chart = self.__book.Charts.Add(pythoncom.Missing, sheet, pythoncom.Missing, pythoncom.Missing)
chart.ChartType = chart_type
chart.HasTitle = True
chart.ChartTitle.Text = title
if show_label: #是否显示数据标签
if y_len == 1:
chart.SeriesCollection(1).ApplyDataLabels()
else:
for i in range(1, y_len):
chart.SeriesCollection(i).ApplyDataLabels()
chart.SetSourceData(ra)
if x_axis:
chart.SetElement(301)
chart.Axes(1, 1).AxisTitle.Text = x_axis
if y_axis:
chart.SetElement(310)
chart.Axes(2, 1).AxisTitle.Text = y_axis
chart.Location(2, title) #这里可以独立出一个参数
sheet.ChartObjects(r"图表 1").Activate()
sheet.ChartObjects(r"图表 1").Copy()
self.__book.Save()
return True
def insert_Scatter(self, dyadic_array, x_len, y_len, title, x_axis='', y_axis=''):
if len(dyadic_array) == 0:
return False
if x_len * y_len == 0:
return False
title = unicode(title, 'gbk').replace('*', '')
sheet = self.__book.Worksheets.Add() # 增加新表，新表为第4个表，相当与wBook.Worksheets(4)
sheet.Name = title
print '_a_' + str(datetime.datetime.now())
for y_index in range(1, y_len + 1):
for x_index in range(1, x_len + 1):
sheet.Cells(x_index, y_index).Value = dyadic_array[x_index - 1][y_index - 1]
print '_b_' + str(datetime.datetime.now())
ra = sheet.Range(sheet.Cells(1, 1), sheet.Cells(x_len, y_len))
chart = self.__book.Charts.Add(pythoncom.Missing, sheet, pythoncom.Missing, pythoncom.Missing)
chart.ChartType = -4169
chart.HasTitle = True
chart.ChartTitle.Text = title
chart.SeriesCollection(1).MarkerStyle = 8
chart.SeriesCollection(1).MarkerSize = 2
chart.SetSourceData(ra)
if x_axis:
chart.SetElement(301)
chart.Axes(1, 1).AxisTitle.Text = x_axis
if y_axis:
chart.SetElement(310)
chart.Axes(2, 1).AxisTitle.Text = y_axis
chart.Location(2, title) #这里可以独立出一个参数
sheet.ChartObjects(r"图表 1").Activate()
sheet.ChartObjects(r"图表 1").Copy()
self.__book.Save()
return True
def un_init(self):
try:
if self.self.__book:
self.self.__book.Close()
if self.__app:
self.__app.Quit()
self.__app = None
except Exception:
self.__app.Quit()
if (__name__ == "__main__"):
try:
BASE_DIR = os.path.dirname(__file__)
w = word(BASE_DIR)
w.append_str(r'插入测试')
dyadic_array = [[1, 2, 3], [4, 5, 6], [7, 8, 9], [7, 8, 9]]
w.append_table(dyadic_array, 4, 3)
w.append_chart(dyadic_array, 4, 3, 0, 'title')
except Exception:
print traceback.format_exc()
finally:
w.un_init()
