Convert Pandas DataFrame to Excel
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:
- Create a sample pandas DataFrame data.
- 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:
- Create a sample pandas DataFrame data.
- Convert DataFrame to CSV using pandas.
- 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:
- Create a sample pandas DataFrame data.
- Use the pandas library to convert the DataFrame data into JSON data.
- 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" | |
) |