2 Examples of How to Create Excel Workbook/Sheet using Python openpyxl

In this tutorial series of using openpyxl library (A library of Python to work with Excel), you will learn how to create workbooks, add spreadsheets, insert data, columns, rows etc.

A little about openpyxl and creating workbooks

In this tutorial series of using openpyxl library (A library of Python to work with Excel), you will learn how to create workbooks, add spreadsheets, insert data, columns, rows etc.

You will also learn how to edit and delete files, worksheets, and workbooks as well.

So, let us start with the first thing – how to create a workbook/excel file by using openpyxl library.

Do you have openpyxl installed?

The openpyxl is not built-in into Python. You have to install the library first.

So, if you have not already installed it, you may do this by using pip- a package manager for Python.

Step 1:

If you working on Windows then open the CMD or PowerShell

Step 2:

Run the following command that uses pip manager

pip install openpyxl

If you have pip working properly, this should install the package.

If it does not work, you may download the package from the official website and follow the instructions:

https://pypi.org/project/openpyxl

Creating an Excel workbook example

Follow these steps to create a workbook.

Step 1:

Create a Python File name: Excel-python.py

Step 2:

Import the Workbook from openpyxl library by using this code:

from openpyxl import Workbook

Step 2:

Create a Workbook object:

Wb_test = Workbook()

Step 3

Save the Workbook with a name:

Wb_test.save("test.xlsx")

That’s it.

So, the complete code to create a Workbook (Excel File) with the name “test.xlsx” is:

# An example of creating a workbook using openpyxl

# excel-python.py

from openpyxl import Workbook

Wb_test = Workbook()

Wb_test.save("test.xlsx")

The code above will create an Excel workbook on the same path where the Python file(Excel-python.py) exists.

Remember that, you do not need to create a file in the filesystem to get started with openpyxl. Executing the above code automatically creates an Excel file with a worksheet as shown in the image below:

Excel-openpyxl

 

You may notice the name of the workbook is “test” and it also has a worksheet namely “Sheet”.

Naming the worksheets example

To give the worksheet name of your choice rather than the default “Sheet”, you may do this as described below.

  • Use the title attribute to set the name of the worksheet.
  • For adding a new worksheet, use the create_sheet() method

In the example below, we again create a new Workbook and name the first worksheet “My_sheet1”.

Then we added a new sheet and named it “My_Sheet2”.

See the complete code and output below:

# Creating a worksheets with custom names

# excel-python.py

from openpyxl import Workbook

Wb_test = Workbook()




#Making this active worksheet

sheet = Wb_test.active

sheet.title = "My_sheet1"




#Creating second worksheet with its name

sheet2 = Wb_test.create_sheet(title="My_sheet2")

Wb_test.save("test_sheet.xlsx")

Output:

Excel-openpyxl-sheets

You can see the red arrows pointing to three things we set in the code:

  • File name (test_sheet)
  • Sheet 1 name (My_sheet1)
  • Sheet 2 name (My_sheet2)