Summary of four methods for Python to write data to Excel

Python is widely used in the field of data processing, and interaction with Excel files is one of the common requirements.

This article will introduce four methods of using Python to write data to Excel files, and explain them in combination with life examples to help novice beginners get started quickly.

1. Use the openpyxl library

openpyxl is a Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. It supports reading and modifying cells, styles, formulas, etc. of Excel files.

Suppose you are a teacher and need to write the student's test scores into an Excel file.

from openpyxl import Workbook
 
# Create a new workbookwb = Workbook()
 
# Get the default worksheetws = 
 
# Write dataws['A1'] = 'Name'
ws['B1'] = 'score'
ws['A2'] = 'Zhang San'
ws['B2'] = 90
ws['A3'] = 'Li Si'
ws['B3'] = 85
 
# Save the file('Student grades.xlsx')

Code description

• Workbook() Creates a new workbook object.

• Get the default worksheet.

• ws['A1'] = 'Name' Write the string "Name" to cell A1.

• ('Student Grade.xlsx') Save the workbook as a file named "Student Grade.xlsx"

2. Use the xlsxwriter library

xlsxwriter is a Python library for creating Excel xlsx files. It supports formatting cells, inserting charts, pictures and other functions.

Suppose you are a salesperson and need to write monthly sales data into an Excel file and generate a chart.

import xlsxwriter
 
# Create a new workbookworkbook = ('Sales Data.xlsx')
 
# Add a worksheetworksheet = workbook.add_worksheet()
 
# Write datadata = [
    ['month', 'Sales'],
    ['January', 100],
    ['February', 120],
    ['March', 150],
]
 
row = 0
col = 0
for item in data:
    worksheet.write_row(row, col, item)
    row += 1
 
# Create a chartchart = workbook.add_chart({'type': 'column'})
chart.add_series({
    'name': 'Sales',
    'categories': '=Sheet1!$A$2:$A$4',
    'values': '=Sheet1!$B$2:$B$4',
})
worksheet.insert_chart('D2', chart)
 
# Close the workbook()

Code description

('Sales Data.xlsx') Creates a new workbook object.

workbook.add_worksheet() Adds a new worksheet.

worksheet.write_row(row, col, item) Writes a row of data to the worksheet.

workbook.add_chart() Creates a chart object.

chart.add_series() Adds a chart data series.

worksheet.insert_chart('D2', chart) Insert the chart into the worksheet.

() Close the workbook and save the file.

3. Use the pandas library

pandas is a powerful data analysis library that allows easy reading and writing data in various formats, including Excel files.

Suppose you are a data analyst and need to read data from the database and write it to an Excel file.

import pandas as pd
 
# Create a DataFramedata = {
    'Name': ['Zhang San', 'Li Si', 'Wang Wu'],
    'age': [20, 25, 30],
    'City': ['Beijing', 'Shanghai', 'Guangzhou'],
}
df = (data)
 
# Write DataFrame to Excel filedf.to_excel('User Information.xlsx', sheet_name='Sheet1', index=False)

Code description

(data) Creates a DataFrame object.

df.to_excel('User Information.xlsx', sheet_name='Sheet1', index=False) Writes DataFrame to an Excel file named "User Information.xlsx", specifying the worksheet name "Sheet1", and does not write to the row index.

4. Use win32com library (Windows Only)

win32com is a Python library for accessing Windows COM objects that can be used to manipulate Excel applications.

Suppose you already have an Excel file and need to add some data to it. You can use win32com to open the Excel application and do it.

import 
 
# Open Excel applicationexcel = ("")
 
# Open the workbookworkbook = ("Already have file.xlsx")
 
# Get a worksheetworksheet = ("Sheet1")
 
# Write data(1, 1).Value = "New Data"
 
# Save the file()
 
# Close Excel application()

Code description

("") Open the Excel application.

("Already File.xlsx") Open a workbook named "Already File.xlsx".

("Sheet1") Gets a worksheet named "Sheet1".

(1, 1).Value = "New Data" Writes the string "New Data" to cell A1.

() Save the workbook.

() Close the Excel application.

This is the end of this article about the four methods of writing data to Excel by Python. For more related contents of Python Excel writing data, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!