python＋excel＝openpyxl（二）

创建一个workbook

>>> from openpyxl import Workbook

>>> 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")

>>> for row in range(10, 20):

... for col in range(27, 54):

... _ = ws3.cell(column=col, row=row, value="".format(get_column_letter(col)))

>>> print(ws3['AA10'].value)

AA

>>> wb.save(filename = dest_filename)

读取一张已存在的workbook

直接通过sheet的title获取整张表，然后通过具体cell的名称来获取值

>>> from openpyxl import load_workbook

>>> wb = load_workbook(filename = 'empty_book.xlsx')

>>> sheet_ranges = wb['range names']

>>> print(sheet_ranges['D18'].value)

3

使用number_format获取单元格格式

>>> import datetime

>>> from openpyxl import Workbook

>>> 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'

使用公式

>>> import openpyxl

>>> wb = openpyxl.load_workbook('F:\\PycharmProjects\\untitled\\测试表格.xlsx')

>>> ws = wb.active

>>> ws['A11'] = '=SUM(A1:B1)'

>>> wb.save(‘F:\\PycharmProjects\\untitled\\测试表格.xlsx’)

结果如下图

新梦想技术分享

openpyxl模块不会评估公式，但是它可以判断公式的名称是否有效：

>>> from openpyxl.utils import FORMULAE

>>> "HEX2DEC" in FORMULAE

True

如果你尝试去使用一个未知的公式，所谓未知公式就是指这个公式没有包含在openpyxl初始化的模块中。这一类公式就必须使用 _xlfn. 作为前缀来参与运算

合并/取消合并 单元格

合并单元格时，除左上角以外的所有单元格都将从工作表中删除。 为了携带合并单元格的边界信息，将合并单元格的边界单元格创建为MergeCells，它们始终具有值None。 有关格式化合并单元格的信息，请参见样式化合并单元格。

>>> from openpyxl.workbook import Workbook

>>> wb = Workbook()

>>> ws = wb.active

>>> ws.merge_cells('A2:D2')

>>> ws.unmerge_cells('A2:D2')

>>> # or equivalently

>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

插入图片

>>> from openpyxl import Workbook

>>> from openpyxl.drawing.image import Image

>>> wb = Workbook()

>>> ws = wb.active

>>> ws['A1'] = 'You should see three logos below'

>>> # create an image

>>> img = Image('logo.png')

>>> # add to worksheet and anchor next to cells

>>> ws.add_image(img, 'A1')

>>> wb.save('logo.xlsx')

隐藏单元格

>>> import openpyxl

>>> wb = openpyxl.Workbook()

>>> ws = wb.create_sheet()

>>> ws.column_dimensions.group('A','D', hidden=True)

>>> ws.row_dimensions.group(1,10, hidden=True)

>>> wb.save('group.xlsx')