Easily Create or Edit Excel Files with Python

Excel files are an essential tool for storing, managing, analyzing, and presenting data. They are widely used in various industries, such as finance, accounting, marketing, and healthcare, to perform a spectrum of data processing tasks. Given the ubiquity of Excel and its usefulness, it’s crucial to know how to create Excel files. In this article, we will explain how to create Excel files along with how to edit Excel files using Python.

• Create Excel XLS or XLSX Files in Python
• Edit Excel XLS or XLSX Files in Python

Python Library to Create or Edit Excel Files

To create and edit Excel files in Python, this article uses a Python Excel library: Spire.XLS for Python.

Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.

You can easily install Spire.XLS for Python from pypi by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.

Create Excel XLS or XLSX Files in Python

You can create Excel XLS or XLSX files with a wide variety of elements such as text, numeric data, images, formulas, hyperlinks, comments, shapes, and charts with Spire.XLS for Python.

In the following code snippet, you will see how to create an Excel XLS or XLSX file with text and numeric data in Python:

from spire.xls import *
from spire.xls.common import *

#Create a Workbook
workbook = Workbook()
#Get the first worksheet
sheet = workbook.Worksheets[0]

#Add some data to the worksheet
sheet.Range[1, 1].Text = "Department"
sheet.Range[2, 1].Text = "Accounting and Finance"
sheet.Range[3, 1].Text = "Research and Development"
sheet.Range[4, 1].Text = "Human Resources"
sheet.Range[5, 1].Text = "Sales"
sheet.Range[1, 2].Text = "Name"
sheet.Range[2, 2].Text = "Martin"
sheet.Range[3, 2].Text = "Patrick"
sheet.Range[4, 2].Text = "Karen"
sheet.Range[5, 2].Text = "Anthony"
sheet.Range[1, 3].Text = "Salary"
sheet.Range[2, 3].NumberValue = 6100
sheet.Range[3, 3].NumberValue = 7800
sheet.Range[4, 3].NumberValue = 5400
sheet.Range[5, 3].NumberValue = 7400

#Set style for cells of the header row
sheet.Range[1, 1, 1, 3].Style.Font.FontName = "Calibri"
sheet.Range[1, 1, 1, 3].Style.Font.Size = 12
sheet.Range[1, 1, 1, 3].Style.Font.IsBold = True
sheet.Range[1, 1, 1, 3].Style.HorizontalAlignment = HorizontalAlignType.Center

#Set style for cells of the remaining rows
sheet.Range[2, 1, 5, 3].Style.Font.FontName = "Calibri"
sheet.Range[2, 1, 5, 3].Style.Font.Size = 11
sheet.Range[2, 1, 5, 3].Style.HorizontalAlignment = HorizontalAlignType.Center

#Autofit column width
sheet.AllocatedRange.AutoFitColumns()

#Save the result file to an XLSX file
workbook.SaveToFile("CreateExcel.xlsx", ExcelVersion.Version2016)
#Save the result file to an XLS file
#workbook.SaveToFile("CreateExcel.xls", ExcelVersion.Version97to2003)
workbook.Dispose()

Create Excel XLS or XLSX Files With Python

Edit Excel XLS or XLSX Files in Python

In addition to creating an Excel file from scratch, you can also edit an existing Excel file. For example, you can edit the existing elements in the file or add new elements to the file.

In the following code snippet, you will see how to update the value of a cell in an Excel file in Python:

from spire.xls import *
from spire.xls.common import *

#Create a Workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("CreateExcel.xlsx")

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Update the text of the cell [2, 1]
sheet.Range[2, 1].Text = "Updated Cell"
#Set a background color for the cell [2, 1]
sheet.Range[2, 1].Style.Color = Color.get_MediumSpringGreen()

#Save the result file to an XLSX file
workbook.SaveToFile("EditExcel.xlsx", ExcelVersion.Version2016)
#Save the result file to an XLS file
#workbook.SaveToFile("EditExcel.xls", ExcelVersion.Version97to2003)
workbook.Dispose()

Edit Excel XLS or XLSX Files With Python

Conclusion

In this article, we have explored how to create or edit Excel files in Python. We hope it’s helpful to you.

Related Topics

Python — How to Convert Excel XLS or XLSX to PDF

Read Data from Excel Files in Python — A Comprehensive Guide

Python — How to Export Data from Database to Excel (Step by Step Guide)