Create and Read Excel Files in Python

Today many professionals—data scientists, analysts, and automation engineers—use Python to work with Excel ([XLS][16], [XLSX][17]) files. Python libraries automate data manipulation, eliminating manual Excel work. In this article you will learn how to create and read Excel files in Python with a Python Excel library, and how to add data, pivot tables, and charts.

This article will cover the following points:

  • [Installing Python Excel Library][1]
  • [How to Read Excel (XLS or XLSX) Files in Python][2]
  • [Create Excel Files in Python][3]
  • [Writing to an Excel Sheet using Python][14]
  • [Creating Charts in Excel Sheets in Python][13]
  • [Adding Pivot Table in Excel using Python][12]
  • [FAQs][30]

Python Excel Library - Installation

To create and read Excel (XLS, XLSX) files in Python, we will use [Aspose.Cells for Python via Java][5]. The efficacy of this Python Excel library is manifest in its ability to cater to a litany of tasks related to Excel files, such as data extraction, data manipulation, and even the creation of Excel files from scratch.

Please [download][6] or install the package from PyPI using the pip command given below:

pip install aspose-cells

Reading Excel Files in Python

An Excel workbook contains worksheets, each with a grid of cells identified by row and column indexes. While many packages can read Excel files in Python, this section uses Aspose Python Excel library.

Following are the steps to read XLSX in Python:

  1. First, load the Excel file using the [Workbook][20] class.
  2. Then, obtain a reference to the [WorksheetCollection][21] using [Workbook.getWorksheets()][22] method.
  3. Finally, loop through the worksheets in the collection, and for each worksheet, iterate through its rows and columns to access and print the cell values.

The following code sample demonstrates how to read an Excel file using Python.

To reference a specific worksheet, use the [Workbook.getWorksheets().get(index)][24] method. The following code sample shows such a case.

Python Create Excel Files

This section shows how to create Excel files in Python.

Following are the steps to create an Excel file in Python:

  1. Firstly, create a new object of [Workbook][20] class.
  2. Secondly, get the reference of the desired [Worksheet][23] using [Workbook.getWorksheets().get(index)][24] method.
  3. After that, insert values in the desired cells using [Worksheet.getCells().get().putValue()][25] method.
  4. Lastly, save the workbook using [Workbook.Save][26] method.

The following code sample demonstrates how to create an Excel file using Python. You will see the output as follows:

Python Create Excel Files

Python Write Data to Excel

We have covered how to create and read Excel files in Python with our Python Excel library. Next, let’s write data to an Excel file. Create or load a workbook as shown earlier, then use the [Worksheet.getCells().get().putValue()][25] method to write values to specific cells.

Python code for writing data to Excel file is given below: The output will be shown as follows:

Python Write Data to Excel

Python Creating Charts in Excel Sheets

Charts visualize data trends and relationships. Our Python Excel library supports many chart types—bar, column, line, scatter, pie, and more—that can be customized. Below is how to create a chart in an Excel sheet.

The steps to create a chart in an Excel file in Python are given as follows:

  1. Create or load an Excel file using the [Workbook][20] class.
  2. Add values in the cells of the desired worksheet.
  3. Obtain chart collection using the [Worksheet.getCharts()][27] method.
  4. Add a new chart in the charts collection using [Worksheet.getCharts().add(type, upperLeftRow, upperLeftColumn, lowerRightRow, lowerRightColumn)][28] method.
  5. Specify NSeries for the chart.
  6. Save the Excel file using the [Workbook.save(fileName)][26] method.

The following code shows how to create a chart in an Excel sheet using Python: The following screenshot shows the output of the above code.

Python Creating Charts in Excel Sheets

Python Excel Pivot Table: How to Create Pivot Table in Excel using Python

Pivot tables let you quickly analyze large data sets by summarizing and organizing information. This section shows how to create a pivot table in Excel using Python.

Steps