ÿþ<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Winnovative Excel Library for .NET</title> <link href="styles/styles.css" rel="stylesheet" type="text/css"> <link href="styles/csharp.css" rel="stylesheet" type="text/css"> </head> <body> <div style="width: 1024px"> <table width="100%"> <tr> <td colspan="2" style="height: 62px"> <!-- Header --> <table style="width: 100%"> <tr> <td style="width: 707px"> </td> <td> <img src="images/logo.jpg" height="50" /> </td> </tr> </table> </td> </tr> <tr> <td style="width: 5%"> </td> <td style="width: 95%"> <!-- Content --> <table width="90%"> <tr> <td style="height: 189px;"> </td> </tr> <tr> <td style="height: 40px;"> <span class="doctitle">Winnovative Excel Library for .NET</span>&nbsp; </td> </tr> <tr> <td style="height: 33px;"> </td> </tr> <tr> <td> <span class="title1">User s Guide</span><br /> </td> </tr> <tr> <td style="height: 77px"> </td> </tr> <tr> <td> <table> <tr> <td> <!--<img src="images/excel_library-250.png" />--> </td> </tr> </table> </td> </tr> <tr> <td> </td> </tr> <tr> <td style="height: 22px"> </td> </tr> <tr style="page-break-before: always"> <td class="title1" style="height: 23px"> Contents </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> <table> <tr> <td colspan="2" style="height: 30px"> <a class="contents" href="#Introduction">1. Introduction </a> </td> </tr> <tr> <td colspan="2" style="height: 30px"> <a class="contents" href="#Installation">2. Installation </a> </td> </tr> <tr> <td colspan="2" style="height: 30px"> <a class="contents" href="#Requirements">3. Requirements </a> </td> </tr> <tr> <td colspan="2" style="height: 30px"> <a class="contents" href="#API">4. Excel Library API </a> </td> </tr> <tr> <td style="width: 63px"> </td> <td> <a class="title4" href="#ExcelWorkbook">4.1 The ExcelWorkbook class</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#AddingWorksheets">4.2 Adding Worksheets to a Workbook</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#AccessingCells">4.3 Accessing Cells and Ranges</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#SettingValues">4.4 Setting Values and Formatting</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#DefiningFormulas">4.5 Defining Formulas</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#SettingStyles">4.6 Setting Cell Styles</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#InteractiveFeatures">4.7 Interactive Features</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#AddingImages">4.8 Adding Images to a Worksheet</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#AddingCharts">4.9 Adding Chart Shapes and Chart Worksheets</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#ImportingCSV">4.10 Importing Data from CSV </a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#ImportingDataTable">4.11 Importing Data from a DataTable Object </a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#DataValidation">4.12 Data Validation </a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#PageSetup">4.13 Worksheet Page Setup</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#SecurityFeatures">4.14 Security Features</a> </td> </tr> <tr> <td style="width: 63px"> </td> <td class="title4"> <a class="title4" href="#CodeSample">4.15 Code Sample</a> </td> </tr> <tr> <td colspan="2" style="height: 30px"> <a class="contents" href="#Licensing">5. Licensing </a> </td> </tr> </table> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr style="page-break-before: always"> <td style="height: 23px" class="title2"> <a name="Introduction" class="bookmark">1. Introduction</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px; text-align: justify"> The Winnovative Excel Library for .NET consists in a single .NET strong named assembly <em>wnvxls.dll</em> 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 .xlsx documents from the scratch or to edit existing Excel documents. Using the Winnovative 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.&nbsp;<br /> <br /> 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. <br /> <br /> The programming interface is object oriented, has a clear design, is extremely easy to understand and use. </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title2"> <a name="Installation" class="bookmark">2. Installation</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px; text-align: justify"> The Winnovative 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.<br /> <br /> <span class="subtitle2">2.1 Bin Folder</span><br /> <br /> The <em>Bin</em> 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 <em>Doc</em> folder.<br /> <br /> <em>wnvxls.dll</em> - is the Excel library .NET assembly that you can link in any .NET application, either Windows Forms or ASP.NET. <br /> <em>wnvxls.xml</em> - is the Excel Library API documentation used by Microsoft Visual Studio 2005 and 2008 IntelliSense. <br /> <br /> <span class="subtitle2">2.1&nbsp; Doc Folder<br /> </span> <br /> <em>Doc</em> folder contains the Excel Library API reference in chm and html format and this User's Guide document.<br /> <br /> <em>WnvExcelLibDoc.chm</em> - contains the Excel Library API reference, with comments for each class, method or property<br /> <em>HTML folder</em> - contains the Excel Library API reference in html format<br /> <em>User's Guide.html </em>- is the document you are currently reading<br /> <br /> <span class="subtitle2">2.3 &nbsp; &nbsp;Samples Folder<br /> </span> <br /> <em>Samples</em> 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. <br /> <br /> There is a sample application for each of the most important features of the Excel library. In the <em>WindowsForms</em> folder there are Windows Forms applications and in the <em>AspNet</em> folder there are web applications. </td> </tr> <tr> <td style="height: 23px; text-align: justify"> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title2"> <a name="Requirements" class="bookmark">3. Requirements and Recommendations</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> The recommended hardware and software resources for successfully using the Winnovative 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. </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px; text-align: justify"> <span class="subtitle2"></span><em>Operating System</em>: All Windows Versions<br /> <em>Hardware Architecture</em>: 32-bit, 64-bit<br /> <em>Free RAM</em>: 256 MB<br /> Microsoft .NET Framework 2.0 or 3.5 </td> </tr> <tr> <td style="height: 23px; text-align: justify"> </td> </tr> <tr> <td style="height: 23px" class="title2"> <a name="API" class="bookmark">4. Excel Library API</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> The Winnovative Excel Library for .NET API is fully documented in the <em>Doc/WnvExcelLibDoc.chm</em> document. In order to use the library you have include the <em>Winnovative.ExcelLib</em> namespace in your application. </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td> The Winnovative Excel Library for .NET can create and edit Excel 97-2003 (.xls) and Excel 2007 (.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. <br /> <br /> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="ExcelWorkbook" class="bookmark">4.1 The ExcelWorkbook Class </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> The <strong>ExcelWorkbook</strong> class represents an Excel workbook in the Winnovative Excel Library framework. An ExcelWorkbook class can be created from an existing Excel document for modification or an empty workbook can be created.<br /> <br /> 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 Winnovative Excel Library API.<br /> <br /> There are also <em>ExcelWorkbook</em> constructors accepting a password when creating an <em>ExcelWorkbook</em> object from a password protected Excel document. Here are the <em>ExcelWorkbook</em> class constructors:<br /> <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">/// Creates a workbook with a single worksheet. The default workbook format is Excel 2003.</span> <span class="rem">/// To create a workbook for a different Excel format, like Excel 2007,you can use the constructor with the Format parameter.</span> <span class="kwrd">public</span> ExcelWorkbook() <span class="rem">/// Creates a workbook with a single worksheet. The format of the workbook is specified by the workbookFormat parameter. </span> <span class="rem">/// The currently supported formats are Excel 97-2003 format and Excel 2007 format. </span> <span class="kwrd">public</span> ExcelWorkbook(ExcelWorkbookFormat workbookFormat) <span class="rem">/// Creates a workbook with a single worksheet. The format of the workbook is specified by the workbookFormat parameter.</span> <span class="rem">/// The currently supported formats are Excel 97-2003 format and Excel 2007 format. </span> <span class="kwrd">public</span> ExcelWorkbook(ExcelWorkbookFormat workbookFormat, ExcelWorkbookDefaultSettings defaultSettings) <span class="rem">/// Opens a workbook from the specified Excel file</span> <span class="kwrd">public</span> ExcelWorkbook(<span class="kwrd">string</span> excelFileName) <span class="rem">/// Opens a workbook from the specified Excel file with the specified password</span> <span class="kwrd">public</span> ExcelWorkbook(<span class="kwrd">string</span> excelFileName, <span class="kwrd">string</span> openPassword) <span class="rem">/// Opens a workbook from the specified Excel file with the specified password</span> <span class="kwrd">public</span> ExcelWorkbook(<span class="kwrd">string</span> excelFileName, <span class="kwrd">string</span> openPassword, ExcelWorkbookDefaultSettings defaultSettings) <span class="rem">/// Opens a workbook from the specified Excel stream</span> <span class="kwrd">public</span> ExcelWorkbook(Stream excelStream) <span class="rem">/// Opens a workbook from the specified Excel stream with the specified password</span> <span class="kwrd">public</span> ExcelWorkbook(Stream excelStream, <span class="kwrd">string</span> openPassword) <span class="rem">/// Opens a workbook from the specified Excel stream with the specified password</span> <span class="kwrd">public</span> ExcelWorkbook(Stream excelStream, <span class="kwrd">string</span> openPassword, ExcelWorkbookDefaultSettings defaultSettings)</pre> <br /> <br /> The code below shows how to create a workbook in the Excel 97-2003 format (.xls format): <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> ExcelWorkbook workbook = <span class="kwrd">new</span> ExcelWorkbook(ExcelWorkbookFormat.Xls_2003);</pre> The code below shows how to create a workbook in the Excel 2007 format (.xlsx format): <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> ExcelWorkbook workbook = <span class="kwrd">new</span> ExcelWorkbook(ExcelWorkbookFormat.Xlsx_2007);</pre> <br /> The <em>ExcelWorkbook</em> 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 <em>AddWorksheet()</em> method of the ExcelWorkbook.Worksheets collection. The worksheets from collection can be accessed by index or by name.<br /> <br /> 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: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// set workbook description properties</span> workbook.DocumentProperties.Subject = <span class="str">"Getting started sample"</span>; workbook.DocumentProperties.Comments = <span class="str">"Getting started with Winnovative Excel library for .NET"</span>;</pre> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="AddingWorksheets" class="bookmark">4.2 Adding Worksheets to a Workbook </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> The <strong>ExcelWorkbook</strong> 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 <i>AddWorksheet()</i> 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:<br /> <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet(<span class="str"> "Simple Chart"</span>);</pre> <br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> ExcelWorksheet secondWorksheet = workbook.Worksheets[1]; ExcelWorksheet secondWorksheet = workbook.Worksheets[<span class="str">"Simple Chart"</span>];</pre> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="AccessingCells" class="bookmark">4.3 Accessing Cells and Ranges </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> A worksheet can be viewed as a collection of cells or ranges of cells. With Winnovative 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. <br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// the top left cell of the worksheet in A1 notation</span> ExcelRange firstCell = worksheet[<span class="str">"A1"</span>]; <span class="rem">// the top left cell of the worksheet referenced by row and columns indexes</span> ExcelRange firstCell = worksheet[1,1]; <span class="rem">// a range of 4 cells from the top left corner of the worksheet in A1 notation</span> ExcelRange firstRange = worksheet[<span class="str">"A1:B2"</span>]; <span class="rem">// a range of 4 cells from the top left corner of the worksheet </span> <span class="rem">// referenced by row and column indexes</span> ExcelRange firstRange = worksheet[1, 1, 2, 2]; </pre> <br /> <br /> The Winnovative 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: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> ExcelNamedRange workbookNamedRange = workbook.NamedRanges.AddNamedRange(worksheet[<span class="str">"A1"</span>], <span class="str">"FirstCell"</span>); ExcelNamedRange worksheetNamedRange = worksheet.NamedRanges.AddNamedRange(worksheet[<span class="str">"A1"</span>], <span class="str">"FirstCell"</span>);</pre> <br /> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="SettingValues" class="bookmark">4.4 Setting Values and Formatting </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> 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 <b><i>Value</i></b> 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 <i>System.Double</i> 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 <i>System.DateTime</i> or <i>System.String</i> types. <br /> Additionally, the ExcelRange class defines a set of properties like <i>Text</i>, <i>NumberValue</i> or <i>DateTimeValue</i> which are the equivalent of the Value property when this property is assigned with values of type System.String, System.Double or System.DateTime. <br /> The formatting information that will be used when the values are displayed by an Excel viewer can be specified using the <i>ExcelRange.Style.Number.NumberFormatString</i> 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. <br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// set a text value</span> worksheet[<span class="str">"A7"</span>].Value = <span class="str">"This is a string value assigned with 'Value' property"</span>; worksheet[<span class="str">"A8"</span>].Text = <span class="str">"This is a string value assigned with 'Text' property"</span>; <span class="rem">// set a number value</span> <span class="kwrd">double</span> numberValue = 1234567.809; worksheet[<span class="str">"A16"</span>].Value = numberValue; worksheet[<span class="str">"A16"</span>].Style.Number.NumberFormatString = <span class="str">"#,##0.00"</span>; <span class="rem">// set a date value</span> <span class="kwrd">string</span> dateFormatString = <span class="str">"m/d/yyyy"</span>; worksheet[<span class="str">"A32"</span>].Value = DateTime.Now; worksheet[<span class="str">"A32"</span>].Style.Number.NumberFormatString = dateFormatString;</pre> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="DefiningFormulas" class="bookmark">4.5 Defining Formulas </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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 <i>ExcelRange.Formula</i> property of the ExcelRange class. An array formula string can be assigned to a cell or range using the <i>ExcelRange.FormulaArray</i> property of the ExcelRange class. <br /> 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. <br /> The sample code below shows how to assign various formulas to the cells of a worksheet: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// the excel viewer will calculate the sum of the numbers in the range C7:G7</span> <span class="rem">// and will assign the result to C9 cell</span> worksheet[<span class="str">"C9"</span>].Formula = <span class="str">"=SUM(C7:G7)"</span>; <span class="rem">// the excel viewer will calculate the minimum value in the range C7:G7</span> <span class="rem">// and will assign the result to C9 cell</span> worksheet[<span class="str">"C9"</span>].Formula = <span class="str">"=MIN(C7:G7)"</span>;</pre> <br /> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="SettingStyles" class="bookmark">4.6 Setting Cell Styles</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> 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.<br /> <br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// Add a style used for all the cells containing the hello world text</span> ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle(<span class="str">"HelloWorldStyle"</span>); helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; helloWorldStyle.Font.Size = 14; helloWorldStyle.Font.Bold = <span class="kwrd">true</span>; 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; <span class="kwrd">if</span> (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; } <span class="kwrd">else</span> { 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[<span class="str">"C5"</span>].Text = <span class="str">"Hello World !!!"</span>; <span class="rem">// set the 'HelloWorldStyle' for the cell</span> worksheet[<span class="str">"C5"</span>].Style = helloWorldStyle;</pre> <br /> The style can be also customized inline:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> worksheet[<span class="str">"A21"</span>].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; worksheet[<span class="str">"A21"</span>].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Top; worksheet[<span class="str">"A21"</span>].Style.Font.Bold = <span class="kwrd">true</span>; worksheet[<span class="str">"A21"</span>].Style.Font.Color = Color.Red; worksheet[<span class="str">"A21"</span>].Value = <span class="str">"Top Left"</span>;</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="InteractiveFeatures" class="bookmark">4.7 Interactive Features</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> Using the Winnovative Excel Library for .NET you can add hyperlinks and comments to a worksheet. <br /> <br /> 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.<br /> <br /> The sample code below shows how to add a hyperlink to a web page in a worksheet: <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// create a hyperlink to the product website</span> ExcelRange websiteLinkSource = worksheet[25, 1]; <span class="kwrd">string</span> websiteUrl = <span class="str">"http://www.winnovative-software.com"</span>; ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl); websiteHyperlink.Text = <span class="str">"Visit product website"</span>; websiteHyperlink.ToolTip = <span class="str">"Visit product website"</span>;</pre> <br /> <br /> The code sample below shows how to add a link to a range from another worksheet:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// create a named range used as target for the link to second worksheet</span> ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet[<span class="str">"A1"</span>], <span class="str">"SecondWorksheet"</span>); ExcelRange worksheetLinkSource = worksheet[23, 1]; ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, <span class="str">"SecondWorksheet"</span>); secondWorksheetLink.Text = <span class="str">"Go To Next Worksheet"</span>; secondWorksheetLink.ToolTip = <span class="str">"Go To Next Worksheet"</span>;</pre> <br /> <br /> 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: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> worksheet[<span class="str">"A9"</span>].AddComment(<span class="str">"Calculate the sum of the numbers in the range C7:G7"</span>);</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="AddingImages" class="bookmark">4.8 Adding Images to a Worksheet</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> Using the Winnovative 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// get a System.Drawing.Image object</span> <span class="kwrd">string</span> demoImagesPath = System.IO.Path.Combine(Server.MapPath(<span class="str">"~"</span>), <span class="str">@"Images"</span>); System.Drawing.Image demoImage = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"demo_250x300.jpg"</span>)); <span class="rem">// add an image specifing the top left corner</span> ExcelPicture demoExcelPicture = worksheet.Pictures.AddPicture(1, 7, demoImage); <span class="rem">// get the coordinates of the image</span> <span class="kwrd">int</span> leftColumnIndex = demoExcelPicture.LeftColumnIndex; <span class="kwrd">int</span> topRowIndex = demoExcelPicture.TopRowIndex; <span class="kwrd">int</span> rightColumnIndex = demoExcelPicture.RightColumnIndex; <span class="kwrd">int</span> bottomRowIndex = demoExcelPicture.BottomRowIndex; <span class="kwrd">string</span> excelPictureSumary = String.Format(<span class="str">"Left Column: {0}, Top Row: {1}, Right Column: {2}, Bottom Row: {3}"</span>, leftColumnIndex, topRowIndex, rightColumnIndex, bottomRowIndex);</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="AddingCharts" class="bookmark">4.9 Adding Chart Shapes and Chart Worksheets</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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<br /> The library is able to generate the chart series from the data source range or the chart series can be manually defined.<br /> The code sample below shows how to add a chart with automatically defined series: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="preproc">#region</span> ADD A CHART WITH AUTOMATICALLY GENERATED SERIES TO THE WORKSHEET ExcelRange dataSourceRange = worksheet[<span class="str">"B7:G12"</span>]; <span class="kwrd">bool</span> seriesDataByRows = rbSeriesByRows.Checked; ExcelChart chart = worksheet.Charts.AddChart(chartType, dataSourceRange, seriesDataByRows, 2, 16, 8, 33); <span class="rem">// set chart title</span> chart.Title.Text = <span class="str">"Product Units Sold per Quarter - Auto Generated Series"</span>; chart.Title.Font.Size = 12; chart.Title.Font.Color = Color.DarkBlue; <span class="rem">// set chart area style</span> <span class="kwrd">if</span> (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); } <span class="kwrd">else</span> { 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; } <span class="rem">// set value axis title</span> chart.ValueAxis.Title.Text = <span class="str">"Units sold"</span>; chart.ValueAxis.Title.Font.Size = 10; chart.ValueAxis.Title.Font.Bold = <span class="kwrd">true</span>; <span class="rem">// set value axis text style</span> chart.ValueAxis.Font.Size = 8; chart.ValueAxis.Font.Bold = <span class="kwrd">false</span>; chart.ValueAxis.Font.Italic = <span class="kwrd">true</span>; chart.ValueAxis.ShowVerticalTitleText(); <span class="rem">// set category axis title</span> chart.CategoryAxis.Title.Text = <span class="str">"Analyzed products"</span>; chart.CategoryAxis.Title.Font.Size = 10; chart.CategoryAxis.Title.Font.Bold = <span class="kwrd">true</span>; <span class="rem">// set category axis text style</span> chart.CategoryAxis.Font.Size = 8; chart.CategoryAxis.Font.Bold = <span class="kwrd">false</span>; chart.CategoryAxis.Font.Italic = <span class="kwrd">true</span>; <span class="rem">// set chart legend style</span> 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 = <span class="kwrd">true</span>;</pre> <br /> The code sample below shows how to add a chart with manually defined series: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="preproc">#region</span> ADD A CHART WITH CUSTOM SERIES TO THE WORKSHEET ExcelChart customSeriesChart = worksheet.Charts.AddChart(chartType, 2, 36, 8, 59); <span class="rem">// set chart data table</span> customSeriesChart.ShowDataTable = <span class="kwrd">true</span>; customSeriesChart.DataTable.ShowLegendKey = <span class="kwrd">true</span>; <span class="rem">// set chart title</span> customSeriesChart.Title.Text = <span class="str">"Product Units Sold per Quarter - Custom Series"</span>; customSeriesChart.Title.Font.Size = 12; customSeriesChart.Title.Font.Color = Color.DarkBlue; <span class="rem">// create the category names range</span> ExcelRange categoryNamesRange = worksheet[<span class="str">"C7:E7"</span>]; <span class="rem">// Add chart series</span> <span class="rem">// add first series for the first quarter sales</span> ExcelChartSeries firstQuarterSeries = customSeriesChart.Series.AddSeries(<span class="str">"First Quarter Sales"</span>); firstQuarterSeries.ChartType = chartType; firstQuarterSeries.CategoryNamesRange = worksheet[<span class="str">"C7:E7"</span>]; firstQuarterSeries.ValuesRange = worksheet[<span class="str">"C8:E8"</span>]; firstQuarterSeries.DataPoints.All.Label.ContainsValue = <span class="kwrd">true</span>; firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8; firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = <span class="kwrd">true</span>; <span class="rem">//add second series for second quarter sales</span> ExcelChartSeries secondQuarterSeries = customSeriesChart.Series.AddSeries(<span class="str">"Second Quarter Sales"</span>); secondQuarterSeries.ChartType = chartType; secondQuarterSeries.CategoryNamesRange = categoryNamesRange; secondQuarterSeries.ValuesRange = worksheet[<span class="str">"C9:E9"</span>]; secondQuarterSeries.DataPoints.All.Label.ContainsValue = <span class="kwrd">true</span>; secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8; secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = <span class="kwrd">true</span>; <span class="rem">// add third series for fourth quarter sales</span> ExcelChartSeries fourthQuarterSeries = customSeriesChart.Series.AddSeries(<span class="str">"Fourth Quarter Sales"</span>); fourthQuarterSeries.ChartType = chartType; fourthQuarterSeries.CategoryNamesRange = categoryNamesRange; fourthQuarterSeries.ValuesRange = worksheet[<span class="str">"C11:E11"</span>]; fourthQuarterSeries.DataPoints.All.Label.ContainsValue = <span class="kwrd">true</span>; fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8; fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = <span class="kwrd">true</span>; <span class="rem">// set chart area style</span> <span class="kwrd">if</span> (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); } <span class="kwrd">else</span> { 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; } <span class="rem">// set value axis title</span> customSeriesChart.ValueAxis.Title.Text = <span class="str">"Units sold"</span>; customSeriesChart.ValueAxis.Title.Font.Size = 10; customSeriesChart.ValueAxis.Title.Font.Bold = <span class="kwrd">true</span>; <span class="rem">// set value axis text style</span> customSeriesChart.ValueAxis.Font.Size = 8; customSeriesChart.ValueAxis.Font.Bold = <span class="kwrd">false</span>; customSeriesChart.ValueAxis.Font.Italic = <span class="kwrd">true</span>; customSeriesChart.ValueAxis.ShowVerticalTitleText(); <span class="rem">// set category axis text style</span> customSeriesChart.CategoryAxis.Font.Size = 8; customSeriesChart.CategoryAxis.Font.Bold = <span class="kwrd">false</span>; customSeriesChart.CategoryAxis.Font.Italic = <span class="kwrd">true</span>; <span class="rem">// set chart legend style</span> 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 = <span class="kwrd">true</span>; <span class="preproc">#endregion</span></pre> <br /> The current version of the library has limitation in reading Excel 2007 charts. Excel 97-2003 charts can be both read and written by the library but Excel 2007 charts can only be written.<br /> <br /> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="ImportingCSV" class="bookmark">4.10 Importing Data from CSV </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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: <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// The data from CSV file (numbers, dates, etc) was saved for the English US culture and </span> <span class="rem">// the CSV parser uses the current thread culture</span> <span class="rem">// Temporary set the en-US culture for the current thread and restore the old culture after the CSV file was loaded</span> System.Globalization.CultureInfo oldCulture = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = <span class="kwrd">new</span> System.Globalization.CultureInfo(<span class="str">"en-US"</span>); <span class="rem">// get the Excel workbook format</span> ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007; <span class="rem">// create the CSV stream</span> <span class="kwrd">string</span> dataFilePath = System.IO.Path.Combine(Server.MapPath(<span class="str">"~"</span>), <span class="str">@"Data\awemployees.csv"</span>); System.IO.FileStream csvDataStream = <span class="kwrd">new</span> System.IO.FileStream(dataFilePath, System.IO.FileMode.Open); <span class="rem">// load the data from the CSV stream to a new workbook in the specified format</span> ExcelWorkbook workbook = ExcelWorkbook.LoadFromCsv(csvDataStream, Encoding.GetEncoding(<span class="str">"windows-1252"</span>), <span class="str">","</span>, 5, 1, workbookFormat, <span class="kwrd">null</span>); <span class="rem">// close the CSV stream</span> csvDataStream.Close(); <span class="rem">// restore the current culture</span> System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture;</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="ImportingDataTable" class="bookmark">4.11 Importing Data from a DataTable Object </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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 : <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">// load an existing Excel file to a temporary workbook</span> <span class="rem">// create the Excel stream</span> <span class="kwrd">string</span> dataFilePath = System.IO.Path.Combine(Server.MapPath(<span class="str">"~"</span>), <span class="str">@"Data\awemployees.xls"</span>); System.IO.FileStream sourceXlsDataStream = <span class="kwrd">new</span> System.IO.FileStream(dataFilePath, System.IO.FileMode.Open); ExcelWorkbook tempWorkbook = <span class="kwrd">new</span> ExcelWorkbook(sourceXlsDataStream); ExcelWorksheet tempWorksheet = tempWorkbook.Worksheets[0]; <span class="rem">// get the data from the used range of the temporary workbook to a .NET DataTable object</span> DataTable exportedDataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, <span class="kwrd">true</span>); <span class="rem">//close the temporary workbook</span> tempWorkbook.Close(); <span class="rem">//close the data stream</span> sourceXlsDataStream.Close(); <span class="rem">// Create the workbook in which the data from the DataTable will be loaded</span> ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007; <span class="rem">// create the workbook in the desired format with a single worksheet</span> ExcelWorkbook workbook = <span class="kwrd">new</span> ExcelWorkbook(workbookFormat); <span class="rem">// get the first worksheet in the workbook</span> ExcelWorksheet worksheet = workbook.Worksheets[0]; <span class="rem">// set the default worksheet name</span> worksheet.Name = <span class="str">"Data Loaded from a DataTable"</span>; <span class="rem">// load data from DataTable into the worksheet</span> worksheet.LoadDataTable(exportedDataTable, 5, 1, <span class="kwrd">true</span>);</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="DataValidation" class="bookmark">4.12 Data Validation </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="preproc">#region</span> Add Data Validation <span class="preproc">#region</span> Validate data from a list worksheet[<span class="str">"A5:E5"</span>].Merge(); worksheet[<span class="str">"A5:E5"</span>].Style = textMessageStyle; worksheet[<span class="str">"A5:E5"</span>].Value = <span class="str">"Select a value from the list:"</span>; <span class="rem">// set the range to be validated</span> worksheet[<span class="str">"G5"</span>].Style = dataValidationStyle; worksheet[<span class="str">"G5"</span>].ColumnWidthInChars = 25; worksheet[<span class="str">"G5"</span>].AddComment(<span class="str">"Click this cell to select a value from list."</span>); ExcelDataValidator listValidator = worksheet[<span class="str">"G5"</span>].DataValidator; listValidator.AllowedDataType = ExcelDataValidatorDataType.List; listValidator.AllowedValues = <span class="kwrd">new</span> <span class="kwrd">string</span>[] { <span class="str">"HTML to PDF Converter"</span>, <span class="str">"PDF Merge"</span>, <span class="str">"PDF Security"</span>, <span class="str">"Excel Library for .NET"</span> }; listValidator.InputMessageText = <span class="str">"Select a value from the list"</span>; listValidator.ShowInputMessage = <span class="kwrd">true</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Validate a whole number between 0 and 10 worksheet[<span class="str">"A7:E7"</span>].Merge(); worksheet[<span class="str">"A7:E7"</span>].Style = textMessageStyle; worksheet[<span class="str">"A7:E7"</span>].Value = <span class="str">"Enter a whole number between 0 and 10 :"</span>; <span class="rem">// set the range to be validated</span> worksheet[<span class="str">"G7"</span>].Style = dataValidationStyle; worksheet[<span class="str">"G7"</span>].ColumnWidthInChars = 25; worksheet[<span class="str">"G7"</span>].AddComment(<span class="str">"Click this cell to enter a whole number."</span>); <span class="rem">// Data Validation for Numbers</span> ExcelDataValidator wholeNumberValidator = worksheet[<span class="str">"G7"</span>].DataValidator; wholeNumberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber; wholeNumberValidator.Operator = ExcelDataValidatorOperator.Between; wholeNumberValidator.Value1 = 0; wholeNumberValidator.Value2 = 10; wholeNumberValidator.ErrorAlertText = <span class="str">"A number between 0 to 10 is expected"</span>; wholeNumberValidator.ShowErrorAlert = <span class="kwrd">true</span>; wholeNumberValidator.ErrorAlertTitle = <span class="str">"Whole Number Validation Error"</span>; wholeNumberValidator.InputMessageText = <span class="str">"Enter a whole number between 0 and 10"</span>; wholeNumberValidator.ShowInputMessage = <span class="kwrd">true</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Validate a date between 01/01/2000 and 12/31/2009 worksheet[<span class="str">"A9:E9"</span>].Merge(); worksheet[<span class="str">"A9:E9"</span>].Style = textMessageStyle; worksheet[<span class="str">"A9:E9"</span>].Value = <span class="str">"Enter a date between 01/01/2000 and 12/31/2009 :"</span>; <span class="rem">// set the range to be validated</span> worksheet[<span class="str">"G9"</span>].Style = dataValidationStyle; worksheet[<span class="str">"G9"</span>].ColumnWidthInChars = 25; worksheet[<span class="str">"G9"</span>].Style.Number.NumberFormatString = <span class="str">"m/d/yyyy"</span>; worksheet[<span class="str">"G9"</span>].Value = <span class="kwrd">new</span> DateTime(2008, 12, 15); <span class="rem">// default value</span> worksheet[<span class="str">"G9"</span>].AddComment(<span class="str">"Double-Click this cell to enter a date in local format."</span>); ExcelDataValidator dateValidator = worksheet[<span class="str">"G9"</span>].DataValidator; dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date; dateValidator.Operator = ExcelDataValidatorOperator.Between; dateValidator.Value1 = <span class="kwrd">new</span> DateTime(2000, 1, 1); dateValidator.Value2 = <span class="kwrd">new</span> DateTime(2009, 12, 31); ; dateValidator.ErrorAlertText = <span class="str">"A date between 01/01/2000 and 12/31/2009 is expected"</span>; dateValidator.ShowErrorAlert = <span class="kwrd">true</span>; dateValidator.ErrorAlertTitle = <span class="str">"Date Validation Error"</span>; dateValidator.InputMessageText = <span class="str">"Enter a date between 01/01/2000 and 12/31/2009"</span>; dateValidator.ShowInputMessage = <span class="kwrd">true</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Validate the length of a text worksheet[<span class="str">"A11:E11"</span>].Merge(); worksheet[<span class="str">"A11:E11"</span>].Style = textMessageStyle; worksheet[<span class="str">"A11:E11"</span>].Value = <span class="str">"Enter a text with length between 2 and 5 chars:"</span>; <span class="rem">// set the range to be validated</span> worksheet[<span class="str">"G11"</span>].Style = dataValidationStyle; worksheet[<span class="str">"G11"</span>].ColumnWidthInChars = 25; worksheet[<span class="str">"G11"</span>].AddComment(<span class="str">"Click this cell to enter a text."</span>); ExcelDataValidator textLengthValidator = worksheet[<span class="str">"G11"</span>].DataValidator; textLengthValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength; textLengthValidator.Operator = ExcelDataValidatorOperator.Between; textLengthValidator.Value1 = 2; textLengthValidator.Value2 = 5; textLengthValidator.ErrorAlertTitle = <span class="str">"Text Length Validation Error"</span>; textLengthValidator.ErrorAlertText = <span class="str">"A text with length between 2 and 5 chars is expected"</span>; textLengthValidator.ShowErrorAlert = <span class="kwrd">true</span>; textLengthValidator.InputMessageText = <span class="str">"Enter a text with length between 2 and 5 characters"</span>; textLengthValidator.ShowInputMessage = <span class="kwrd">true</span>; <span class="preproc">#endregion</span> <span class="preproc">#endregion</span> </pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="PageSetup" class="bookmark">4.13 Worksheet Page Setup </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> The Winnovative 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="preproc">#region</span> WORKSHEET PAGE SETUP <span class="rem">// set worksheet paper size and orientation, margins, header and footer</span> 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; <span class="rem">// add header and footer</span> <span class="rem">//display a logo image in the left part of the header</span> <span class="kwrd">string</span> imagesPath = System.IO.Path.Combine(Server.MapPath(<span class="str">"~"</span>), <span class="str">@"Images"</span>); System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"logo.jpg"</span>)); worksheet.PageSetup.LeftHeaderFormat = <span class="str">"&amp;G"</span>; worksheet.PageSetup.LeftHeaderPicture = logoImg; <span class="rem">// display worksheet name in the right part of the header</span> worksheet.PageSetup.RightHeaderFormat = <span class="str">"&amp;A"</span>; <span class="rem">// add worksheet header and footer</span> <span class="rem">// display the page number in the center part of the footer</span> worksheet.PageSetup.CenterFooterFormat = <span class="str">"&amp;P"</span>; <span class="rem">// display the workbook file name in the left part of the footer</span> worksheet.PageSetup.LeftFooterFormat = <span class="str">"&amp;F"</span>; <span class="rem">// display the current date in the right part of the footer</span> worksheet.PageSetup.RightFooterFormat = <span class="str">"&amp;D"</span>; <span class="preproc">#endregion</span></pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="SecurityFeatures" class="bookmark">4.14 Security Features </a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td> Using Winnovative 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.<br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="rem">//set a password required when the workbook is opened by an Excel viewer.</span> workbook.DocumentSecurity.OpenWorkbookPassword = <span class="str">"open"</span>; <span class="rem">//set a password required by an Excel editor in order to modify the workbook.</span> workbook.DocumentSecurity.WriteWorkbookPassword = <span class="str">"write"</span>;</pre> <br /> <br /> 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:<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> workbook.DocumentSecurity.ProtectWorkbook(<span class="kwrd">true</span>, <span class="kwrd">true</span>);</pre> <br /> 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<br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> worksheet.WorksheetSecurity.ProtectWorksheet(ExcelProtectionAllowFlags.AllowDefault );</pre> <br /> </td> </tr> <tr> <td style="height: 23px" class="title3"> <a name="CodeSample" class="bookmark">4.15 Code Sample</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 26px"> </td> </tr> <tr> <td style="height: 23px"> Below there is a full code sample illustrating the main features of the Winnovative Excel Library for .NET. The code was taken from the Getting Started sample available in the Samples\AspNet\CS\ExcelLibraryFeaturesDemo\Default.aspx.cs file.<br /> <br /> <!-- code formatted by http://manoli.net/csharpformat/ --> <pre class="csharpcode"> <span class="kwrd">protected</span> <span class="kwrd">void</span> lnkBtnCreateWorkbook_Click(<span class="kwrd">object</span> sender, EventArgs e) { <span class="rem">// get the Excel workbook format</span> ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007; <span class="rem">// create the workbook in the desired format with a single worksheet</span> ExcelWorkbook workbook = <span class="kwrd">new</span> ExcelWorkbook(workbookFormat); <span class="rem">// set the license key before saving the workbook</span> workbook.LicenseKey = <span class="str">"RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw="</span>; <span class="rem">// set workbook description properties</span> workbook.DocumentProperties.Subject = <span class="str">"Getting started sample"</span>; workbook.DocumentProperties.Comments = <span class="str">"Getting started with Winnovative Excel library for .NET"</span>; <span class="preproc">#region</span> CREATE CUSTOM WORKBOOK STYLES <span class="preproc">#region</span> Add a style used <span class="kwrd">for</span> the cells <span class="kwrd">in</span> the worksheet title area ExcelCellStyle titleStyle = workbook.Styles.AddStyle(<span class="str">"WorksheetTitleStyle"</span>); <span class="rem">// center the text in the title area</span> titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center; titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; <span class="rem">// set the title area borders</span> 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; <span class="kwrd">if</span> (workbookFormat == ExcelWorkbookFormat.Xls_2003) { <span class="rem">// set the solid fill for the title area range with a custom color</span> titleStyle.Fill.FillType = ExcelCellFillType.SolidFill; titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204); } <span class="kwrd">else</span> { <span class="rem">// set the gradient fill for the title area range with a custom color</span> titleStyle.Fill.FillType = ExcelCellFillType.GradientFill; titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204); titleStyle.Fill.GradientFillOptions.Color2 = Color.White; } <span class="rem">// set the title area font </span> titleStyle.Font.Size = 14; titleStyle.Font.Bold = <span class="kwrd">true</span>; titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Add a style used <span class="kwrd">for</span> all the cells <span class="kwrd">in</span> the index column ExcelCellStyle indexStyle = workbook.Styles.AddStyle(<span class="str">"IndexColumnStyle"</span>); indexStyle.Font.Size = 12; indexStyle.Font.Bold = <span class="kwrd">false</span>; 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; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Add a style used <span class="kwrd">for</span> all the cells <span class="kwrd">in</span> the country name column ExcelCellStyle countryNameStyle = workbook.Styles.AddStyle(<span class="str">"CountryNameStyle"</span>); countryNameStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; countryNameStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; countryNameStyle.Font.Size = 12; countryNameStyle.Font.Bold = <span class="kwrd">true</span>; 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; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Add a style used <span class="kwrd">for</span> all the cells containing the hello world text ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle(<span class="str">"HelloWorldStyle"</span>); helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; helloWorldStyle.Font.Size = 14; helloWorldStyle.Font.Bold = <span class="kwrd">true</span>; 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; <span class="kwrd">if</span> (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; } <span class="kwrd">else</span> { 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; } <span class="preproc">#endregion</span> <span class="preproc">#endregion</span> <span class="rem">// get the first worksheet in the workbook</span> ExcelWorksheet worksheet = workbook.Worksheets[0]; <span class="rem">// set the default worksheet name</span> worksheet.Name = <span class="str">"Hello World"</span>; <span class="preproc">#region</span> WORKSHEET PAGE SETUP <span class="rem">// set worksheet paper size and orientation, margins, header and footer</span> 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; <span class="rem">// add header and footer</span> <span class="rem">//display a logo image in the left part of the header</span> <span class="kwrd">string</span> imagesPath = System.IO.Path.Combine(Server.MapPath(<span class="str">"~"</span>), <span class="str">@"Images"</span>); System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"logo.jpg"</span>)); worksheet.PageSetup.LeftHeaderFormat = <span class="str">"&amp;G"</span>; worksheet.PageSetup.LeftHeaderPicture = logoImg; <span class="rem">// display worksheet name in the right part of the header</span> worksheet.PageSetup.RightHeaderFormat = <span class="str">"&amp;A"</span>; <span class="rem">// add worksheet header and footer</span> <span class="rem">// display the page number in the center part of the footer</span> worksheet.PageSetup.CenterFooterFormat = <span class="str">"&amp;P"</span>; <span class="rem">// display the workbook file name in the left part of the footer</span> worksheet.PageSetup.LeftFooterFormat = <span class="str">"&amp;F"</span>; <span class="rem">// display the current date in the right part of the footer</span> worksheet.PageSetup.RightFooterFormat = <span class="str">"&amp;D"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> WRITE THE WORKSHEET TOP TITLE <span class="rem">// merge the cells in the range to create the title area </span> worksheet[<span class="str">"A2:G3"</span>].Merge(); <span class="rem">// gets the merged range containing the top left cell of the range</span> ExcelRange titleRange = worksheet[<span class="str">"A2"</span>].MergeArea; <span class="rem">// set the text of title area</span> worksheet[<span class="str">"A2"</span>].Text = <span class="str">"Say 'Hello World' in Different Languages"</span>; <span class="rem">// set a row height of 18 points for each row in the range</span> titleRange.RowHeightInPoints = 18; <span class="rem">// set the worksheet top title style</span> titleRange.Style = titleStyle; <span class="preproc">#endregion</span> <span class="preproc">#region</span> WRITE <span class="str">'HELLO WORLD'</span> IN DIFFERENT LANGUAGES System.Drawing.Image usFlagImg = <span class="kwrd">null</span>; System.Drawing.Image frFlagImg = <span class="kwrd">null</span>; System.Drawing.Image deFlagImg = <span class="kwrd">null</span>; System.Drawing.Image esFlagImg = <span class="kwrd">null</span>; System.Drawing.Image ruFlagImg = <span class="kwrd">null</span>; System.Drawing.Image itFlagImg = <span class="kwrd">null</span>; System.Drawing.Image ptFlagImg = <span class="kwrd">null</span>; System.Drawing.Image nlFlagImg = <span class="kwrd">null</span>; <span class="rem">// set the separator between index column and text column</span> worksheet.SetColumnWidthInChars(2, 3); <span class="rem">// set the country image column</span> worksheet.SetColumnWidthInChars(6, 5); <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> English worksheet[<span class="str">"A5"</span>].Style = indexStyle; worksheet[<span class="str">"A5"</span>].Value = 1; worksheet[<span class="str">"C5"</span>].Text = <span class="str">"Hello World !!!"</span>; worksheet[<span class="str">"C5"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C5"</span>].RowHeightInPoints = 19.5; usFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"us.png"</span>)); worksheet.Pictures.AddPicture(6, 5, usFlagImg); worksheet[5, 6, 5, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[5, 7].Text = <span class="str">"English"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> French worksheet[<span class="str">"A7"</span>].Style = indexStyle; worksheet[<span class="str">"A7"</span>].Value = 2; worksheet[<span class="str">"C7"</span>].Text = <span class="str">"Bonjour tout le monde !!!"</span>; worksheet[<span class="str">"C7"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C7"</span>].Style.Font.Color = Color.Blue; worksheet[<span class="str">"C7"</span>].RowHeightInPoints = 19.5; frFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"fr.png"</span>)); worksheet.Pictures.AddPicture(6, 7, frFlagImg); worksheet[7, 6, 7, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[7, 7].Text = <span class="str">"French"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> German worksheet[<span class="str">"A9"</span>].Style = indexStyle; worksheet[<span class="str">"A9"</span>].Value = 3; worksheet[<span class="str">"C9"</span>].Text = <span class="str">"Hallo Welt"</span>; worksheet[<span class="str">"C9"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C9"</span>].Style.Font.Color = Color.Red; worksheet[<span class="str">"C9"</span>].RowHeightInPoints = 19.5; deFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"de.png"</span>)); worksheet.Pictures.AddPicture(6, 9, deFlagImg); worksheet[9, 6, 9, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[9, 7].Text = <span class="str">"German"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> Spanish worksheet[<span class="str">"A11"</span>].Style = indexStyle; worksheet[<span class="str">"A11"</span>].Value = 4; worksheet[<span class="str">"C11"</span>].Text = <span class="str">"Hola Mundo"</span>; worksheet[<span class="str">"C11"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C11"</span>].Style.Font.Color = Color.Orange; worksheet[<span class="str">"C11"</span>].RowHeightInPoints = 19.5; esFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"es.png"</span>)); worksheet.Pictures.AddPicture(6, 11, esFlagImg); worksheet[11, 6, 11, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[11, 7].Text = <span class="str">"Spanish"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> Russian worksheet[<span class="str">"A13"</span>].Style = indexStyle; worksheet[<span class="str">"A13"</span>].Value = 5; worksheet[<span class="str">"C13"</span>].Text = <span class="str">"@825B <8@"</span>; worksheet[<span class="str">"C13"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C13"</span>].Style.Font.Color = Color.Indigo; worksheet[<span class="str">"C13"</span>].RowHeightInPoints = 19.5; ruFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"ru.png"</span>)); worksheet.Pictures.AddPicture(6, 13, ruFlagImg); worksheet[13, 6, 13, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[13, 7].Text = <span class="str">"Russian"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> Italian worksheet[<span class="str">"A15"</span>].Style = indexStyle; worksheet[<span class="str">"A15"</span>].Value = 6; worksheet[<span class="str">"C15"</span>].Text = <span class="str">"Ciao a tutti"</span>; worksheet[<span class="str">"C15"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C15"</span>].Style.Font.Color = Color.Green; worksheet[<span class="str">"C15"</span>].RowHeightInPoints = 19.5; itFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"it.png"</span>)); worksheet.Pictures.AddPicture(6, 15, itFlagImg); worksheet[15, 6, 15, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[15, 7].Text = <span class="str">"Italian"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> Dutch worksheet[<span class="str">"A17"</span>].Style = indexStyle; worksheet[<span class="str">"A17"</span>].Value = 7; worksheet[<span class="str">"C17"</span>].Text = <span class="str">"Hallo Wereld"</span>; worksheet[<span class="str">"C17"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C17"</span>].Style.Font.Color = Color.Blue; worksheet[<span class="str">"C17"</span>].RowHeightInPoints = 19.5; nlFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"nl.png"</span>)); worksheet.Pictures.AddPicture(6, 17, nlFlagImg); worksheet[17, 6, 17, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[17, 7].Text = <span class="str">"Dutch"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> Say hello world <span class="kwrd">in</span> Portuguese worksheet[<span class="str">"A19"</span>].Style = indexStyle; worksheet[<span class="str">"A19"</span>].Value = 8; worksheet[<span class="str">"C19"</span>].Text = <span class="str">"Olá Mundo"</span>; worksheet[<span class="str">"C19"</span>].Style = helloWorldStyle; worksheet[<span class="str">"C19"</span>].Style.Font.Color = Color.Red; worksheet[<span class="str">"C19"</span>].RowHeightInPoints = 19.5; ptFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, <span class="str">"pt.png"</span>)); worksheet.Pictures.AddPicture(6, 19, ptFlagImg); worksheet[19, 6, 19, 7].StyleName = <span class="str">"CountryNameStyle"</span>; worksheet[19, 7].Text = <span class="str">"Portuguese"</span>; <span class="preproc">#endregion</span> <span class="rem">// autofit the index column width</span> worksheet.AutofitColumn(1); <span class="rem">// autofit the Hello World text column</span> worksheet.AutofitColumn(3); <span class="rem">// autofit the country name column</span> worksheet.AutofitColumn(7); <span class="preproc">#endregion</span> <span class="preproc">#region</span> WRITE WORKSHEET CREATION TIMESTAMP worksheet[28, 1, 28, 3].Merge(); ExcelRange timestampTextRange = worksheet[28, 1].MergeArea; timestampTextRange.Style.Font.Bold = <span class="kwrd">true</span>; worksheet[28, 1].Text = <span class="str">"Workbook Creation Date &amp; Time:"</span>; worksheet[28, 4, 28, 7].Merge(); ExcelRange timestampDateRange = worksheet[28, 4].MergeArea; timestampDateRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left; timestampDateRange.Style.Number.NumberFormatString = <span class="str">"mm/dd/yyyy hh:mm:ss"</span>; timestampDateRange.Style.Font.Color = Color.DarkBlue; timestampDateRange.Style.Font.Bold = <span class="kwrd">true</span>; worksheet[28, 4].Value = DateTime.Now; <span class="preproc">#endregion</span> <span class="preproc">#region</span> ADD A SECOND WORKSHEET TO THE WORKBOOK ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet(<span class="str">"Simple Chart"</span>); <span class="preproc">#region</span> SECOND WORKSHEET PAGE SETUP <span class="rem">// set worksheet paper size and orientation, margins, header and footer</span> 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; <span class="rem">// add header and footer</span> <span class="rem">//display a logo image in the left part of the header</span> secondWorksheet.PageSetup.LeftHeaderFormat = <span class="str">"&amp;G"</span>; secondWorksheet.PageSetup.LeftHeaderPicture = logoImg; <span class="rem">// display worksheet name in the right part of the header</span> secondWorksheet.PageSetup.RightHeaderFormat = <span class="str">"&amp;A"</span>; <span class="rem">// add worksheet header and footer</span> <span class="rem">// display the page number in the center part of the footer</span> secondWorksheet.PageSetup.CenterFooterFormat = <span class="str">"&amp;P"</span>; <span class="rem">// display the workbook file name in the left part of the footer</span> secondWorksheet.PageSetup.LeftFooterFormat = <span class="str">"&amp;F"</span>; <span class="rem">// display the current date in the right part of the footer</span> secondWorksheet.PageSetup.RightFooterFormat = <span class="str">"&amp;D"</span>; <span class="preproc">#endregion</span> <span class="preproc">#region</span> WRITE THE SECOND WORKSHEET TOP TITLE <span class="rem">// merge the cells in the range to create the title area </span> secondWorksheet[<span class="str">"A2:G3"</span>].Merge(); <span class="rem">// gets the merged range containing the top left cell of the range</span> ExcelRange secondTitleRange = secondWorksheet[<span class="str">"A2"</span>].MergeArea; <span class="rem">// set the text of title area</span> secondWorksheet[<span class="str">"A2"</span>].Text = <span class="str">"Simple Chart Demo"</span>; <span class="rem">// set a row height of 18 points for each row in the range</span> secondTitleRange.RowHeightInPoints = 18; <span class="rem">// set the worksheet top title style</span> secondTitleRange.Style = titleStyle; <span class="preproc">#endregion</span> <span class="preproc">#region</span> CREATE DATA TABLE FOR THE CHART IN SECOND WORKSHEET ExcelCellStyle chartValuesStyle = workbook.Styles.AddStyle(<span class="str">"ChartValuesStyle"</span>); chartValuesStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center; chartValuesStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; chartValuesStyle.Font.Color = Color.Black; chartValuesStyle.Font.Bold = <span class="kwrd">true</span>; <span class="kwrd">if</span> (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; } <span class="kwrd">else</span> { chartValuesStyle.Fill.FillType = ExcelCellFillType.GradientFill; chartValuesStyle.Fill.GradientFillOptions.Color1 = Color.LightGreen; chartValuesStyle.Fill.GradientFillOptions.Color2 = Color.White; chartValuesStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown; } <span class="rem">// set the products tile row text and style </span> secondWorksheet[<span class="str">"C6:G6"</span>].Merge(); secondWorksheet[<span class="str">"C6"</span>].Text = <span class="str">"Analyzed Products"</span>; ExcelRange productsTitle = secondWorksheet[<span class="str">"C6"</span>].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 = <span class="kwrd">true</span>; 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; <span class="rem">// set the quarters title column text and style</span> secondWorksheet[<span class="str">"A8:A11"</span>].Merge(); secondWorksheet[<span class="str">"A8"</span>].Text = <span class="str">"Units Sold per Quarter"</span>; ExcelRange quartersTitle = secondWorksheet[<span class="str">"A8"</span>].MergeArea; <span class="rem">// set vertical orientation for the text from bottom to top</span> quartersTitle.Style.Alignment.Orientation = 90; <span class="rem">// wrap text inside the merged range</span> quartersTitle.Style.Alignment.WrapText = <span class="kwrd">true</span>; quartersTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center; quartersTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; quartersTitle.Style.Font.Size = 12; quartersTitle.Style.Font.Bold = <span class="kwrd">true</span>; 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; <span class="rem">// set the style for the product names row</span> ExcelRange productNamesRange = secondWorksheet[<span class="str">"B7:G7"</span>]; productNamesRange.RowHeightInPoints = 21; productNamesRange.ColumnWidthInChars = 16; productNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center; productNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; productNamesRange.Style.Font.Bold = <span class="kwrd">true</span>; productNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill; productNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153); <span class="rem">// set the styles for the quarter names range</span> ExcelRange quarterNamesRange = secondWorksheet[<span class="str">"B8:B11"</span>]; quarterNamesRange.RowHeightInPoints = 16.5; quarterNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right; quarterNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center; quarterNamesRange.Style.Font.Bold = <span class="kwrd">true</span>; quarterNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill; quarterNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153); <span class="rem">// set the row height for the Yearly Total row</span> ExcelRange yearlyTotalRange = secondWorksheet[<span class="str">"B12:G12"</span>]; 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 = <span class="kwrd">true</span>; yearlyTotalRange.Style.Fill.FillType = ExcelCellFillType.SolidFill; yearlyTotalRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 255); <span class="rem">// set the series name column</span> secondWorksheet[<span class="str">"B8"</span>].Text = <span class="str">"First Quarter"</span>; secondWorksheet[<span class="str">"B9"</span>].Text = <span class="str">"Second Quarter"</span>; secondWorksheet[<span class="str">"B10"</span>].Text = <span class="str">"Third Quarter"</span>; secondWorksheet[<span class="str">"B11"</span>].Text = <span class="str">"Fourth Quarter"</span>; secondWorksheet[<span class="str">"B12"</span>].Text = <span class="str">"Yearly Total"</span>; <span class="rem">// set the categories name row</span> secondWorksheet[<span class="str">"C7"</span>].Text = <span class="str">"HTML to PDF"</span>; secondWorksheet[<span class="str">"D7"</span>].Text = <span class="str">"PDF Merge"</span>; secondWorksheet[<span class="str">"E7"</span>].Text = <span class="str">"PDF Security"</span>; secondWorksheet[<span class="str">"F7"</span>].Text = <span class="str">"Web Chart"</span>; secondWorksheet[<span class="str">"G7"</span>].Text = <span class="str">"Excel Library"</span>; <span class="rem">// set the chart value style</span> ExcelRange chartValuesRange = secondWorksheet[<span class="str">"C8:G11"</span>]; chartValuesRange.Style = chartValuesStyle; <span class="rem">// set the chart values</span> secondWorksheet[<span class="str">"C8"</span>].Value = 1000; secondWorksheet[<span class="str">"D8"</span>].Value = 500; secondWorksheet[<span class="str">"E8"</span>].Value = 200; secondWorksheet[<span class="str">"F8"</span>].Value = 400; secondWorksheet[<span class="str">"G8"</span>].Value = 800; secondWorksheet[<span class="str">"C9"</span>].Value = 850; secondWorksheet[<span class="str">"D9"</span>].Value = 680; secondWorksheet[<span class="str">"E9"</span>].Value = 350; secondWorksheet[<span class="str">"F9"</span>].Value = 230; secondWorksheet[<span class="str">"G9"</span>].Value = 640; secondWorksheet[<span class="str">"C10"</span>].Value = 950; secondWorksheet[<span class="str">"D10"</span>].Value = 450; secondWorksheet[<span class="str">"E10"</span>].Value = 175; secondWorksheet[<span class="str">"F10"</span>].Value = 350; secondWorksheet[<span class="str">"G10"</span>].Value = 520; secondWorksheet[<span class="str">"C11"</span>].Value = 500; secondWorksheet[<span class="str">"D11"</span>].Value = 700; secondWorksheet[<span class="str">"E11"</span>].Value = 250; secondWorksheet[<span class="str">"F11"</span>].Value = 460; secondWorksheet[<span class="str">"G11"</span>].Value = 320; secondWorksheet[<span class="str">"C12"</span>].Formula = <span class="str">"=SUM(C8:C11)"</span>; secondWorksheet[<span class="str">"D12"</span>].Formula = <span class="str">"=SUM(D8:D11)"</span>; secondWorksheet[<span class="str">"E12"</span>].Formula = <span class="str">"=SUM(E8:E11)"</span>; secondWorksheet[<span class="str">"F12"</span>].Formula = <span class="str">"=SUM(F8:F11)"</span>; secondWorksheet[<span class="str">"G12"</span>].Formula = <span class="str">"=SUM(G8:G11)"</span>; <span class="rem">// auto fit the width of the quarter names column</span> secondWorksheet[<span class="str">"B7"</span>].AutofitColumns(); <span class="preproc">#endregion</span> <span class="preproc">#region</span> ADD A CHART TO THE SECOND WORKSHEET ExcelRange dataSourceRange = secondWorksheet[<span class="str">"B7:G12"</span>]; ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, dataSourceRange, <span class="kwrd">true</span>, 2, 15, 8, 32); chart.ShowDataTable = <span class="kwrd">false</span>; <span class="rem">// set chart title</span> chart.Title.Text = <span class="str">"Product Units Sold per Quarter"</span>; chart.Title.Interior.FillType = ExcelShapeFillType.NoFill; chart.Title.Font.Size = 12; chart.Title.Font.Color = Color.DarkBlue; <span class="rem">// set chart area style</span> <span class="kwrd">if</span> (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); } <span class="kwrd">else</span> { 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; } <span class="rem">// set value axis title</span> chart.ValueAxis.Title.Text = <span class="str">"Units sold"</span>; chart.ValueAxis.Title.Font.Size = 10; chart.ValueAxis.Title.Font.Bold = <span class="kwrd">true</span>; <span class="rem">// set value axis text style</span> chart.ValueAxis.Font.Size = 8; chart.ValueAxis.Font.Bold = <span class="kwrd">false</span>; chart.ValueAxis.Font.Italic = <span class="kwrd">true</span>; chart.ValueAxis.ShowVerticalTitleText(); <span class="rem">// set category axis title</span> chart.CategoryAxis.Title.Text = <span class="str">"Analyzed products"</span>; chart.CategoryAxis.Title.Font.Size = 10; chart.CategoryAxis.Title.Font.Bold = <span class="kwrd">true</span>; <span class="rem">// set category axis text style</span> chart.CategoryAxis.Font.Size = 8; chart.CategoryAxis.Font.Bold = <span class="kwrd">false</span>; chart.CategoryAxis.Font.Italic = <span class="kwrd">true</span>; <span class="rem">// set chart legend style</span> 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 = <span class="kwrd">true</span>; <span class="rem">// show a label with total number of units sold in a year</span> chart.Series[<span class="str">"Yearly Total"</span>].DataPoints.All.Label.ContainsValue = <span class="kwrd">true</span>; chart.Series[<span class="str">"Yearly Total"</span>].DataPoints.All.Label.LabelFormat.Font.Size = 8; chart.Series[<span class="str">"Yearly Total"</span>].DataPoints.All.Label.LabelFormat.Font.Italic = <span class="kwrd">true</span>; <span class="preproc">#endregion</span> <span class="preproc">#endregion</span> <span class="preproc">#region</span> CREATE HYPERLINKS <span class="rem">// create the merged range where to add the link to the second worksheet</span> worksheet[23, 1, 23, 3].Merge(); <span class="rem">// create a named range used as target for the link to second worksheet</span> ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet[<span class="str">"A1"</span>], <span class="str">"SecondWorksheet"</span>); ExcelRange worksheetLinkSource = worksheet[23, 1]; ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, <span class="str">"SecondWorksheet"</span>); secondWorksheetLink.Text = <span class="str">"Go To Next Worksheet"</span>; secondWorksheetLink.ToolTip = <span class="str">"Go To Next Worksheet"</span>; <span class="rem">// creat the merged range where to add the link to the product website</span> worksheet[25, 1, 25, 3].Merge(); <span class="rem">// create a hyperlink to the product website</span> ExcelRange websiteLinkSource = worksheet[25, 1]; <span class="kwrd">string</span> websiteUrl = <span class="str">"http://www.winnovative-software.com"</span>; ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl); websiteHyperlink.Text = <span class="str">"Visit product website"</span>; websiteHyperlink.ToolTip = <span class="str">"Visit product website"</span>; <span class="preproc">#endregion</span> <span class="rem">// SAVE THE WORKBOOK</span> <span class="rem">// Save the Excel document in the current HTTP response stream</span> <span class="kwrd">string</span> outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? <span class="str">"GettingStarted.xls"</span> : <span class="str">"GettingStarted.xlsx"</span>; System.Web.HttpResponse httpResponse = System.Web.HttpContext.Current.Response; <span class="rem">// Prepare the HTTP response stream for saving the Excel document</span> <span class="rem">// Clear any data that might have been previously buffered in the output stream</span> httpResponse.Clear(); <span class="rem">// Set output stream content type for Excel 97-2003 (.xls) or Excel 2007 (.xlsx)</span> <span class="kwrd">if</span> (workbookFormat == ExcelWorkbookFormat.Xls_2003) httpResponse.ContentType = <span class="str">"Application/x-msexcel"</span>; <span class="kwrd">else</span> httpResponse.ContentType = <span class="str">"Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"</span>; <span class="rem">// Add the HTTP header to announce the Excel document either as an attachment or inline</span> httpResponse.AddHeader(<span class="str">"Content-Disposition"</span>, String.Format(<span class="str">"attachment; filename={0}"</span>, outFileName)); <span class="rem">// Save the workbook to the current HTTP response output stream</span> <span class="rem">// and close the workbook after save to release all the allocated resources</span> <span class="kwrd">try</span> { workbook.Save(httpResponse.OutputStream); } <span class="kwrd">catch</span> (Exception ex) { <span class="rem">// report any error that might occur during save</span> Session[<span class="str">"ErrorMessage"</span>] = ex.Message; Response.Redirect(<span class="str">"ErrorPage.aspx"</span>); } <span class="kwrd">finally</span> { <span class="rem">// close the workbook and release the allocated resources</span> workbook.Close(); <span class="preproc">#region</span> Dispose the Image <span class="kwrd">object</span> <span class="rem">// release the flag images</span> <span class="kwrd">if</span> (usFlagImg != <span class="kwrd">null</span>) usFlagImg.Dispose(); <span class="kwrd">if</span> (frFlagImg != <span class="kwrd">null</span>) frFlagImg.Dispose(); <span class="kwrd">if</span> (deFlagImg != <span class="kwrd">null</span>) deFlagImg.Dispose(); <span class="kwrd">if</span> (esFlagImg != <span class="kwrd">null</span>) esFlagImg.Dispose(); <span class="kwrd">if</span> (ruFlagImg != <span class="kwrd">null</span>) ruFlagImg.Dispose(); <span class="kwrd">if</span> (itFlagImg != <span class="kwrd">null</span>) itFlagImg.Dispose(); <span class="kwrd">if</span> (ptFlagImg != <span class="kwrd">null</span>) ptFlagImg.Dispose(); <span class="kwrd">if</span> (nlFlagImg != <span class="kwrd">null</span>) nlFlagImg.Dispose(); <span class="kwrd">if</span> (logoImg != <span class="kwrd">null</span>) logoImg.Dispose(); <span class="preproc">#endregion</span> } <span class="rem">// End the response and finish the execution of this page</span> httpResponse.End(); }</pre> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px" class="title2"> <a name="Licensing" class="bookmark">5. Licensing</a> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> A unique license key string is generated for each purchase. In order to unlock the Winnovative Excel Library for .NET product you have to set the <em><strong>LicenseKey</strong></em> property of the <em><strong>ExcelWorkbook</strong></em> class with the license key string you have received after the product purchase before saving the Excel workbook. <br /> <br /> 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 <a href="http://www.winnovative-software.com/buy.aspx"> Buy Now</a> page of our website. </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td style="height: 23px"> </td> </tr> <tr> <td colspan="2"> <!-- Footer --> Copyright 2009 by <a href="http://www.winnovative-software.com" target="_blank">Winnovative Software Solutions</a> </td> </tr> </table> </td> </tr> </table> </div> </body> </html>