Excel spreadsheet library for C#/VB.NET

ExpertXls Excel Spreadsheet Library for .NET

Create Excel Spreadsheets in ASP.NET / VB.NET

Edit Excel Workbooks with ExpertXls Excel Library for .NET

This sample shows how to load an Excel workbook within the library and add new elements or edit existing elements. The first row of the first worksheet will contain a time stamp with the date time when the workbook was modified. Also a chart will be created in the second worksheet.
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 EditWorkbook
    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

        Dim testDocFile As String = Nothing
        If workbookFormat = ExcelWorkbookFormat.Xls_2003 Then
            testDocFile = System.IO.Path.Combine(Server.MapPath("~"), "Data\GettingStarted.xls")
        Else
            testDocFile = System.IO.Path.Combine(Server.MapPath("~"), "Data\GettingStarted.xlsx")
        End If

        ' open the test document for edit            
        Dim workbook As ExcelWorkbook = New ExcelWorkbook(testDocFile)

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

        ' set workbook description properties
        workbook.DocumentProperties.Subject = "Edit Excel workbook sample"
        workbook.DocumentProperties.Comments = "Edit Excel workbooks with ExpertXls Excel library for .NET"

        ' get the first 2 worksheets from workbook and modify their name
        Dim firstWorksheet As ExcelWorksheet = workbook.Worksheets.Item(0)
        firstWorksheet.Name = "Modified " + firstWorksheet.Name

        Dim secondWorksheet As ExcelWorksheet = workbook.Worksheets.Item(1)
        secondWorksheet.Name = "Modified " + secondWorksheet.Name

        ' WRITE FIRST WORKSHEET MODIFICATION TIMESTAMP

        Dim timestampTextStyle As ExcelCellStyle = workbook.Styles.AddStyle("TimestampTextStyle")
        timestampTextStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
        timestampTextStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left
        timestampTextStyle.Font.Size = 12
        timestampTextStyle.Font.Color = Color.Green
        timestampTextStyle.Font.Bold = True

        Dim timestampDateStyle As ExcelCellStyle = workbook.Styles.AddStyle("TimestampDateStyle")
        timestampDateStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
        timestampDateStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right
        timestampDateStyle.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss"
        timestampDateStyle.Font.Size = 12
        timestampDateStyle.Font.Color = Color.DarkBlue
        timestampDateStyle.Font.Bold = True

        firstWorksheet.Item("A1:D1").Merge()
        Dim timestampTextRange As ExcelRange = firstWorksheet.Item("A1").MergeArea
        timestampTextRange.RowHeightInPoints = 30
        timestampTextRange.Style = timestampTextStyle
        firstWorksheet.Item("A1").Text = "Workbook Modification Date & Time:"

        firstWorksheet.Item("E1:G1").Merge()
        Dim timestampDateRange As ExcelRange = firstWorksheet.Item("E1").MergeArea
        timestampDateRange.RowHeightInPoints = 30
        timestampDateRange.Style = timestampDateStyle
        firstWorksheet.Item("E1").Value = DateTime.Now

        ' WRITE SECOND WORKSHEET MODIFICATION TIMESTAMP

        secondWorksheet.Item("A1:D1").Merge()
        Dim timestampTextRange2 As ExcelRange = secondWorksheet.Item("A1").MergeArea
        timestampTextRange2.RowHeightInPoints = 30
        timestampTextRange2.Style = timestampTextStyle
        secondWorksheet.Item("A1").Text = "Workbook Modification Date & Time:"

        secondWorksheet.Item("E1:G1").Merge()
        Dim timestampDateRange2 As ExcelRange = secondWorksheet.Item("E1").MergeArea
        timestampDateRange2.RowHeightInPoints = 30
        timestampDateRange2.Style = timestampDateStyle
        secondWorksheet.Item("E1").Value = DateTime.Now

        ' ADD A CHART TO THE SECOND WORKSHEET

        secondWorksheet.Item("A14:G14").Merge()
        Dim addedChartMessageRange As ExcelRange = secondWorksheet.Item("A14").MergeArea
        addedChartMessageRange.RowHeightInPoints = 30
        addedChartMessageRange.Style = timestampTextStyle
        addedChartMessageRange.Value = "The chart below was added to an existing worksheet"

        Dim chart As ExcelChart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, secondWorksheet.Item("B7:G12"), True, 2, 15, 8, 32)
        chart.ShowDataTable = False

        ' set chart title
        chart.Title.Text = "Product Units Sold per Quarter"
        chart.Title.Interior.FillType = ExcelShapeFillType.NoFill
        chart.Title.Font.Size = 12
        chart.Title.Font.Color = Color.DarkBlue

        ' set chart area style
        If (workbookFormat = ExcelWorkbookFormat.Xls_2003) Then
            chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill
            chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153)

            chart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill
            chart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204)
        Else
            chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill
            chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset
            chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat

            chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill
            chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery
        End If

        ' set value axis title
        chart.ValueAxis.Title.Text = "Units sold"
        chart.ValueAxis.Title.Font.Size = 10
        chart.ValueAxis.Title.Font.Bold = True

        ' set value axis text style
        chart.ValueAxis.Font.Size = 8
        chart.ValueAxis.Font.Bold = False
        chart.ValueAxis.Font.Italic = True
        chart.ValueAxis.ShowVerticalTitleText()

        ' set category axis title
        chart.CategoryAxis.Title.Text = "Analyzed products"
        chart.CategoryAxis.Title.Font.Size = 10
        chart.CategoryAxis.Title.Font.Bold = True

        ' set category axis text style
        chart.CategoryAxis.Font.Size = 8
        chart.CategoryAxis.Font.Bold = False
        chart.CategoryAxis.Font.Italic = True

        ' set chart legend style
        chart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill
        chart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204)

        chart.Legend.Font.Size = 8
        chart.Legend.Font.Bold = True

        ' show a label with total number of units sold in a year
        'chart.Series.Item("Yearly Total").DataPoints.All.Label.ContainsValue = True
        'chart.Series.Item("Yearly Total").DataPoints.All.Label.LabelFormat.Font.Size = 8
        'chart.Series.Item("Yearly Total").DataPoints.All.Label.LabelFormat.Font.Italic = True

        ' SAVE THE WORKBOOK

        ' Save the Excel document in the current HTTP response stream

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

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

    End Sub
End Class

ExpertXls Excel Spreadsheet Library for .NET