How to Write Excel File in Python

Utilizing the Python programming language to create Excel files allows for various operations to be executed on a spreadsheet through the xlwt library. This approach is particularly effective for writing data and formatting content within files that utilize the .xls file extension.

Should you wish to record data into a file without the hassle of managing every single detail manually, employing a for loop can help streamline the entire procedure to some extent.

Write Excel File Using xlsxwriter Module

The xlsxwriter module allows us to create Excel files. This Python module is specifically designed for generating files in the XLSX format. It enables the writing of text, numerical values, and formulas across various worksheets. Additionally, it provides support for a variety of features, including charts, formatting options, images, page configuration, auto filters, conditional formatting, and much more.

To install the xlsxwriter module, we must execute the following command:

pip install xlsxwriter

Note: Throughout XlsxWriter, rows and columns are zero-indexed. The first cell in a worksheet is listed as, A1 is (0,0), B1 is (0,1), A2 is (1,0), B2 is (1,1), and so on.

Example: Writing to Excel Using A1 Notation

A1 notation refers to the way cells are identified in Excel by their column letter and row number. For instance, let's explore an example where we will use A1 notation to input text into an Excel spreadsheet.

import xlsxwriter

# here we are mentioning the full path where we want to save the file

file_path = r"C:\Users\mk\Desktop\logicpractice.xlsx"

# Create workbook at the specified location

workbook = xlsxwriter.Workbook(file_path)

worksheet = workbook.add_worksheet()

# Write values

worksheet.write('A1', 'Hello..')

worksheet.write('B1', 'T')

worksheet.write('C1', 'Point')

worksheet.write('D1', 'Tech')

worksheet.write('E1', 'Pvt Ltd')

# Close (save) workbook

workbook.close()

Output:

Explanation:

In the preceding example, we initiated the process by importing the xlsxwriter library. The Excel file was designated as 'logicpractice.xlsx'. We utilized A1 notation to input the data into the Excel spreadsheet.

Write an Excel File Using the openpyxl Module

This is identified as a package that is typically suggested for those who need to read from and write to .xlsx, xlsm, xltx, and xltm file formats. You can verify this by executing type(wb).

The load_workbook function accepts a parameter and outputs a workbook object, which signifies the file. Ensure that you are operating within the same directory as your spreadsheet. Failing to do so will result in an error during the import process.

By utilizing a for loop in conjunction with the range function, we can effortlessly print the values from the rows where column 2 contains data. In cases where those specific cells are unpopulated, the output will display None.

Python Example to Write an Excel File using the openpyxl Module

To illustrate the process of creating an Excel file with the help of the openpyxl library in Python, we will walk through a practical example.

# import openpyxl module

import openpyxl

# we are calling a Workbook() function of openpyxl

wb = openpyxl.Workbook()

sheet = wb.active

c1 = sheet.cell(row = 1, column = 1)

# writing values to cells

c1.value = "ABHAY"

c2 = sheet.cell(row= 1 , column = 2)

c2.value = "SINGH"

c3 = sheet['C1']

c3.value = "CONTENT DEVELOPER"

# A2 means column = 1 & row = 2.

c4 = sheet['A2']

c4.value = "VIVEK"

# B2 means column = 2 & row = 2.

c5 = sheet['B2']

c5.value = "SRIVASTAVA"

# printing in 2nd row and 3rd column

c6 = sheet['C2']

c6.value = "CONTENT REVIEWER"

# We are saving the file using the save() method in a specific location we want

wb.save(r"C:\Users\mk\Desktop\logicpractice_tech.xlsx")

Output:

Explanation:

In the example provided, the openpyxl library has been utilized to input data into an Excel file. The openpyxl library is compatible with file formats such as xlsx, xlsm, xltx, and xltm. In this instance, we have displayed the data across various rows and columns within the XLSX file.

Writing data to Excel files with xlwt

In addition to the XlsxWriter package, the xlwt library can also be utilized to generate spreadsheets that hold your data. This library serves as an alternative option for writing data and formatting details, making it well-suited for creating files with the .xls file extension. It offers a variety of functionalities for performing numerous tasks within the spreadsheet.

It offers functionalities including text formatting, image insertion, chart creation, page configuration, automatic filters, conditional formatting, and a variety of additional features.

Pandas offers robust functionalities for importing various types of data from Excel spreadsheets. Additionally, we can export the results back into pandas as well.

Python Example to Write data to Excel files with xlwt

To illustrate the process of writing data to Excel files using the xlwt library in Python, let’s consider a practical example.

import xlwt

# Create a new workbook

wb = xlwt.Workbook()

# Add a new sheet

sheet = wb.add_sheet('Sheet1')

# Write values to the cells

sheet.write(0, 0, "ABHAY")               # A1 (row=0, col=0)

sheet.write(0, 1, "SINGH")              # B1 (row=0, col=1)

sheet.write(0, 2, "CONTENT DEVELOPER") # C1 (row=0, col=2)

sheet.write(1, 0, "VIVEK")              # A2 (row=1, col=0)

sheet.write(1, 1, "SRIVASTAVA")        # B2 (row=1, col=1)

sheet.write(1, 2, "CONTENT REVIEWER")  # C2 (row=1, col=2)

# Save the workbook as an .xls file

file_path = r"C:\Users\mk\Desktop\xlwtfile.xls"

wb.save(file_path)

Note: The xlwt module only supports the .xls file, and that file isn't supported anymore. Hence, we can't display the output as the result produced is garbage data.

Writing Files with pyexcel

You can effortlessly export your arrays back to a spreadsheet by utilizing the saveas function. To do this, simply provide the array along with the name of the target file in the destfile_name argument.

It enables the specification of the delimiter by introducing the dest_delimiter parameter. You have the option to provide the character you wish to utilize as a delimiter within the quotation marks " ".

Python Example to Write Files with pyexcel

To illustrate the process of writing files using pyexcel in Python, let’s consider an example.

#importing the pyexcel

import pyexcel

# Data to write

data = [

    ['ABHAY', 'SINGH', 'CONTENT DEVELOPER', '100% attendance'],

    ['VIVEK', 'SRIVASTAVA', 'CONTENT REVIEWER', '95% attendance']

]

# Save as .xlsx file

file_path = r"C:\Users\mk\Desktop\hlogicpractice_tech.xlsx"

pyexcel.save_as(array=data, dest_file_name=file_path)

print(f"File saved at: {file_path}")

Output:

Explanation:

In the preceding example, the pyexcel module has been utilized to save the data into an Excel file that has the .xlsx file extension.

Conclusion

In this instructional guide, we explored different techniques available in Python for writing data to an Excel spreadsheet. Throughout this process, we utilized several libraries, including xlsxwriter, openpyxl, xlwt, and the pyexcel module, to effectively save data into the Excel file.

Python Write Excel File FAQs

1. Which libraries are commonly used to write Excel files in Python?

• ✅ Pandas: Pandas is the most popular and easiest library used to read Excel.
• ✅ Openpyxl: Openpyxl is also a popular library used to work with .xlsx files (read/write)
• ✅ Xlrd: Xlrd is a Legacy library, which means that it is not used now.

2. How can we write a DataFrame to an Excel file using pandas?

To export a DataFrame to an Excel file utilizing Pandas, we can use the following approach:

import pandas as pd

data = {

    "Name": ["John", "Lucy", "Peter"],

    "Age": [28, 34, 25]

}

df = pd.DataFrame(data)

df.to_excel("output.xlsx", index=False)

3. How can we write to a specific sheet in Excel?

We can directly input data into a designated sheet in Excel by utilizing the following code:

df.to_excel("output.xlsx", sheet_name="Employees", index=False)

4. How can we write multiple DataFrames to different sheets?

with pd.ExcelWriter("multi_sheets.xlsx") as writer:

    df.to_excel(writer, sheet_name="Sheet1", index=False)

    df.to_excel(writer, sheet_name="Sheet2", index=False)

5. How can we append new data to an existing Excel file?

from openpyxl import load_workbook

import pandas as pd

df = pd.DataFrame({"Name": ["Example Text"], "Age": [14]})

with pd.ExcelWriter("output.xlsx", engine="openpyxl", mode="a", if_sheet_exists="overlay") as writer:

    df.to_excel(writer, sheet_name="Employees", index=False, header=False, startrow=writer.sheets["Employees"].max_row)