Excel spreadsheet library for C#/VB.NET

ExpertXls Excel Spreadsheet Library for .NET

Create Excel Spreadsheets in ASP.NET / VB.NET

Add Formulas to an Excel Worksheet Using ExpertXls 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.
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 external viewer. ExpertXls library can generate both XLS and XLSX files.
Workbook Format:
Create Workbook Button Create Excel Workbook
Source Code:

Imports ExpertXls.ExcelLib
Imports System.Drawing

Partial Class FormulasDemo
    Inherits System.Web.UI.Page

    Protected Sub lnkBtnCreateWorkbook_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkBtnCreateWorkbook.Click
        ' get the Excel workbook format
        Dim workbookFormat As ExcelWorkbookFormat
        If radioXlsFormat.Checked Then
            workbookFormat = ExcelWorkbookFormat.Xls_2003
            workbookFormat = ExcelWorkbookFormat.Xlsx_2007
        End If

        ' create the workbook in the desired format with a single worksheet
        Dim workbook As ExcelWorkbook = New ExcelWorkbook(workbookFormat)

        ' set the license key before saving the workbook
        'workbook.LicenseKey = "your license key here";

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


        ' Add a style used for the cells in the worksheet title area

        Dim titleStyle As ExcelCellStyle = 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.Item(ExcelCellBorderIndex.Bottom).Color = Color.Green
        titleStyle.Borders.Item(ExcelCellBorderIndex.Bottom).LineStyle = ExcelCellLineStyle.Medium
        titleStyle.Borders.Item(ExcelCellBorderIndex.Top).Color = Color.Green
        titleStyle.Borders.Item(ExcelCellBorderIndex.Top).LineStyle = ExcelCellLineStyle.Medium
        titleStyle.Borders.Item(ExcelCellBorderIndex.Left).Color = Color.Green
        titleStyle.Borders.Item(ExcelCellBorderIndex.Left).LineStyle = ExcelCellLineStyle.Medium
        titleStyle.Borders.Item(ExcelCellBorderIndex.Right).Color = Color.Green
        titleStyle.Borders.Item(ExcelCellBorderIndex.Right).LineStyle = ExcelCellLineStyle.Medium
        If workbookFormat = ExcelWorkbookFormat.Xls_2003 Then
            ' 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)
            ' 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
        End If
        ' set the title area font 
        titleStyle.Font.Size = 14
        titleStyle.Font.Bold = True
        titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single

        ' Add a style used for text messages

        Dim textMessageStyle As ExcelCellStyle = 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.Item(ExcelCellBorderIndex.Bottom).LineStyle = ExcelCellLineStyle.Thin
        textMessageStyle.Borders.Item(ExcelCellBorderIndex.Top).LineStyle = ExcelCellLineStyle.Thin
        textMessageStyle.Borders.Item(ExcelCellBorderIndex.Left).LineStyle = ExcelCellLineStyle.Thin
        textMessageStyle.Borders.Item(ExcelCellBorderIndex.Right).LineStyle = ExcelCellLineStyle.Thin

        ' Add a style used for the formula results

        Dim formulaResultStyle As ExcelCellStyle = 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.Item(ExcelCellBorderIndex.Bottom).LineStyle = ExcelCellLineStyle.Thin
        formulaResultStyle.Borders.Item(ExcelCellBorderIndex.Top).LineStyle = ExcelCellLineStyle.Thin
        formulaResultStyle.Borders.Item(ExcelCellBorderIndex.Left).LineStyle = ExcelCellLineStyle.Thin
        formulaResultStyle.Borders.Item(ExcelCellBorderIndex.Right).LineStyle = ExcelCellLineStyle.Thin

        ' get the first worksheet in the workbook
        Dim worksheet As ExcelWorksheet = workbook.Worksheets.Item(0)

        ' set the default worksheet name
        worksheet.Name = "Formulas Demo"


        ' 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
        Dim imagesPath As String = System.IO.Path.Combine(Server.MapPath("~"), "Images")
        Dim logoImg As System.Drawing.Image = 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"


        ' merge the cells in the range to create the title area 
        ' gets the merged range containing the top left cell of the range
        Dim titleRange As ExcelRange = worksheet.Item("A2").MergeArea
        ' set the text of title area
        worksheet.Item("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

        ' set first column width
        worksheet.Item("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 = ","c
        workbook.FormulaRowsSeparator = ";"c
        workbook.FormulaNumberFormat = System.Globalization.NumberFormatInfo.InvariantInfo

        worksheet.Item("A5:G5").Style = textMessageStyle
        worksheet.Item("A5:G5").Value = "Ordinary Formulas:"

        worksheet.Item("A7").Value = "FORMULA DATA:"
        worksheet.Item("A7").Style.Font.Bold = True

        worksheet.Item("C7").Value = 100.5
        worksheet.Item("D7").Value = 50.5
        worksheet.Item("E7").Value = 89
        worksheet.Item("F7").Value = 15.75
        worksheet.Item("G7").Value = 44.25

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

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

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

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

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

        worksheet.Item("A20:G20").Style = textMessageStyle
        worksheet.Item("A20:G20").Value = "Array Formulas:"

        worksheet.Item("A22").Value = "FORMULA DATA:"
        worksheet.Item("A22").Style.Font.Bold = True
        worksheet.Item("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.Item("C22:G22").FormulaArray = "{10, -5, 2, 21, -20}"

        'worksheet.Item("C22").Value = 10;
        'worksheet.Item("D22").Value = -5;
        'worksheet.Item("E22").Value = 2;
        'worksheet.Item("F22").Value = 21;
        'worksheet.Item("G22").Value = -20;

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

        ' defined 2 named ranges to sum in a third range
        Dim firstRange As ExcelNamedRange = worksheet.NamedRanges.AddNamedRange(worksheet.Item("C22:G22"), "FirstRange")
        Dim secondRange As ExcelNamedRange = worksheet.NamedRanges.AddNamedRange(worksheet.Item("C7:G7"), "SecondRange")

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

        ' evaluate workbook formulas


        ' Save the Excel document in the current HTTP response stream

        Dim outFileName As String
        If workbookFormat = ExcelWorkbookFormat.Xls_2003 Then
            outFileName = "FormulasDemo.xls"
            outFileName = "FormulasDemo.xlsx"
        End If

        Dim httpResponse As System.Web.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

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

        ' 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
        Catch ex As Exception
            ' report any error that might occur during save
            Session.Item("ErrorMessage") = ex.Message
            ' close the workbook and release the allocated resources

            ' Dispose the Image object

            If Not logoImg Is Nothing Then
            End If

        End Try

        ' End the response and finish the execution of this page

    End Sub
End Class

ExpertXls Excel Spreadsheet Library for .NET