Free Support Forum - aspose.com

A quesion about WebWorksheets.RunAllFormulas()

Hi Admin:

There is issue about "WebWorksheets.RunAllFormulas()".I found this method is inefficient when I load the Excel file include many formulas and it even can appear system error,like"Stack empty".

In my application, when many users(about 10 users) use the system at the same time,a majority of user can meet the "Stack empty" error,one or two users can meet the "Object reference not set to an instance of an object." error. Maybe one user didn't meet any error. The following content is the detail information about these error:

"Stack empty"

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 2009/7/15 下午 03:45:19
Event time (UTC): 2009/7/15 上午 07:45:19
Event ID: c5a2e574635a42b1adf3073c0d7b39d6
Event sequence: 337
Event occurrence: 6
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT-3-128921157178261916
Trust level: Full
Application Virtual Path: /
Application Path: D:\RFQ\
Machine name: MSLRFQ_BK

Process information:
Process ID: 1944
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: InvalidOperationException
Exception message: Stack empty.

Request information:
Request URL:
http://10.86.0.168/Approve.aspx?PID=9305019&H=994&GUID=cca733ea-788a-4b85-849d-6d2df76389af
Request path: /Approve.aspx
User host address: 10.86.16.20
User: MITACAD\kasint.zhi
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information:
Thread ID: 18
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Collections.Stack.Pop()
at Aspose.Grid.Web.Data.WebWorksheets.CalculateCellFormulaOrGetValue(ࡁ cell)
at Aspose.Grid.Web.Data.WebWorksheets.RunAllFormulasCore(Boolean returnChangedCells)
at Aspose.Grid.Web.GridWeb.ֲ(String ֳ, Boolean ִ, Boolean ֵ)
at Aspose.Grid.Web.GridWeb.׌(String ׍, NameValueCollection ׎)
at System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)



"Object reference not set to an instance of an object."

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 2009/7/14 下午 05:17:18
Event time (UTC): 2009/7/14 上午 09:17:18
Event ID: 5ad6a46b282447e3959efef043f19719
Event sequence: 2294
Event occurrence: 13
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT-6-128920294765266160
Trust level: Full
Application Virtual Path: /
Application Path: D:\RFQ\
Machine name: MSLRFQ_BK

Process information:
Process ID: 3220
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: NullReferenceException
Exception message: Object reference not set to an instance of an object.

Request information:
Request URL:
http://10.86.0.168/Approve.aspx?PID=8677009&H=708&GUID=65d6c416-ea4f-4f8c-853e-eaf782631213
Request path: /Approve.aspx
User host address: 10.86.16.76
User: MITACAD\sam.t
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information:
Thread ID: 8
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at Aspose.Grid.Web.Data.WebWorksheets.CalculateCellFormulaOrGetValue(ࡁ cell)
at Aspose.Grid.Web.Data.WebWorksheets.RunAllFormulasCore(Boolean returnChangedCells)
at Aspose.Grid.Web.GridWeb.ֲ(String ֳ, Boolean ִ, Boolean ֵ)
at Aspose.Grid.Web.GridWeb.׌(String ׍, NameValueCollection ׎)
at System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

After I debug, I found these two errors all stop the folowing code:

this.gwDatabase.WebWorksheets.RunAllFormulas();

If I load less sheets, it didn't appeared error usually.But when three users visit my application at the same time, the CPU of server's loading is over 80% and the issue maybe appeared. when 10 users visit my application, the issue certainly appeared and the CPU of server's loading is about 100%

My server's CPU is Inter Xeon 2.4G, the Pic:2009-07-15_175823 is the CPU loading scene for 10 users visit my application at the same time.

In my application, the data(inlcude formula) from the database, so the sheets is created by code. The following code is part of my application:

……

public static void SheetBuilder(GridWeb grid, string sheetName, string templateID, string aliasID, DataView constants, bool isQuotingProcess, bool isCreatingProcess)
{

WebWorksheet sheet = grid.WebWorksheets[grid.WebWorksheets.Add(sheetName)];
sheet.IsProtected = true;

int templateSheetIndex = 99;//當前要載入的sheet是在原template位置是??

//columns width
DataView dv = OracleHelper.DataView("SELECT SHEET_INDEX,COLUMN_WIDTH,COLUMN_INDEX FROM RFQ_TEMPLATECOLUMNS_WIDTH A,RFQ_TEMPLATESHEETS B WHERE A.TSHEET_ID=B.ID AND B.TEMPLATE_ID=" + templateID + " AND B.SHEET_NAME='" + sheetName.Replace("'", "''") + "' AND COLUMN_INDEX IS NOT NULL ORDER BY A.COLUMN_INDEX");
foreach (DataRowView drv in dv)
{
if (templateSheetIndex == 99) templateSheetIndex = int.Parse(drv["SHEET_INDEX"].ToString());
sheet.Cells.SetColumnWidth(int.Parse(drv["COLUMN_INDEX"].ToString()), Unit.Point(int.Parse(drv["COLUMN_WIDTH"].ToString())));
}

//sheet data
dv = OracleHelper.DataView("SELECT (SELECT CHOOSED FROM RFQ_TEMPLATEITEMS WHERE TEMPLATE_ID=" + templateID + " AND A.SHEET_INDEX=0 AND ROWINDEX=B.ROW_INDEX ) AS SELECTED,(SELECT STATUS FROM RFQ_MAINSHEET_VIEW WHERE ALIAS_ID=" + (String.IsNullOrEmpty(aliasID) ? "0" : aliasID) + " AND A.SHEET_INDEX = 0 AND B.COLUMN_INDEX=6 AND ROWINDEX=B.ROW_INDEX AND ROWNUM=1) STATUS,B.COLUMN_INDEX,NVL (C.CUSTOM, B.CUSTOM) AS CUSTOM,DECODE(C.ID,NULL,B.FORMULA,C.FORMULA) AS FORMULA,B.ISLOCKED,B.ISREADONLY,B.NUMBERTYPE,B.ROW_INDEX,NVL (C.STRINGVALUE, B.STRINGVALUE) AS STRINGVALUE FROM RFQ_TEMPLATESHEETS A,RFQ_TEMPLATECELLS B,RFQ_ALIASCELLS C WHERE A.TEMPLATE_ID=" + templateID + " AND A.ID=B.TSHEET_ID AND A.SHEET_NAME='" + sheetName.Replace("'", "''") + "' AND C.TCELL_ID(+)=B.ID AND C.ALIAS_ID(+)=" + (String.IsNullOrEmpty(aliasID) ? "0" : aliasID) + " ORDER BY B.ROW_INDEX,B.COLUMN_INDEX");
foreach (DataRowView drv in dv)
{
WebCell cell = sheet.Cells[int.Parse(drv["ROW_INDEX"].ToString()), int.Parse(drv["COLUMN_INDEX"].ToString())];

cell.IsLocked = (drv["ISLOCKED"].ToString() == "1" ? true : false);
cell.IsReadonly = (drv["ISREADONLY"].ToString() == "1" ? true : false);

#region Formula
if (drv["FORMULA"].ToString() != "")
{
cell.Formula = drv["FORMULA"].ToString();
//cell.IsLocked = true;
if (cell.Formula.Contains("!") && (templateSheetIndex == 0 || cell.Row == 0)) cell.Style.ForeColor = System.Drawing.Color.Chocolate; //只有主報價sheet才添加
}
#endregion

if (!String.IsNullOrEmpty(drv["CUSTOM"].ToString())) cell.Custom = drv["CUSTOM"].ToString();

#region NumberType
string numberType = drv["NUMBERTYPE"].ToString();
if (!String.IsNullOrEmpty(numberType) && numberType != "General")
{
SetNumberType(cell, numberType);
if (!cell.IsLocked) SetValidation(cell);
}
#endregion

//VALUE
if (!String.IsNullOrEmpty(aliasID) && templateSheetIndex == 0 && cell.Column == MainConstant.StatusIndex) //status 欄要特別賦值
{
cell.PutValue(drv["STATUS"].ToString());
cell.IsLocked = true;
}
else
{
string val = drv["STRINGVALUE"].ToString();
if (cell.NumberType == NumberType.Percentage2) //百分数一定要转换为数字才行.
{
if (val.EndsWith("%")) val = (double.Parse(val.Substring(0, val.Length - 1)) / 100).ToString();
cell.PutValue(val, true);
}
else
{
if (val != "" && val != "#VALUE?" && val != "#REF?") cell.PutValue(drv["STRINGVALUE"].ToString(), true);//cell.StringValue = drv["STRINGVALUE"].ToString();//
}
}

//Color
cell.Style.BackColor = ((cell.Row > 0) ? ((!cell.IsLocked) ? System.Drawing.Color.PowderBlue : System.Drawing.Color.White) : System.Drawing.Color.AliceBlue);

#region 如果是對別名進行操作
if (!String.IsNullOrEmpty(aliasID))
{
if (templateSheetIndex == 0)
{
//select column
if (cell.Column == MainConstant.SelectIndex && !cell.IsLocked)
{
string selected = drv["SELECTED"].ToString();
string value = drv["STRINGVALUE"].ToString().ToLower();
if (isQuotingProcess || isCreatingProcess) //如果是創建報價或報價中,則顯示checkbox供用戶勾選; else 導出報表則不要
{
if (selected == "1" || selected == "2")
{
cell.Validation = new Validation();
cell.Validation.ValidationType = ValidationType.CheckBox;
cell.PutValue(((value != "false") ? "true" : "false"), true);
if (isQuotingProcess) cell.IsLocked = (selected == "1");
}
}
else
{
cell.PutValue(((value.ToLower() != "false") ? "true" : "false"), false);
}
}
//role validation -- 如果是創建中.
if (isCreatingProcess && cell.Column == MainConstant.RoleIndex)
{
cell.Validation = RoleValidation;
}
}

if (constants != null) SetConstantCellValue(cell, constants); //常量賦值
}
#endregion

//如果是報價中的ICT SHEET
if (isQuotingProcess && templateSheetIndex != 0 && cell.Row == 0 && cell.StringValue.ToUpper().Replace(" ", "") == "UNITPRICE(CNY)") sheet.Cells.SetColumnWidth(cell.Column, Unit.Point(0));
}

//如果是查看报价,则Amount要重新计算.
if (templateSheetIndex == 0 && !String.IsNullOrEmpty(aliasID) && !isQuotingProcess && !isCreatingProcess)
{
for (int i = 1; i <= sheet.Cells.MaxRow; i++)
{
string s = sheet.Cells[i, MainConstant.SelectIndex].StringValue;
string v = sheet.Cells[i, MainConstant.StatusIndex].StringValue;
if (!String.IsNullOrEmpty(s) && s.ToLower() == "false")
{
sheet.Cells[i, MainConstant.AmountIndex].Formula = null;
sheet.Cells[i, MainConstant.AmountIndex].PutValue("0", true);
}
//update by ivan;報價delete后不參與計算,參數變為零
if (!string.IsNullOrEmpty(v) && v == "不報價")
{
sheet.Cells[i, MainConstant.AmountIndex].Formula = null;
sheet.Cells[i, MainConstant.AmountIndex].PutValue("0", true);
}
}
}

//rows height
dv = OracleHelper.DataView("SELECT SHEET_INDEX,ROW_HEIGHT,ROW_INDEX FROM RFQ_TEMPLATEROWS_HEIGHT A,RFQ_TEMPLATESHEETS B WHERE A.TSHEET_ID=B.ID AND B.TEMPLATE_ID=" + templateID + " AND B.SHEET_NAME='" + sheetName.Replace("'", "''") + "' AND ROW_INDEX IS NOT NULL ORDER BY A.ROW_INDEX");
foreach (DataRowView drv in dv) sheet.Cells.SetRowHeight(int.Parse(drv["ROW_INDEX"].ToString()), Unit.Point(int.Parse(drv["ROW_HEIGHT"].ToString())));

dv.Dispose();
}

……

For your understanding, I create a sample project(Attachment file:TestAspose.zip), you may operate as the following step:

1.Open 10 "default.aspx" at the same time;

2.Click the "CreateSheets" button,and Create the sheets in every page.

3.Click the "RunAllFormula" button in every page。

It is a pity,the error only appear one times in my sample project when I test.So, you may can't find the error,but it really exist!

the attachment(PCA 20090707.xls) is my data source.

How long will you get the cause of this issue?

If you know the cause of this issue,please tell me when will you fix it.

Hi,

Thanks for providing us details with sample project.

We will check and get back to you soon.

Thank you.

Hi,

Well, we tested the project following the specified steps you mentioned several times. But the issue did not appear. We try to use a member variable instead of the formulas running stack internal now. Hopeful this can resolve the issue. Please try the attached version v<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2.0.1.2013 and let us know if it works fine now.

Thank you.

Ok,I will try it.

It is regrettable that the issue still exist when update the newest version to my application.

It is worth to mention that the error rate seems to reduce. I run the program in 10 pege at the same time , 9 errors appeared and 1 normal when I use old version the day before yesterday. But 7 errors appeared and 3 normal when I use new version today.

Under my testing, I think the error,RunAllFormula(),is connected with the number of the user at the same time in our application and the server's performance. this method,RunAllFormaula(),expend too hight system resource. By the way, only 3 errors appeared in my application when I use the new server with Inter Xeon 3.2G and 3.5G Ram.

I think it is very difficult to communicate effectively between you and me by this way. I even can give my source code to you.I don’t know if you have Oracle database,because my application use it.Or you give me other effective way to communicate with you,e.g telephone.I have just one goal:let you know the real cause of this issue.It is very important for me, and if this issue can’t be fixed,my project will be redo.

Hi,

My MSN is lauyue@hotmail.com. Please contact me directly.

Thanks

Hi,

We have enhanced the performance of formula calculation engine in v<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2.0.1.2014 (attached). And, we tested your TestAspose project with it. The old version (v2.0.1.2013) takes about 71 seconds to calculate about 71 * 26 * 251 = 463346 formulas once on my system. The new version takes about 7.6 seconds to calculate all formulas at a time.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please test with the following steps:

1. Using v2.0.1.2013, set the GridWeb.EnableAjax = false, test it.

2. Using v2.0.1.2014, set the GridWeb.EnableAjax = false, test it.

3. Using v2.0.1.2014, set the GridWeb.EnableAjax = true, test it.

Please tell us the 3 test results. Thank you very much!

1. Using v<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2.0.1.2013, set the GridWeb.EnableAjax = false, test it.------221.2 secodes

2. Using v2.0.1.2014, set the GridWeb.EnableAjax = false, test it.------9.78 secondes<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

3. Using v2.0.1.2014, set the GridWeb.EnableAjax = true, test it.-------18 secondes

Above testing result all are average value.

Judging from the test results,the performance of the “RunAllFormual()” of the new version of Aspose.Grid.Web is higher than old. It is a good news for me. I will use the new version to test by my application in tomorrow. And I will tell you test result to you,too.

Many thanks for your professional dedication.

First, I need to thank you for the new version of Aspose.Grid.Web and you assuredly enhanced it's performance and my application has not appeared that issue.

But I found other serious issue in the new version of Aspose.Grid.Web(V2.0.1.2014).I have build a test project and it may help you to understand this issue.

1.Set my test project in your server(TestAspose_7z.zip).

2.Input the "newpca.xls" into the "File" textbox in the default.aspx(like pic:2009-07-28_234238.png).

3.Press the "LoadExcel" button and load the Excel file in the page.

4.Press the "sample_other" sheet and make it to change the active sheet.

5.Input some data into the cells in "sample_other" sheet.(like pic:2009-07-28_175347.jpg)

6.Press the "RFQ_total" sheet and make it to change the active sheet.

7.Input some data into the cells(F8,F9) in "RFQ_total" sheet.(like pic:2009-07-28_175540.jpg)

8.Press the "fixture_list" sheet and make it to change the active sheet.

9.Input some data into the cells in "fixture_list" sheet.(like pic:2009-07-28_175450.jpg)

10.Press the "PCA_BFT" sheet and make it to change the active sheet.

11.Input some data into the cells in "PCA_BFT" sheet.(like pic:2009-07-28_175655.jpg)

12.Press the "RFQ_total" sheet and make it to change the active sheet.

13.The formula of F36 cell is "=F22+SUM(F24:F35)" ,But you can find the value of this cell is F22*2.--------------this is a serious error!(like pic:2009-07-28_175814.jpg)

14.If I use the V2.0.1.2013 of Aspose.Grid.Web displace the V2.0.1.2014, this issue will not appear.

So, please check this issue and fix it,thank you very much!


Hi,

Thanks for the project with details.

We will check your issue and get back to you soon.

Thank you.

Hi,

Please try the attached version, we have fixed your mentioned issue.

Thank you.

well, I have test and the issue did not appeared.

But I am sorry , I forgot to post the other issue in here.

Please use the attachment file "newsys.xls" and load it in default.aspx in my test project. You will find this issue(If you use v2.0.1.2016 version, you may can't get this issue,please use V2.0.1.2014):

1.Set my test porject on your server.

2.Put the newsys.xls in the root of my test project.

3.Input the "newsys.xls" in the "File" textbox. (like pic:2009-07-29_162447.jpg)

4,look over the F41 cell , the formula of this cell is "=F40/F8" ,and F8 is not 0,but why dose it show "#DIV/0" ? (like pic:2009-07-29_162630.jpg)

Hi,

Well, I have tested your file with v2.0.1.2016 and the formula resultant value in F41 cell is: "0.0000" of "RFQ_total" worksheet when I load it into GridWeb. If I try to click the submit button, the formula is also not giving me any error or invalid values.

Kindly make sure that you are using the newer version which we posted you in the previous post.

Thank you.

The issues you have found earlier (filed as 9694) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.