Use calculate MS Excel worksheet formulas in classic Asp

Aspose.Cells Product Family
1

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.

2

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.

3

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.

4

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.

5

Thats brilliant, Thanks.