Free Support Forum - aspose.cloud

Import / Load Data into Excel Spreadsheet with Aspose.Cells REST API

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.

https://docs.aspose.cloud/display/cellscloud/Set+Value+of+a+Cell+in+a+Worksheet


Any ideas on how to do this?

Thanks
John

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:
https://docs.aspose.cloud/display/cellscloud/Set+Range+Value+in+Excel+Worksheet

Thank you.

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

Hi,


We will get back to you soon.

Thank you.

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.