Winnovative Software Logo

 HTML to PDF Converter - Excel Library - ASP.NET Charts - RTF to PDF Converter
 PDF Merge and Split - PDF Security - PDF Viewers - PDF to Text - Images Extractor

  
Skip Navigation Links
 
Add Formulas to an Excel Worksheet Using Winnovative Excel Library for .NET
This sample shows how to add formulas to a worksheet. The library also offers an integrated formula calculation engine which allows you to calculate the formula results and set the result in the workbook.
Getting Started
Cells and Ranges
Cell Styles
Excel Data Types
Excel Charts
Adding Images
Adding Formulas
Data Validation
Load/Get DataTable
Import from CSV
Edit Workbooks
To create the Excel workbook first select the format of the generated workbook and press the Create Workbook button. The Excel workbook will be created on the server and sent as an attachment to the browser. You will be prompted to open the generated workbook in an exernal viewer. The free Excel Viewer from Microsoft supports both the Excel 97-2003 (.xls) format and the new Excel 2007 (.xlsx) format.
Workbook Format:
Create Workbook Button Create Excel Workbook 




protected void lnkBtnCreateWorkbook_Click(object sender, EventArgs e)
{
    // get the Excel workbook format
    ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : 
                ExcelWorkbookFormat.Xlsx_2007;

    // create the workbook in the desired format with a single worksheet
    ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);

    // set the license key before saving the workbook
    workbook.LicenseKey = "RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw=";

    // set workbook description properties
    workbook.DocumentProperties.Subject = "Formulas demo";
    workbook.DocumentProperties.Comments = 
                "Add formulas to an Excel worksheet using Winnovative Excel library
                    for .NET";

    #region CREATE CUSTOM WORKBOOK STYLES

    #region Add a style used for the cells in the worksheet title area

    ExcelCellStyle titleStyle = workbook.Styles.AddStyle("WorksheetTitleStyle");
    // center the text in the title area
    titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
    titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    // set the title area borders
    titleStyle.Borders[ExcelCellBorderIndex.Bottom].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Top].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Left].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
    titleStyle.Borders[ExcelCellBorderIndex.Right].Color = Color.Green;
    titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
    {
        // set the solid fill for the title area range with a custom color
        titleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
        titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
    }
    else
    {
        // set the gradient fill for the title area range with a custom color
        titleStyle.Fill.FillType = ExcelCellFillType.GradientFill;
        titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
        titleStyle.Fill.GradientFillOptions.Color2 = Color.White;
    }
    // set the title area font 
    titleStyle.Font.Size = 14;
    titleStyle.Font.Bold = true;
    titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single;



    #endregion

    #region Add a style used for text messages

    ExcelCellStyle textMessageStyle = workbook.Styles.AddStyle("TextMessageStyle");
    textMessageStyle.Font.Size = 12;
    textMessageStyle.Font.Bold = true;
    textMessageStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    textMessageStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
    textMessageStyle.Fill.FillType = ExcelCellFillType.SolidFill;
    textMessageStyle.Fill.SolidFillOptions.BackColor = Color.LightBlue;
    textMessageStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Thin;
    textMessageStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thin;
    textMessageStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Thin;
    textMessageStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Thin;

    #endregion

    #region Add a style used for the formula results

    ExcelCellStyle formulaResultStyle = workbook.Styles.AddStyle("FormulaResultStyle");
    formulaResultStyle.Font.Size = 10;
    formulaResultStyle.Font.Bold = true;
    formulaResultStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
    formulaResultStyle.Fill.FillType = ExcelCellFillType.SolidFill;
    formulaResultStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 204);
    formulaResultStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Thin;
    formulaResultStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thin;
    formulaResultStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Thin;
    formulaResultStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Thin;

    #endregion

    #endregion

    // get the first worksheet in the workbook
    ExcelWorksheet worksheet = workbook.Worksheets[0];

    // set the default worksheet name
    worksheet.Name = "Formulas Demo";

    #region WORKSHEET PAGE SETUP

    // set worksheet paper size and orientation, margins, header and footer
    worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
    worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
    worksheet.PageSetup.LeftMargin = 1;
    worksheet.PageSetup.RightMargin = 1;
    worksheet.PageSetup.TopMargin = 1;
    worksheet.PageSetup.BottomMargin = 1;

    // add header and footer

    //display a logo image in the left part of the header
    string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
    System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "logo.jpg"));
    worksheet.PageSetup.LeftHeaderFormat = "&G";
    worksheet.PageSetup.LeftHeaderPicture = logoImg;
    // display worksheet name in the right part of the header
    worksheet.PageSetup.RightHeaderFormat = "&A";

    // add worksheet header and footer
    // display the page number in the center part of the footer
    worksheet.PageSetup.CenterFooterFormat = "&P";
    // display the workbook file name in the left part of the footer
    worksheet.PageSetup.LeftFooterFormat = "&F";
    // display the current date in the right part of the footer
    worksheet.PageSetup.RightFooterFormat = "&D";

    #endregion

    #region WRITE THE WORKSHEET TOP TITLE

    // merge the cells in the range to create the title area 
    worksheet["A2:G3"].Merge();
    // gets the merged range containing the top left cell of the range
    ExcelRange titleRange = worksheet["A2"].MergeArea;
    // set the text of title area
    worksheet["A2"].Text = "Adding Formulas to
        an Excel Worksheet";

    // set a row height of 18 points for each row in the range
    titleRange.RowHeightInPoints = 18;
    // set the worksheet top title style
    titleRange.Style = titleStyle;

    #endregion

    // set first column width
    worksheet["A1"].ColumnWidthInChars = 37;

    // Note: 
    // Set the properties below before assigning any formula or array
        formula to a cell
    // The properties below are used when parsing the formula assigned
        to a cell or a range of cells
    workbook.FormulaParamsSeparator = ',';
    workbook.FormulaRowsSeparator = ';';
    workbook.FormulaNumberFormat = System.Globalization.NumberFormatInfo.InvariantInfo;

    worksheet["A5:G5"].Merge();
    worksheet["A5:G5"].Style = textMessageStyle;
    worksheet["A5:G5"].Value = "Ordinary Formulas:";

    worksheet["A7"].Value = "FORMULA DATA:";
    worksheet["A7"].Style.Font.Bold = true;

    worksheet["C7"].Value = 100.5;
    worksheet["D7"].Value = 50.5;
    worksheet["E7"].Value = 89;
    worksheet["F7"].Value = 15.75;
    worksheet["G7"].Value = 44.25;

    worksheet["A9"].Value = "SUM(C7:G7)";
    worksheet["A9"].Style.Font.Color = Color.DarkBlue;
    worksheet["A9"].AddComment("Calculate the
        sum of the numbers in the range C7:G7");
    // add formula
    worksheet["C9"].Formula = "=SUM(C7:G7)";
    worksheet["C9"].Style = formulaResultStyle;

    worksheet["A11"].Value = "AVERAGE(C7:G7)";
    worksheet["A11"].Style.Font.Color = Color.DarkBlue;
    worksheet["A11"].AddComment("Calculate the
        average of the numbers in the range C7:G7");
    // add formula
    worksheet["C11"].Formula = "=AVERAGE(C7:G7)";
    worksheet["C11"].Style = formulaResultStyle;

    worksheet["A13"].Value = "MIN(C7:G7)";
    worksheet["A13"].Style.Font.Color = Color.DarkBlue;
    worksheet["A13"].AddComment("Calculate the
        mimimum value in the range C7:G7");
    // add formula
    worksheet["C13"].Formula = "=MIN(C7:G7)";
    worksheet["C13"].Style = formulaResultStyle;

    worksheet["A15"].Value = "C7 + 100.45";
    worksheet["A15"].Style.Font.Color = Color.DarkBlue;
    worksheet["A15"].AddComment("Add a constant
        to the value from C7");
    // add formula
    worksheet["C15"].Formula = "=C7 + 100.45";
    worksheet["C15"].Style = formulaResultStyle;

    worksheet["A17"].Value = "IF(C7>E7, C7,
        E7)";
    worksheet["A17"].Style.Font.Color = Color.DarkBlue;
    worksheet["A17"].AddComment("Calculate maximum
        between C7 and D7 using the IF function");
    // add formula
    worksheet["C17"].Formula = "=IF(C7>E7,
        E7, E7)";
    worksheet["C17"].Style = formulaResultStyle;


    worksheet["A20:G20"].Merge();
    worksheet["A20:G20"].Style = textMessageStyle;
    worksheet["A20:G20"].Value = "Array Formulas:";

    worksheet["A22"].Value = "FORMULA DATA:";
    worksheet["A22"].Style.Font.Bold = true;
    worksheet["A22"].AddComment("Intialized with
        an array formula");

    // use an array formula to initialize the test data
    // it is equivalent with the commented code below
    worksheet["C22:G22"].FormulaArray = "{10,
        -5, 2, 21, -20}";

    /*worksheet["C22"].Value = 10;
 worksheet["D22"].Value = -5;
 worksheet["E22"].Value = 2;
 worksheet["F22"].Value = 21;
 worksheet["G22"].Value = -20;*/

    worksheet["A24"].Value = "AVERAGE(IF(C22:G22>0,C22:G22,FALSE))";
    worksheet["A24"].Style.Font.Color = Color.DarkBlue;
    worksheet["A24"].AddComment("Calculate the
        average of the positive numbers in the range C22:G22");
    // add formula
    worksheet["C24"].FormulaArray = "=AVERAGE(IF(C22:G22>0,C22:G22,FALSE))";
    worksheet["C24"].Style = formulaResultStyle;

    // defined 2 named ranges to sum in a third range
    ExcelNamedRange firstRange = worksheet.NamedRanges.AddNamedRange(worksheet["C22:G22"], "FirstRange");
    ExcelNamedRange secondRange = worksheet.NamedRanges.AddNamedRange(worksheet["C7:G7"], "SecondRange");

    worksheet["A26"].Value = "FirstRange + SecondRange";
    worksheet["A26"].Style.Font.Color = Color.DarkBlue;
    worksheet["A26"].AddComment("Add the values
        from ranges C22:G22 and C7:G7 and assign the result to C26:G26");
    // add formula
    worksheet["C26:G26"].FormulaArray = "FirstRange
        + SecondRange";
    worksheet["C26:G26"].Style = formulaResultStyle;

    // Save the Excel document in the current HTTP response stream

    string outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? "FormulasDemo.xls" : 
                "FormulasDemo.xlsx";

    System.Web.HttpResponse httpResponse = System.Web.HttpContext.Current.Response;

    // Prepare the HTTP response stream for saving the Excel document

    // Clear any data that might have been previously buffered in the
        output stream
    httpResponse.Clear();

    // Set output stream content type for Excel 97-2003 (.xls) or Excel
        2007 (.xlsx)
    if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
        httpResponse.ContentType = "Application/x-msexcel";
    else
        httpResponse.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    // Add the HTTP header to announce the Excel document either as an
        attachment or inline
    httpResponse.AddHeader("Content-Disposition", String.Format("attachment; filename={0}", outFileName));

    // Save the workbook to the current HTTP response output stream
    // and close the workbook after save to release all the allocated
        resources
    try
    {
        workbook.Save(httpResponse.OutputStream);
    }
    catch (Exception ex)
    {
        // report any error that might occur during save
        Session["ErrorMessage"] = ex.Message;
        Response.Redirect("ErrorPage.aspx");
    }
    finally
    {
        // close the workbook and release the allocated resources
        workbook.Close();

        #region Dispose the Image object

        if (logoImg != null)
            logoImg.Dispose();

        #endregion
    }

    // End the response and finish the execution of this page
    httpResponse.End();
}
Copyright © 2007- Winnovative Software Solutions