In this blog, I am going to explain how Python can be used to handle an Excel sheet. Here we will cover how to add a new sheet, delete an existing sheet, add data to the cell, read data from a sheet. Here I will be using the openpyxl module of Python, we can also use Pandas to play with Excel sheets but here we will use the openpyxl module.

Using this module we can do a lot of things like reading data from Excel convert it to JSON and index it to Elasticsearch using Python. In the same way, we can read data from text files or Elasticsearch and can write it to an Excel sheet. We can also apply conditional formatting on the Excel sheet to make it more effective. I am assuming that Python is already installed on your machine. So let's start handling Excel using Python.

We need to install the openpyxl module before writing the code and for that, we can use the following command (I am using Ubuntu 19.04):

sudo pip3 install openpyxl 

The above command will install the openpyxl module for Python. After getting the success message we can start writing the Python code to handle the Excel sheet.

Create a blank Excel sheet
Now we can create a blank Excel sheet to verify if everything is working as desired. So let us create a python blank file "testexcel.py" and add the following code:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# create the workbook object using the Workbook method of openpyxl
wb=exl.Workbook()

# now save workbook by providing the file name 
wb.save(filename) 

Now save this python file and execute it using the following command:

python3 testexcel.py

After executing this command you can see the "testexcel.xlsx" on the same folder where your Python script is saved. We can provide the full path of the Excel file in case we want to save it somewhere else.

Get Excel file details

Now we are going to read the Excel sheet and will try to fetch the file details and the sheet details etc. I have added three sheets in the previous file so that it will make things more clear to understand.

We need to write the following code to get the Excel sheet details:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Getting the basic properties of the workbook like last modified by, Created date time, etc
prop = workbook.properties
print(prop)

# Getting the sheets in the workbook 
worksheet = workbook.worksheets
print(worksheet)

# Getting the sheet names in a workbook as a list of strings
sheetname = workbook.sheetnames
print(sheetname)

# Getting the active sheet in the workbook
active = workbook.active
print(active)

After saving the file we can run the Python file which will give the following output:

<openpyxl.packaging.core.DocumentProperties object>
Parameters:
creator='openpyxl', title=None, description=None, subject=None, identifier=None, language='en-IN', created=datetime.datetime(2019, 12, 30, 14, 33, 54), modified=datetime.datetime(2019, 12, 30, 14, 47, 50), lastModifiedBy=None, category=None, contentStatus=None, version=None, revision='1', keywords=None, lastPrinted=None
[<Worksheet "Anurag Sheet 1">, <Worksheet "Anurag Sheet 2">, <Worksheet "Anurag Sheet 3">]
['Anurag Sheet 1', 'Anurag Sheet 2', 'Anurag Sheet 3']
<Worksheet "Anurag Sheet 3">

This way we can get the Excel file details by reading the file.

Create a new Sheet
Now we are going to add a new sheet to the Excel file using Python. We need to write the following code for the same:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Creating a new sheet by using create_sheet() method
workbook.create_sheet('Anurag test sheet')

# save the workbook
workbook.save(filename)

After executing the above code we can see a new sheet in the Excel file.

Add data to Excel sheet
We can add data to any cell of the sheet in the following ways:

1) Set value for cell by providing the cell address like A1, B3, etc
sheet['A1'] = 1

2) Set value for cell by providing the row and column number
sheet.cell(row=2, column=2).value = 2

3) We can also append the data in tablular form, see the below code:

employeedata=[('id','name','mobile'),
      (1,'Anurag',1234567890),
      (2,'Suraj',4323456787)]

# append above data
for row in employeedata:
    sheet.append(row)

Now let's write the code to add cell values in the active sheet of Excel:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Take the reference of active sheet
sheet = workbook.active

employeedata=[('id','name','mobile'),
      (1,'Anurag',1234567890),
      (2,'Suraj',4323456787)]

# append above data
for row in employeedata:
    sheet.append(row)

# save the workbook
workbook.save(filename)

After running the above code we can see the data on the Excel file.

In this blog, we have covered how to see the Excel sheet details, write data to an Excel sheet, and add new sheets.

In the next blog "Handle Excel file using Python (Part 2)", we will cover how to read data from an Excel sheet and how to add styles on Excel sheet data.

In case of any doubt please leave your comments. You can also follow me on Twitter: https://twitter.com/anu4udilse

Medium: https://anubioinfo.medium.com/

If you found this article interesting, then you can explore Mastering Kibana 6.0, Kibana 7 Quick Start Guide, Learning Kibana 7, and Elasticsearch 7 Quick Start Guide” books to get more insight about Elastic Stack, how to perform data analysis, and how you can create dashboards for key performance indicators using Kibana.