使用openpyxl 去讀取，寫入，操控Excel的基本設定、表格、圖表

Document: https://openpyxl.readthedocs.io/en/default/

注意，1. openpyxl只支援xlsx格式，舊版的xls格式需要用其他方法去處理。

另外因為EXCEL,windows console 正常顥示中文必須用big5碼，所以編輯，儲存必須用big 5 碼

並且 Python 敘述寫在程式檔案中，執行時，要注意到兩件事：

1. 程式檔案的編碼：若存檔成 big5 碼，則要在程式中的第一行或第二行，告訴 Python 編碼方式

#!/usr/bin/env python

# -*- coding: big5 -*- # Note the first line in the above is for Operating system, the # second line is for Python interpreter

2. 要寫中文字至EXCEL時要做decode 才可正常顥示在EXCEL內。例如: ws4['A1'] = "中文".decode('big5')

3. 最好使用 >>import openpyxl    >>print openpyxl.__version__ 
    確定使用較新版的openpyxl , 太舊的版本無法支援chart 方面的操作但基本操作是可以。
    例如安裝portable Python 2.7.6.1 安裝的OpenPyXL 是1.8.5版,如此在Chart方面會找不到模組。 
4. Python 與中文處理可參考這篇文章

Working with Pandas and NumPy

openpyxl is able to work with the popular libraries Pandas and NumPy, openpyxl has builtin support for the NumPy types float, integer and boolean. DateTimes are supported using the Pandas’ Timestamp type

File Operations

Saving to a file

wb = Workbook()
wb.save('balances.xlsx')

Loading from a file

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.get_sheet_names()
['Sheet2', 'New Title', 'Sheet1']

#------------------------------------------

WorkBook operations 

Write a workbook

from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'
ws1 = wb.active

ws1.title = "range names"
for row in range(1, 40):
    ws1.append(range(600))

ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14

ws3 = wb.create_sheet(title="Data")
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

# add column headings. NB. these must be strings
ws3.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws3.append(row)

print(ws3['A10'].value)

wb.save(filename = dest_filename)

Read an existing workbook

from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(sheet_ranges['A1'].value)

#-----------------------------------------------------------------------

Cell operation 

a cell operation  

write value to single cell   ws['B1'] = '3.14%'
ws.cell(row=4, column=2, value=10)
append a row to a worksheet  ws.append(row)

for i in range(1,101):
   for j in range(1,101):
      ws.cell(row=i, column=j,value=10)

read vauel from cell  ws['B1'].value

Accessing many cells

Ranges of cells can be accessed using slicing

cell_range = ws['A1':'C2']

Ranges of rows or columns can be obtained similarly:

colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]

Using number formats

wb = Workbook()
ws = wb.active 

# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'

# You can enable type inference on a case-by-case basis
wb.guess_types = True
# set percentage using a string followed by the percent sign
 ws['B1'] = '3.14%'

wb.guess_types = False
ws['B1'].value  #0.031400000000000004
ws['B1'].number_format

cell use formulae

# add a simple formula
ws["A1"] = "=SUM(1, 1)"
wb.save("formula.xlsx")

Merge / Unmerge cells

Inserting an image

Worksheet Tables Operations 

Worksheet tables are references to groups of cells. This makes certain operations such as styling the cells in a table easier.

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

tab = Table(displayName="Table1", ref="A1:E5")

# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save("table.xlsx")

Working with styles

Styles are used to change the look of your data while displayed on screen. They are also used to determine the formatting for numbers.

以下是預設值
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
              size=11,
              bold=False,
              italic=False,
              vertAlign=None,
              underline='none',
              strike=False,
              color='FF000000')
fill = PatternFill(fill_type=None,start_color='FFFFFFFF',end_color='FF000000')
border = Border(left=Side(border_style=None,color='FF000000'),
                  right=Side(border_style=None,color='FF000000'),
                    top=Side(border_style=None,color='FF000000'),
                 bottom=Side(border_style=None,color='FF000000'),
               diagonal=Side(border_style=None,color='FF000000'),
                    diagonal_direction=0,
                    outline=Side(border_style=None,color='FF000000'),
                   vertical=Side(border_style=None,
                               color='FF000000'),
                 horizontal=Side(border_style=None,
                                color='FF000000')
                )
alignment=Alignment(horizontal='general',vrtical='bottom',text_rotation=0,
                    wrap_text=False,shrink_to_fit=False,indent=0)
number_format = 'General'
protection = Protection(locked=True,hidden=False)

改變值的方法為

Cell Styles

a1 = ws['A1']
d4 = ws['D4']
ft = Font(color=colors.RED)
a1.font = ft
d4.font = ft

Copying styles

ft1 = Font(name='Arial', size=14)
ft2 = copy(ft1)

Basic Font Colors

font = Font(color=RED)
font = Font(color="FFBB00")

Applying Styles

c = ws['A1']
c.font = Font(size=12)

Styling Merged Cells

def style_range(ws, cell_range, border=Border(), fill=None, font=None, alignment=None):
    """
    Apply styles to a range of cells as if they were a single cell.

    :param ws:  Excel worksheet instance
    :param range: An excel range to style (e.g. A1:F20)
    :param border: An openpyxl Border
    :param fill: An openpyxl PatternFill or GradientFill
    :param font: An openpyxl Font object
    """

    top = Border(top=border.top)
    left = Border(left=border.left)
    right = Border(right=border.right)
    bottom = Border(bottom=border.bottom)

    first_cell = ws[cell_range.split(":")[0]]
    if alignment:
        ws.merge_cells(cell_range)
        first_cell.alignment = alignment

    rows = ws[cell_range]
    if font:
        first_cell.font = font

    for cell in rows[0]:
        cell.border = cell.border + top
    for cell in rows[-1]:
        cell.border = cell.border + bottom

    for row in rows:
        l = row[0]
        r = row[-1]
        l.border = l.border + left
        r.border = r.border + right
        if fill:
            for c in row:
                c.fill = fill

wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border = Border(top=double, left=thin, right=thin, bottom=double)
fill = PatternFill("solid", fgColor="DDDDDD")
fill = GradientFill(stop=("000000", "FFFFFF"))
font = Font(b=True, color="FF0000")
al = Alignment(horizontal="center", vertical="center")
style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al)
wb.save("styled.xlsx")

Edit Page Setup

ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1

Named Styles

Using builtin styles

Chart Operations 

Chart types

The following charts are available:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
for i in range(10):
    ws.append([i])

from openpyxl.chart import BarChart, Reference, Series
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E15")
wb.save("c:\\tmp\\SampleChart.xlsx")