1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<%@ Page Language="C#" MasterPageFile="~/tpl/Demo.Master" AutoEventWireup="true"
    CodeBehind="calculate-formula.aspx.cs" Inherits="Workbooks_Data_CalculateFormula"
    Title="Calculate Formula - Aspose.Cells Demos" %>

<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="Server">
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>&nbsp;</p>
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>
            <table align="center" border="0" cellpadding="0" cellspacing="0" width="90%">
                <tr>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_lft.jpg" width="19" /></td>
                    <td class="demos-heading-bg" width="100%">
                        <h2 class="demos-heading-bg">
                            <font face="Arial" size="4">&nbsp;Calculate Formula - Aspose.Cells</font></h2>
                    </td>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_rt.jpg" width="19" /></td>
                </tr>
            </table>
    </p>
    <p class="componentDescriptionTxt">
        <font face="Arial" size="2">This demo explains how to calculate the results of different
            types of worksheet formulas / functions to process data in the spreadsheet using
            <a href="http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx">
                Aspose.Cells</a> for .NET.</font></p>
    <p class="componentDescriptionTxt">
        <font face="Arial" size="2">The demo compares the formula / function results of Aspose.Cells
            with MS Excel. Aspose.Cells component supports all the commonly used functions related
            to different categories: Mathematical, String, Statistical, DateTime, Logical and
            Lookup and Reference Functions etc. The demo
		makes use of a template excel file which contains a list of all the formulas / functions string of all the categories
            mentioned. The file also contains some
        static data used in different formulas. The demo retrieves the formulas / functions
        string and calculates the formulas / functions. It also retrieves values from the
        formulated cells and inserts into a column.</font></SPAN><span
                style="font-size: 10pt; font-family: Arial">You can either open the resulting excel file into MS Excel
        or save directly to your disk. </span>
    </p>
    <asp:Button ID="btnExecute" runat="server" Text="Process" OnClick="btnExecute_Click" />
</asp:Content>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Aspose.Cells;

public partial class Workbooks_Data_CalculateFormula : System.Web.UI.Page
{
    protected System.Web.UI.WebControls.DropDownList ddlFileVersion;

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnExecute_Click(object sender, EventArgs e)
    {
        //Call Method to create report
        CreateStaticReport();
    }

    public void CreateStaticReport()
    {
        //Open template
        string path = System.Web.HttpContext.Current.Server.MapPath("~");
        path = path.Substring(0, path.LastIndexOf("\\"));
        path += @"\designer\Workbooks\CalculateFormula.xls";

        //Instantiate a workbook
        Workbook workbook = new Workbook(path);

        //Get the cells collection in the first worksheet
        Cells cells = workbook.Worksheets[0].Cells;
        for (int i = 11; i < 86; i++)
        {
            //Get a string value from a cell
            string strFormula = cells[i, 2].StringValue;
            //Set a formula of the Cell
            cells[i, 3].Formula = strFormula;
        }

        //Calculates the result of formulas
        workbook.CalculateFormula();
        for (int i = 11; i < 86; i++)
        {
            //Put values obtaining the calculated values
            cells[i, 4].PutValue(cells[i, 3].Value);
        }

        if (ddlFileVersion.SelectedItem.Value == "XLS")
        {
            ////Save file and send to client browser using selected format
            workbook.Save(HttpContext.Current.Response, "CalculateFormula.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
        }
        else
        {
            workbook.Save(HttpContext.Current.Response, "CalculateFormula.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
        }

        //end response to avoid unneeded html
        HttpContext.Current.Response.End();      

    }
}