Last Updated on March 11, 2022 by Jay
We can use the openpyxl library to create an Excel File in Python. This ability is an essential step for any Excel process automation. The best part – we don’t even need to have the Excel program installed.
Library
Open up a command prompt, and use pip install to download openpyxl library:
pip install openpyxlCreate Excel Workbook and Add Worksheets using Python
For this step, we are creating an Excel file in Python and don’t need to open the Excel program. The below code creates an Excel file in memory, then adds two sheets, namely “Sheet_one” and “Sheet_two”.
Note:
- When a Workbook is created, by default it contains at least one sheet. So this file will contain three (3) sheets in total.
- This Excel file doesn’t exist on the hard drive yet and it doesn’t have a name.
from openpyxl import Workbook
wb = Workbook()
wb.create_sheet("Sheet_one")
wb.create_sheet("Sheet_two")
wb.sheetnames
['Sheet', 'Sheet_one', 'Sheet_two']We can change the sheet name any time by setting the ws.title attribute:
wb['Sheet'].title = 'Sheet_zero'
wb.sheetnames
['Sheet_zero', 'Sheet_one', 'Sheet_two']Copy A Worksheet
To copy a worksheet, call the wb.copy_worksheet() method:
ws0 = wb['Sheet_zero']
wb.copy_worksheet(ws0)
wb.sheetnames
['Sheet_zero', 'Sheet_one', 'sheet_two', 'Sheet_zero Copy']Note that openpyxl has limitations and can not copy everything on a sheet. For example, Images and Charts will not be copied.
Changing Sheet/tab Color
We can use the .sheet_properties.tabColor attribute to set tab colors. The value has to be a RGB hex value, and the easiest way to get those values is from the “Hex” box on Excel’s color palette.
wb['Sheet_zero'].sheet_properties.tabColor ='00FF00'
Write Some Data Into Excel
Check out this tutorial to learn how to access Sheets and Cells.
Let’s put some values into “Sheet_one”. As the below code shows, it’s easy to access a cell using the column-row notation.
ws1 = wb['Sheet_one']
ws1['B2'] = 'Fruits'
ws1['C2'] = 'Sales'To enter values into Sheet programmatically, it’s easier to use the cell() method, which takes two arguments: row and column, both starting from an index of 1.
fruits = ['Apple','Peach','Cherry','Watermelon']
sales = [100,200,300,400]
row_start = 3 #start below the header row 2
col_start = 2 #starts from column B
for i in range(0,len(fruits)):
ws1.cell(row_start+i, col_start).value = fruits[i]
ws1.cell(row_start+i, col_start+1).value = sales[i]
Saving Excel File
Up to this point, the entire Excel file including everything we’ve done is still inside our computer’s memory. We need to save the file to our disk by calling the wb.save() method. If we don’t do this step, our Excel file will disappear after shutting down the Python environment.
wb.save('book_eg.xlsx')This is how we can create an Excel file using Python. Check the below for more resources.
Additional Resoruces
How to Access Excel File In Python