I’m using aspose-cells-python and want to insert a dataframe into an excel sheet. I am able to do it using xlwings, how can i replicate it in aspose cells python via .net. Here is the xlwing code snippet:
wb = xw.Book(INPATH + xyz.xlsx’)
wb.sheets[0].range(“A3”).options(index=False).value = df
wb.save(OUTPATH + ‘output.xlsx’)
You may try the following sample code:
e.g.
Sample code:
# import the python package
import aspose.cells
from aspose.cells import License, Workbook, FileFormatType
workbook = Workbook(INPATH + "xyz.xlsx")
# Get the first worksheet
worksheet = workbook.worksheets[0]
# Get the "A3" cell
cells = worksheet.cells
cell = cells.get("A3")
cell.put_value(df)
workbook.save(OUTPATH + ‘output.xlsx’)
Also, see the Aspose.Cells for Python docs:
https://docs.aspose.com/cells/python-net/
Downloads:
Hope, this helps a bit.
I tried the given code but it doesnt seem to work, this is the error it is giving:
cell.put_value(df)
TypeError: [“can’t build bool from ‘DataFrame’”, “can’t build ‘Int32’ from ‘DataFrame’”, “can’t build ‘Double’ from ‘DataFrame’”, “can’t build String from ‘DataFrame’”, “can’t build String from ‘DataFrame’”, “can’t build String from ‘DataFrame’”, “can’t build DateTime from ‘DataFrame’”, ‘unknown variant type’]
I guess you need to insert data from Pandas Dataframe. There is no direct way to insert the dataframe into cells in one go via Aspose.Cells for Python via .NET, you have to traverse through the data/items to be inserted into different cells of the Excel worksheet accordingly. See the following sample code for your reference. I tested it and it works fine.
e.g.
Sample code:
import pandas as pd
import numpy as np
import aspose.cells
from aspose.cells import Workbook, CellsHelper, License
workbook = Workbook(INPATH + "xyz.xlsx")
# 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)
# convert DataFrame to NumPy array
values = df.values
rowindex = 1
colindex = 0
for row in values:
rowindex += 1
colindex = 0
for item in row:
cell = cells.get(rowindex, colindex)
cell.put_value(item)
colindex += 1
workbook.save(OUTPATH + "output.xlsx")
Hope, this helps a bit.
Yes, I have been using similar approach too. Thought of asking if there was any direct way to insert pandas dataframe in xlsx using aspose python via .NET.
Is there any way we can insert pandas df in xlsx via any aspose cloud API solution( Aspose.Cells Cloud - API References)?
@shiv12345
You can convert a Pandas DataFrame to JSON and then use Aspose.Cells’ JSON data import interface. Here’s a code example that should be helpful. Thank you!
import pandas as pd
from aspose.cells.utility import JsonUtility, JsonLayoutOptions
from aspose.cells import Workbook, CellsHelper, License
data = {'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['New York', 'San Francisco', 'Los Angeles']}
df = pd.DataFrame(data)
# Convert DataFrame to JSON
json_string = df.to_json(orient='records')
workbook = Workbook("Input.xlsx")
# 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("Output.xlsx")
Hope helps a bit!
The JSON approach is subject to testing if it will cater to our requirements.
So, there is no direct way to do it in aspose-cells-python or even in aspose cloud API solution( Aspose.Cells Cloud - API References )?
@shiv12345
The underlying logic of the aspose cloud API solution is using Aspose.Cells, so there is currently no direct way to insert Pandas dataframe in xlsx. Sorry for any inconvenience caused to you.
Yes, I figured so. Thanks!
@John.He As amjad shared, if I want to do this with the Aspose cloud API solution then the API calls for cells.get and put_value will run into thousands and so will the cost.
What solution do you suggest for doing this operation differently, can we do what @xinya.zhu suggested which was using JSON in Aspose cloud API solution? or maybe any other way.
FYI
Problem statement: I want to insert pandas dataframe into an existing xlsx file from a specific cell say A3 by avoiding the cell by cell insertion.
@shiv12345
You can achieve your goals by importing JSON data. Firstly, convert the Pandas dataframe into JSON data through Pandas, and then import JSON using the Aspose.Cells for Python via NET library. If you have any questions, please feel free to contact us.
This whole operation will be available in Aspose cloud API solution right?
It should be available but I am not sure about it. We recommend you to kindly post your query in dedicated cloud forum where one of our colleagues will assist you soon there.
@shiv12345 ,
At present, Cells Cloud does not offer direct support for importing JSON data. I’ve initiated a new ticket with the objective of implementing JSON data import functionality in the upcoming version.
Before the new version is released, I’m providing three temporary solutions:
Solution 1: Using the sheet copy function to copy JSON data to a specified sheet.
Blockquote POST http://api.aspose.cloud/v3.0/cells/Book1.xlsx/worksheets/book1/copy?sourceSheet=Sheet1&sourceWorkbook=input-fewdata.json&sourceFolder=TempFolderPath&folder=TempFolderPath
Solution 2: Converting JSON data to XML and importing it into the workbook.
Solution 3: Converting JSON data to a JSON batch data conversion format and then importing it.
These alternatives provide useful options for working with JSON data in the absence of direct support.