Free Support Forum - aspose.com

Formula Bug 3.0.4

HI, still getting problems with formulas:
If you use the formula =(e8/d8)/(e8/d8) which resovles to = (2.46/1)/(2.46/1)
Then you get an invalid formula error

Steps to reproduce enclosed

Ta

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using Aspose.Excel;

namespace HubTest

{

///

/// Summary description for AsposeExcelTest.

///

public class AsposeExcelTest : System.Web.UI.Page

{

private void Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

//ReproExcelProblem();

ReproExcelProblem1();

_excel.Save(@“M and P Report.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

}

private Excel _excel = null;

private string Sheet1Name=“Targets_Album”;

private string Sheet2Name=“Pricing_Album”;

private string Sheet3Name=“Targets_CD_and_DVD”;

private string Sheet4Name=“Pricing_CD_and_DVD”;

private string Sheet5Name=“Targets_First_Single”;

private string Sheet6Name=“Pricing_First_Single”;

private string Sheet7Name=“Results”;

private string Sheet8Name=“testsheet”;

private void ReproExcelProblem()

{

//there is a problem with this formula

//=(Targets_Album!D17Pricing_Album!BP20)-(Targets_Album!D17Pricing_Album!BP20Pricing_Album!BQ20)+(Targets_CD_and_DVD!D17(Pricing_CD_and_DVD!T20*(1-Pricing_CD_and_DVD!U20)))+(Targets_First_Single!D17*(Pricing_First_Single!BH20*(1-Pricing_First_Single!BI20)))

//string licenseFile = Server.MapPath(@"~\Licence\Aspose.Excel.lic");

//_excel = new Excel(licenseFile, this);

_excel = new Excel();

//create two worksheets

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets[0].Name=Sheet1Name;

_excel.Worksheets[1].Name=Sheet2Name;

_excel.Worksheets[2].Name=Sheet3Name;

_excel.Worksheets[3].Name=Sheet4Name;

_excel.Worksheets[4].Name=Sheet5Name;

_excel.Worksheets[5].Name=Sheet6Name;

_excel.WorksheetsDevil.Name=Sheet7Name;

_excel.Worksheets[7].Name=Sheet8Name;

//create the specific values that are required

//test1

_excel.Worksheets[0].Cells[“b17”].PutValue(1);

_excel.Worksheets[1].Cells[“BP20”].PutValue(2);

_excel.Worksheets[0].Cells[“D17”].PutValue(3);

_excel.Worksheets[1].Cells[“BP20”].PutValue(4);

_excel.Worksheets[1].Cells[“BQ20”].PutValue(5);

_excel.Worksheets[2].Cells[“D17”].PutValue(6);

_excel.Worksheets[3].Cells[“T20”].PutValue(7);

_excel.Worksheets[3].Cells[“u20”].PutValue(8);

_excel.Worksheets[4].Cells[“D17”].PutValue(9);

_excel.Worksheets[5].Cells[“BH20”].PutValue(10);

_excel.Worksheets[5].Cells[“BI20”].PutValue(10);

//test2

_excel.Worksheets[0].Cells[“b1”].PutValue(1);

_excel.Worksheets[1].Cells[“b1”].PutValue(2);

_excel.Worksheets[2].Cells[“b1”].PutValue(3);

_excel.Worksheets[3].Cells[“b1”].PutValue(4);

_excel.Worksheets[4].Cells[“b1”].PutValue(5);

_excel.Worksheets[5].Cells[“b1”].PutValue(6);

_excel.WorksheetsDevil.Cells[“b1”].PutValue(7);

//test3

_excel.Worksheets[0].Cells[“bh20”].PutValue(1);

_excel.Worksheets[1].Cells[“bh20”].PutValue(2);

_excel.Worksheets[2].Cells[“bh20”].PutValue(3);

_excel.Worksheets[3].Cells[“bh20”].PutValue(4);

_excel.Worksheets[4].Cells[“bh20”].PutValue(5);

_excel.Worksheets[5].Cells[“bh20”].PutValue(6);

_excel.WorksheetsDevil.Cells[“bh20”].PutValue(7);

System.Text.StringBuilder formula = new System.Text.StringBuilder();

//formula.AppendFormat("=({0}!b17*{1}!B20)-({0}!b17*{1}!B20*{1}!B20)+({2}!b17*({3}!b20*(1-{3}!b20)))+({4}!b17*({5}!B20*(1-{5}!B20)))",

//this is the base problem formula

formula.AppendFormat("=({0}!D17*{1}!BP20)-({0}!D17*{1}!BP20*{1}!BQ20)+({2}!D17*({3}!T20*(1-{3}!U20)))+({4}!D17*({5}!BH20*(1-{5}!BI20)))",

Sheet1Name,Sheet2Name,Sheet3Name,Sheet4Name,Sheet5Name,Sheet6Name);

_excel.WorksheetsDevil.Cells[0,1].PutValue(“Test Results using problem formula”);

_excel.WorksheetsDevil.Cells[1,1].Formula=formula.ToString();

System.Diagnostics.Debug.WriteLine(formula.ToString().Length);

//now lets create a different formulas to repro problem

//create a formula that is over 259 characters in length

formula = new System.Text.StringBuilder();

formula.AppendFormat("=({0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1)",

Sheet1Name);

System.Diagnostics.Debug.WriteLine(formula.ToString().Length);

_excel.WorksheetsDevil.Cells[2,1].PutValue(“Test Results using a formula of 259 in length”);

_excel.WorksheetsDevil.Cells[3,1].Formula=formula.ToString();

//create a formula that uses 5 sheets

formula = new System.Text.StringBuilder();

formula.AppendFormat("=({0}!b1+{1}!b1+{2}!b1+{3}!b1+{4}!b1+{5}!b1+{0}!b1+{1}!b1+{2}!b1+{3}!b1+{4}!b1+{5}!b1+{0}!b1+{1}!b1+{2}!b1+{3}!b1+{4}!b1+{5}!b1+{0}!b1+{0}!b1+{1}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1+{0}!b1)",

Sheet1Name,Sheet2Name,Sheet3Name,Sheet4Name,Sheet5Name,Sheet6Name);

_excel.WorksheetsDevil.Cells[4,1].PutValue(“Test Results using 5 different pages”);

_excel.WorksheetsDevil.Cells[5,1].Formula=formula.ToString();

//create a formaula that uses cells with a column name of 4

formula = new System.Text.StringBuilder();

formula.AppendFormat("=({0}!bh20+{1}!bh20+{2}!bh20+{3}!bh20+{4}!bh20+{5}!bh20+{0}!bh20+{1}!bh20+{2}!bh20+{3}!bh20+{4}!bh20+{5}!bh20+{0}!bh20+{1}!bh20+{2}!bh20+{3}!bh20+{4}!bh20+{5}!bh20+{0}!bh20+{0}!bh20+{1}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20)",Sheet1Name,Sheet2Name,Sheet3Name,Sheet4Name,Sheet5Name,Sheet6Name);

_excel.WorksheetsDevil.Cells[6,1].PutValue(“Test Results using columns that are 4 characters long”);

_excel.WorksheetsDevil.Cells[7,1].Formula=formula.ToString();

//create a formaula that uses many brackets

formula = new System.Text.StringBuilder();

formula.AppendFormat("=(({0}!bh20+{1}!bh20)+({2}!bh20+{3}!bh20)+({4}!bh20+{5}!bh20+)+({0}!bh20+{1}!bh20)+({2}!bh20+{3}!bh20)+({4}!bh20+{5}!bh20)+({0}!bh20+{1}!bh20)+({2}!bh20+{3}!bh20)+({4}!bh20+{5}!bh20)+({0}!bh20+{0}!bh20)+({1}!bh20+{0}!bh20)+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20+{0}!bh20)",Sheet1Name,Sheet2Name,Sheet3Name,Sheet4Name,Sheet5Name,Sheet6Name);

_excel.WorksheetsDevil.Cells[8,1].PutValue(“Test Results many brackes”);

_excel.WorksheetsDevil.Cells[9,1].Formula=formula.ToString();

//create a formula that tests order of prefrence

string stringForumla="=(1+7)-(4)+(2)+(2)+(2)" ;//result should be 10 but is -2

_excel.WorksheetsDevil.Cells[10,1].PutValue(“Test Results using order of preference”);

_excel.WorksheetsDevil.Cells[11,1].Formula=stringForumla;

stringForumla="=((1+7)-(4))+(2)+(2)+(2)" ;//result should be 10

_excel.WorksheetsDevil.Cells[12,1].PutValue(“Test Results confirming order of preference”);

_excel.WorksheetsDevil.Cells[13,1].Formula=stringForumla;

}

private void ReproExcelProblem1()

{

//there is a problem with this formula

//"=((D8/C8)/(D8/C8))". d8=2.46 and c8 =1

//string licenseFile = Server.MapPath(@"~\Licence\Aspose.Excel.lic");

//_excel = new Excel(licenseFile, this);

_excel = new Excel();

//create two worksheets

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets.Add();

_excel.Worksheets[0].Name=Sheet1Name;

_excel.Worksheets[1].Name=Sheet2Name;

_excel.Worksheets[2].Name=Sheet3Name;

_excel.Worksheets[3].Name=Sheet4Name;

_excel.Worksheets[4].Name=Sheet5Name;

_excel.Worksheets[5].Name=Sheet6Name;

_excel.WorksheetsDevil.Name=Sheet7Name;

_excel.Worksheets[7].Name=Sheet8Name;

//create the specific values that are required

//test1

_excel.Worksheets[1].Cells[“d8”].PutValue(Convert.ToDecimal(“2.6”));

_excel.Worksheets[1].Cells[“c8”].PutValue(Convert.ToDecimal(“1”));

//System.Text.StringBuilder formula = new System.Text.StringBuilder();

_excel.WorksheetsDevil.Cells[2,1].PutValue(“Test Results “);

_excel.WorksheetsDevil.Cells[3,1].Formula=string.Format(”=(({0}/{1})/({2}/{3}))”,

“D8”,

“c8”,

“d8”,

“c8”);

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

//

InitializeComponent();

base.OnInit(e);

}

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

}

}

I fixed this bug. You will get the new fix at the start of next week.

Please download and try v3.0.5 at
http://www.aspose.com/Products/Aspose.Excel/Downloads.html
.