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

Aspose.Cells Cloud Product Family
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.

Set Value of a Cell in a Worksheet|Documentation


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:
https://docs.aspose.cloud/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](http://system.net/);

using [System.IO](http://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 strURI = @“http://api.aspose.com/v1.1/cells/Book1.xlsx/importdata”;**

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)) { }

url = @“http://api.aspose.com/v1.0/storage/file/Book1.xlsx”;

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.