2019年1月27日 星期日

[python] 最簡單的方法把數字存到 Excel 進行後續的分析

Create an Excel file by Python
井民全, Jing, mqJing@gmail.com
如果你想要自動產生 Excel 報表,  來管理你的專案. 你可以用 Python 幫你處理. 下面的程式是參考自 用Python控制Excel的範例 文章, 並且加上簡單的說明.

主要觀念:

關鍵片段:

Example
#!/usr/bin/env python

from win32com.client import Dispatch

xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1

# Check if any workbook exists.
if xlApp.Workbooks.Count == 0:
    # If not, create a new one.
    workbook = xlApp.Workbooks.Add()
else:
    # If yes, use the first one.
    workbook = xlApp.Workbooks[0]

# Check if any sheet exists.
if workbook.Sheets.Count == 0:
    # If not, add a sheet to current workbook.
    sheet = workbook.Sheets.Add()
else:
    # If yes, use the first sheet of current workbook.
    sheet = workbook.Sheets[0]
    
# Generate the multiplication table(9x9).
for i in xrange(2, 10):
    # Cells(, )
    sheet.Cells(1, i).Value = i
    sheet.Cells(1, i).Font.Color = 0xFF0000
    sheet.Cells(i, 1).Value = i
    sheet.Cells(i, 1).Font.Color = 0x00FF00
    
def a2i(ch):
    return ord(ch.upper()) - ord('A') + 1

def i2a(i):
    return chr((i-1) + ord('A'))
    
for i in xrange(2, 10):
    for j in xrange(2, 10):
       # Generate the Excel formula.       
        sheet.Cells(i, j).Formula = '=%s1*A%s' % (i2a(j), i)
        sheet.Cells(i, j).Font.Color = 0x000000
sheet.Name = "Multiplication Table"
workbook.SaveAs('xxx.xls')
xlApp.Quit()
Note: the examle was copy from here

如何設定 Cell 的背景顏色?

sheet.Cells(2,2).Interior.ColorIndex = 44
sheet.Cells(2,2).Interior.Color = 0xFF0000

Referenced Color Index Map

References


at