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.