Convert Pandas DataFrame to Excel

Using Aspose.Cells for Python via .NET API, you can convert pandas DataFrame to Excel , OpenOffice, Pdf, Json and many different formats.

Convert Pandas DataFrame to Excel directly

Here’s an example code snippet to demonstrate how to import data from a pandas DataFrame to an Excel file using Aspose.Cells for Python via .NET:

1. Create a sample pandas DataFrame data.
2. Traverse DataFrame and import data using Aspose.Cells for Python via .NET.

import pandas as pd
import aspose.cells
from aspose.cells import Workbook, CellsHelper, License

workbook = Workbook()
# Get the first worksheet
worksheet = workbook.worksheets[0]
# Get the cells
cells = worksheet.cells

# create a sample DataFrame
data = {'name': ['Alice', 'Bob', 'Charlie', 'David'],
        'age': [25, 32, 18, 47],
        'city': ['New York', 'Paris', 'London', 'Berlin']}

df = pd.DataFrame(data)

rowindex = 0
colindex = 0
for column in df:
    cell = cells.get(rowindex, colindex)
    cell.put_value(df[column].name)        
    colindex += 1

  
for index, row in df.iterrows():
    rowindex += 1
    colindex = 0
    cell = cells.get(rowindex, colindex)
    cell.put_value(row["name"])        
    colindex += 1
    cell = cells.get(rowindex, colindex)
    cell.put_value(row["age"])        
    colindex += 1
    cell = cells.get(rowindex, colindex)
    cell.put_value(row["city"])        
    colindex += 1

workbook.save("out.xlsx")

Convert Pandas DataFrame to XLSX via CSV format

Converting a pandas DataFrame to CSV is a common operation. Using Aspose.Cells for Python via .NET, we can perform additional processing on the generated CSV file, such as creating charts and saving as an XLSX file. The following code demonstrates this workflow:

1. Create a sample pandas DataFrame data.
2. Convert DataFrame to CSV using pandas.
3. Create charts and save as XLSX with Aspose.Cells for Python via .NET.

import pandas as pd
from aspose.cells import Workbook, SaveFormat

# Create a sample DataFrame
df = pd.DataFrame({
    'ProductA': [50, 100, 170, 300],
    'ProductB': [160, 32, 50, 40],
    'Period': ["Q1", "Q2", "Q3", "Q4"]
})

# Save to CSV file 
df.to_csv("ProductData.csv", index=False)
# Load CSV with Aspose.Cells for Python via .NET
workbook = Workbook("ProductData.csv") 
worksheet = workbook.worksheets[0]
# Add a column chart to the worksheet
chartIndex = worksheet.charts.add(ChartType.COLUMN, 5, 0, 15, 5)
# Get reference to the newly added chart
chart = worksheet.charts[chartIndex]
# Add SeriesCollection (chart data source) ranging from cells "A2" to "B5"
chart.n_series.add("A2:B5", True)
# Set series names explicitly
chart.n_series[0].name = "Product A"
chart.n_series[1].name = "Product B"
# Set category data source for SeriesCollection
chart.n_series.category_data = "C2:C5"
# Save as Excel file
workbook.save("ProductDataChart.xlsx", SaveFormat.XLSX)

Convert Pandas DataFrame to Excel via json data

Here’s an example code snippet to demonstrate how to import data from a pandas DataFrame to an Excel file using Aspose.Cells for Python via .NET:

1. Create a sample pandas DataFrame data.
2. Use the pandas library to convert the DataFrame data into JSON data.
3. Import JSON data using Aspose.Cells for Python via .NET.

import pandas as pd
from aspose.cells.utility import JsonUtility, JsonLayoutOptions
from aspose.cells import Workbook, Worksheet, Cells

# Create a sample pandas DataFrame
data = {'Name': ['Alice', 'Bob', 'Charlie'],
        'Age': [25, 30, 35],
        'City': ['New York', 'San Francisco', 'Los Angeles']}
        	
df = pd.DataFrame(data)
# Convert pandas DataFrame to JSON
json_string = df.to_json(orient='records')

workbook = Workbook()
# Get the first worksheet
worksheet = workbook.worksheets[0]

# Get the cells
cells = worksheet.cells
options = JsonLayoutOptions()
unit = JsonUtility()

#  Processes  as table.
options.array_as_table = True
unit.import_data(json_string, cells, 0, 0, options)

workbook.save("out.xlsx")

Exporting Multiple Pandas DataFrames to Excel Sheets

Sometimes, we may also want to insert multiple DataFrames into different worksheets in an Excel file.The following example demonstrates this process.

# Insert multiple DataFrames
import pandas as pd
from aspose.cells import Workbook, WorksheetCollection, SaveFormat

def write_multiple_dataframes_to_excel(dataframes, sheet_names, output_path):
    """
    Write multiple DataFrames to multiple sheets in an Excel file 
    :param dataframes: List of pandas.DataFrame 
    :param sheet_names: List of sheet names (same length as dataframes) 
    :param output_path: Output Excel file path 
    """
    # Create a blank workbook
    workbook = Workbook()

    # There is a default blank sheet — you may delete it to avoid redundancy (optional)

    for df, name in zip(dataframes, sheet_names):
        # Add a new worksheet
        worksheet = workbook.worksheets.add(name)
        cells = worksheet.cells

        # Write column headers
        for col_idx, col_name in enumerate(df.columns):
            cells.get(0, col_idx).put_value(str(col_name))

        # Write row data
        for row_idx, row in enumerate(df.itertuples(index=False), start=1):
            for col_idx, value in enumerate(row):
                cells.get(row_idx, col_idx).put_value(value)

    # Save as Excel file
    workbook.save(output_path, SaveFormat.XLSX)

# Create two DataFrames
df1 = pd.DataFrame({
    "Name": ["Alice", "Bob"],
    "Age": [25, 30]
})

df2 = pd.DataFrame({
    "Product": ["Book", "Pen"],
    "Price": [10.5, 1.99]
})

# Write to Excel
write_multiple_dataframes_to_excel(
    dataframes=[df1, df2],
    sheet_names=["People", "Products"],
    output_path="MultiDataFrame.xlsx"
)