Free Support Forum - aspose.cloud

REST API Loading of Data


#1

Hi,


We are reviewing ASPOSE CELLS for the cloud API & would like to know what is the best way to load a sheet of data with around 5,000 rows of data & 10 columns?

In the API docs I can see a method to set a cell value but this would take 50,000 API calls to load the data, so not good.

http://www.aspose.com/docs/display/cellscloud/Set+Value+of+a+Cell+in+a+Worksheet


Any ideas on how to do this?

Thanks
John

#2

Hi,


Well, if you want to set a constant value to a range of cells, you may do it easily with a single call, see the topic for your reference:
http://www.aspose.com/docs/display/cellscloud/Set+Range+Value+in+Excel+Worksheet

Thank you.

#3

It would not be a constant value.


It would be a list of all contacts in a database with different data such as firstname, lastname, telephone number, etc.

Is there any way to do this?

Thanks
John

#4

Hi,


We will get back to you soon.

Thank you.

#5

Hi,


Well, you may use importdata resource for your needs. You may fill up arrays/ lists etc. and then import data to the sheet accordingly. I have written an example which I will paste it here for your reference, you may refer to it and create your own codes accordingly.
e.g
Sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net;
using System.IO;

namespace SmapleTestCode
{
public class APPKey
{
public static string AppSID { get; set; }
///
/// Represnts the AppKey for the app.
///
public static string AppKey { get; set; }
}

class Program
{
static void Main(string[] args)
{


APPKey.AppSID = “7xxxxxxxxxxxxxxxxxxxxxxxa”;
APPKey.AppKey = “3xxxxxxxxxxxxxxxxxxxxxxxxxa”;

//build URI

String url;
String xml = “”;
//Define Arrays and fill data to it.
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(strURI);
using (HttpWebResponse response = POST(url, xml)) { }
xml = ImportStringArrayOption(“Sheet1”, 2, 2, false, true, tasklist);
url = Sign(strURI);
using (HttpWebResponse response = POST(url, xml)) { }
xml = ImportIntArrayOption(“Sheet1”, 2, 3, false, true, times);
url = Sign(strURI);
using (HttpWebResponse response = POST(url, xml)) { }
xml = ImportStringArrayOption(“Sheet1”, 2, 4, false, true, States);
url = Sign(strURI);
using (HttpWebResponse response = POST(url, xml)) { }
xml = ImportStringArrayOption(“Sheet1”, 2, 5, false, true, CreateTimes);
url = Sign(strURI);
using (HttpWebResponse response = POST(url, xml)) { }
Download(@“e:\test2\out1.xlsx”, url);

}

//Implementation for methods…
private static 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("{0}", data[i]);
}
sb.Append("");
sb.Append("");
return sb.ToString();
}

private static 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("{0}", data[i]);
}
sb.Append("");
sb.Append("");
return sb.ToString();
}

private static string GetImportArrayOptionTag(TypeCode type, bool isBegin)
{
switch (type)
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
return isBegin ? “” : “”;
default:
return isBegin ? “” : “”;
}
}

private static 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("{0}", i + j);
sb.AppendFormat("");
}
}
sb.Append("");
sb.Append("");
return sb.ToString();
}

public static Stream ProcessCommand(string strURI, string strHttpCommand)
{
}

public static Stream ProcessCommand(string strURI, string strHttpCommand, string strContent)
{
}

private static 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), APPKey.AppSID);
}
else
{
builder.Query = String.Format(“appSID={0}”, APPKey.AppSID);
}

builder.Path = builder.Path.TrimEnd(’/’);
byte[] privateKey = System.Text.Encoding.UTF8.GetBytes(APPKey.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);
}

}
private static 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;
}
}
private static 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;
}
}


public static void Download(String path, String url)
{
using (HttpWebResponse response = GET(Sign(url)))
{
using (var stream = File.Create(path))
{
response.GetResponseStream().CopyTo(stream);
}
}
}

}
}

Hope, this helps a bit.

Thank you.