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