Write Different Data Formats to Excel with Python: Text, Arrays, CSV, XML

Excel is widely used for data processing and reporting, but manually entering data can be slow and error-prone. Python offers an efficient way to automate this task. With its powerful libraries and simple syntax, Python makes it easy to write data to Excel—whether you're dealing with small datasets or large volumes of information. Automating the process not only saves time but also reduces human errors, making your workflow faster and more reliable.

Notice: This guide uses Spire.XLS for Python, a robust library that lets you write data to Excel files without needing Microsoft Office.

To install it, run pip install spire.xls, or use pip install free-spire.xls for the free version.

Introduction to Writing Data to Excel

The API used in this guide provides the Workbook class to represent an Excel workbook. You can either create a new workbook by instantiating the Workbook class or load an existing file using the LoadFromFile() method. Note that a newly created workbook contains three worksheets by default.

The API also includes the Worksheet class along with various methods and properties to manipulate worksheet content, including cell data and formatting.

Common data sources include CSV files, plain text, arrays, and XML. When generating reports or structured tables, using code to write such data into Excel files can significantly improve accuracy and efficiency.

The Worksheet.Range[str] property allows access to specific cells or cell ranges using Excel-style references—for example, "A1" for a single cell or "A1:C3" for a range.

To save the workbook, use the Workbook.SaveToFile(str, FileFormat) method, where FileFormat specifies the output format of the Excel file.

Writing Text or Numeric Data to Excel Cells with Python

After creating or loading an Excel workbook using the Workbook class, you can write data and apply formatting using its available methods. Here's how to write text or numbers to specific Excel cells:

• Create a Workbook object to generate an Excel workbook.

• Use Workbook.Worksheets.Clear() to remove the default worksheets, and Workbook.Worksheets.Add() to add a new one.

• Set cell formatting using properties of the Worksheet class.

• Use the Worksheet.Range[].Text property to insert text or numeric values into specified cells.

• Call the Workbook.SaveToFile() to save the workbook.

Code example:

from spire.xls import Workbook
from spire.xls import Worksheet
from spire.xls import FileFormat

# Create an instance of Workbook
workbook = Workbook()

# Delete the default worksheet and creat a new one
workbook.Worksheets.Clear()
worksheet = workbook.Worksheets.Add("Members")

# Set cell format
worksheet.Range["A1:D1"].Style.Font.IsBold = True
worksheet.Rows[0].RowHeight = 30
worksheet.Range["A1:D1"].Style.Font.Size = 12
worksheet.Range["A2:D4"].Style.Font.Size = 11

# Write header data
worksheet.Range["A1"].Text = "NO."
worksheet.Range["B1"].Text = "Name"
worksheet.Range["C1"].Text = "Age"
worksheet.Range["D1"].Text = "Job"

# Write other data
worksheet.Range["A2"].Text = "1"
worksheet.Range["A3"].Text = "2"
worksheet.Range["A4"].Text = "3"

worksheet.Range["B2"].Text = "Alice"
worksheet.Range["B3"].Text = "Luisa"
worksheet.Range["B4"].Text = "Mike"

worksheet.Range["C2"].Text = "35"
worksheet.Range["C3"].Text = "28"
worksheet.Range["C4"].Text = "42"

worksheet.Range["D2"].Text = "Programmer"
worksheet.Range["D3"].Text = "Lawyer"
worksheet.Range["D4"].Text = "Doctor"

# Save the workbook
workbook.SaveToFile("/output/writecontenttocells.xlsx", FileFormat.Version2016)
workbook.Dispose()

Write Arrays to Excel Worksheet with Python

You can use the Worksheet.InsertArray(arrObject, firstRow, firstColumn, isVertical) method to insert arrays into an Excel worksheet, specifying the starting position and direction. Let’s check out how it works in Python.

Detailed steps to write arrays to an Excel worksheet:

• Create a Workbook object.

• Clear default worksheets and add a new one named "Sales Data".

• Set cell formatting as needed.

• Create a 2D array tableArray.

• Loop through the array and insert data into specified cells using the Worksheet.InsertArray() method.

• Save and close the workbook using the SaveToFile() method.

Code example:

from spire.xls import Workbook, FileFormat

# Create an instance of Workbook
workbook = Workbook()

# Delete the default worksheet and create a new one
workbook.Worksheets.Clear()
worksheet = workbook.Worksheets.Add("Sales Report")

# Set cell format
worksheet.Range["A1:C1"].Style.Font.IsBold = True
worksheet.Rows[0].RowHeight = 30
worksheet.Range["A1:C1"].Style.Font.Size = 12
worksheet.Range["A2:C7"].Style.Font.Size = 11

# Specify the data
tableArray = [
    ["Date", "Product", "Sales"],
    ["20220101", "A", "1000"],
    ["20220101", "B", "1500"],
    ["20220102", "A", "1200"],
    ["20220102", "B", "1800"],
    ["20220103", "A", "900"],
    ["20220103", "B", "1600"]
]

# Insert each row individually
row_index = 1
for row in tableArray:
    worksheet.InsertArray(row, row_index, 1, False)  # False = horizontal row
    row_index += 1

# Save the workbook
workbook.SaveToFile("/output/writecontenttoworksheet.xlsx", FileFormat.Version2016)
workbook.Dispose()

Writing CSV Data to an Excel Worksheet Using Python

When writing CSV data to an Excel worksheet, you can directly use the Workbook.LoadFromFile() method to load a CSV file and save it as an XLSX file, effectively converting the CSV into an Excel workbook. When loading the CSV file, you can specify the delimiter as a parameter to support CSV files with different separators.

Key steps explained:

• Create an instance of the Workbook class.

• Use the Workbook.LoadFromFile() method to load the CSV file.

• Use the Workbook.SaveToFile() method to save the CSV as an Excel workbook.

Code example:

from spire.xls import Workbook
from spire.xls import FileFormat

# Create a Workbook object
workbook = Workbook()

# Load a CSV file
workbook.LoadFromFile("/input/sample csv.csv", ",")

# Save the Excel file
workbook.SaveToFile("/output/writeCSVdata.xlsx", FileFormat.Version2016)
workbook.Dispose()

Write XML Data to an Excel Worksheet Using Python

To write XML data into an Excel worksheet, you first need to read and parse the XML file. Then, write the headers to the worksheet, followed by inserting the corresponding data into cells. The code should be adjusted based on the structure of the XML file. Here’s how it works in Python.

• Create a Workbook object.

• Clear the default worksheet and add a new one using the Workbook.Worksheets.Add() method.

• Set the desired cell formatting.

• Load the XML data using the parse() method from the xml.etree.ElementTree module to get the root and child elements.

• Use the first student node to determine the number of columns and extract the header fields.

• Write the headers to the worksheet by iterating over the header nodes and setting them as column titles.

• Write the data by iterating through each student node and its child nodes, using the Worksheet.SetValue() method to insert the values into the worksheet.

• Save the workbook to an Excel file.

Code example:

from spire.xls import *
import xml.etree.ElementTree as ET

# Create a Workbook object
workbook = Workbook()

# Clear the default worksheet and add a worksheet
workbook.Worksheets.Clear()
worksheet = workbook.Worksheets.Add("Student Data")

# Set cell format
for i in range(len(worksheet.Columns)):
    col = worksheet.Columns.get_Item(i)
    col.ColumnWidth = 10

# Load an XML file
xml_tree = ET.parse("/input/studentdata.xml")
xml_root = xml_tree.getroot()

# Get the first order node to determine the number of columns and table headers
first_student = xml_root.find("student")
header = list(first_student.iter())[1:]  # Skip the first node

# Write the table header
for col_index, header_node in enumerate(header, start=1):
    header_text = header_node.tag
    worksheet.SetValue(1, col_index, header_text)

# Write data
row_index = 2
for student in xml_root.iter("student"):
    for col_index, data_node in enumerate(list(student.iter())[1:], start=1):  # Skip the first node
        value = data_node.text
        header_text = list(header[col_index - 1].iter())[0].tag
        worksheet.SetValue(row_index, col_index, value)
    row_index += 1

# Save the Workbook
workbook.SaveToFile("/output/writeXMLdata.xlsx")
workbook.Dispose()

The Bottom Line

With the methods demonstrated above, you can easily write text, arrays, CSV, or XML data into Excel worksheets. Choosing the right approach based on your data format can help streamline your workflow and improve efficiency. Hopefully, the examples provided in this article serve as a useful reference for your data handling tasks.