Python Lists to Excel: Clean Data Export and Formatting

In today’s data-driven world, Python has become a go-to language for data processing and automation. A very common requirement in real-world applications is exporting processed data—especially data organized as Python lists—into Excel files for sharing, visualization, reporting, or further analysis.

While Python offers multiple ways to write data to Excel, many developers struggle to find a solution that is both efficient and flexible, particularly when formatting, styling, and handling larger datasets are involved. Choosing the right library can make a significant difference in both development speed and output quality.

In this article, we’ll explore how to write Python list data to Excel files using Spire.XLS for Python, a powerful and developer-friendly Excel library. Beyond basic data export, it enables advanced formatting, precise layout control, and scalable data handling—making it well suited for automation workflows and report generation.

Environment Setup

Spire.XLS for Python is a professional Excel library designed for creating, reading, editing, converting, and printing Excel files programmatically. One of its key advantages is that it works without requiring Microsoft Excel or Office to be installed, which makes it especially suitable for server-side and cloud environments.

The library supports a wide range of Excel formats, including .xls, .xlsx, .xlsm, and .xlt. It also provides a rich API for working with workbooks, worksheets, cells, styles, charts, formulas, comments, and more.

When writing list data to Excel, Spire.XLS for Python allows you to:

• Precisely control where data is written
• Apply cell-level formatting and styles
• Automatically handle different data types
• Generate well-structured and visually polished Excel files

Installation

Before getting started, make sure Spire.XLS for Python is installed in your environment. You can install it easily using pip:

pip install Spire.XLS

Once installed, you can import and use it directly in your Python projects.

Writing a Python List to Excel: Basic Example

Let’s begin with a simple scenario: exporting a basic Python list to an Excel file.

Scenario

Assume we have a list where each inner list represents a row of data:

data = [
    ["Department", "Budget (USD)", "Actual Spend (USD)", "Difference"],
    ["Marketing", 80000, 76500, -3500],
    ["Engineering", 150000, 162300, 12300],
    ["Administration", 50000, 48200, -1800]
]

Code Example

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

def write_simple_list_to_excel(data_list, file_path="simple_data.xlsx"):
    """
    Write a simple Python list to an Excel file.
    :param data_list: List containing data, where each sublist represents a row.
    :param file_path: Output Excel file path.
    """
    # Create a new workbook
    workbook = Workbook()
    # Get the first worksheet
    sheet = workbook.Worksheets[0]

    # Iterate through the list and write data to cells
    for row_idx, row_data in enumerate(data_list):
        for col_idx, cell_value in enumerate(row_data):
            # Assign values to corresponding cells
            sheet.Range[row_idx + 1, col_idx + 1].Value = str(cell_value)

    # Autofit column widths
    sheet.Range.AutoFitColumns()

    # Save the workbook as an Excel file
    workbook.SaveToFile(file_path, FileFormat.Version2016)
    workbook.Dispose()
    print(f"Data successfully written to: {file_path}")

# Call the function
write_simple_list_to_excel(data)

Output Preview

Key Points Explained

1. Workbook() creates a new Excel workbook.
2. workbook.Worksheets[0] retrieves the default worksheet.
3. Range[row, column] uses 1-based indexing to locate cells.
4. Value assigns data to a cell. Strings and numbers are handled automatically.
5. SaveToFile() saves the workbook in .xlsx format.
6. Dispose() releases resources and prevents memory leaks.

Important Note:
Row and column indices in Spire.XLS start at 1, not 0, which is why row_idx + 1 and col_idx + 1 are used.

Advanced Usage: Formatting and Complex Data Types

In real applications, Excel files often require more than raw data. Titles, styles, column widths, and proper date formatting are usually essential.

Scenario

• The first row should be treated as a header with formatting.
• Data includes dates and timestamps that must display correctly.

Code Example

from spire.xls import *
import datetime

def write_advanced_list_to_excel(data_list, file_path="advanced_data.xlsx"):
    """
    Write list data with headers and formatting to Excel.
    :param data_list: List containing data, first row as headers.
    :param file_path: Output Excel file path.
    """
    workbook = Workbook()
    sheet = workbook.Worksheets[0]

    # Write header row with styles
    header_data = data_list[0]
    for col_idx, header_text in enumerate(header_data):
        header_cell = sheet.Range[1, col_idx + 1]
        header_cell.Value = header_text
        header_cell.Style.Font.IsBold = True
        header_cell.Style.KnownColor = ExcelColors.LightYellow
        header_cell.Style.HorizontalAlignment = HorizontalAlignType.Center

    # Write data rows
    for row_idx, row_data in enumerate(data_list[1:]):
        for col_idx, cell_value in enumerate(row_data):
            cell = sheet.Range[row_idx + 2, col_idx + 1]

            if isinstance(cell_value, datetime.datetime):
                cell.DateTimeValue = DateTime.Parse(str(cell_value))
                cell.Style.NumberFormat = "yyyy-mm-dd hh:mm"
            elif isinstance(cell_value, datetime.date):
                cell.DateTimeValue = DateTime.Parse(str(cell_value))
                cell.Style.NumberFormat = "yyyy-mm-dd"
            else:
                cell.Value = str(cell_value)

    # Auto-fit columns
    for i in range(1, 5):
        sheet.AutoFitColumn(i)

    workbook.SaveToFile(file_path, FileFormat.Version2016)
    workbook.Dispose()
    print(f"Formatted Excel file created: {file_path}")

# Sample data with dates
advanced_data = [
    ["Order ID", "Customer", "Amount (USD)", "Order Date"],
    ["SO-20231026001", "Shenzhen Zhilian Tech", 32800, datetime.date(2023, 10, 26)],
    ["SO-20231025002", "Beijing Ruida Info", 15600, datetime.datetime(2023, 10, 25, 14, 30)],
    ["SO-20231027003", "Shanghai Qiming Systems", 48900, datetime.date(2023, 10, 27)],
    ["SO-20231028004", "Hangzhou CloudData", 21450, datetime.datetime(2023, 10, 28, 9, 15)]
]

write_advanced_list_to_excel(advanced_data)

Output Preview

Notable Features

• Cell Styling via cell.Style (font, color, alignment)
• Date handling using DateTimeValue and NumberFormat
• Automatic column sizing with AutoFitColumn()

Error Handling and Performance Optimization

Common Issues and Solutions

• Invalid file paths or permissions
  Ensure the output directory exists and has write permissions.

• Incorrect data types
  Use dedicated properties like DateTimeValue for date-related data.

• Large datasets and memory usage
  Consider batch writing or preprocessing data before exporting.

Recommended Pattern

try:
    workbook = Workbook()
    # Excel operations
    workbook.SaveToFile(file_path, FileFormat.Version2016)
except Exception as e:
    print(f"Error writing Excel file: {e}")
finally:
    if 'workbook' in locals():
        workbook.Dispose()

Performance Tips

1. Save the workbook only once, after all data is written.
2. Apply styles to ranges instead of individual cells when possible.
3. Preprocess data before passing it to the Excel library.
4. Always call Dispose() to release resources.

Conclusion

This article demonstrated how to export Python list data to Excel using Spire.XLS for Python, starting from basic data writing and extending to advanced formatting and robust error handling. Whether you’re generating reports, exporting analysis results, or building automated workflows, this approach provides both flexibility and reliability.

By exploring additional features such as charts, formulas, and data validation, you can further integrate Spire.XLS for Python into your data processing pipeline and unlock even greater automation potential.