Winnovative Software Logo

 HTML to PDF Converter - Excel Library - ASP.NET Charts - RTF to PDF Converter
 PDF Merge and Split - PDF Security - PDF Viewers - PDF to Text - Images Extractor

  
Skip Navigation Links
 
Edit Excel Workbooks with Winnovative 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.
Getting Started
Cells and Ranges
Cell Styles
Excel Data Types
Excel Charts
Adding Images
Adding Formulas
Data Validation
Load/Get DataTable
Import from CSV
Edit Workbooks
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 exernal viewer. The free Excel Viewer from Microsoft supports both the Excel 97-2003 (.xls) format and the new Excel 2007 (.xlsx) format.
Workbook Format:
Create Workbook Button Create Excel Workbook 




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 = "RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw=";

    // set workbook description properties
    workbook.DocumentProperties.Subject = "Edit Excel workbook sample";
    workbook.DocumentProperties.Comments = 
                    "Edit Excel workbooks with Winnovative 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:E1"].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:E1"].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 (.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();
}
Copyright © 2007- Winnovative Software Solutions