Bulk cell updates

I’m currently setting a number of cells in a worksheet using the http://api.aspose.com/v1.1/workbook_name/worksheets/worksheet_name/cells/C4?value=my_value&type=a_type syntax. Is there a way of doing this as a bulk operation, so that I can set, say, 20 cells in a single http request? I need to set each cell to a different value.

Thanks!

Hi,

Thanks for your posting and using Aspose.Cells for Cloud.

Well, you may use importdata resource for your needs.

e.g

http://api.aspose.com/v1.1/cells/testimport.xlsx/importdata

You may fill up
arrays/ lists etc. and then import data to the sheet accordingly. We have
written an example which we will provide it here for your reference, you
may refer to it and create your own code accordingly.

We have also attached the code text file for you to use it.

C#

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using [System.Net](http://system.net/);

using [System.IO](http://system.io/);

using Aspose.Cells.Cloud.SDK;

using Aspose.Cells.Cloud.SDK.Cells;

using Aspose.Cells.Cloud.SDK.Common;

using System.Xml.Serialization;

using System.Xml;

namespace TestConsole

{

public class CustomCase

{

const String BaseProductUri = @“http:[//api.aspose.com/v1.1](https://api.aspose.com/v1.1)”;

const String AppSID = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”;

const String AppKey = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”;

public string Sign(string url)

{

try

{

UriBuilder builder = new UriBuilder(url);

if (builder.Query != null && builder.Query.Length > 1)

{

builder.Query = String.Format("{0}&appSID={1}", builder.Query.Substring(1), AppSID);

}

else

{

builder.Query = String.Format(“appSID={0}”, AppSID);

}

builder.Path = builder.Path.TrimEnd(’/’);

byte[] privateKey = System.Text.Encoding.UTF8.GetBytes(AppKey);

System.Security.Cryptography.HMACSHA1 algorithm = new System.Security.Cryptography.HMACSHA1(privateKey);

byte[] sequence = System.Text.ASCIIEncoding.ASCII.GetBytes(builder.Uri.AbsoluteUri);

byte[] hash = algorithm.ComputeHash(sequence);

String signature = Convert.ToBase64String(hash);

signature = signature.TrimEnd(’=’);

signature = System.Web.HttpUtility.UrlEncode(signature);

signature = System.Text.RegularExpressions.Regex.Replace(signature, “%[0-9a-f]{2}”, e => e.Value.ToUpper());

return String.Format("{0}&signature={1}", builder.Uri.AbsoluteUri, signature);

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

}

public void Test()

{

try

{

String baseurl = @“http:[//api.aspose.com/v1.1/cells/testimport.xlsx/importdata](https://api.aspose.com/v1.1/cells/testimport.xlsx/importdata)”;

String url;

String xml = “”;

int[] nos = new int[] { 1, 2, 3, 4, 5 };

string[] tasklist = new string[] { “task 1”, “task 2”, “task 3”, “task 4”, “task 5” };

int[] times = new int[] { 11, 20, 9, 14, 2 };

string[] States = new string[] { “Release”, “Release”, “Release”, “Release”, “Open” };

string[] CreateTimes = new string[] { “2014-03-13”, “2014-03-13”, “2014-03-13”, “2014-03-13”, “2014-03-13” };

xml = ImportIntArrayOption(“Sheet1”, 2, 1, false, true, nos);

url = Sign(baseurl);

using (HttpWebResponse response = POST(url, xml)) { }

xml = ImportStringArrayOption(“Sheet1”, 2, 2, false, true, tasklist);

url = Sign(baseurl);

using (HttpWebResponse response = POST(url, xml)) { }

xml = ImportIntArrayOption(“Sheet1”, 2, 3, false, true, times);

url = Sign(baseurl);

using (HttpWebResponse response = POST(url, xml)) { }

xml = ImportStringArrayOption(“Sheet1”, 2, 4, false, true, States);

url = Sign(baseurl);

using (HttpWebResponse response = POST(url, xml)) { }

xml = ImportStringArrayOption(“Sheet1”, 2, 5, false, true, CreateTimes);

url = Sign(baseurl);

using (HttpWebResponse response = POST(url, xml)){}

url = @“http:[//api.aspose.com/v1.0/storage/file/testimport.xlsx](https://api.aspose.com/v1.0/storage/file/testimport.xlsx)”;

Download(@“D:\Projects\Test\Aspose.Cells.Cloud\testimport.xlsx”, url);

}

catch (Exception e)

{

Console.Write(e.Message);

}

}

public HttpWebResponse POST(String url, String xml)

{

try

{

Uri uri = new Uri(url);

HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(uri);

request.Method = “POST”;

request.ContentType = “application/xml”;

if (String.IsNullOrEmpty(xml))

{

request.ContentLength = 0;

}

else

{

byte[] arr = System.Text.Encoding.UTF8.GetBytes(xml);

request.ContentLength = arr.Length;

Stream dataStream = request.GetRequestStream();

dataStream.Write(arr, 0, arr.Length);

dataStream.Close();

}

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

return response;

}

catch (Exception ex)

{

throw ex;

}

}

public HttpWebResponse GET(String url)

{

try

{

Uri uri = new Uri(url);

HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(uri);

request.Method = “GET”;

request.ContentType = “application/xml”;

request.ContentLength = 0;

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

return response;

}

catch (Exception ex)

{

throw ex;

}

}

private string ImportIntArrayOption(String sheetname, int row, int column, bool IsInsert, bool IsVertical, int[] data)

{

StringBuilder sb = new StringBuilder();

sb.Append("");

sb.AppendFormat("{0}{1}{2}", sheetname, row, column);

sb.AppendFormat("{0}{1}", IsInsert.ToString().ToLower(), IsVertical.ToString().ToLower());

sb.Append("");

for (int i = 0; i < data.Length; i++)

{

sb.AppendFormat("<int>{0}</int>", data[i]);

}

sb.Append("");

sb.Append("");

return sb.ToString();

}

private string ImportStringArrayOption(String sheetname, int row, int column, bool IsInsert, bool IsVertical, string[] data)

{

StringBuilder sb = new StringBuilder();

sb.Append("");

sb.AppendFormat("{0}{1}{2}", sheetname, row, column);

sb.AppendFormat("{0}{1}", IsInsert.ToString().ToLower(), IsVertical.ToString().ToLower());

sb.Append("");

for (int i = 0; i < data.Length; i++)

{

sb.AppendFormat("<string>{0}</string>", data[i]);

}

sb.Append("");

sb.Append("");

return sb.ToString();

}

private string GetImportArrayOptionTag(TypeCode type, bool isBegin)

{

switch (type)

{

case TypeCode.Int16:

case TypeCode.Int32:

case TypeCode.Int64:

return isBegin ? “” : “”;

default:

return isBegin ? “” : “”;

}

}

private string ImportImportBatchDataOption(String sheetname, bool IsInsert)

{

int row = 0, column = 0;

StringBuilder sb = new StringBuilder();

sb.Append("");

sb.AppendFormat("{0}", sheetname);

sb.Append("");

for (int i = 0; i < 100; i++)

{

for (int j = 0; j < 10; j++)

{

sb.AppendFormat("");

sb.AppendFormat("{0}", row + i);

sb.AppendFormat("{0}", column + j);

sb.AppendFormat("{0}", i % 3 == 0 ? “int” : “string”);

sb.AppendFormat("<value>{0}</value>", i + j);

sb.AppendFormat("");

}

}

sb.Append("");

sb.Append("");

return sb.ToString();

}

public void Download(String path,String url)

{

using (HttpWebResponse response = GET(Sign(url)))

{

using (var stream = File.Create(path))

{

response.GetResponseStream().CopyTo(stream);

}

}

}

}

}```

Thanks! This looks like what I’m looking for. Do you have examples of what the raw XML request body (or better yet, a JSON request body) should look like? I’m using the straight REST API, not C#, so it’s hard for me to run the sample code you posted. Alternatively, is there a documentation page for /importdata that I could look like?

Hi,

Thanks for your posting and considering Aspose.Cells for Cloud.

I am afraid, the documentation does not state what should be in request body. You can check the help regarding URIs from this location.

( http://api.aspose.com/v1.1/Help )

( http://api.aspose.com/v1.1/Help/Api/POST-cells-name-importdata_appSid_storage_folder )

I will run the C# code at my end and let you know what you should pass in request body and get back to you asap.

Hi,

Thanks for considering Aspose.Cells for Cloud.

You will have to pass or in a request body as XML.

The screenshot shows how looks like. ImportStringArrayOption also looks similar

I have also attached the abc.xml file which contains xml for and tags.

I have also attached the output Excel file generated with the above C# code for your reference.

XML

Sheet122falsetruetask 1task 2task 3task 4task 5

Sheet123falsetrue11209142

Hi,

Thanks for considering Aspose.Cells for Cloud.

I have also uploaded the runnable sample project that illustrates how to import bulk data using Aspose.Cells for Cloud API. It will be helpful for you or other users who are facing the same issue.

Just add your AppSID and AppKey and run the project. It will first upload the testimport.xlsx file found in the current working directory in the cloud, then import data inside it. After that, it will download it and copy it to myOutput.xlsx inside the current working directory.

Hi, I’m having some trouble getting this to work in my own code. I’m testing with the following Curl command:

curl -X POST http://api.aspose.com/v1.1/cells/Proposal%20Joshua%20Haas%201411157762399.xlsx/importdata?appSID=4bd5ed12-558f-447a-949f-3ba772e1fc90&signature=KPLYToC2OfnfyMR1L3NpzMfW5mA -d "
 Control Panel
 71
 3
 false
 true
 4"
’ -H 'Content-Type: application/xml

I get a 200 OK response, but when I download the worksheet, I don’t see the number 4 being written. Is there something wrong with my request?

Also, some additional questions:

-What about other data types? I want to write datetimes and decimals as well, not just strings and ints.

-Is it possible to skip rows in an array? I have some blank spaces I don’t want to write to.

-Is it possible to send multiple arrays in the same request? The sample XML you posted seems to have multiple arrays in it, but when I try that, I get the error “Value cannot be null.\r\nParameter name: importOption”

Basically, my goal is I have a complicated spreadsheet that takes a lot of inputs, in different locations and with different data types, that I’m trying to automatically fill out. When I was filling out each cell one-by-one, it was taking a really long time, since each cell was introducing a round-trip request. There’s no way to send a list of cell names, types, and values?

Hi,

Thanks for your posting and using Aspose.Cells for Cloud.

Your request looks good. Could you please try the simple Excel file name. For example, abc.xlsx

http://api.aspose.com/v1.1/cells/abc.xlsx/importdata?.…

and see how it goes at your end.

You can only append one array e.g only one of the ImportIntArrayOption , ImportStringArrayOption , ImportBatchDataOption .

If you want to use different types of cells data, then you should use ImportBatchDataOption

Please see the above C# code, you will find ImportBatchDataOption there and it will suit your needs, because you can specify cell location [rowindex, columnindex], cell type, cell value

Here is an example of ImportBatchDataOption

Sheet1

3
4
Possible values are: empty, object, dbnull, boolean, char, sbyte, byte, int16, uint16, int, int32, uint32, int64, uint64, single, double, decimal, datetime, string
something

3
4
datetime
20-10-2014

3
4
boolean
False

Could you please explain me Curl? Are you using Linux? What is your environment? Could you provide me screenshots showing how you are executing REST commands? It will help me investigate your issue at my end. You can also point me to good resources on the internet.

Hi,

Thanks for using Aspose.Cells for Cloud.

We have fixed the issue of Import Batch Data Option.

Please check this post for your reference.

( Create Excel file from XML/JSON payload )