Xlsxwriter Read Excel

In this Python tutorial, I will discuss xlsxwriter read Excel, where you will learn how to read the existing Excel file on your system using the functions from the xlsxwriter module.

You will not only learn how to read the Excel file but also learn how to create an Excel file where you will insert or write data to the worksheet in the Excel file with proper cells.

To read an Excel file, you will learn about another module called openpyxl, which allows you to create and read Excel files.

Let’s start,

Xlsxwriter Read Excel

A third-party module called Xlswriter in Python allows you to create a new Excel file like ms-excel. This module has several functions that you can use to create an Excel file.

Although the purpose of this tutorial is to show you how to read Excel files using the xlsxwriter module, I will also show you how to create an Excel file.

To use the xlsxwriter, first install this module using the command below.

If you are using the Jupyter Notebook, then use the command below.

!pip install xlsxwriter

If you using the computer terminal or command prompt, execute the command below.

pip install xlswriter

After installing xlswriter, the next step is to create a workbook; this workbook is similar to the ms-excel file where your data resides.

To create a workbook, just call the constructor named Workbook() from the xlswriter module as shown below.

workbook = xlsxwriter.Workbook("user.xlsx)

The above code creates a workbook object named ‘workbook’ to which you can add a worksheets tab like in MS Excel and then write data or apply formatting.

READ:  How to Print the Characters in a String Separated By Space

However, you can also see that the Workbook() takes a file name with an extension, which is user.xlsx, creates a file execel, and returns the workbook object.

To add a worksheet to the ‘workbook’, use the add_worksheet() method as shown below in the code.

worksheet = workbook.add_worksheet()

Now you can write data to the worksheet in specific cells using methods like write() and write_row() of the object ‘worksheet’. For example, look at the code below.

worksheet.write("A1", "First Name")
worksheet.write("B1", "Last Name")

After writing, you must close the file using the close() method as shown below.

workbook.close()

Remember, if you don’t close your file when creating a new one, you will not see the file, so it is necessary to close the file.

Creating Excel File Before Xlsxwriter Read Excel

The above picture is of the Excel file the user created in my system. I have opened that file in MS Excel. As you can see, it contains two cells, A and B, with data for First Name and Last Name.

Also, look at the worksheet tab, which is sheet 1. Here you have opened the file using the MS Excel application, but we need to open that file and read data from the file using the function of the xlsxwriter module.

For that head to the next section.

Xlsxwriter Python Read Excel

It is not possible to read the Excel file ‘user.xlsx’ using the xlswriter, so you will need to use another module. The other module is openpyxl, which allows you to create and read the Excel file.

But here we will see how to read the Excel file created by the xlsxwriter module.

READ:  Python program for a diamond pattern [2 Methods]

First, install the module openpyxl using the code below.

!pip install openpyxl 
OR
pip install openpyxl

After installing, load the Excel file you want to read. Openpyxl has a method called load_workbook(), which accepts the path of the Excel file, in this case, user.xlsx.

workbook = openpyxl.load_workbook('/content/user.xlsx')

The above code loads the user.xlsx file from the location ‘/content/user.xlsx’. As you know, this Excel file contains one worksheet (sheet). To access that worksheet, call the method worksheets[0], as shown below.

worksheet = workbook.worksheets[0]

This line of code workbook.worksheets[0] select the first worksheet from the workbook (excel file). Here, index 0 represents the first worksheet; if your Excel file contains more than one worksheet, then the second worksheet will be represented by index 1 and so on for other worksheets.

Also, each worksheet contains the cells, and each cell contains the data, so in your case, you created two cells in the workbook, which were A1 and B1, if you remember.

So, to read the data from each cell, you will have to access that cell. To access that cell and its value, pass the cell name as an index to the worksheet as shown below.

data = worksheet['A1'].value

In the above code, worksheet[‘A1’].value accesses cell A1 of the worksheet, uses the ‘value’ attribute to access the value of that cell, and stores that value in a new variable called ‘data’.

Now, print the value in the data variable using the code below.

print(data)
Xlsxwriter Python Read Excel

Look at the output and successfully read the data from the cell ‘A1’ of the Excel file ‘user.xlsx’. Similarly, if you want to access the value of the cell ‘B1’, just specify the cell as worksheet[‘B1’].value.

READ:  Python dictionary length - Everything you need to know

This is how to read the Excel file on your system using the openpyxl module of Python.

From the above example, I hope that you understand how to create and read Excel.

Conclusion

In this Python tutorial, you learned about xlsxwriter read Excel to create and read the Excel file.

You also learned how to install the xlsxwriter and openpyxl modules in your environment, how to use them to create a new workbook, and how to use openpyxl to read the data from that workbook.

You may like to read: