Excel spreadsheet library for C#/VB.NET

ExpertXls Excel Spreadsheet Library for .NET

Create Excel Spreadsheets in ASP.NET / C#

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:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 System.Drawing;
using ExpertXls.ExcelLib;

public partial class EditWorkbook : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

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

        string testDocFile = null;
        if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
            testDocFile = System.IO.Path.Combine(Server.MapPath("~"), @"Data\GettingStarted.xls");
        else
            testDocFile = System.IO.Path.Combine(Server.MapPath("~"), @"Data\GettingStarted.xlsx");

        // open the test document for edit            
        ExcelWorkbook workbook = 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
        ExcelWorksheet firstWorksheet = workbook.Worksheets[0];
        firstWorksheet.Name = "Modified " + firstWorksheet.Name;

        ExcelWorksheet secondWorksheet = workbook.Worksheets[1];
        secondWorksheet.Name = "Modified " + secondWorksheet.Name;

        #region WRITE FIRST WORKSHEET MODIFICATION TIMESTAMP

        ExcelCellStyle timestampTextStyle = 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;

        ExcelCellStyle timestampDateStyle = 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["A1:D1"].Merge();
        ExcelRange timestampTextRange = firstWorksheet["A1"].MergeArea;
        timestampTextRange.RowHeightInPoints = 30;
        timestampTextRange.Style = timestampTextStyle;
        firstWorksheet["A1"].Text = "Workbook Modification Date & Time:";

        firstWorksheet["E1:G1"].Merge();
        ExcelRange timestampDateRange = firstWorksheet["E1"].MergeArea;
        timestampDateRange.RowHeightInPoints = 30;
        timestampDateRange.Style = timestampDateStyle;
        firstWorksheet["E1"].Value = DateTime.Now;

        #endregion

        #region WRITE SECOND WORKSHEET MODIFICATION TIMESTAMP

        secondWorksheet["A1:D1"].Merge();
        ExcelRange timestampTextRange2 = secondWorksheet["A1"].MergeArea;
        timestampTextRange2.RowHeightInPoints = 30;
        timestampTextRange2.Style = timestampTextStyle;
        secondWorksheet["A1"].Text = "Workbook Modification Date & Time:";

        secondWorksheet["E1:G1"].Merge();
        ExcelRange timestampDateRange2 = secondWorksheet["E1"].MergeArea;
        timestampDateRange2.RowHeightInPoints = 30;
        timestampDateRange2.Style = timestampDateStyle;
        secondWorksheet["E1"].Value = DateTime.Now;

        #endregion

        #region ADD A CHART TO THE SECOND WORKSHEET

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

        ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, secondWorksheet["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)
        {
            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;
        }

        // 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["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
        chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Size = 8;
        chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Italic = true;*/

        #endregion

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

        string outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? "ModifiedWorkbook.xls" : "ModifiedWorkbook.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-2013 (.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();
        }

        // End the response and finish the execution of this page
        httpResponse.End();
    }
}

ExpertXls Excel Spreadsheet Library for .NET