ExpertXls Excel Library for .NET
User’s Guide
Contents
1. Introduction
2. Installation
3. Requirements
4. Excel Library API
| 4.1 The ExcelWorkbook class
| 4.2 Adding Worksheets to a Workbook
| 4.3 Accessing Cells and Ranges
| 4.4 Setting Values and Formatting
| 4.5 Defining Formulas
| 4.6 Setting Cell Styles
| 4.7 Interactive Features
| 4.8 Adding Images to a Worksheet
| 4.9 Adding Chart Shapes and Chart Worksheets
| 4.10 Importing Data from CSV
| 4.11 Importing Data from a DataTable Object
| 4.12 Data Validation
| 4.13 Worksheet Page Setup
| 4.14 Security Features
| 4.15 Code Sample
5. Licensing
1. Introduction
The ExpertXls Excel Library for .NET consists in a single .NET strong named assembly expertxls.dll that can be referenced directly in any .NET application (ASP.NET, Windows Forms, Console, Web Services, Windows Services, etc) to create Excel 97-2003 .xls and Excel 2007 or above .xlsx documents from the scratch or to edit existing Excel documents. Using the ExpertXls Excel Library for .NET you can easily create Excel documents with complex cell and shape styles, 2D and 3D charts, import data from CSV and DataTable objects, add formulas and array formulas, protect workbook and worksheets. The product does not require any installation and it does not use any third party tools. It's just an assembly that you can directly link with your .NET application. The library is also designed to work with ASP.NET applications running in environments with restricted permissions like an ASP.NET application running with Medium trust level. The programming interface is object oriented, has a clear design, is extremely easy to understand and use.
2. Installation
The ExpertXls Excel Library for .NET 2.0 is delivered as a zip archive and it doesn't have an installer. You have to simply unzip the archive in a folder on the disk. Below is a brief description of the folders from the archive. 2.1 Bin Folder The Bin folder contains the .NET 2.0 assembly that you can reference in your application and a XML file containing API documentation used by Microsoft Visual Studio 2005 IntelliSense to offer inline documentation for classes, methods and properties while writing code. The same documentation is available offline as a .chm document in the Doc folder. expertxls.dll - is the Excel library .NET assembly that you can link in any .NET application, either Windows Forms or ASP.NET. expertxls.xml - is the Excel Library API documentation used by Microsoft Visual Studio 2005 and 2008 IntelliSense. 2.1 Doc Folder Doc folder contains the Excel Library API reference in chm and html format and this User's Guide document. ExpertXLS-API.chm - contains the Excel Library API reference, with comments for each class, method or property HTML folder - contains the Excel Library API reference in html format ExpertXLS Developer Guide.html - is the document you are currently reading 2.3 Samples Folder Samples folder contains C# and VB.NET sample applications to offer you ready to use code for ASP.NET and Windows Forms applications. The sample projects can be loaded with Microsoft Visual Studio 2005 or Microsoft Visual Sudio 2008. Each sample has separate solution files .sln for Visual Studio 2005 and Visual Studio 2008. These solution files and can directly opened with the corresponding version of Microsoft Visual Studio. There is a sample application for each of the most important features of the Excel library. In the WindowsForms folder there are Windows Forms applications and in the AspNet folder there are web applications.
3. Requirements and Recommendations
The recommended hardware and software resources for successfully using the ExpertXls Excel Library for .NET are listed below. These requirements should be enough for any of the Excel Library features. We successfully tested the product in such an environment. If during the usage of product you find any additional requirements for any of the Excel Library features please let us know about it.
Operating System: All Windows Versions Hardware Architecture: 32-bit, 64-bit Free RAM: 256 MB Microsoft .NET Framework 2.0 or 3.5
4. Excel Library API
The ExpertXls Excel Library for .NET API is fully documented in the Doc/ExpertXLS-API.chm document. In order to use the library you have include the ExpertXls.ExcelLib namespace in your application.
The ExpertXls Excel Library for .NET can create and edit Excel 97-2003 (.xls) and Excel 2007 or above (.xlsx) documents. Addtionally there is an interface for loading data from a CSV (Comma Separated) file or to save the data from a worksheet to a file in this format.
4.1 The ExcelWorkbook Class
The ExcelWorkbook class represents an Excel workbook in the ExpertXls Excel Library framework. An ExcelWorkbook class can be created from an existing Excel document for modification or an empty workbook can be created. A workbook contains a collection of worksheets and a worksheet is a collection of cells and ranges of cells. The cells and ranges of cells in a worksheet can be accessed using a reference string in A1 notation or can be accessed using the row and column indexes. When creating an ExcelWorkbook object from an existing Excel document, the existing Excel document is passed to the ExcelWorkbook class constructor as the path of the Excel file on disk or as a stream containing the Excel document image. In either case the resulted ExcelWorkbook object can be further modified using the ExpertXls Excel Library API. There are also ExcelWorkbook constructors accepting a password when creating an ExcelWorkbook object from a password protected Excel document. Here are the ExcelWorkbook class constructors:
            /// Creates a workbook with a single worksheet. The default workbook
                format is Excel 2003.
            /// To create a workbook for a different Excel format, like Excel
                2007,you can use the constructor with the Format parameter.
            public ExcelWorkbook()

            /// Creates a workbook with a single worksheet. The format of the
                workbook is specified by the workbookFormat parameter.
            /// The currently supported formats are Excel 97-2003 format and Excel
                2007 or above format.
            public ExcelWorkbook(ExcelWorkbookFormat workbookFormat)

            /// Creates a workbook with a single worksheet. The format of the
                workbook is specified by the workbookFormat parameter.
            /// The currently supported formats are Excel 97-2003 format and Excel
                2007 or above format.
            public ExcelWorkbook(ExcelWorkbookFormat workbookFormat, ExcelWorkbookDefaultSettings defaultSettings)

            /// Opens a workbook from the specified Excel file
            public ExcelWorkbook(string excelFileName)

            /// Opens a workbook from the specified Excel file with the specified
                password
            public ExcelWorkbook(string excelFileName, string openPassword)

            /// Opens a workbook from the specified Excel file with the specified
                password
            public ExcelWorkbook(string excelFileName, string openPassword, ExcelWorkbookDefaultSettings defaultSettings)

            /// Opens a workbook from the specified Excel stream
            public ExcelWorkbook(Stream excelStream)

            /// Opens a workbook from the specified Excel stream with the specified
                password
            public ExcelWorkbook(Stream excelStream, string openPassword)

            /// Opens a workbook from the specified Excel stream with the specified
                password
            public ExcelWorkbook(Stream excelStream, string openPassword, ExcelWorkbookDefaultSettings defaultSettings)
The code below shows how to create a workbook in the Excel 97-2003 format (.xls format):
        ExcelWorkbook workbook = new ExcelWorkbook(ExcelWorkbookFormat.Xls_2003);
The code below shows how to create a workbook in the Excel 2007 or above format (.xlsx format):
        ExcelWorkbook workbook = new ExcelWorkbook(ExcelWorkbookFormat.Xlsx_2007);
The ExcelWorkbook class offers access to the Worksheets collection, global styles collection, named ranges collection, security features, document properties, etc. For example you can add a new worksheet to the workbook using the AddWorksheet() method of the ExcelWorkbook.Worksheets collection. The worksheets from collection can be accessed by index or by name. The workbook properties like author name, subject, comments or the last modification data can be specified using the DocumentProperties property of the ExcelWorkbook class. This property returns an object of type ExcelDocumentProperties whose properties can be set with desired values:
            // set workbook description properties
            workbook.DocumentProperties.Subject = "Getting started sample";
            workbook.DocumentProperties.Comments = "Getting started with ExpertXls
                Excel library for .NET";
4.2 Adding Worksheets to a Workbook
The ExcelWorkbook class offers access to its collection of worksheets using the Worksheets property. By default, when a workbook is created, its Worksheets collection contains a single worksheet. More worksheets can be added to the collection using the AddWorksheet() method. You can add a worksheet with the default name or you can specify the name of the new worksheet as parameter.Below is a sample code to add a second worksheet with the name 'Simple Chart' to the workbook:
        ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet(
            "Simple Chart");
A reference to a worksheet of the workbook can be obtained using one of the two index properties of the ExcelWorksheetsCollection. The first one returns the worksheet at the specified zero based index and the second one returns the worksheet with the specified name:
        ExcelWorksheet secondWorksheet = workbook.Worksheets[1];
        ExcelWorksheet secondWorksheet = workbook.Worksheets["Simple Chart"];
4.3 Accessing Cells and Ranges
A worksheet can be viewed as a collection of cells or ranges of cells. With ExpertXls Excel Library for .NET you can address a cell or a range of cells using the A1 string notation or using the row and column indexe numbers (the row and column indexes are one-based indexes). For example the, the cell from the top left corner of the worksheet (the cell from the first column of the first row of the worksheet), can be referenced by the "A1" string using the A1 notation or by the (1,1) row and column pair. Similarly, a range of cells can be referenced in A1 notation by specifying the top left cell and the bottom row cell separated by a ':' character and it can be referenced with column and row indexes by specifying the row and column indexes of the top left cell and the bottom row cell. Examples:
            // the top left cell of the worksheet in A1 notation
            ExcelRange firstCell = worksheet["A1"];
            // the top left cell of the worksheet referenced by row and columns
                indexes
            ExcelRange firstCell = worksheet[1,1];

            // a range of 4 cells from the top left corner of the worksheet in
                A1 notation
            ExcelRange firstRange = worksheet["A1:B2"];
            // a range of 4 cells from the top left corner of the worksheet
            // referenced by row and column indexes
            ExcelRange firstRange = worksheet[1, 1, 2, 2];
The ExpertXls Excel Library for .NET offers also the possibility to assign a name for a range of a worksheet. Further that range can be referenced by its assigned name in formulas or when accessing that range instead of providing its full A1 reference string or its row and column indexes. A named range is represented by a ExcelNamedRange object in the library and it can be defined at worksheet or at workbook level. A named range defined at worksheet level can be referenced only from the worksheet where it was defined while a named range defined at workbook level can accessed from any worksheet of the workbook. The code below creates two named ranges, the first at workbook level and the second at worksheet level:
            ExcelNamedRange workbookNamedRange = workbook.NamedRanges.AddNamedRange(worksheet["A1"], "FirstCell");
            ExcelNamedRange worksheetNamedRange = worksheet.NamedRanges.AddNamedRange(worksheet["A1"], "FirstCell");
4.4 Setting Values and Formatting
An Excel worksheet can contain data of various types like number values, date and time values, texts, boolean values, etc. These type of values can be assigned to a cell or a range of cells using the Value property of the ExcelRange class. The library will determine Excel data type based on the .NET type of the object assigned to the Value property. For example, if a value of .NET type System.Double will be assigned to the Value property of the ExcelRange, the Excel type of all the cells in the range will be set to Number and the value of the cells will be specified value. The behavior is similar for values of System.DateTime or System.String types. Additionally, the ExcelRange class defines a set of properties like Text, NumberValue or DateTimeValue which are the equivalent of the Value property when this property is assigned with values of type System.String, System.Double or System.DateTime. The formatting information that will be used when the values are displayed by an Excel viewer can be specified using the ExcelRange.Style.Number.NumberFormatString property of the ExcelRange. It is recommended to set the formatting string for a cell or for a range of cells after a value was assigned to that cell or range. This ensures the formatting is correctly applied for that value. Below there are a few code samples for setting values of various type and for applying various formatting strings to the cells in a worksheet:
            // set a text value
            worksheet["A7"].Value = "This is a string
                value assigned with 'Value' property";
            worksheet["A8"].Text = "This is a string
                value assigned with 'Text' property";

            // set a number value
            double numberValue = 1234567.809;
            worksheet["A16"].Value = numberValue;
            worksheet["A16"].Style.Number.NumberFormatString = "#,##0.00";

            // set a date value
            string dateFormatString = "m/d/yyyy";
            worksheet["A32"].Value = DateTime.Now;
            worksheet["A32"].Style.Number.NumberFormatString = dateFormatString;
4.5 Defining Formulas
The ExpertXls Excel Library for .NET offers the possibility of assigning formulas to cells and ranges of cells either in A1 or R1C1 notation. The library offers support both for ordinary formulas and for the array formulas. A formula string can be assigned to a cell or range using the ExcelRange.Formula property of the ExcelRange class. An array formula string can be assigned to a cell or range using the ExcelRange.FormulaArray property of the ExcelRange class. The Formula and FormulaArray properties assume the formulas are expressed in A1 notation. For assigning a formula in R1C1 notation the corresponding FormulaR1C1 and FormulaArrayR1C1 can be used. The sample code below shows how to assign various formulas to the cells of a worksheet:
            // the excel viewer will calculate the sum of the numbers in the range
                C7:G7
            // and will assign the result to C9 cell
            worksheet["C9"].Formula = "=SUM(C7:G7)";

            // the excel viewer will calculate the minimum value in the range
                C7:G7
            // and will assign the result to C9 cell
            worksheet["C9"].Formula = "=MIN(C7:G7)";
4.6 Setting Cell Styles
The style of a cell or a range of cells be accessed by the ExcelRange.Style property. This property can be assigned with a global style defined at the workbook level or the ExcelRange.Style property can be customized directly. When many cells or ranges of cells have the same style is more efficient and more elegant to define a global style at workbook level to be assigned to cells and ranges. The code sample below is an example for this:
            // Add a style used for all the cells containing the hello world text

            ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
            helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
            helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            helloWorldStyle.Font.Size = 14;
            helloWorldStyle.Font.Bold = true;

            helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;

            if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
            {
                helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
                helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
                helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
            }
            else
            {
                helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
                helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
                helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
            }

            worksheet["C5"].Text = "Hello World !!!";

            // set the 'HelloWorldStyle' for the cell
            worksheet["C5"].Style = helloWorldStyle;
The style can be also customized inline:
            worksheet["A21"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
            worksheet["A21"].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Top;
            worksheet["A21"].Style.Font.Bold = true;
            worksheet["A21"].Style.Font.Color = Color.Red;
            worksheet["A21"].Value = "Top Left";
4.7 Interactive Features
Using the ExpertXls Excel Library for .NET you can add hyperlinks and comments to a worksheet. A hyperlink can be a link to a web page, a mailto link, a link to a file or folder in the local system or network or a link to a range from a worksheet. A hyperlink is represented in library by the class ExcelHyperlink. An object of this type can be obtained from the Hyperlinks collection of the worksheet. A new object can be added to this collection using the AddHyperlink property of the ExcelHyperlinksCollection or an existing object can be retrieved from collection by its zero based index in collection. The sample code below shows how to add a hyperlink to a web page in a worksheet:
            // create a hyperlink to the product website
            ExcelRange websiteLinkSource = worksheet[25, 1];
            string websiteUrl = "http://www.expertcomponents.com";
            ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl);
            websiteHyperlink.Text = "Visit product website";
            websiteHyperlink.ToolTip = "Visit product website";
The code sample below shows how to add a link to a range from another worksheet:
            // create a named range used as target for the link to second worksheet
            ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet["A1"], "SecondWorksheet");
            ExcelRange worksheetLinkSource = worksheet[23, 1];
            ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, "SecondWorksheet");
            secondWorksheetLink.Text = "Go To Next Worksheet";
            secondWorksheetLink.ToolTip = "Go To Next Worksheet";
A comment can be easily assigned to a cell or a range of cells using the ExcelRange.AddComment() method as in the sample code below:
         worksheet["A9"].AddComment("Calculate the
             sum of the numbers in the range C7:G7");
4.8 Adding Images to a Worksheet
Using the ExpertXls Excel Library for .NET you can easily add images to a worksheet. The worksheet has a collection of pictures represented by the Pictures property of the ExcelWorksheet class. A new picture can be added to this collection using the AddPicture() method. When adding a picture you can specify the range in the worksheet where this picture will be placed and the image to be added either as an image file path or as a .NET System.Drawing.Image object. The sample code below ilustrates how easy is to add an image to a worksheet:
            // get a System.Drawing.Image object
            string demoImagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
            System.Drawing.Image demoImage = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "demo_250x300.jpg"));

            // add an image specifing the top left corner
            ExcelPicture demoExcelPicture = worksheet.Pictures.AddPicture(1, 7, demoImage);

            // get the coordinates of the image
            int leftColumnIndex = demoExcelPicture.LeftColumnIndex;
            int topRowIndex = demoExcelPicture.TopRowIndex;
            int rightColumnIndex = demoExcelPicture.RightColumnIndex;
            int bottomRowIndex = demoExcelPicture.BottomRowIndex;

            string excelPictureSumary = String.Format("Left
                Column: {0}, Top Row: {1}, Right Column: {2}, Bottom Row: {3}",
                leftColumnIndex, topRowIndex, rightColumnIndex, bottomRowIndex);
4.9 Adding Chart Shapes and Chart Worksheets
The ExpertXls Excel Library for .NET offers support both for adding chart shapes to a worksheet and for adding chart worksheets to a worbook. The collection of charts in a worksheet is represented by the ExcelWorksheet.Charts property and the collection of chart worksheets in a workbook is represented by the ExcelWorkbook.ChartWorksheets property The library is able to generate the chart series from the data source range or the chart series can be manually defined. The code sample below shows how to add a chart with automatically defined series:
            #region ADD A CHART WITH AUTOMATICALLY GENERATED SERIES TO THE WORKSHEET

            ExcelRange dataSourceRange = worksheet["B7:G12"];
            bool seriesDataByRows = rbSeriesByRows.Checked;

            ExcelChart chart = worksheet.Charts.AddChart(chartType, dataSourceRange, seriesDataByRows, 2, 16, 8, 33);

            // set chart title
            chart.Title.Text = "Product Units Sold per Quarter - Auto Generated
                Series";
            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;
The code sample below shows how to add a chart with manually defined series:
            #region ADD A CHART WITH CUSTOM SERIES TO THE WORKSHEET

            ExcelChart customSeriesChart = worksheet.Charts.AddChart(chartType, 2, 36, 8, 59);

            // set chart data table
            customSeriesChart.ShowDataTable = true;
            customSeriesChart.DataTable.ShowLegendKey = true;

            // set chart title
            customSeriesChart.Title.Text = "Product Units Sold per Quarter - Custom
                Series";
            customSeriesChart.Title.Font.Size = 12;
            customSeriesChart.Title.Font.Color = Color.DarkBlue;

            // create the category names range
            ExcelRange categoryNamesRange = worksheet["C7:E7"];

            // Add chart series

            // add first series for the first quarter sales
            ExcelChartSeries firstQuarterSeries = customSeriesChart.Series.AddSeries("First
                Quarter Sales");
            firstQuarterSeries.ChartType = chartType;
            firstQuarterSeries.CategoryNamesRange = worksheet["C7:E7"];
            firstQuarterSeries.ValuesRange = worksheet["C8:E8"];

            firstQuarterSeries.DataPoints.All.Label.ContainsValue = true;
            firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
            firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

            //add second series for second quarter sales
            ExcelChartSeries secondQuarterSeries = customSeriesChart.Series.AddSeries("Second
                Quarter Sales");
            secondQuarterSeries.ChartType = chartType;
            secondQuarterSeries.CategoryNamesRange = categoryNamesRange;
            secondQuarterSeries.ValuesRange = worksheet["C9:E9"];

            secondQuarterSeries.DataPoints.All.Label.ContainsValue = true;
            secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
            secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

            // add third series for fourth quarter sales
            ExcelChartSeries fourthQuarterSeries = customSeriesChart.Series.AddSeries("Fourth
                Quarter Sales");
            fourthQuarterSeries.ChartType = chartType;
            fourthQuarterSeries.CategoryNamesRange = categoryNamesRange;
            fourthQuarterSeries.ValuesRange = worksheet["C11:E11"];

            fourthQuarterSeries.DataPoints.All.Label.ContainsValue = true;
            fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
            fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;

            // set chart area style
            if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
            {
                customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
                customSeriesChart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

                customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
                customSeriesChart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
            }
            else
            {
                customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
                customSeriesChart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
                customSeriesChart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;

                customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
                customSeriesChart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
            }

            // set value axis title
            customSeriesChart.ValueAxis.Title.Text = "Units sold";
            customSeriesChart.ValueAxis.Title.Font.Size = 10;
            customSeriesChart.ValueAxis.Title.Font.Bold = true;

            // set value axis text style
            customSeriesChart.ValueAxis.Font.Size = 8;
            customSeriesChart.ValueAxis.Font.Bold = false;
            customSeriesChart.ValueAxis.Font.Italic = true;
            customSeriesChart.ValueAxis.ShowVerticalTitleText();

            // set category axis text style
            customSeriesChart.CategoryAxis.Font.Size = 8;
            customSeriesChart.CategoryAxis.Font.Bold = false;
            customSeriesChart.CategoryAxis.Font.Italic = true;

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

            customSeriesChart.Legend.Font.Size = 8;
            customSeriesChart.Legend.Font.Bold = true;

            #endregion
The current version of the library has limitation in reading Excel 2007 or above charts. Excel 97-2003 charts can be both read and written by the library but Excel 2007 charts can only be written.
4.10 Importing Data from CSV
The ExpertXls Excel Library for .NET offers support for loading CSV directly into an Excel worksheet and for saving an Excel worksheet to a CSV file. A CSV file can be loaded into a workbook using the static method ExcelWorkbook.LoadFromCSV() and a worksheet from a workbook can be exported to CSV using the ExcelWorkbook.SaveToCSV() static method. Below is a sample code for creating a workbook from a CSV file:
            // The data from CSV file (numbers, dates, etc) was saved for the
                English US culture and
            // the CSV parser uses the current thread culture
            // Temporary set the en-US culture for the current thread and restore
                the old culture after the CSV file was loaded
            System.Globalization.CultureInfo oldCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            // get the Excel workbook format
            ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;

            // create the CSV stream
            string dataFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Data\awemployees.csv");
            System.IO.FileStream csvDataStream = new System.IO.FileStream(dataFilePath, System.IO.FileMode.Open);

            // load the data from the CSV stream to a new workbook in the specified
                format
            ExcelWorkbook workbook = ExcelWorkbook.LoadFromCsv(csvDataStream, Encoding.GetEncoding("windows-1252"), ",", 5, 1, workbookFormat, null);

            // close the CSV stream
            csvDataStream.Close();
            // restore the current culture
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture;
4.11 Importing Data from a DataTable Object
The ExpertXls Excel Library for .NET offers support for loading values from a System.DataTable object directly into an Excel worksheet and for saving values from an Excel worksheet to a System.DataTable object. A DataTable object can be loaded into a worksheet using the method ExcelWorksheet.LoadDataTable() and a worksheet from a workbook can be exported to DataTable object using the ExcelWorksheet.GetDataTable() method. Below is a sample code for loading a DataTable into a worksheet and for getting a DataTable object from a worksheet :
            // load an existing Excel file to a temporary workbook

            // create the Excel stream
            string dataFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Data\awemployees.xls");
            System.IO.FileStream sourceXlsDataStream = new System.IO.FileStream(dataFilePath, System.IO.FileMode.Open);

            ExcelWorkbook tempWorkbook = new ExcelWorkbook(sourceXlsDataStream);
            ExcelWorksheet tempWorksheet = tempWorkbook.Worksheets[0];

            // get the data from the used range of the temporary workbook to a
                .NET DataTable object
            DataTable exportedDataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, true);

            //close the temporary workbook
            tempWorkbook.Close();
            //close the data stream
            sourceXlsDataStream.Close();

            // Create the workbook in which the data from the DataTable will be
                loaded
            ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;

            // create the workbook in the desired format with a single worksheet
            ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);

            // get the first worksheet in the workbook
            ExcelWorksheet worksheet = workbook.Worksheets[0];

            // set the default worksheet name
            worksheet.Name = "Data Loaded from a DataTable";

            // load data from DataTable into the worksheet
            worksheet.LoadDataTable(exportedDataTable, 5, 1, true);
4.12 Data Validation
The ExpertXls Excel Library for .NET offers support for validating data entered in the worksheet cells. Using this feature you can ensure that the value entered in a cell is from a predefined list of values, a number or date is in a specified range or that the length of a string is in a specified range. The data validation is implemented using the ExcelRange.DataValidator property. The code sample below shows how to add data validation to a worksheet:
            #region Add Data Validation

            #region Validate data from a list

            worksheet["A5:E5"].Merge();
            worksheet["A5:E5"].Style = textMessageStyle;
            worksheet["A5:E5"].Value = "Select a value
                from the list:";

            // set the range to be validated
            worksheet["G5"].Style = dataValidationStyle;
            worksheet["G5"].ColumnWidthInChars = 25;
            worksheet["G5"].AddComment("Click this cell
                to select a value from list.");

            ExcelDataValidator listValidator = worksheet["G5"].DataValidator;
            listValidator.AllowedDataType = ExcelDataValidatorDataType.List;
            listValidator.AllowedValues = new string[] { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET" };
            listValidator.InputMessageText = "Select a value from the list";
            listValidator.ShowInputMessage = true;

            #endregion

            #region Validate a whole number between 0 and 10

            worksheet["A7:E7"].Merge();
            worksheet["A7:E7"].Style = textMessageStyle;
            worksheet["A7:E7"].Value = "Enter a whole
                number between 0 and 10 :";

            // set the range to be validated
            worksheet["G7"].Style = dataValidationStyle;
            worksheet["G7"].ColumnWidthInChars = 25;
            worksheet["G7"].AddComment("Click this cell
                to enter a whole number.");

            // Data Validation for Numbers
            ExcelDataValidator wholeNumberValidator = worksheet["G7"].DataValidator;
            wholeNumberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber;
            wholeNumberValidator.Operator = ExcelDataValidatorOperator.Between;
            wholeNumberValidator.Value1 = 0;
            wholeNumberValidator.Value2 = 10;
            wholeNumberValidator.ErrorAlertText = "A number between 0 to 10 is
                expected";
            wholeNumberValidator.ShowErrorAlert = true;
            wholeNumberValidator.ErrorAlertTitle = "Whole Number Validation Error";
            wholeNumberValidator.InputMessageText = "Enter a whole number between
                0 and 10";
            wholeNumberValidator.ShowInputMessage = true;

            #endregion

            #region Validate a date between 01/01/2000 and 12/31/2009

            worksheet["A9:E9"].Merge();
            worksheet["A9:E9"].Style = textMessageStyle;
            worksheet["A9:E9"].Value = "Enter a date
                between 01/01/2000 and 12/31/2009 :";

            // set the range to be validated
            worksheet["G9"].Style = dataValidationStyle;
            worksheet["G9"].ColumnWidthInChars = 25;
            worksheet["G9"].Style.Number.NumberFormatString = "m/d/yyyy";
            worksheet["G9"].Value = new DateTime(2008, 12, 15); // default value
            worksheet["G9"].AddComment("Double-Click
                this cell to enter a date in local format.");

            ExcelDataValidator dateValidator = worksheet["G9"].DataValidator;
            dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date;
            dateValidator.Operator = ExcelDataValidatorOperator.Between;
            dateValidator.Value1 = new DateTime(2000, 1, 1);
            dateValidator.Value2 = new DateTime(2009, 12, 31); ;
            dateValidator.ErrorAlertText = "A date between 01/01/2000 and 12/31/2009
                is expected";
            dateValidator.ShowErrorAlert = true;
            dateValidator.ErrorAlertTitle = "Date Validation Error";
            dateValidator.InputMessageText = "Enter a date between 01/01/2000
                and 12/31/2009";
            dateValidator.ShowInputMessage = true;

            #endregion

            #region Validate the length of a text

            worksheet["A11:E11"].Merge();
            worksheet["A11:E11"].Style = textMessageStyle;
            worksheet["A11:E11"].Value = "Enter a text
                with length between 2 and 5 chars:";

            // set the range to be validated
            worksheet["G11"].Style = dataValidationStyle;
            worksheet["G11"].ColumnWidthInChars = 25;
            worksheet["G11"].AddComment("Click this cell
                to enter a text.");

            ExcelDataValidator textLengthValidator = worksheet["G11"].DataValidator;
            textLengthValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength;
            textLengthValidator.Operator = ExcelDataValidatorOperator.Between;
            textLengthValidator.Value1 = 2;
            textLengthValidator.Value2 = 5;
            textLengthValidator.ErrorAlertTitle = "Text Length Validation Error";
            textLengthValidator.ErrorAlertText = "A text with length between 2
                and 5 chars is expected";
            textLengthValidator.ShowErrorAlert = true;
            textLengthValidator.InputMessageText = "Enter a text with length between
                2 and 5 characters";
            textLengthValidator.ShowInputMessage = true;

            #endregion

            #endregion
4.13 Worksheet Page Setup
The ExpertXls Excel Library for .NET offers advanced support for setting the worksheet page. You can set the page size, orientation and margins, add headers and footers with text and images. The worksheet page properties can be accessed by the ExcelWorksheet.PageSetup property. The code sample below shows how the worksheet page properties can be set:
            #region 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
            string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
            System.Drawing.Image logoImg = 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";

            #endregion
4.14 Security Features
Using ExpertXls Excel Library for .NET you can password protect a workbook (this feature is supported only for Excel 97-2003 workbooks), protect the workbook structure and windows, protect the worksheet data. The ExcelWorkbook.DocumentSecurity property allows you to set an open password and a write password for the workbook. The DocumentSecurity.OpenWorkbookPassword property can be used to set the open password for the workbook required by an Excel viewer before opening the workbook and the DocumentSecurity.WriteWorkbookPassword property can be used to specify a password required by an Excel editor in order to modify the workbook. The code sample below show how these properties can be set:
                    //set a password required when the workbook is opened by an Excel
                        viewer.
                    workbook.DocumentSecurity.OpenWorkbookPassword = "open";

                    //set a password required by an Excel editor in order to modify the
                        workbook.
                    workbook.DocumentSecurity.WriteWorkbookPassword = "write";
The ExcelWorkbook.DocumentSecurity.ProtectWorkbook() method allows you to protect the workbook structure and windows. Optionally a password can be specified. This password is required in order to remove the protection. The statement below shows how to protect the workbook structure and windows:
         workbook.DocumentSecurity.ProtectWorkbook(true, true);
Another security feature allows you to protect the worksheet data. This feature can be accessed using the ExcelWorksheet.WorksheetSecurity property. The code below will use the default protection flags to protect the worksheet data
         worksheet.WorksheetSecurity.ProtectWorksheet(ExcelProtectionAllowFlags.AllowDefault );
4.15 Code Sample
Below there is a full code sample illustrating the main features of the ExpertXls Excel Library for .NET. The code was taken from the Getting Started sample available in the Samples\AspNet\CS\ExcelLibraryFeaturesDemo\Default.aspx.cs file.
        protected void lnkBtnCreateWorkbook_Click(object sender, EventArgs e)
        {
            // get the Excel workbook format
            ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;

            // create the workbook in the desired format with a single worksheet
            ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);

            // set the license key before saving the workbook
            workbook.LicenseKey = "RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw=";

            // set workbook description properties
            workbook.DocumentProperties.Subject = "Getting started sample";
            workbook.DocumentProperties.Comments = "Getting started with ExpertXls
                Excel library for .NET";

            #region CREATE CUSTOM WORKBOOK STYLES

            #region Add a style used for the cells in the worksheet title area

            ExcelCellStyle titleStyle = 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[ExcelCellBorderIndex.Bottom].Color = Color.Green;
            titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
            titleStyle.Borders[ExcelCellBorderIndex.Top].Color = Color.Green;
            titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
            titleStyle.Borders[ExcelCellBorderIndex.Left].Color = Color.Green;
            titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
            titleStyle.Borders[ExcelCellBorderIndex.Right].Color = Color.Green;
            titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
            if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
            {
                // 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;
            }
            // set the title area font
            titleStyle.Font.Size = 14;
            titleStyle.Font.Bold = true;
            titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single;

            #endregion

            #region Add a style used for all the cells in the index column

            ExcelCellStyle indexStyle = workbook.Styles.AddStyle("IndexColumnStyle");
            indexStyle.Font.Size = 12;
            indexStyle.Font.Bold = false;
            indexStyle.Fill.FillType = ExcelCellFillType.SolidFill;
            indexStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
            indexStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Thin;
            indexStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thin;
            indexStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Thin;
            indexStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Thin;

            #endregion

            #region Add a style used for all the cells in the country name column

            ExcelCellStyle countryNameStyle = workbook.Styles.AddStyle("CountryNameStyle");
            countryNameStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
            countryNameStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            countryNameStyle.Font.Size = 12;
            countryNameStyle.Font.Bold = true;
            countryNameStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Dot;
            countryNameStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Dot;
            countryNameStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Dot;
            countryNameStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Dot;

            countryNameStyle.Fill.FillType = ExcelCellFillType.PatternFill;
            countryNameStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_12_5_Percent;
            countryNameStyle.Fill.PatternFillOptions.BackColor = Color.White;
            countryNameStyle.Fill.PatternFillOptions.PatternColor = Color.Green;

            #endregion

            #region Add a style used for all the cells containing the hello world text

            ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
            helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
            helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            helloWorldStyle.Font.Size = 14;
            helloWorldStyle.Font.Bold = true;

            helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
            helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;

            if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
            {
                helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
                helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
                helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
            }
            else
            {
                helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
                helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
                helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
            }

            #endregion

            #endregion

            // get the first worksheet in the workbook
            ExcelWorksheet worksheet = workbook.Worksheets[0];

            // set the default worksheet name
            worksheet.Name = "Hello World";

            #region 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
            string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
            System.Drawing.Image logoImg = 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";

            #endregion

            #region WRITE THE WORKSHEET TOP TITLE

            // merge the cells in the range to create the title area
            worksheet["A2:G3"].Merge();
            // gets the merged range containing the top left cell of the range
            ExcelRange titleRange = worksheet["A2"].MergeArea;
            // set the text of title area
            worksheet["A2"].Text = "Say 'Hello World'
                in Different Languages";

            // 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;

            #endregion

            #region WRITE 'HELLO WORLD' IN DIFFERENT LANGUAGES

            System.Drawing.Image usFlagImg = null;
            System.Drawing.Image frFlagImg = null;
            System.Drawing.Image deFlagImg = null;
            System.Drawing.Image esFlagImg = null;
            System.Drawing.Image ruFlagImg = null;
            System.Drawing.Image itFlagImg = null;
            System.Drawing.Image ptFlagImg = null;
            System.Drawing.Image nlFlagImg = null;

            // set the separator between index column and text column
            worksheet.SetColumnWidthInChars(2, 3);
            // set the country image column
            worksheet.SetColumnWidthInChars(6, 5);

            #region Say hello world in English

            worksheet["A5"].Style = indexStyle;
            worksheet["A5"].Value = 1;

            worksheet["C5"].Text = "Hello World !!!";
            worksheet["C5"].Style = helloWorldStyle;
            worksheet["C5"].RowHeightInPoints = 19.5;

            usFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "us.png"));
            worksheet.Pictures.AddPicture(6, 5, usFlagImg);
            worksheet[5, 6, 5, 7].StyleName = "CountryNameStyle";
            worksheet[5, 7].Text = "English";

            #endregion

            #region  Say hello world in French

            worksheet["A7"].Style = indexStyle;
            worksheet["A7"].Value = 2;

            worksheet["C7"].Text = "Bonjour tout le monde
                !!!";
            worksheet["C7"].Style = helloWorldStyle;
            worksheet["C7"].Style.Font.Color = Color.Blue;
            worksheet["C7"].RowHeightInPoints = 19.5;

            frFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "fr.png"));
            worksheet.Pictures.AddPicture(6, 7, frFlagImg);
            worksheet[7, 6, 7, 7].StyleName = "CountryNameStyle";
            worksheet[7, 7].Text = "French";

            #endregion

            #region Say hello world in German

            worksheet["A9"].Style = indexStyle;
            worksheet["A9"].Value = 3;

            worksheet["C9"].Text = "Hallo Welt";
            worksheet["C9"].Style = helloWorldStyle;
            worksheet["C9"].Style.Font.Color = Color.Red;
            worksheet["C9"].RowHeightInPoints = 19.5;

            deFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "de.png"));
            worksheet.Pictures.AddPicture(6, 9, deFlagImg);
            worksheet[9, 6, 9, 7].StyleName = "CountryNameStyle";
            worksheet[9, 7].Text = "German";

            #endregion

            #region Say hello world in Spanish

            worksheet["A11"].Style = indexStyle;
            worksheet["A11"].Value = 4;

            worksheet["C11"].Text = "Hola Mundo";
            worksheet["C11"].Style = helloWorldStyle;
            worksheet["C11"].Style.Font.Color = Color.Orange;
            worksheet["C11"].RowHeightInPoints = 19.5;

            esFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "es.png"));
            worksheet.Pictures.AddPicture(6, 11, esFlagImg);
            worksheet[11, 6, 11, 7].StyleName = "CountryNameStyle";
            worksheet[11, 7].Text = "Spanish";

            #endregion

            #region Say hello world in Russian

            worksheet["A13"].Style = indexStyle;
            worksheet["A13"].Value = 5;

            worksheet["C13"].Text = "Привет мир";
            worksheet["C13"].Style = helloWorldStyle;
            worksheet["C13"].Style.Font.Color = Color.Indigo;
            worksheet["C13"].RowHeightInPoints = 19.5;

            ruFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "ru.png"));
            worksheet.Pictures.AddPicture(6, 13, ruFlagImg);
            worksheet[13, 6, 13, 7].StyleName = "CountryNameStyle";
            worksheet[13, 7].Text = "Russian";

            #endregion

            #region Say hello world in Italian

            worksheet["A15"].Style = indexStyle;
            worksheet["A15"].Value = 6;

            worksheet["C15"].Text = "Ciao a tutti";
            worksheet["C15"].Style = helloWorldStyle;
            worksheet["C15"].Style.Font.Color = Color.Green;
            worksheet["C15"].RowHeightInPoints = 19.5;

            itFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "it.png"));
            worksheet.Pictures.AddPicture(6, 15, itFlagImg);
            worksheet[15, 6, 15, 7].StyleName = "CountryNameStyle";
            worksheet[15, 7].Text = "Italian";

            #endregion

            #region Say hello world in Dutch

            worksheet["A17"].Style = indexStyle;
            worksheet["A17"].Value = 7;

            worksheet["C17"].Text = "Hallo Wereld";
            worksheet["C17"].Style = helloWorldStyle;
            worksheet["C17"].Style.Font.Color = Color.Blue;
            worksheet["C17"].RowHeightInPoints = 19.5;

            nlFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "nl.png"));
            worksheet.Pictures.AddPicture(6, 17, nlFlagImg);
            worksheet[17, 6, 17, 7].StyleName = "CountryNameStyle";
            worksheet[17, 7].Text = "Dutch";

            #endregion

            #region Say hello world in Portuguese

            worksheet["A19"].Style = indexStyle;
            worksheet["A19"].Value = 8;

            worksheet["C19"].Text = "Olá Mundo";
            worksheet["C19"].Style = helloWorldStyle;
            worksheet["C19"].Style.Font.Color = Color.Red;
            worksheet["C19"].RowHeightInPoints = 19.5;

            ptFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "pt.png"));
            worksheet.Pictures.AddPicture(6, 19, ptFlagImg);
            worksheet[19, 6, 19, 7].StyleName = "CountryNameStyle";
            worksheet[19, 7].Text = "Portuguese";

            #endregion

            // autofit the index column width
            worksheet.AutofitColumn(1);
            // autofit the Hello World text column
            worksheet.AutofitColumn(3);
            // autofit the country name column
            worksheet.AutofitColumn(7);

            #endregion

            #region WRITE WORKSHEET CREATION TIMESTAMP

            worksheet[28, 1, 28, 3].Merge();
            ExcelRange timestampTextRange = worksheet[28, 1].MergeArea;
            timestampTextRange.Style.Font.Bold = true;
            worksheet[28, 1].Text = "Workbook Creation Date & Time:";

            worksheet[28, 4, 28, 7].Merge();
            ExcelRange timestampDateRange = worksheet[28, 4].MergeArea;
            timestampDateRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
            timestampDateRange.Style.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss";
            timestampDateRange.Style.Font.Color = Color.DarkBlue;
            timestampDateRange.Style.Font.Bold = true;
            worksheet[28, 4].Value = DateTime.Now;

            #endregion

            #region ADD A SECOND WORKSHEET TO THE WORKBOOK

            ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet("Simple
                Chart");

            #region SECOND WORKSHEET PAGE SETUP

            // set worksheet paper size and orientation, margins, header and footer
            secondWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
            secondWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
            secondWorksheet.PageSetup.LeftMargin = 1;
            secondWorksheet.PageSetup.RightMargin = 1;
            secondWorksheet.PageSetup.TopMargin = 1;
            secondWorksheet.PageSetup.BottomMargin = 1;

            // add header and footer

            //display a logo image in the left part of the header
            secondWorksheet.PageSetup.LeftHeaderFormat = "&G";
            secondWorksheet.PageSetup.LeftHeaderPicture = logoImg;
            // display worksheet name in the right part of the header
            secondWorksheet.PageSetup.RightHeaderFormat = "&A";

            // add worksheet header and footer
            // display the page number in the center part of the footer
            secondWorksheet.PageSetup.CenterFooterFormat = "&P";
            // display the workbook file name in the left part of the footer
            secondWorksheet.PageSetup.LeftFooterFormat = "&F";
            // display the current date in the right part of the footer
            secondWorksheet.PageSetup.RightFooterFormat = "&D";

            #endregion

            #region WRITE THE SECOND WORKSHEET TOP TITLE

            // merge the cells in the range to create the title area
            secondWorksheet["A2:G3"].Merge();
            // gets the merged range containing the top left cell of the range
            ExcelRange secondTitleRange = secondWorksheet["A2"].MergeArea;
            // set the text of title area
            secondWorksheet["A2"].Text = "Simple Chart
                Demo";

            // set a row height of 18 points for each row in the range
            secondTitleRange.RowHeightInPoints = 18;
            // set the worksheet top title style
            secondTitleRange.Style = titleStyle;

            #endregion

            #region CREATE DATA TABLE FOR THE CHART IN SECOND WORKSHEET

            ExcelCellStyle chartValuesStyle = workbook.Styles.AddStyle("ChartValuesStyle");
            chartValuesStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            chartValuesStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            chartValuesStyle.Font.Color = Color.Black;
            chartValuesStyle.Font.Bold = true;
            if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
            {
                chartValuesStyle.Fill.FillType = ExcelCellFillType.PatternFill;
                chartValuesStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
                chartValuesStyle.Fill.PatternFillOptions.BackColor = Color.White;
                chartValuesStyle.Fill.PatternFillOptions.PatternColor = Color.Green;
            }
            else
            {
                chartValuesStyle.Fill.FillType = ExcelCellFillType.GradientFill;
                chartValuesStyle.Fill.GradientFillOptions.Color1 = Color.LightGreen;
                chartValuesStyle.Fill.GradientFillOptions.Color2 = Color.White;
                chartValuesStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown;
            }

            // set the products tile row text and style
            secondWorksheet["C6:G6"].Merge();
            secondWorksheet["C6"].Text = "Analyzed Products";
            ExcelRange productsTitle = secondWorksheet["C6"].MergeArea;
            productsTitle.RowHeightInPoints = 21;
            productsTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            productsTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            productsTitle.Style.Font.Size = 12;
            productsTitle.Style.Font.Bold = true;
            productsTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
            productsTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
            productsTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
            productsTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Orange;

            // set the quarters title column text and style
            secondWorksheet["A8:A11"].Merge();
            secondWorksheet["A8"].Text = "Units Sold
                per Quarter";
            ExcelRange quartersTitle = secondWorksheet["A8"].MergeArea;
            // set vertical orientation for the text from bottom to top
            quartersTitle.Style.Alignment.Orientation = 90;
            // wrap text inside the merged range
            quartersTitle.Style.Alignment.WrapText = true;
            quartersTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            quartersTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            quartersTitle.Style.Font.Size = 12;
            quartersTitle.Style.Font.Bold = true;
            quartersTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
            quartersTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
            quartersTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
            quartersTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Green;

            // set the style for the product names row
            ExcelRange productNamesRange = secondWorksheet["B7:G7"];
            productNamesRange.RowHeightInPoints = 21;
            productNamesRange.ColumnWidthInChars = 16;
            productNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            productNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            productNamesRange.Style.Font.Bold = true;
            productNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
            productNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

            // set the styles for the quarter names range
            ExcelRange quarterNamesRange = secondWorksheet["B8:B11"];
            quarterNamesRange.RowHeightInPoints = 16.5;
            quarterNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
            quarterNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            quarterNamesRange.Style.Font.Bold = true;
            quarterNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
            quarterNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);

            // set the row height for the Yearly Total row
            ExcelRange yearlyTotalRange = secondWorksheet["B12:G12"];
            yearlyTotalRange.RowHeightInPoints = 21;
            yearlyTotalRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
            yearlyTotalRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
            yearlyTotalRange.Style.Font.Color = Color.Blue;
            yearlyTotalRange.Style.Font.Bold = true;
            yearlyTotalRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
            yearlyTotalRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 255);

            // set the series name column
            secondWorksheet["B8"].Text = "First Quarter";
            secondWorksheet["B9"].Text = "Second Quarter";
            secondWorksheet["B10"].Text = "Third Quarter";
            secondWorksheet["B11"].Text = "Fourth Quarter";
            secondWorksheet["B12"].Text = "Yearly Total";

            // set the categories name row
            secondWorksheet["C7"].Text = "HTML to PDF";
            secondWorksheet["D7"].Text = "PDF Merge";
            secondWorksheet["E7"].Text = "PDF Security";
            secondWorksheet["F7"].Text = "Web Chart";
            secondWorksheet["G7"].Text = "Excel Library";

            // set the chart value style

            ExcelRange chartValuesRange = secondWorksheet["C8:G11"];
            chartValuesRange.Style = chartValuesStyle;

            // set the chart values

            secondWorksheet["C8"].Value = 1000;
            secondWorksheet["D8"].Value = 500;
            secondWorksheet["E8"].Value = 200;
            secondWorksheet["F8"].Value = 400;
            secondWorksheet["G8"].Value = 800;

            secondWorksheet["C9"].Value = 850;
            secondWorksheet["D9"].Value = 680;
            secondWorksheet["E9"].Value = 350;
            secondWorksheet["F9"].Value = 230;
            secondWorksheet["G9"].Value = 640;

            secondWorksheet["C10"].Value = 950;
            secondWorksheet["D10"].Value = 450;
            secondWorksheet["E10"].Value = 175;
            secondWorksheet["F10"].Value = 350;
            secondWorksheet["G10"].Value = 520;

            secondWorksheet["C11"].Value = 500;
            secondWorksheet["D11"].Value = 700;
            secondWorksheet["E11"].Value = 250;
            secondWorksheet["F11"].Value = 460;
            secondWorksheet["G11"].Value = 320;

            secondWorksheet["C12"].Formula = "=SUM(C8:C11)";
            secondWorksheet["D12"].Formula = "=SUM(D8:D11)";
            secondWorksheet["E12"].Formula = "=SUM(E8:E11)";
            secondWorksheet["F12"].Formula = "=SUM(F8:F11)";
            secondWorksheet["G12"].Formula = "=SUM(G8:G11)";

            // auto fit the width of the quarter names column
            secondWorksheet["B7"].AutofitColumns();

            #endregion

            #region ADD A CHART TO THE SECOND WORKSHEET

            ExcelRange dataSourceRange = secondWorksheet["B7:G12"];
            ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, dataSourceRange, 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

            #endregion

            #region CREATE HYPERLINKS

            // create the merged range where to add the link to the second worksheet
            worksheet[23, 1, 23, 3].Merge();
            // create a named range used as target for the link to second worksheet
            ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet["A1"], "SecondWorksheet");
            ExcelRange worksheetLinkSource = worksheet[23, 1];
            ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, "SecondWorksheet");
            secondWorksheetLink.Text = "Go To Next Worksheet";
            secondWorksheetLink.ToolTip = "Go To Next Worksheet";

            // creat the merged range where to add the link to the product website
            worksheet[25, 1, 25, 3].Merge();
            // create a hyperlink to the product website
            ExcelRange websiteLinkSource = worksheet[25, 1];
            string websiteUrl = "http://www.expertcomponents.com";
            ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl);
            websiteHyperlink.Text = "Visit product website";
            websiteHyperlink.ToolTip = "Visit product website";

            #endregion

            // SAVE THE WORKBOOK

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

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

                #region Dispose the Image object
                // release the flag images
                if (usFlagImg != null)
                    usFlagImg.Dispose();
                if (frFlagImg != null)
                    frFlagImg.Dispose();
                if (deFlagImg != null)
                    deFlagImg.Dispose();
                if (esFlagImg != null)
                    esFlagImg.Dispose();
                if (ruFlagImg != null)
                    ruFlagImg.Dispose();
                if (itFlagImg != null)
                    itFlagImg.Dispose();
                if (ptFlagImg != null)
                    ptFlagImg.Dispose();
                if (nlFlagImg != null)
                    nlFlagImg.Dispose();
                if (logoImg != null)
                    logoImg.Dispose();

                #endregion
            }

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

        }
5. Licensing
A unique license key string is generated for each purchase. In order to unlock the ExpertXls Excel Library for .NET product you have to set the LicenseKey property of the ExcelWorkbook class with the license key string you have received after the product purchase before saving the Excel workbook. The license key contains the information about the purchased product like the product version and license type and is uniquely associated with an order ID. More details about the license types and pricing can be found on the Buy Now page of our website.
Copyright ExpertPdf Components