Free Support Forum - aspose.com

Use calculate MS Excel worksheet formulas in classic Asp

I’m coding in an legacy asp page and need to use formulas in my cells, how can I go about this?



Thanks in advance.

Dave.

Hi,

See a sample code segment here:

Sample code:

[test.asp page]

<%@ LANGUAGE = VBScript %>


<% Option Explicit %>




Aspose.Cells Formula ASP sample




Aspose.Cells Formula ASP sample




<%




'Create an Excel object
Dim xls
Set xls = CreateObject(“Aspose.Cells.Workbook”)


'Open a designer file (template)
xls.Open_5 “e:\test\book1.xls”


'Put data into this file
Dim sheet
Set sheet = xls.worksheets.item(0)


Dim cells
Set cells = sheet.cells


Dim cell
Set cell = cells.item_3(“A1”)
cell.PutValue_3 12

Dim cell2
Set cell2 = cells.item_3(“A2”)
cell2.PutValue_3 24

Dim cell3
Set cell3 = cells.item_3(“A3”)
cell3.formula = “=SUM(A1:A2)”


'Save the document to the stream
Dim stream
set stream = xls.SaveToStream()

Response.Clear


'Specify the document type
Response.ContentType = “application/vnd.ms-excel”


'Other options:
'Response.ContentType = “text/plain”
'Response.ContentType = “text/html”


'Specify how the document is sent to the browser.
Response.AddHeader “content-disposition”,“attachment; filename=MyBook.xls”


'Another option could be:
'Response.AddHeader “content-disposition”,“inline; filename=MyBook.xls”;


'Get data bytes from the stream and send it to the response.
Dim bytes
bytes = stream.ToArray()


Response.BinaryWrite(bytes)
Response.End





%>


Thank you.

Thanks very much for the detail of the reply Amjad! Just one question, im running the forumula and getting a 0 in that cell, any ideas on what that could be from common problems that might occur..

Thanks,
Dave.

Hi,

If you want to retrieve the calculated values against formulas at run time, please call Workbook.CalculateFormula method first.

See the sample code:

<%




'Create an Excel object
Dim xls
Set xls = CreateObject(“Aspose.Cells.Workbook”)


'Open a designer file (template)
xls.Open_5 “e:\test\book1.xls”


'Put data into this file
Dim sheet
Set sheet = xls.worksheets.item(0)


Dim cells
Set cells = sheet.cells


Dim cell
Set cell = cells.item_3(“A1”)
cell.PutValue_3 12

Dim cell2
Set cell2 = cells.item_3(“A2”)
cell2.PutValue_3 24

Dim cell3
Set cell3 = cells.item_3(“A3”)
cell3.formula = “=SUM(A1:A2)”

'Calcualte the Formulas
xls.CalculateFormula

Response.Write(cell3.stringValue)


%>


Thank you.

Thats brilliant, Thanks.