Essential Studio for Asp.Net | Demos

SHOWCASE SAMPLES
Showcase samples

    Formulas are entries in Excel that have an equation that calculates the value to be displayed. A typical formula might contain cells, constants, and even functions. Essential XlsIO has support for reading and writing formulas.


    Click the button to view an Excel spreadsheet generated by Essential XlsIO. Please note that Microsoft Excel Viewer or Microsoft Excel is required to view the resultant document.

    Read Formula from the template document
    Formula String
    Computed Value
     
    <%@ Page MasterPageFile="~/samplebrowser.master" Language="C#" AutoEventWireup="true"
        CodeFile="Formulas.aspx.cs" Inherits="WebSampleBrowser.XlsIO.Formulas" %>
    
    <asp:content id="Content2" contentplaceholderid="ControlsSection" runat="server">
        
        <p>
            Formulas are entries in Excel that have an equation that calculates the value to
            be displayed. A typical formula might contain cells, constants, and even functions.
            Essential XlsIO has support for reading and writing formulas.
        </p>
        <br />
        <p>
            Click the button to view an Excel spreadsheet generated by Essential XlsIO. 
            Please note that Microsoft Excel Viewer or Microsoft Excel is required to view the resultant document.
        </p>
        <div>
            <table id="PanelTable" width="100%" cellpadding="0" cellspacing="0" border="0">
                <tr>
                    <td>                    
                        <b>Read Formula from the template document</b>
                        <br />
                        <div style="margin-top: 5px; line-height: 30px">
                            <asp:Label ID="Label2" runat="server" Text="Formula String" Width="110px"></asp:Label>
                            <asp:Label Width="248px" ID="txtFormula" runat="server" ReadOnly="true" Font-Bold="True" ></asp:Label>
                            <br />
                            <asp:Label ID="Label1" runat="server" Text="Computed Value" Width="110px"></asp:Label>
                            <asp:Label Width="248px" ID="txtFormulaNumber" runat="server" ReadOnly="true" Font-Bold="True"></asp:Label>
                        </div>
                        <table style="margin-top: 3px" width="35%" cellpadding="0" cellspacing="0" border="0">
                            <tr>
                                <td align="left">
                                    <asp:Button Width="150px" ID="Button1" runat="server" Text="Write Formula" OnClick="Button1_Click" />
                                </td>
                                <td align="left">
                                    <asp:Button Width="150px" style="margin-left: 10px" ID="Button3" runat="server" Text="Read Formula" OnClick="Button3_Click" />
                                </td>
                            </tr>
                            <tr>
                                <td align="right">
                                    &nbsp;
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
        </div>
    </asp:content>
    
    
    #region Copyright Syncfusion Inc. 2001 - 2016
    // Copyright Syncfusion Inc. 2001 - 2016. All rights reserved.
    // Use of this code is subject to the terms of our license.
    // A copy of the current license can be obtained at any time by e-mailing
    // licensing@syncfusion.com. Any infringement will be prosecuted under
    // applicable laws. 
    #endregion
    using System;
    using System.Data;
    using System.Configuration;
    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 Syncfusion.XlsIO;
    
    namespace WebSampleBrowser.XlsIO
    {
        public partial class Formulas : System.Web.UI.Page
        {
            # region Page Load
            /// <summary>
            /// Handles the page load
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            protected void Page_Load(object sender, EventArgs e)
            {
            }
            # endregion
    
            # region Events
            /// <summary>
            /// Creates spreadsheet
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            protected void Button1_Click(object sender, EventArgs e)
            {
                //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
                //The instantiation process consists of two steps.
    
                //Step 1 : Instantiate the spreadsheet creation engine.
                ExcelEngine excelEngine = new ExcelEngine();
                //Step 2 : Instantiate the excel application object.
                IApplication application = excelEngine.Excel;
    
                //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
                //The new workbook will have 3 worksheets
                IWorkbook workbook = application.Workbooks.Create(3);
                //The first worksheet object in the worksheets collection is accessed.
                IWorksheet sheet = workbook.Worksheets[0];
    
                #region Insert Array Formula
    
                sheet.Range["A2"].Text = "Array formulas";
                sheet.Range["B2:E2"].FormulaArray = "{10,20,30,40}";
                sheet.Names.Add("ArrayRange", sheet.Range["B2:E2"]);
                sheet.Range["B3:E3"].FormulaArray = "ArrayRange+100";
                sheet.Range["A2"].CellStyle.Font.Bold = true;
                sheet.Range["A2"].CellStyle.Font.Size = 14;
    
                #endregion
    
                #region Excel functions
    
                sheet.Range["A5"].Text = "Formula";
                sheet.Range["B5"].Text = "Result";
    
                sheet.Range["A7"].Text = "ABS(ABS(-B3))";
                sheet.Range["B7"].Formula = "ABS(ABS(-B3))";
    
                sheet.Range["A9"].Text = "SUM(B3,C3)";
                sheet.Range["B9"].Formula = "SUM(B3,C3)";
    
                sheet.Range["A11"].Text = "MIN({10,20,30;5,15,35;6,16,36})";
                sheet.Range["B11"].Formula = "MIN({10,20,30;5,15,35;6,16,36})";
    
                sheet.Range["A13"].Text = "LOOKUP(B3,B3:E8)";
                sheet.Range["B13"].Formula = "LOOKUP(B3,B3:E3)";
    
                sheet.Range["A5:B5"].CellStyle.Font.Bold = true;
                sheet.Range["A5:B5"].CellStyle.Font.Size = 14;
    
                #endregion
    
                #region Simple formulas
                sheet.Range["C7"].Number = 10;
                sheet.Range["C9"].Number = 10;
                sheet.Range["A15"].Text = "C7+C9";
                sheet.Range["B15"].Formula = "C7+C9";
    
                #endregion
    
                sheet.Range["B1"].Text = "Excel formula support";
                sheet.Range["B1"].CellStyle.Font.Bold = true;
                sheet.Range["B1"].CellStyle.Font.Size = 14;
                sheet.Range["B1:E1"].Merge();
                sheet.Range["A1:A15"].AutofitColumns();
    
                //Saving the workbook to disk.
                workbook.SaveAs("Sample.xls", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
    
                //Close the workbook.
                workbook.Close();
                excelEngine.Dispose();
            }
    
            /// <summary>
            /// Reads formula from the spreadsheet
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            protected void Button3_Click(object sender, EventArgs e)
            {
                //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
                //The instantiation process consists of two steps.
    
                //Step 1 : Instantiate the spreadsheet creation engine.
                ExcelEngine excelEngine = new ExcelEngine();
                //Step 2 : Instantiate the excel application object.
                IApplication application = excelEngine.Excel;
    
                IWorkbook workbook = application.Workbooks.Open(XlsIOHelper.ResolveApplicationDataPath("FormulaTemplate.xls", Request));
    
                //The first worksheet object in the worksheets collection is accessed.
                IWorksheet sheet = workbook.Worksheets[0];
    
                //Read computed Formula Value. 
                this.txtFormulaNumber.Text = sheet.Range["C1"].FormulaNumberValue.ToString();
    
                //Read Formula
                this.txtFormula.Text = sheet.Range["C1"].Formula;
    
                //Close the workbook.
                workbook.Close();
    
                //No exception will be thrown if there are unsaved workbooks.
                excelEngine.ThrowNotSavedOnDestroy = false;
                excelEngine.Dispose();
            }
            # endregion
        }
    }