https://api.aspose.cloud/v3.0/cells/{file_name}/importdata
I want to output JSON data with the above REST API at the specified position. The data will be output on the first line even if the start line is specified as 10 as shown below. Is there a difference in usage?
request_body = {
“Data”: json_data,
“ImportDataType”: “JsonToTable”,
“InsertRows”: False,
“StartRow”: 10,
“StartColumn”:. 2,
“DestinationWorksheet”: sheet_name
}
@Skillnavi
Could you please clarify what specific issue you are facing with the output JSON data when using the importdata API? Are you receiving an error or is the output not as expected?
No error occurred.
The specified JSON is output in tabular format, but the position specification does not work.
I want to overwrite the specified start row and column with the JSON data specified at the beginning.
@Skillnavi ,
Thank you for your feedback. We will provide you with a solution.
@Skillnavi ,
Regarding batch data import, we recommend the following three methods:
- Importing data from a CSV file.
- Batch importing Cell data.
- Importing JSON data.
Please refer to the Net SDK code for the three APIs:
CellsApi cellsApi = new CellsApi(Environment.GetEnvironmentVariable("CellsCloudClientId"), Environment.GetEnvironmentVariable("CellsCloudClientSecret") );
cellsApi.UploadFile(new UploadFileRequest(@"D:\cells.cloud.family\cells.cloud\src\testdata\Cells\TestImportData.xlsx", "TestImportData.xlsx"));
cellsApi.UploadFile(new UploadFileRequest(@"D:\cells.cloud.family\cells.cloud\src\testdata\Cells\TestImportDataCSV.csv", "TestImportDataCSV.csv"));
ImportCSVDataOption importCSVDataOption = new ImportCSVDataOption();
importCSVDataOption.ConvertNumericData = true;
importCSVDataOption.SourceFile = "TestImportDataCSV.csv";
importCSVDataOption.DestinationWorksheet = "Sheet1";
importCSVDataOption.ImportDataType = "CsvData";
importCSVDataOption.IsInsert = true;
importCSVDataOption.FirstRow = 10;
importCSVDataOption.SeparatorString = ";";
importCSVDataOption.FirstColumn = 9;
PostImportDataRequest postImportDataRequest = new PostImportDataRequest() { name = "TestImportData.xlsx", importOption = importCSVDataOption };
cellsApi.PostImportData(postImportDataRequest);
using (Stream stream = cellsApi.DownloadFile(new DownloadFileRequest("TestImportData.xlsx")))
{
using (Stream downloadFile = File.OpenWrite(@"C:\TestData\cells.cloud\download\ImportCsvDataWith26372.xlsx"))
{
stream.CopyTo(downloadFile);
stream.Close();
downloadFile.Close();
}
}
CellsApi cellsApi = new CellsApi(Environment.GetEnvironmentVariable("CellsCloudClientId"), Environment.GetEnvironmentVariable("CellsCloudClientSecret"));
cellsApi.UploadFile(new UploadFileRequest(@"D:\cells.cloud.family\cells.cloud\src\testdata\Cells\TestImportData.xlsx", "TestImportData.xlsx"));
ImportBatchDataOption importBatchDataOption = new ImportBatchDataOption();
importBatchDataOption.DestinationWorksheet = "Sheet1";
importBatchDataOption.ImportDataType = "BatchData";
importBatchDataOption.IsInsert = true;
importBatchDataOption.BatchData = new List<CellValue> { };
CellValue cellValue = new CellValue();
cellValue.value = "1000";
cellValue.columnIndex = 1;
cellValue.rowIndex = 4;
cellValue.type = "int";
cellValue.style = new Style();
cellValue.style.Custom = "#,###";
importBatchDataOption.BatchData.Add(cellValue);
CellValue cellStringValue = new CellValue();
cellStringValue.value = "Hello World!";
cellStringValue.columnIndex = 1;
cellStringValue.rowIndex = 5;
cellStringValue.type = "string";
cellStringValue.style = new Style();
cellStringValue.style.Font = new Font();
cellStringValue.style.Font.Size = 36;
importBatchDataOption.BatchData.Add(cellStringValue);
// You can continue to add the required Cell objects.
PostImportDataRequest postImportDataRequest = new PostImportDataRequest() { name = "TestImportData.xlsx", importOption = importBatchDataOption };
cellsApi.PostImportData(postImportDataRequest);
using (Stream stream = cellsApi.DownloadFile(new DownloadFileRequest("TestImportData.xlsx")))
{
using (Stream downloadFile = File.OpenWrite(@"C:\TestData\cells.cloud\download\ImportBatchDataWith26372.xlsx"))
{
stream.CopyTo(downloadFile);
stream.Close();
downloadFile.Close();
}
}
byte[] fileBytes = File.ReadAllBytes(@"D:\cells.cloud.family\cells.cloud\src\testdata\Cells\input-fewdata.json");
string jsonData = Convert.ToBase64String(fileBytes);
CellsApi cellsApi = new CellsApi(Environment.GetEnvironmentVariable("CellsCloudClientId"), Environment.GetEnvironmentVariable("CellsCloudClientSecret"));
cellsApi.UploadFile(new UploadFileRequest(@"D:\cells.cloud.family\cells.cloud\src\testdata\Cells\TestImportData.xlsx", "TestImportData.xlsx"));
PostWorkbookImportJsonRequest postWorkbookImportJsonRequest = new PostWorkbookImportJsonRequest();
postWorkbookImportJsonRequest.importJsonRequest = new ImportJsonRequest();
postWorkbookImportJsonRequest.importJsonRequest.JsonFileSource = new DataSource { DataSourceType = "RequestFiles" };
postWorkbookImportJsonRequest.importJsonRequest.JsonContent = jsonData;
postWorkbookImportJsonRequest.importJsonRequest.ImportPosition = new ImportPosition { SheetName = "Sheet1" , RowIndex = 3, ColumnIndex =4 };
postWorkbookImportJsonRequest.name = "TestImportData.xlsx";
using (Stream stream = cellsApi.PostWorkbookImportJson(postWorkbookImportJsonRequest))
{
using (Stream downloadFile = File.OpenWrite(@"C:\TestData\cells.cloud\download\ImportJsonDataWith26372.xlsx"))
{
stream.CopyTo(downloadFile);
stream.Close();
downloadFile.Close();
}
}
@Skillnavi ,
Can you tell me the SDK of the language you are using? Or are you using a programming language that directly calls the Web API?
@Skillnavi ,
Please refer to the Import JSON Web API specification and the calling example.
POST https://api.aspose.cloud/v3.0/cells/Template.xlsx/importjson?folder=CellsTests
Request Body:
{
“JsonFileSource”: {
“DataSourceType”: “RequestFiles”
},
“JsonContent”: “base64String”,
“ImportPosition”: {
“SheetName”: “Sheet1”,
“RowIndex”: 3,
“ColumnIndex”: 4
}
}
I am using a programming language that calls the Web API directly.
As I mentioned before, the following WEB API.
POST
https://api.aspose.cloud/v3.0/cells/{name}/importjson
Request Header:
{"Authorization": f"Bearer {token}", "Content-Type": "application/json"}
Request Body:
request_body = {
"importJsonRequest": {
"JsonFileSource": {"DataSourceType": "RequestFiles"},
"JsonContent": json_base64,
"ImportPosition": {
"SheetName": sheet_name,
"RowIndex": row_index,
"ColumnIndex": column_index,
},
},
"name": cloud_filename,
}
Error Message:
Object reference not set to an instance of an object.
@Skillnavi ,
You need to upload the file to the Cells cloud, then change {name} to the specific file name.
For example, if you uploaded a Template.xlsx file to the root directory of the Cells cloud storage, your Web API real URI is: https://api.aspose.cloud/v3.0/cells/Template.xlsx/importjson
At this time, the request body only needs to describe JSON-related information, so the request body is:
{
“JsonFileSource”: {“DataSourceType”: “RequestFiles”},
“JsonContent”: json_base64,
“ImportPosition”: {
“SheetName”: sheet_name,
“RowIndex”: row_index,
“ColumnIndex”: column_index,
}
}
Please refer to Aspose Cells Cloud - API References: PostWorkbookImportJson and Import Json data into Excel of online document
Of course I upload the Excel file and specify the upload file name in the URL.
POST
https://api.aspose.cloud/v3.0/cells/Template.xlsx/importjson
Request Body:
request_body = {
"importJsonRequest": {
"JsonFileSource": {"DataSourceType": "RequestFiles"},
"JsonContent": json_base64,
"ImportPosition": {
"SheetName": sheet_name,
"RowIndex": row_index,
"ColumnIndex": column_index
}
}
}
Error Message:
Object reference not set to an instance of an object.
I think I’m missing some specification from the error message, but I couldn’t figure it out from the information provided.
The DataSourceType is specified as RequestFiles, is this correct?
Is there a reference manual or something that shows what can be specified?
@Skillnavi ,
We operate using the curl command in PowerShell, just for your reference.
curl -v “https://api.aspose.cloud/v3.0/cells/storage/file/TestImportData.xlsx” -X PUT -H “Authorization: Bearer your token” -H “Content-Type: multipart/form-data” -F “File=@TestImportData.xlsx”
curl -v “https://api.aspose.cloud/v3.0/cells/TestImportData.xlsx/importjson” -X POST -H “Authorization: Bearer your token” -H “Content-Type:application/json” -d ‘{“JsonFileSource”: { “DataSourceType”: “RequestFiles” },“JsonContent”: “base64String”, “ImportPosition”: { “SheetName”: “Sheet1”, “RowIndex”: 3, “ColumnIndex”: 4 } }’ --output “ImportJsonDataOK.xlsx”
We will continue to check the issue.
@Skillnavi ,
Please refer to the following request body to update your request body:
request_body = {
“JsonFileSource”: {“DataSourceType”: “RequestFiles”},
“JsonContent”: json_base64,
“ImportPosition”: {
“SheetName”: sheet_name,
“RowIndex”: row_index,
“ColumnIndex”: column_index
}
}
curl -v “https://api.aspose.cloud/v3.0/cells/TestImportData.xlsx/importjson” -X POST -H “Authorization: Bearer your token” -H “Content-Type:application/json” -d ‘{“JsonFileSource”: { “DataSourceType”: “RequestFiles” },“JsonContent”: “base64String”, “ImportPosition”: { “SheetName”: “Sheet1”, “RowIndex”: 3, “ColumnIndex”: 4 } }’ --output “ImportJsonDataOK.xlsx”
Does the curl run example return an Excel file in the response?
The request body matches what I described, right?
@Skillnavi ,
The example of running curl returns an Excel file in the response. If you want to save it to cloud storage, please add the outPath query parameter. Additionally, ensure that your request body is correct. I noticed that your single and double quotes seem to have issues; they appear to be full-width characters instead of standard English characters.
Your point is incorrect since the request body does not contain multibyte characters.
I simply streamed the response and the error went away.
Unfortunately, the received Excel file does not reflect the JSON data I sent. The Excel file I sent without any changes was returned as is.
It seems that JSON data cannot be recognized. Please let me know if there is a JSON format that can be recognized.
@Skillnavi ,
We have attached a method for converting a JSON file to a base64 string using PowerShell as a reference, along with the JSON file and the resulting processed file.
$base64String = [Convert]::ToBase64String(
[Text.Encoding]::UTF8.GetBytes(
(Get-Content -Path “path\to\your.json” -Raw)
)
)
codes.zip (19.6 KB)
I have checked the attached json.
{
[
"Aspose.Cells cloud",
"Conversion",
10127712,
"Post"
],
[
"Aspose.Cells cloud",
"Split",
910121,
"Post"
],
[
"Aspose.Cells cloud",
"Search",
10123221,
"Post"
]
}
I see that it was table data in list format.
I assumed the following JSON.
{
[
{
"name": "Scott", "age": 35, "department": "Sales", "position": "Manager"
},
{
"name": "Tiger", "age": 28, "department": "Develop", "position": "Staff"
},
{
"name": "Ichiro", "age": 42, "department": "HR", "position": "GM"
}
]
}
If the response is a file, I can’t use this API because I don’t know if the data was successfully imported or not.
@Skillnavi ,
Please add the outPath query parameter, the result file saves to cloud storage and result response is FileInfo response.
{“FileInfo”:{“Size”:12466,“Folder”:“TestJosn.xlsx”},“Code”:200,“Status”:“OK”}
Refer to curl command in powershell:
curl -v “https://cells-api.qa.aspose.cloud/v3.0/cells/TestImportData.xlsx/importjson?outPath=TestJosn.xlsx” -X POST -H “Authorization: Bearer your token” -H “Content-Type:application/json” -d ‘{“JsonFileSource”: { “DataSourceType”: “RequestFiles” },“JsonContent”: “base64string”, “ImportPosition”: { “SheetName”: “Sheet1”, “RowIndex”: 3, “ColumnIndex”: 4 } }’
At the same time, we also attached the new JSON data processing result file.
codes2.zip (10.8 KB)