close
如果你正在學習如何操控Excel,小編是認為利用openpyxl的方式較簡潔，直覺和簡單化，不過利用Win32Com也是可以的
請參考下面的範例說明
#使用Python的 PyWin32 和 the win32com library 去操作Excel 的圖形
# ExcelChart
# Creates a Microsoft Excel Chart given a data range
# and whole bunch of other parameters
# -*- coding: big5 -*-
import win32com.client
from win32com.client import constants
#定義一個Excel應用程式 Class
# ExcelApp
class ExcelApp:
def __init__(self):
self.excel = win32com.client.Dispatch("Excel.Application")
self.workbooks = []
self.SetDefaultSheetNum(1)
def Show(self):
self.excel.Visible = 1
def Hide(self):
self.excel.Visible = 0
def Quit(self):
for wkb in self.workbooks:
wkb.Close()
self.excel.Quit()
def SetDefaultSheetNum(self, numSheets):
self.excel.SheetsInNewWorkbook = numSheets
def AddWorkbook(self, filename):
workbook = ExcelWorkbook(self.excel, filename)
self.workbooks.append(workbook)
return workbook
#定義一個Excel Workbook Class
# ExcelWorkbook
class ExcelWorkbook:
def __init__(self, excel, filename):
self.filename = filename
self.excel = excel
self.workbook = self.excel.Workbooks.Add()
self.worksheets = {}
def AddWorksheet(self, name):
worksheet = ExcelWorksheet(self.excel, self.workbook, name)
self.worksheets[name] = worksheet
return worksheet
def AddChart(self, name, afterSheet):
chart = ExcelChart(self.excel, self.workbook, name, afterSheet)
self.worksheets[name] = chart
return chart
def Save(self):
self.workbook.SaveAs(self.filename)
def Close(self):
self.worksheets = {}
self.workbook.Close()
def SetAuthor(self, author):
self.workbook.Author = author
#定義一個Excel WorkSheet Class
# ExcelWorksheet
class ExcelWorksheet:
def __init__(self, excel, workbook, sheetname):
self.sheetname = sheetname
self.excel = excel
self.workbook = workbook
self.worksheet = self.workbook.Worksheets.Add()
self.worksheet.Name = sheetname
def Activate(self):
self.worksheet.Activate()
def SetCell(self, row, col, value):
self.worksheet.Cells(row,col).Value = value
def GetCell(self, row, col):
return self.worksheet.Cells(row,col).Value
def SetFont(self, row, col, font, size):
self.worksheet.Cells(row,col).Font.Name = font
self.worksheet.Cells(row,col).Font.Size = size
def GetFont(self, row, col):
font = self.worksheet.Cells(row,col).Font.Name
size = self.worksheet.Cells(row,col).Font.Size
return (font, size)
#定義一個Chart Class
class ExcelChart:
def __init__(self, excel, workbook, chartname, afterSheet):
self.chartname = chartname
self.excel = excel
self.workbook = workbook
self.chartname = chartname
self.afterSheet = afterSheet
def SetTitle(self, chartTitle):
self.chartTitle = chartTitle
def SetType(self, chartType):
self.chartType = chartType
def SetSource(self, chartSource):
self.chartSource = chartSource
def SetPlotBy(self, plotBy):
self.plotBy = plotBy
def SetCategoryLabels(self, numCategoryLabels):
self.numCategoryLabels = numCategoryLabels
def SetSeriesLabels(self, numSeriesLabels):
self.numSeriesLabels = numSeriesLabels
def SetCategoryTitle(self, categoryTitle):
self.categoryTitle = categoryTitle
def SetValueTitle(self, valueTitle):
self.valueTitle = valueTitle
def CreateChart(self):
self.chart = self.workbook.Charts.Add(After=self.afterSheet)
self.chart.ChartWizard(Gallery=win32com.client.constants.xlColumn, \
CategoryLabels=1, \
SeriesLabels=1, \
CategoryTitle = self.categoryTitle, \
ValueTitle = self.valueTitle, \
PlotBy=self.plotBy, \
Title=self.chartTitle)
self.chart.SetSourceData(self.chartSource, self.plotBy)
#self.chart.HasAxis = (constants.xlCategory, constants.xlPrimary)
#self.chart.Axes(constants.xlCategory).HasTitle = 1
#self.chart.Axes(constants.xlCategory).AxisTitle.Text = self.categoryTitle
#self.chart.Axes(constants.xlValue).HasTitle = 1
#self.chart.Axes(constants.xlValue).AxisTitle.Text = self.valueTitle
#self.chart.Axes(constants.xlValue).AxisTitle.Orientation = constants.xlUpward
#self.chart.PlotBy = self.plotBy
#self.chart.Name = self.chartname
#self.chart.HasTitle = 1
#self.chart.ChartTitle.Text = self.chartTitle
#self.chart.HasDataTable = 0
#self.chart.ChartType = self.chartType
def SetLegendPosition(self, legendPosition):
self.chart.Legend.Position = legendPosition
def PlotByColumns(self):
self.chart.PlotBy = constants.xlColumns
def PlotByRows(self):
self.chart.PlotBy = constants.xlRows
def SetCategoryAxisRange(self, minValue, maxValue):
self.chart.Axes(constants.xlCategory).MinimumScale = minValue
self.chart.Axes(constants.xlCategory).MaximumScale = maxValue
def SetValueAxisRange(self, minValue, maxValue):
self.chart.Axes(constants.xlValue).MinimumScale = minValue
self.chart.Axes(constants.xlValue).MaximumScale = maxValue
def ApplyDataLabels(self, dataLabelType):
self.chart.ApplyDataLabels(dataLabelType)
def SetBorderLineStyle(self, lineStyle):
self.chart.PlotArea.Border.LineStyle = lineStyle
def SetInteriorStyle(self, interiorStyle):
self.chart.PlotArea.Interior.Pattern = interiorStyle
#----- begin main program -----------
def Main():
excel = ExcelApp()
excel.Show()
workbook = excel.AddWorkbook("c:\\tmp\\games1.xlsx")
games = workbook.AddWorksheet("Game Sales")
accessories = workbook.AddWorksheet("Accessories")
games.Activate()
games.SetFont(1,1,"Arial",18)
games.SetCell(1,1, "Excel Controlled from Python - Game Sales")
months = ["January", "February", "March"]
systems = ["Nintendo GameCube", "Sony Playstation 2", "Microsoft XBox"]
for i in range(len(months)): games.SetCell(3, i+2, months[i])
for j in range(len(systems)): games.SetCell(4 + j, 1, systems[j])
for i in range(4,6+1):
for j in range(2,4+1):
games.SetCell(i,j, i*j)
chart = workbook.AddChart("Gaming Sales Chart", games.worksheet)
chart.SetTitle("Games Sold by Platform Type per Month")
chart.SetSource(games.worksheet.Range("A3:D6"))
chart.SetType(win32com.client.constants.xlColumn)
chart.SetPlotBy(win32com.client.constants.xlRows)
chart.SetCategoryTitle("Months")
chart.SetValueTitle("Sales")
chart.SetCategoryLabels(1)
chart.SetSeriesLabels(1)
chart.CreateChart()
workbook.Save()
excel.Quit()
if __name__ == '__main__':
Main()
注意事項:
1. 如果出現如下的__getattr__ raise AttributeError(a) AttributeError: xlLline 錯誤表示你的電腦並没認識 win32com.client.constancts的常數定義,所以你要到 C:\XX\Portable Python 2.7.6.1\App\lib\site-packages\win32com\client\下執行make.py
Traceback (most recent call last):
File "<module1>", line 206, in <module>
File "<module1>", line 193, in Main
File "I:\software dictionary\Portable Python 2.7.6.1\App\lib\site-packages\win32com\client\__init__.py", line 170, in __getattr__
raise AttributeError(a)
AttributeError: xlLLine
2. 執行 make.py 完後會出現一個選項視窗,要選擇EXCEL的type library,執行完後會匯入 Excel 的 type library到你的系統內,產生的訊息如下
Generating to C:\Users\stanley\AppData\Local\Temp\gen_py\2.7\00020813-0000-0000-C000-000000000046x0x1x7.py
Building definitions from type library...
Generating...
Importing module
Exit code: 0
參考資料
https://gist.github.com/mikepsn/27dd0d768ccede849051
文章標籤
全站熱搜
留言列表
