创建一个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="{0}".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')
|