>>> 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)
>>> from openpyxl import load_workbook >>> wb = load_workbook(filename = 'empty_book.xlsx') >>> sheet_ranges = wb['range names'] >>> print(sheet_ranges['D18'].value) 3
注意
There are several flags that can be used in load_workbook.
警告
openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name.
>>> 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'
>>> from openpyxl import Workbook >>> wb = Workbook() >>> ws = wb.active >>> # add a simple formula >>> ws["A1"] = "=SUM(1, 1)" >>> wb.save("formula.xlsx")
警告
NB you must use the English name for a function and function arguments must be separated by commas and not other punctuation such as semi-colons.
openpyxl never evaluates formula but it is possible to check the name of a formula:
>>> from openpyxl.utils import FORMULAE >>> "HEX2DEC" in FORMULAE True
If you’re trying to use a formula that isn’t known this could be because you’re using a formula that was not included in the initial specification. Such formulae must be prefixed with _xlfn. to work.
When you merge cells all cells but the top-left one are removed from the worksheet. To carry the border-information of the merged cell, the boundary cells of the merged cell are created as MergeCells which always have the value None. See Styling Merged Cells for information on formatting merged cells.
>>> 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')
