Environment: v3.2 Aspose, Win2k3, IIS6, c#
Hi, I have a financial report I create which contains an involved formula, ie over 259 characters, referencing 5 pages, and many brackets. When the excel sheet is produced the value that this formula is wrong, coming up with a negative value. If you go into the formula and remove and replace any character the formula is correct. Thus the formula in itself has no problem but must under the hood be misrepresented. Ive tried to narrow it down to formula length or number of sheets used, but whilst I can reproduce the problem using our problem formula, I havent found out exactly what it is.
This is a real urgent one for us, its on global application and this is the main financial report for it
Ive enclosed the code to repro beneath: Ive enclosed the excel file which if you go to the test results, and if you in delete a bracket readd it of the first formula you will see the value change…Thanks in advance
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();
_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();
}
#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
}
}