ExpertXls Excel Spreadsheet Library for .NET

Imports ExpertXls.ExcelLib
Imports System.Drawing


Partial Class DataTypes
    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
        Else
            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 = "Data types and data formatting demo"
        workbook.DocumentProperties.Comments = "Add formatted data to an Excel worksheet using ExpertXls Excel library for .NET"

        ' CREATE CUSTOM WORKBOOK STYLES

        ' 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)
        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
        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 = 10
        textMessageStyle.Font.Bold = True
        textMessageStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
        textMessageStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left
        textMessageStyle.Fill.FillType = ExcelCellFillType.SolidFill
        textMessageStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 204)
        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 data validation ranges

        Dim dataValidationStyle As ExcelCellStyle = workbook.Styles.AddStyle("DataValidationStyle")
        dataValidationStyle.Font.Size = 10
        dataValidationStyle.Font.Bold = True
        dataValidationStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
        dataValidationStyle.Fill.FillType = ExcelCellFillType.SolidFill
        dataValidationStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(153, 204, 0)
        dataValidationStyle.Borders.Item(ExcelCellBorderIndex.Bottom).LineStyle = ExcelCellLineStyle.Thin
        dataValidationStyle.Borders.Item(ExcelCellBorderIndex.Top).LineStyle = ExcelCellLineStyle.Thin
        dataValidationStyle.Borders.Item(ExcelCellBorderIndex.Left).LineStyle = ExcelCellLineStyle.Thin
        dataValidationStyle.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 = "Data Types Demo"

        ' 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
        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"

        ' WRITE THE WORKSHEET TOP TITLE

        ' merge the cells in the range to create the title area 
        worksheet.Item("A2:G3").Merge()
        ' 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 = "Data Types and Data Formatting Demo"

        ' 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 CELLS WITH VARIOUS VALUES AND FORMATTING

        worksheet.Item("A1").ColumnWidthInChars = 16
        worksheet.Item("C1").ColumnWidthInChars = 16
        worksheet.Item("E1").ColumnWidthInChars = 16

        worksheet.Item("A5:E5").Merge()
        worksheet.Item("A5:E5").Style = textMessageStyle
        worksheet.Item("A5:E5").Value = "String Data Type"

        worksheet.Item("A7").Value = "This is a string value assigned with 'Value' property"
        worksheet.Item("A8").Text = "This is a string value assigned with 'Text' property"

        worksheet.Item("A10:E10").Merge()
        worksheet.Item("A10:E10").Style = textMessageStyle
        worksheet.Item("A10:E10").Value = "Number Data Type"

        Dim numberValue As Double = 1234567.809

        worksheet.Item("A12").Value = numberValue
        worksheet.Item("C12").Value = "Formatting String:"
        worksheet.Item("E12").Value = "Default (General)"

        Dim numberFormatString As String = "0"
        worksheet.Item("A13").Value = numberValue
        worksheet.Item("A13").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C13").Value = "Formatting String:"
        worksheet.Item("E13").Value = numberFormatString

        numberFormatString = "0.00"
        worksheet.Item("A14").Value = numberValue
        worksheet.Item("A14").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C14").Value = "Formatting String:"
        worksheet.Item("E14").Value = numberFormatString

        numberFormatString = "#,##0"
        worksheet.Item("A15").Value = numberValue
        worksheet.Item("A15").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C15").Value = "Formatting String:"
        worksheet.Item("E15").Value = numberFormatString

        numberFormatString = "#,##0.00"
        worksheet.Item("A16").Value = numberValue
        worksheet.Item("A16").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C16").Value = "Formatting String:"
        worksheet.Item("E16").Value = numberFormatString

        numberFormatString = "0%"
        worksheet.Item("A17").Value = numberValue
        worksheet.Item("A17").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C17").Value = "Formatting String:"
        worksheet.Item("E17").Value = numberFormatString

        numberFormatString = "0.00%"
        worksheet.Item("A18").Value = numberValue
        worksheet.Item("A18").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C18").Value = "Formatting String:"
        worksheet.Item("E18").Value = numberFormatString

        numberFormatString = "0.00E+00"
        worksheet.Item("A19").Value = numberValue
        worksheet.Item("A19").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C19").Value = "Formatting String:"
        worksheet.Item("E19").Value = numberFormatString

        numberFormatString = "# ?/?"
        worksheet.Item("A20").Value = numberValue
        worksheet.Item("A20").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C20").Value = "Formatting String:"
        worksheet.Item("E20").Value = numberFormatString

        numberFormatString = "# ??/??"
        worksheet.Item("A21").Value = numberValue
        worksheet.Item("A21").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C21").Value = "Formatting String:"
        worksheet.Item("E21").Value = numberFormatString

        numberFormatString = "_( #,##0_);\( #,##0\ )"
        worksheet.Item("A25").Value = numberValue
        worksheet.Item("A25").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C25").Value = "Formatting String:"
        worksheet.Item("E25").Value = numberFormatString

        numberFormatString = "_( #,##0_);[Red]\( #,##0\ )"
        worksheet.Item("A22").Value = numberValue
        worksheet.Item("A22").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C22").Value = "Formatting String:"
        worksheet.Item("E22").Value = numberFormatString

        numberFormatString = "_( #,##0.00_);\( #,##0.00\ )"
        worksheet.Item("A23").Value = numberValue
        worksheet.Item("A23").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C23").Value = "Formatting String:"
        worksheet.Item("E23").Value = numberFormatString

        numberFormatString = "_( #,##0.00_);[Red]\( #,##0.00\ )"
        worksheet.Item("A24").Value = numberValue
        worksheet.Item("A24").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C24").Value = "Formatting String:"
        worksheet.Item("E24").Value = numberFormatString

        numberFormatString = "_(* #,##0_);_(* \( #,##0\ );_(* ""-""_);_( @_ )"
        worksheet.Item("A25").Value = numberValue
        worksheet.Item("A25").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C25").Value = "Formatting String:"
        worksheet.Item("E25").Value = numberFormatString

        numberFormatString = "_(* #,##0.00_);_(* \( #,##0.00\ );_(* ""-""??_);_( @_ )"
        worksheet.Item("A26").Value = numberValue
        worksheet.Item("A26").Style.Number.NumberFormatString = numberFormatString
        worksheet.Item("C26").Value = "Formatting String:"
        worksheet.Item("E26").Value = numberFormatString

        worksheet.Item("A29:E29").Merge()
        worksheet.Item("A29:E29").Style = textMessageStyle
        worksheet.Item("A29:E29").Value = "Date and Time"

        Dim dateTimeValue As DateTime = DateTime.Now

        worksheet.Item("A31").Value = dateTimeValue
        worksheet.Item("C31").Value = "Formatting String:"
        worksheet.Item("E31").Value = "Default (mm/dd/yyyy)"

        Dim dateFormatString As String = "m/d/yyyy"
        worksheet.Item("A32").Value = dateTimeValue
        worksheet.Item("A32").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C32").Value = "Formatting String:"
        worksheet.Item("E32").Value = dateFormatString

        dateFormatString = "d\-mmm\-yy"
        worksheet.Item("A33").Value = dateTimeValue
        worksheet.Item("A33").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C33").Value = "Formatting String:"
        worksheet.Item("E33").Value = dateFormatString

        dateFormatString = "d\-mmm"
        worksheet.Item("A34").Value = dateTimeValue
        worksheet.Item("A34").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C34").Value = "Formatting String:"
        worksheet.Item("E34").Value = dateFormatString

        dateFormatString = "d\-mmm"
        worksheet.Item("A35").Value = dateTimeValue
        worksheet.Item("A35").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C35").Value = "Formatting String:"
        worksheet.Item("E35").Value = dateFormatString

        dateFormatString = "mmm\-yy"
        worksheet.Item("A36").Value = dateTimeValue
        worksheet.Item("A36").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C36").Value = "Formatting String:"
        worksheet.Item("E36").Value = dateFormatString

        dateFormatString = "h:mm AM/PM"
        worksheet.Item("A37").Value = dateTimeValue
        worksheet.Item("A37").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C37").Value = "Formatting String:"
        worksheet.Item("E37").Value = dateFormatString

        dateFormatString = "h:mm:ss AM/PM"
        worksheet.Item("A38").Value = dateTimeValue
        worksheet.Item("A38").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C38").Value = "Formatting String:"
        worksheet.Item("E38").Value = dateFormatString

        dateFormatString = "h:mm"
        worksheet.Item("A39").Value = dateTimeValue
        worksheet.Item("A39").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C39").Value = "Formatting String:"
        worksheet.Item("E39").Value = dateFormatString

        dateFormatString = "h:mm:ss"
        worksheet.Item("A40").Value = dateTimeValue
        worksheet.Item("A40").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C40").Value = "Formatting String:"
        worksheet.Item("E40").Value = dateFormatString

        dateFormatString = "m/d/yy h:mm"
        worksheet.Item("A41").Value = dateTimeValue
        worksheet.Item("A41").Style.Number.NumberFormatString = dateFormatString
        worksheet.Item("C41").Value = "Formatting String:"
        worksheet.Item("E41").Value = dateFormatString

        ' SAVE THE WORKBOOK

        ' Save the Excel document in the current HTTP response stream

        Dim outFileName As String
        If workbookFormat = ExcelWorkbookFormat.Xls_2003 Then
            outFileName = "DataTypes.xls"
        Else
            outFileName = "DataTypes.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
        httpResponse.Clear()

        ' 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"
        Else
            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
        Try
            workbook.Save(httpResponse.OutputStream)
        Catch ex As Exception
            ' report any error that might occur during save
            Session.Item("ErrorMessage") = ex.Message
            Response.Redirect("ErrorPage.aspx")
        Finally
            ' close the workbook and release the allocated resources
            workbook.Close()

            ' Dispose the Image object

            If Not logoImg Is Nothing Then
                logoImg.Dispose()
            End If

        End Try

        ' End the response and finish the execution of this page
        httpResponse.End()

    End Sub
End Class