ExpertXls Excel Spreadsheet Library for .NET

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();
    }
}