How to Create Excel File in Python

Sharing is caring!

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 openpyxl

Create 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:

  1. When a Workbook is created, by default it contains at least one sheet. So this file will contain three (3) sheets in total.
  2. 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'
Excel color palette
Excel color palette

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]
Python write data to Excel
Python write data to Excel

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

How to Use Python to Read Excel Formula

Work with Excel Named Range in Python

Leave a Reply

Your email address will not be published. Required fields are marked *