openpyxl can create a workbook, write text, numbers, and formulas, then save it. The same workbook object can add or remove sheets, set fonts, change row height and column width, and merge cells. You do not need Excel installed to produce the file.
import openpyxl
wb = openpyxl.Workbook()
print(wb.sheetnames) # ['Sheet']
sheet = wb.active
print(sheet.title) # Sheet
sheet.title = "MyNewTitle"
wb.save("NewExcelFile.xlsx")
A new workbook starts with one sheet named Sheet. After save, NewExcelFile.xlsx is in the current working directory and its only tab is MyNewTitle.
Any change is discarded unless you call save. Saving under a new name leaves the original file alone, which is the safer habit while a script is still being tested.
import openpyxl
wb = openpyxl.load_workbook("filetest.xlsx")
sheet = wb.active
sheet.title = "Working on Save as"
wb.save("example_filetest.xlsx")
import openpyxl
wb = openpyxl.Workbook()
wb.create_sheet() # Sheet1 at the end
wb.create_sheet(index=0, title="1st Sheet")
wb.create_sheet(index=2, title="2nd Sheet")
print(wb.sheetnames)
del wb["1st Sheet"]
del wb["Sheet1"]
print(wb.sheetnames)
wb.save("sheets.xlsx")
create_sheet() with no arguments appends a sheet. index chooses the position. The first sheet is index 0. title sets the tab name. Removing a sheet is covered step by step in Delete a sheet.
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Writing new Value!"
print(sheet["F6"].value)
wb.save("cells.xlsx")
More ways to address a cell, including a loop, are on the write to a cell page.
In current openpyxl you assign a Font directly to the cell. The older Style class has been removed.
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Bold Arial"
sheet["F6"].font = Font(name="Arial", bold=True)
sheet["D7"] = "32 pt Italic"
sheet["D7"].font = Font(size=32, italic=True)
wb.save("Appliedstyle.xlsx")
Font accepts name, size, bold, italic, and color. Color is a hex string such as "FF0000" for red.
Write the formula as text, starting with =.
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = 500
sheet["F7"] = 800
sheet["D3"] = "=SUM(F6:F7)"
wb.save("Applyingformula.xlsx")
Excel shows 1300 in D3 when the file is opened. openpyxl stores the formula. It does not calculate it.
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Tall row"
sheet["D7"] = "Wide column"
sheet.row_dimensions[6].height = 65
sheet.column_dimensions["D"].width = 25
wb.save("Heightandwidth.xlsx")
The default row height is 15 points in current Excel (older files often used 12.75). One point is 1/72 inch. Row height can run from 0 to 409. Column width can run from 0 to 255. A height or width of 0 hides that row or column.
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells("B2:D3")
sheet["B2"] = "Cells merged together."
sheet.merge_cells("F6:F7")
wb.save("Mergingcells.xlsx")
The argument is the rectangle to merge: the first address is the top-left cell, the second is the bottom-right. Put the visible value in the top-left cell of that rectangle. The other cells in the merge should stay empty.
wb = openpyxl.load_workbook("Mergingcells.xlsx")
sheet = wb.active
sheet.unmerge_cells("B2:D3")
sheet.unmerge_cells("F6:F7")
wb.save("unmerged.xlsx")
Libraries covered: openpyxl, xlrd, xlsxwriter, xlwt, and xlutils.