I’ve logged into the Aspose.Excel forum and tried to start a new thread, but I get redirected to an error page.
So, I’m submitting my support request via email instead.
Here’s my problem…
I’m building a spreadsheet that contains formulas. So far, I have formulas that use the built-in SUM function, i.e.: “=SUM(C5:C20)”, and other formulas that multiply the values in two cells together, i.e.: “=(C5*E5)”. The spreadsheet gets generated, and all the values showing up look correct. I’m able to select cells (with the mouse pointer or the arrow keys) that have a value or contain a formula utilizing the SUM function. However, when I try to select a cell that has a formula utilizing multiplication, I get an “EXCEL.EXE Application Error” dialog box with the following message:
The instruction at “0x3008a214” referenced memory at “0xd05bbfc8”. The memory could not be “read”.
I get this error consistently. I’ve even saved the web-generated Excel file offline, and opened it offline, and get the same error message.
What might be wrong?
P.S.: Here’s a code snippet that shows how I’m populating the cells with values and formulas:
'Populate the Merch Stands section of the report
For i As Int16 = 0 To dtMerchStands.Rows.Count - 1
For j As Int16 = 0 To dtMerchStands.Columns.Count - 1
Select Case j
Case 2, 4, 5, 6, 7
newCells(currentRow, j).Style = styleDecimal
Case Else
newCells(currentRow, j).Style = styleDefault
End Select
newCells(currentRow,
j).PutValue(dtMerchStands.Rows(i)(j))
formula = “=(C” & (currentRow + 1) & “*E” & (currentRow + 1) & “)”
newCells(currentRow, 9).Style = styleDecimal
newCells(currentRow, 9).Formula = formula
formula = “=(C” & (currentRow + 1) & “*F” & (currentRow + 1) & “)”
newCells(currentRow, 10).Style = styleDecimal
newCells(currentRow, 10).Formula = formula
formula = “=(C” & (currentRow + 1) & “*G” & (currentRow + 1) & “)”
newCells(currentRow, 11).Style = styleDecimal
newCells(currentRow, 11).Formula = formula
formula = “=(C” & (currentRow + 1) & “*H” & (currentRow + 1) & “)”
newCells(currentRow, 12).Style = styleDecimal
newCells(currentRow, 12).Formula = formula
Next
currentRow += 1
Next
'Insert Merch Stands section summary items
formula = “=SUM(C5:C” & (currentRow - 1) & “)”
newCells(currentRow, 2).Style = styleDecimal
newCells(currentRow, 2).Formula = formula
formula = “=SUM(J5:J” & (currentRow - 1) & “)”
newCells(currentRow, 9).Style = styleDecimal
newCells(currentRow, 9).Formula = formula
formula = “=SUM(K5:K” & (currentRow - 1) & “)”
newCells(currentRow, 10).Style = styleDecimal
newCells(currentRow, 10).Formula = formula
formula = “=SUM(L5:L” & (currentRow - 1) & “)”
newCells(currentRow, 11).Style = styleDecimal
newCells(currentRow, 11).Formula = formula
formula = “=SUM(M5:M” & (currentRow - 1) & “)”
newCells(currentRow, 12).Style = styleDecimal
newCells(currentRow, 12).Formula = formula
'Push the workbook down to the client
newExcel.Save(“CompartmentBasis.xls”,
SaveType.OpenInBrowser, FileFormatType.Excel2000, Response)
Nathan
Dear Nathan,
This reply just addresses the forum posting error.
Please check The Url of the Forums Changed! first.
I have tried both url’s. It’s OK to post and reply.
So please tell me what the posting problem is and I will fix it.
This is the error I’m getting when I try to start a new thread:
Server Error in ‘/forums’ Application.
--------------------------------------------------------------------------------
Did not get back a forum group for forum group id 10
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: AspNetForums.Components.ForumGroupNotFoundException: Did not get back a forum group for forum group id 10
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[ForumGroupNotFoundException: Did not get back a forum group for forum group id 10]
AspNetForums.ForumGroups.GetForumGroup(Int32 forumGroupID) +214
AspNetForums.Controls.WhereAmI.get_ForumGroup() +62
AspNetForums.Controls.WhereAmI.InitializeSkin(Control skin) +472
AspNetForums.Controls.SkinnedForumWebControl.CreateChildControls() +24
System.Web.UI.Control.EnsureChildControls() +100
System.Web.UI.Control.PreRenderRecursiveInternal() +38
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Control.PreRenderRecursiveInternal() +125
System.Web.UI.Page.ProcessRequestMain() +1470
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.288; ASP.NET Version:1.0.3705.288
Dear Nathan,
Thanks for your consideration.
I have test your code and there is no problem to set multiplication formula. So I guess the problem may be caused by the styles or other code. So could you give me the total code and the result file? I will investigate it thoroughly.
I’m blocked from sending/receiving ZIP file attachments and files with a VB extension, so I’m sending you the Excel result file by email, and here’s the full VB.NET code that builds the result file:
Imports Aspose.Excel
Imports Oracle.DataAccess.Client
Imports System.Text
Public Class WebForm1
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code “
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents RegularExpressionValidator1 As System.Web.UI.WebControls.RegularExpressionValidator
Protected WithEvents txtCompartment As System.Web.UI.WebControls.TextBox
Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents ValidationSummary1 As System.Web.UI.WebControls.ValidationSummary
Protected WithEvents lblMessages As System.Web.UI.WebControls.Label
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As DataSet
Dim compartment As Int16 = CType(txtCompartment.Text, Int16)
Try
ds = BuildDataSet(compartment)
SendToExcel(ds)
Catch ex As Exception
lblMessages.Text = ex.Message
End Try
End Sub
Private Function BuildDataSet(ByVal compartment As Int16) As DataSet
Dim sqlMerch As StringBuilder = New StringBuilder
Dim sqlPremerch As StringBuilder = New StringBuilder
Dim sqlPreSum As StringBuilder = New StringBuilder
Dim conn As OracleConnection = _
New OracleConnection(ConfigurationSettings.AppSettings(“OracleConnectionString”))
Dim cmd As OracleCommand
Dim daMerch As OracleDataAdapter
Dim daPremerch As OracleDataAdapter
Dim daPreSum As OracleDataAdapter
Dim ds As DataSet = New DataSet
ds.Tables.Add(New DataTable(“merch_stands”))
ds.Tables.Add(New DataTable(“premerch_stands”))
ds.Tables.Add(New DataTable(“premerch_summary”))
sqlMerch = sqlMerch.Append(“SELECT stand.stand stand,”)
sqlMerch = sqlMerch.Append(” stand.est_yr est_yr,“)
sqlMerch = sqlMerch.Append(” stand_area.net acres,“)
sqlMerch = sqlMerch.Append(” (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) age,“)
sqlMerch = sqlMerch.Append(” grow_pine_pulpwd_cords(stand.event,0,‘peracre’) pp,“)
sqlMerch = sqlMerch.Append(” grow_pine_sawlog_cords(stand.event,0,‘peracre’) ps,“)
sqlMerch = sqlMerch.Append(” grow_hrdw_pulpwd_cords(stand.event,0,‘peracre’) hp,“)
sqlMerch = sqlMerch.Append(” grow_hrdw_sawlog_cords(stand.event,0,‘peracre’) hs")
sqlMerch = sqlMerch.Append(" FROM stand,“)
sqlMerch = sqlMerch.Append(” stand_area")
sqlMerch = sqlMerch.Append(" WHERE (stand.comp = " & compartment & “)”)
sqlMerch = sqlMerch.Append(" AND")
sqlMerch = sqlMerch.Append(" (stand.event = stand_area.event(+))“)
sqlMerch = sqlMerch.Append(” AND")
sqlMerch = sqlMerch.Append(" (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) >= 16 “)
sqlMerch = sqlMerch.Append(“ORDER BY stand.stand”)
sqlPremerch = sqlPremerch.Append(“SELECT stand.stand stand,”)
sqlPremerch = sqlPremerch.Append(” (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) age,“)
sqlPremerch = sqlPremerch.Append(” stand_area.net acres,“)
sqlPremerch = sqlPremerch.Append(” stand.est_yr est_yr")
sqlPremerch = sqlPremerch.Append(" FROM stand,“)
sqlPremerch = sqlPremerch.Append(” stand_area")
sqlPremerch = sqlPremerch.Append(" WHERE (stand.comp = " & compartment & “)”)
sqlPremerch = sqlPremerch.Append(" AND")
sqlPremerch = sqlPremerch.Append(" (stand.event = stand_area.event(+))“)
sqlPremerch = sqlPremerch.Append(” AND")
sqlPremerch = sqlPremerch.Append(" (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) <= 15 “)
sqlPremerch = sqlPremerch.Append(“ORDER BY stand.stand”)
sqlPreSum = sqlPreSum.Append(“SELECT age,”)
sqlPreSum = sqlPreSum.Append(” SUM(acres) acres")
sqlPreSum = sqlPreSum.Append(" FROM (“)
sqlPreSum = sqlPreSum.Append(” SELECT (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) age,“)
sqlPreSum = sqlPreSum.Append(” stand_area.net acres")
sqlPreSum = sqlPreSum.Append(" FROM stand,“)
sqlPreSum = sqlPreSum.Append(” stand_area")
sqlPreSum = sqlPreSum.Append(" WHERE (stand.comp = " & compartment & “)”)
sqlPreSum = sqlPreSum.Append(" AND")
sqlPreSum = sqlPreSum.Append(" (stand.event = stand_area.event(+))“)
sqlPreSum = sqlPreSum.Append(” AND")
sqlPreSum = sqlPreSum.Append(" (TO_NUMBER(TO_CHAR(SYSDATE,‘YYYY’)) - stand.est_yr) <= 15 “)
sqlPreSum = sqlPreSum.Append(”) “)
sqlPreSum = sqlPreSum.Append(“GROUP BY age”)
Try
daMerch = New OracleDataAdapter(sqlMerch.ToString, conn)
daMerch.Fill(ds.Tables(“merch_stands”))
daPremerch = New OracleDataAdapter(sqlPremerch.ToString, conn)
daPremerch.Fill(ds.Tables(“premerch_stands”))
daPreSum = New OracleDataAdapter(sqlPreSum.ToString, conn)
daPreSum.Fill(ds.Tables(“premerch_summary”))
Catch ex As Exception
Dim message As String = “An Oracle exception occurred: " & ControlChars.CrLf & _
ex.Message & ControlChars.CrLf & ex.StackTrace
Throw New ApplicationException(message, ex)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
conn.Dispose()
End Try
Return ds
End Function
Private Sub SendToExcel(ByVal ds As DataSet)
Dim dtMerchStands As DataTable = ds.Tables(“merch_stands”)
Dim dtPremerchStands As DataTable = ds.Tables(“premerch_stands”)
Dim dtPremerchSum As DataTable = ds.Tables(“premerch_summary”)
Dim currentRow As Int16 = 4
Dim currentColumn As Int16 = 9
Dim formula As String
Dim currentRange As Range
Try
'Set up a new Excel workbook object
Dim xlsTemplate As String = MapPath(”.”) & “\BasisCalculation.xls”
Dim license As String = MapPath(“.”) & “\bin\Aspose.Excel.lic”
Dim newExcel As Excel = New Excel(license, Me)
newExcel.Open(xlsTemplate)
Dim newSheet As Worksheet = newExcel.Worksheets.Item(0)
Dim newCells As Cells = newSheet.Cells
'Set up styles
Dim styles As Styles = newExcel.Styles
Dim styleIndex As Int16 = styles.Add
'Default style
Dim styleDefault As Style = styles(styleIndex)
styleDefault.Font.Size = 10
styleDefault.Font.Name = “Arial”
styleDefault.Borders.Item(BorderType.TopBorder).LineStyle = CellBorderType.Thin
styleDefault.Borders.Item(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
styleDefault.Borders.Item(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
styleDefault.Borders.Item(BorderType.RightBorder).LineStyle = CellBorderType.Thin
'Two decimal place style
styleIndex = styles.Add
Dim styleDecimal As Style = styles(styleIndex)
styleDecimal.Font.Size = 10
styleDecimal.Font.Name = “Arial”
styleDecimal.Borders.Item(BorderType.TopBorder).LineStyle = CellBorderType.Thin
styleDecimal.Borders.Item(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
styleDecimal.Borders.Item(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
styleDecimal.Borders.Item(BorderType.RightBorder).LineStyle = CellBorderType.Thin
styleDecimal.Number = 2
'Summary style
styleIndex = styles.Add
Dim styleSummary As Style = styles(styleIndex)
styleSummary.Font.Size = 10
styleSummary.Font.Name = “Arial”
styleSummary.Borders.Item(BorderType.TopBorder).LineStyle = CellBorderType.Thin
styleSummary.Borders.Item(BorderType.LeftBorder).LineStyle = CellBorderType.Thin
styleSummary.Borders.Item(BorderType.BottomBorder).LineStyle = CellBorderType.Thin
styleSummary.Borders.Item(BorderType.RightBorder).LineStyle = CellBorderType.Thin
styleSummary.ForegroundColor = Color.Aqua
styleSummary.Number = 2
'Summary Label style
styleIndex = styles.Add
Dim styleSummaryLabel As Style = styles(styleIndex)
styleSummaryLabel.Font.Size = 10
styleSummaryLabel.Font.Name = “Arial”
styleSummaryLabel.Font.IsBold() = True
styleSummaryLabel.HorizontalAlignment = TextAlignmentType.Right
'General Summary Box Label style
styleIndex = styles.Add
Dim styleGenSumBoxLabel As Style = styles(styleIndex)
styleGenSumBoxLabel.Font.Size = 14
styleGenSumBoxLabel.Font.Name = “Arial”
styleGenSumBoxLabel.Font.IsBold = True
styleGenSumBoxLabel.ForegroundColor = Color.FromArgb(13434828)
styleGenSumBoxLabel.Borders.Item(BorderType.TopBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxLabel.Borders.Item(BorderType.LeftBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxLabel.Borders.Item(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxLabel.Borders.Item(BorderType.RightBorder).LineStyle = CellBorderType.Medium
'General Summary Box Value style
styleIndex = styles.Add
Dim styleGenSumBoxValue As Style = styles(styleIndex)
styleGenSumBoxValue.Font.Size = 12
styleGenSumBoxValue.Font.Name = “Arial”
styleGenSumBoxValue.Font.IsBold = True
styleGenSumBoxValue.ForegroundColor = Color.Yellow
styleGenSumBoxValue.Borders.Item(BorderType.TopBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxValue.Borders.Item(BorderType.LeftBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxValue.Borders.Item(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxValue.Borders.Item(BorderType.RightBorder).LineStyle = CellBorderType.Medium
styleGenSumBoxValue.Number = 2
'14 point, bold, left justified style
styleIndex = styles.Add
Dim styleLeftBold14 As Style = styles(styleIndex)
styleLeftBold14.Font.Size = 14
styleLeftBold14.Font.Name = “Arial”
styleLeftBold14.Font.IsBold = True
'12 point, left justified style
styleIndex = styles.Add
Dim styleLeft12 As Style = styles(styleIndex)
styleLeft12.Font.Size = 12
styleLeft12.Font.Name = “Arial”
'Section Box style
styleIndex = styles.Add
Dim styleSectionBox As Style = styles(styleIndex)
styleSectionBox.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Medium
styleSectionBox.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Medium
styleSectionBox.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
styleSectionBox.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Medium
'Populate report header
newCells(0, 0).PutValue(“Compartment: " & txtCompartment.Text)
'Populate the Merch Stands section of the report
For i As Int16 = 0 To dtMerchStands.Rows.Count - 1
For j As Int16 = 0 To dtMerchStands.Columns.Count - 1
Select Case j
Case 2, 4, 5, 6, 7
newCells(currentRow, j).Style = styleDecimal
Case Else
newCells(currentRow, j).Style = styleDefault
End Select
newCells(currentRow, j).PutValue(dtMerchStands.Rows(i)(j))
formula = “=(C” & (currentRow + 1) & “*E” & (currentRow + 1) & “)”
'newCells(currentRow, 9).Style = styleDecimal
newCells(currentRow, 9).Formula = formula
formula = “=(C” & (currentRow + 1) & “*F” & (currentRow + 1) & “)”
'newCells(currentRow, 10).Style = styleDecimal
newCells(currentRow, 10).Formula = formula
formula = “=(C” & (currentRow + 1) & “*G” & (currentRow + 1) & “)”
'newCells(currentRow, 11).Style = styleDecimal
newCells(currentRow, 11).Formula = formula
formula = “=(C” & (currentRow + 1) & “*H” & (currentRow + 1) & “)”
'newCells(currentRow, 12).Style = styleDecimal
newCells(currentRow, 12).Formula = formula
Next
currentRow += 1
Next
'Insert Merch Stands section summary items
newCells(currentRow, 1).Style = styleSummaryLabel
newCells(currentRow, 1).PutValue(“Ttl Merch Acres”)
formula = “=SUM(C5:C” & (currentRow - 1) & “)”
newCells(currentRow, 2).Style = styleSummary
newCells(currentRow, 2).Formula = formula
newCells(currentRow, 8).Style = styleSummaryLabel
newCells(currentRow, 8).PutValue(“Total Volume in Cords”)
formula = “=SUM(J5:J” & (currentRow - 1) & “)”
newCells(currentRow, 9).Style = styleSummary
newCells(currentRow, 9).Formula = formula
formula = “=SUM(K5:K” & (currentRow - 1) & “)”
newCells(currentRow, 10).Style = styleSummary
newCells(currentRow, 10).Formula = formula
formula = “=SUM(L5:L” & (currentRow - 1) & “)”
newCells(currentRow, 11).Style = styleSummary
newCells(currentRow, 11).Formula = formula
formula = “=SUM(M5:M” & (currentRow - 1) & “)”
newCells(currentRow, 12).Style = styleSummary
newCells(currentRow, 12).Formula = formula
'Insert Merch Stand Volume summary
currentRow += 3
currentRange = newCells.CreateRange(currentRow, 9, 1, 4)
currentRange.Style = styleGenSumBoxLabel
newCells(currentRow, 9).PutValue(“Timber Volume Totals / Cords”)
currentRow += 1
newCells(currentRow, 10).Style = styleLeftBold14
newCells(currentRow, 10).PutValue(“Pine”)
newCells(currentRow, 12).Style = styleGenSumBoxValue
formula = “=(J” & (currentRow - 3) & " + K” & (currentRow - 3) & “)”
newCells(currentRow, 12).Formula = formula
currentRow += 1
newCells(currentRow, 10).Style = styleLeftBold14
newCells(currentRow, 10).PutValue(“Hardwood”)
newCells(currentRow, 12).Style = styleGenSumBoxValue
formula = “=(L” & (currentRow - 4) & " + M" & (currentRow - 4) & “)”
newCells(currentRow, 12).Formula = formula
'Push the workbook down to the client
newExcel.Save(“CompartmentBasis.xls”, SaveType.OpenInBrowser, FileFormatType.Excel2000, Response)
Catch ex As Exception
lblMessages.Text = “An exception occurred in function SendToExcel:” & ControlChars.CrLf
lblMessages.Text += ex.Message & ControlChars.CrLf & ex.StackTrace
End Try
End Sub
End Class
Dear Nathan,
The CompBasis.zip file you sent me contains 3 file:
1. 10514.xls
2.WebForm1.aspx
3.WebForm1.aspx.cs
Is 10514.xls the designer file or result file? I found the problem exists in this file.
If it is the result file, could you give me the designer file -BasisCalculation.xls?
Dear Nathan,
I found the problem and fixed it. Please re-download fix 1.5.8.
I’ve applied hotfix 1.5.8, and wanted to report back that the hotfix did fix the problem. Thank you, once again, for your prompt turnaround. Keep up the good work!