Convert Excel to Pandas DataFrame

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

Convert Excel to Pandas DataFrame via json data

Here’s an example code snippet to demonstrate how to export excel data to a pandas DataFrame via json data using Aspose.Cells for Python via .NET:

1. Create a Workbook and add some values.
2. Export excel data to JSON string.
3. Use the pandas library to read JSON data.

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

# Create a new Aspose.Cells Workbook
workbook = Workbook()

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

# Get the cells
cells = worksheet.cells

# Add some values
cells.get("A1").value = "Name"
cells.get("B1").value = "Age"
cells.get("C1").value = "City"
cells.get("A2").value = "Alice"
cells.get("B2").value = 25
cells.get("C2").value = "New York"
cells.get("A3").value = "Bob"
cells.get("B3").value = 30
cells.get("C3").value = "San Francisco"
cells.get("A4").value = "Charlie"
cells.get("B4").value = 35
cells.get("C4").value = "Los Angeles"

jsonSaveOptions = JsonSaveOptions()

# Save data to json string
json = JsonUtility.export_range_to_json(cells.max_display_range, jsonSaveOptions);

print(json)

# Read json string using pandas
dfData = pd.read_json(json)

print(dfData)

Convert Pandas DataFrame to Excel directly

Here’s an example code snippet to demonstrate how to export excel data to a pandas DataFrame directly using Aspose.Cells for Python via .NET:

1. Create a Workbook and add some values.
2. Traverse excel data and export data to Pandas DataFrame 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, JsonSaveOptions

# Create a new Aspose.Cells Workbook
workbook = Workbook()

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

# Get the cells
cells = worksheet.cells

# Add some values
cells.get("A1").value = "Name"
cells.get("B1").value = "Age"
cells.get("C1").value = "City"
cells.get("A2").value = "Alice"
cells.get("B2").value = 25
cells.get("C2").value = "New York"
cells.get("A3").value = "Bob"
cells.get("B3").value = 30
cells.get("C3").value = "San Francisco"
cells.get("A4").value = "Charlie"
cells.get("B4").value = 35
cells.get("C4").value = "Los Angeles"

rowCount = cells.max_data_row
columnCount = cells.max_data_column


columnDatas=[]
for c in range(columnCount + 1):
    currCell = cells.get_cell(0, c)
    columnDatas.append(currCell.value)

result = pd.DataFrame(columns=columnDatas, dtype=object)

for i in range(1, rowCount + 1):
    rowarray = []
    for j in range(columnCount + 1):
        currCell = cells.get_cell(i, j)
        rowarray.append(currCell.value)
    
    print(rowarray)
    result.loc[i - 1] = rowarray

print(result)