Add cell values and Calculate formula in Excel with PHP SDK of Aspose.Cells Cloud API

Aspose.Cells Cloud Product Family
1

I have 5 complex excel files with formulas and I need to add some values to some cells and get the output of another cell after the formula is executed

Can I do this with your Cloud APIs?

2

Hi,

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

It will support your needs with Could APIs.

But we have issues about calculateformula.

We need few weeks to fix the issues.



C#


String baseurl = @"http://api.aspose.com/v1.1/cells/Fumal.xlsx/";

String url = string.Format("{0}{1}", baseurl, "worksheets/sheet1/cells/E6?value=1000&type=int");

using (HttpWebResponse response = POST(Sign(url), null))

{

}

url = string.Format("{0}{1}", baseurl, "calculateformula");

using (HttpWebResponse response = POST(Sign(url), null))

{

}

url = string.Format("{0}{1}", baseurl, "worksheets/sheet1/cells/E11");

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

{

StreamReader sr = new StreamReader (response.GetResponseStream());

String result = sr.ReadToEnd();

}


3

Ok thanks!


Do you have an ETA?

The excel files is about 96MB is this a problem?

Do you have the code for PHP?

Thanks
4

Hi,

Thanks for your posting and considering Aspose.Cells.

We will try to fix it in estimated three weeks time.

You cannot use 96MB file currently, if you have purchased Cloud APIs than you can use upto 30MB file and in some cases you can use upto 50MB file. If you are using trial version, then you can use upto 5MB file.

Please also read the following blog.

( Execute Mail Merge of Any Type to Create Documents )

For PHP code, we will work on your query and get back to you asap.

5

Hi,

Thanks for using Aspose.Cells.

Please see the following PHP code for using Aspose.Cells for Cloud Calculate Formula feature.

I have attached the TestCalculateFormula.php which tests Calculate Formula.

I have also attached the source.xlsx file which contains

A1: 100
A2: 100
A3: =Sum(A1:A2) //200

First, the code uploads source.xlsx file on Cloud, then it sets the value of cell A1 to 500, then it reads the value of cell A3, which is still 200 and then it calculates the formula and reads the value of cell A3 again, this time its gives correct value that is 600.

A1: 500

A2: 100

A3: =Sum(A1:A2) //600

I have also attached the full Virtual Directory which also contains Aspose PHP Cloud SDK for your ease.

PHP

<?php
require '/Aspose/Cloud/Common/AsposeApp.php';
require '/Aspose/Cloud/Exception/AsposeCloudException.php';
require '/Aspose/Cloud/Common/Utils.php';
require '/Aspose/Cloud/Storage/Folder.php';

use Aspose\Cloud\Common\AsposeApp;
use Aspose\Cloud\Common\Utils;

//Test Calculate Formula
TestCalculateFormula();

function TestCalculateFormula()
{
AsposeApp::$appSID = "xxxxxx-xxxxx-xxxxx-xxxxx-xxxxxx";
AsposeApp::$appKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

//Upload the source file with the formula
UploadFile(getcwd() . "\\source.xlsx");

//Set cell A1 as 500
SetCellValue("source.xlsx", "Sheet1", "A1", "500", "int");

//Read the cell A3 value before CalculateFormula
GetCellValue("source.xlsx", "Sheet1", "A3");

//Calculate the formulas
CalculateFormula("source.xlsx");

//Read the cell A3 value after CalculateFormula
GetCellValue("source.xlsx", "Sheet1", "A3");
}

function UploadFile($filePath)
{
$fileName = basename($filePath);
$URIRequest = "http://api.aspose.com/v1.1/storage/file/" . $fileName;

$URISigned = Utils::Sign($URIRequest);

$check = Utils::uploadFileBinary($URISigned, $filePath);
}

function DownloadFile($fileName, $outputPath)
{
$strURI = "http://api.aspose.com/v1.1/storage/file/" . $fileName;
$signedURI = Utils::Sign($strURI);

$responseStream = Utils::processCommand($signedURI, "GET", "", "");

Utils::saveFile($responseStream, getcwd() . "/" . $outputPath);
}

function SetCellValue($workbookName, $worksheetName, $cellName, $cellValue, $cellType)
{
$strURI = "http://api.aspose.com/v1.1/cells/" . $workbookName . "/worksheets/" . $worksheetName . "/cells/"
. $cellName . "?value=" . $cellValue . "&type=" . $cellType;

$signedURI = Utils::Sign($strURI);

$responseStream = Utils::processCommand($signedURI, "POST", "", "");

echo "\r\nResponseStream After Setting Cell Value: \r\n" . $responseStream . "\r\n\r\n";

}

function GetCellValue($workbookName, $worksheetName, $cellName)
{
$strURI = "http://api.aspose.com/v1.1/cells/" . $workbookName . "/worksheets/" . $worksheetName . "/cells/" . $cellName;

$signedURI = Utils::Sign($strURI);

$responseStream = Utils::processCommand($signedURI, "GET", "", "");

echo "\r\nResponseStream After Getting Cell Value: \r\n" . $responseStream . "\r\n\r\n";
}


function CalculateFormula($workbookName)
{
$strURI = "http://api.aspose.com/v1.1/cells/" . $workbookName . "/CalculateFormula";

$signedURI = Utils::Sign($strURI);

$responseStream = Utils::processCommand($signedURI, "POST", "", "");

echo "\r\nResponseStream After Workbook CalculateFormula: \r\n" . $responseStream . "\r\n\r\n";

}

?>


Echo Output:

ResponseStream After Setting Cell Value:
{"Cell":{"Name":"A1","Row":0,"Column":0,"Value":"500","Type":"IsNumeric","Formula":null,"IsFormula":false,"IsMerged":false,"IsArrayHeader":false,"IsInArray":false,"IsErrorValue":false,"IsInTable":false,"IsStyleSet":false,"HtmlString":"500","Style":{"link":{"Href":"/style","Rel":"self","Type":null,"Title":null}},"Worksheet":null,"link":{"Href":"http://api.aspose.com/v1.1/cells/source.xlsx/worksheets/Sheet1/cells/A1","Rel":"self","Type":null,"Title":null}},"Code":200,"Status":"OK"}


ResponseStream After Getting Cell Value:
{"Cell":{"Name":"A3","Row":2,"Column":0,"Value":"200","Type":"IsNumeric","Formula":"=SUM(A1:A2)","IsFormula":true,"IsMerged":false,"IsArrayHeader":false,"IsInArray":false,"IsErrorValue":false,"IsInTable":false,"IsStyleSet":false,"HtmlString":"200","Style":{"link":{"Href":"/style","Rel":"self","Type":null,"Title":null}},"Worksheet":null,"link":{"Href":"http://api.aspose.com/v1.1/cells/source.xlsx/worksheets/Sheet1/cells/A3","Rel":"self","Type":null,"Title":null}},"Code":200,"Status":"OK"}


ResponseStream After Workbook CalculateFormula:
{"Code":200,"Status":"OK"}


ResponseStream After Getting Cell Value:
{"Cell":{"Name":"A3","Row":2,"Column":0,"Value":"600","Type":"IsNumeric","Formula":"=SUM(A1:A2)","IsFormula":true,"IsMerged":false,"IsArrayHeader":false,"IsInArray":false,"IsErrorValue":false,"IsInTable":false,"IsStyleSet":false,"HtmlString":"600","Style":{"link":{"Href":"/style","Rel":"self","Type":null,"Title":null}},"Worksheet":null,"link":{"Href":"http://api.aspose.com/v1.1/cells/source.xlsx/worksheets/Sheet1/cells/A3","Rel":"self","Type":null,"Title":null}},"Code":200,"Status":"OK"}