|
|
|
Winnovative Excel Library for .NET
|
|
User Guide
|
|
|
|
|
|
|
Table of Contents
|
|
|
|
|
|
|
1. Introduction
|
|
The Winnovative Excel Library for .NET consists in a single .NET strong named assembly
wnvxls.dll that can be referenced directly in any .NET application (ASP.NET,
Windows Forms, Console, Web Services, Windows Services, etc) to create Excel 97-2003
.xls and Excel 2007 .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.
The product does not require any installation and it does not use any third party
tools. It's just an assembly that you can directly link with your .NET application.
The library is also designed to work with ASP.NET applications running in environments
with restricted permissions like an ASP.NET application running with Medium trust
level.
The programming interface is object oriented, has a clear design, is extremely easy
to understand and use.
|
|
|
2. Installation
|
|
The Winnovative Excel Library for .NET is delivered as a zip archive and it
doesn't have an installer. You have to simply unzip the archive in a folder on the
disk. Below is a brief description of the folders from the archive.
2.1 Bin Folder
The Bin folder contains the .NET assemblies for .NET 2.0 and .NET 4.0 that you can reference in
your application and a XML files containing API documentation used by Microsoft Visual
Studio IntelliSense to offer inline documentation for classes, methods and
properties while writing code. The same documentation is available offline as a
.chm document in the Doc folder.
wnvxls.dll - is the Excel library .NET assembly that you can link in any
.NET application, either Windows Forms or ASP.NET.
wnvxls.xml - is the Excel Library API documentation used by Microsoft Visual
Studio IntelliSense.
2.1 Doc Folder
Doc folder contains the Excel Library API reference in chm and html format
and this User Guide document.
WnvExcelLibDoc.chm - contains the Excel Library API reference, with comments
for each class, method or property
HTML folder - contains the Excel Library API reference in html format
UserGuide.html - is the document you are currently reading
2.3 Samples Folder
Samples folder contains C# and VB.NET sample applications to offer you
ready to use code for ASP.NET and Windows Forms applications. The sample projects
can be loaded with Microsoft Visual Studio, each sample has separate solution files .sln
for Visual Studio 2010 and Visual Studio 2012. These solution files and can directly
opened with the corresponding version of Microsoft Visual Studio.
There is a sample application for each of the most important features of the Excel
library. In the WindowsForms folder there are Windows Forms applications
and in the AspNet folder there are web applications.
|
|
|
|
3. Requirements and Recommendations
|
|
| The recommended hardware and software resources for successfully using the 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.
|
|
Operating System: All Windows Versions
Hardware Architecture: 32-bit, 64-bit
Free RAM: 256 MB
Microsoft .NET Framework 2.0, 3.5, 4.0 or later
|
|
|
4. Excel Library API
|
|
| The Winnovative Excel Library for .NET API is fully documented in the Doc/WnvExcelLibDoc.chm
document. In order to use the library you have include the Winnovative.ExcelLib
namespace in your application.
|
|
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.
|
|
|
4.1 The ExcelWorkbook Class
|
|
The ExcelWorkbook 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.
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.
There are also ExcelWorkbook constructors accepting a password when creating
an ExcelWorkbook object from a password protected Excel document. Here
are the ExcelWorkbook class constructors:
/// Creates a workbook with a single worksheet. The default workbook
format is Excel 2003.
/// To create a workbook for a different Excel format, like Excel
2007,you can use the constructor with the Format parameter.
public ExcelWorkbook()
/// Creates a workbook with a single worksheet. The format of the
workbook is specified by the workbookFormat parameter.
/// The currently supported formats are Excel 97-2003 format and Excel
2007 format.
public ExcelWorkbook(ExcelWorkbookFormat workbookFormat)
/// Creates a workbook with a single worksheet. The format of the
workbook is specified by the workbookFormat parameter.
/// The currently supported formats are Excel 97-2003 format and Excel
2007 format.
public ExcelWorkbook(ExcelWorkbookFormat workbookFormat, ExcelWorkbookDefaultSettings defaultSettings)
/// Opens a workbook from the specified Excel file
public ExcelWorkbook(string excelFileName)
/// Opens a workbook from the specified Excel file with the specified
password
public ExcelWorkbook(string excelFileName, string openPassword)
/// Opens a workbook from the specified Excel file with the specified
password
public ExcelWorkbook(string excelFileName, string openPassword, ExcelWorkbookDefaultSettings defaultSettings)
/// Opens a workbook from the specified Excel stream
public ExcelWorkbook(Stream excelStream)
/// Opens a workbook from the specified Excel stream with the specified
password
public ExcelWorkbook(Stream excelStream, string openPassword)
/// Opens a workbook from the specified Excel stream with the specified
password
public ExcelWorkbook(Stream excelStream, string openPassword, ExcelWorkbookDefaultSettings defaultSettings)
The code below shows how to create a workbook in the Excel 97-2003 format (.xls
format):
ExcelWorkbook workbook = new ExcelWorkbook(ExcelWorkbookFormat.Xls_2003);
The code below shows how to create a workbook in the Excel 2007 format (.xlsx format):
ExcelWorkbook workbook = new ExcelWorkbook(ExcelWorkbookFormat.Xlsx_2007);
The ExcelWorkbook class offers access to the Worksheets collection, global
styles collection, named ranges collection, security features, document properties,
etc. For example you can add a new worksheet to the workbook using the AddWorksheet()
method of the ExcelWorkbook.Worksheets collection. The worksheets from collection
can be accessed by index or by name.
The workbook properties like author name, subject, comments or the last modification
data can be specified using the DocumentProperties property of the ExcelWorkbook
class. This property returns an object of type ExcelDocumentProperties whose properties
can be set with desired values:
// set workbook description properties
workbook.DocumentProperties.Subject = "Getting started sample";
workbook.DocumentProperties.Comments = "Getting started with Winnovative
Excel library for .NET";
|
|
|
4.2 Adding Worksheets to a Workbook
|
|
The ExcelWorkbook class offers access to its collection of worksheets
using the Worksheets property. By default, when a workbook is created, its Worksheets
collection contains a single worksheet. More worksheets can be added to the collection
using the AddWorksheet() method. You can add a worksheet with the default
name or you can specify the name of the new worksheet as parameter.Below is a sample
code to add a second worksheet with the name 'Simple Chart' to the workbook:
ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet(
"Simple Chart");
A reference to a worksheet of the workbook can be obtained using one of the two
index properties of the ExcelWorksheetsCollection. The first one returns the worksheet
at the specified zero based index and the second one returns the worksheet with
the specified name:
ExcelWorksheet secondWorksheet = workbook.Worksheets[1];
ExcelWorksheet secondWorksheet = workbook.Worksheets["Simple Chart"];
|
|
|
4.3 Accessing Cells and Ranges
|
|
A worksheet can be viewed as a collection of cells or ranges of cells. With 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.
Similarly, a range of cells can be referenced in A1 notation by specifying the top
left cell and the bottom row cell separated by a ':' character and it can be referenced
with column and row indexes by specifying the row and column indexes of the top
left cell and the bottom row cell. Examples:
// the top left cell of the worksheet in A1 notation
ExcelRange firstCell = worksheet["A1"];
// the top left cell of the worksheet referenced by row and columns
indexes
ExcelRange firstCell = worksheet[1,1];
// a range of 4 cells from the top left corner of the worksheet in
A1 notation
ExcelRange firstRange = worksheet["A1:B2"];
// a range of 4 cells from the top left corner of the worksheet
// referenced by row and column indexes
ExcelRange firstRange = worksheet[1, 1, 2, 2];
The 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:
ExcelNamedRange workbookNamedRange = workbook.NamedRanges.AddNamedRange(worksheet["A1"], "FirstCell");
ExcelNamedRange worksheetNamedRange = worksheet.NamedRanges.AddNamedRange(worksheet["A1"], "FirstCell");
|
|
|
4.4 Setting Values and Formatting
|
|
An Excel worksheet can contain data of various types like number values, date and
time values, texts, boolean values, etc. These type of values can be assigned to
a cell or a range of cells using the Value property of the ExcelRange
class. The library will determine Excel data type based on the .NET type of the
object assigned to the Value property. For example, if a value of .NET type System.Double
will be assigned to the Value property of the ExcelRange, the Excel type of all
the cells in the range will be set to Number and the value of the cells will be
specified value. The behavior is similar for values of System.DateTime or
System.String types.
Additionally, the ExcelRange class defines a set of properties like Text,
NumberValue or DateTimeValue which are the equivalent of the Value
property when this property is assigned with values of type System.String, System.Double
or System.DateTime.
The formatting information that will be used when the values are displayed by an
Excel viewer can be specified using the ExcelRange.Style.Number.NumberFormatString
property of the ExcelRange. It is recommended to set the formatting string for a
cell or for a range of cells after a value was assigned to that cell or range. This
ensures the formatting is correctly applied for that value.
Below there are a few code samples for setting values of various type and for applying
various formatting strings to the cells in a worksheet:
// set a text value
worksheet["A7"].Value = "This is a string
value assigned with 'Value' property";
worksheet["A8"].Text = "This is a string
value assigned with 'Text' property";
// set a number value
double numberValue = 1234567.809;
worksheet["A16"].Value = numberValue;
worksheet["A16"].Style.Number.NumberFormatString = "#,##0.00";
// set a date value
string dateFormatString = "m/d/yyyy";
worksheet["A32"].Value = DateTime.Now;
worksheet["A32"].Style.Number.NumberFormatString = dateFormatString;
|
|
|
4.5 Defining Formulas
|
|
|
The 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 ExcelRange.Formula property of the ExcelRange
class. An array formula string can be assigned to a cell or range using the ExcelRange.FormulaArray
property of the ExcelRange class.
The Formula and FormulaArray properties assume the formulas are expressed in A1
notation. For assigning a formula in R1C1 notation the corresponding FormulaR1C1
and FormulaArrayR1C1 can be used.
The sample code below shows how to assign various formulas to the cells of a worksheet:
// the excel viewer will calculate the sum of the numbers in the range
C7:G7
// and will assign the result to C9 cell
worksheet["C9"].Formula = "=SUM(C7:G7)";
// the excel viewer will calculate the minimum value in the range
C7:G7
// and will assign the result to C9 cell
worksheet["C9"].Formula = "=MIN(C7:G7)";
|
|
|
4.6 Setting Cell Styles
|
|
|
The style of a cell or a range of cells be accessed by the ExcelRange.Style property.
This property can be assigned with a global style defined at the workbook level
or the ExcelRange.Style property can be customized directly.
When many cells or ranges of cells have the same style is more efficient and more
elegant to define a global style at workbook level to be assigned to cells and ranges.
The code sample below is an example for this:
// Add a style used for all the cells containing the hello world text
ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
helloWorldStyle.Font.Size = 14;
helloWorldStyle.Font.Bold = true;
helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;
if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
{
helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
}
else
{
helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
}
worksheet["C5"].Text = "Hello World !!!";
// set the 'HelloWorldStyle' for the cell
worksheet["C5"].Style = helloWorldStyle;
The style can be also customized inline:
worksheet["A21"].Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
worksheet["A21"].Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Top;
worksheet["A21"].Style.Font.Bold = true;
worksheet["A21"].Style.Font.Color = Color.Red;
worksheet["A21"].Value = "Top Left";
|
|
4.7 Interactive Features
|
|
|
Using the Winnovative Excel Library for .NET you can add hyperlinks and comments
to a worksheet.
A hyperlink can be a link to a web page, a mailto link, a link to a file or folder
in the local system or network or a link to a range from a worksheet. A hyperlink
is represented in library by the class ExcelHyperlink. An object of this type can
be obtained from the Hyperlinks collection of the worksheet. A new object can be
added to this collection using the AddHyperlink property of the ExcelHyperlinksCollection
or an existing object can be retrieved from collection by its zero based index in
collection.
The sample code below shows how to add a hyperlink to a web page in a worksheet:
// create a hyperlink to the product website
ExcelRange websiteLinkSource = worksheet[25, 1];
string websiteUrl = "http://www.winnovative-software.com";
ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl);
websiteHyperlink.Text = "Visit product website";
websiteHyperlink.ToolTip = "Visit product website";
The code sample below shows how to add a link to a range from another worksheet:
// create a named range used as target for the link to second worksheet
ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet["A1"], "SecondWorksheet");
ExcelRange worksheetLinkSource = worksheet[23, 1];
ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, "SecondWorksheet");
secondWorksheetLink.Text = "Go To Next Worksheet";
secondWorksheetLink.ToolTip = "Go To Next Worksheet";
A comment can be easily assigned to a cell or a range of cells using the ExcelRange.AddComment()
method as in the sample code below:
worksheet["A9"].AddComment("Calculate the
sum of the numbers in the range C7:G7");
|
|
4.8 Adding Images to a Worksheet
|
|
|
Using the 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:
// get a System.Drawing.Image object
string demoImagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
System.Drawing.Image demoImage = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "demo_250x300.jpg"));
// add an image specifing the top left corner
ExcelPicture demoExcelPicture = worksheet.Pictures.AddPicture(1, 7, demoImage);
// get the coordinates of the image
int leftColumnIndex = demoExcelPicture.LeftColumnIndex;
int topRowIndex = demoExcelPicture.TopRowIndex;
int rightColumnIndex = demoExcelPicture.RightColumnIndex;
int bottomRowIndex = demoExcelPicture.BottomRowIndex;
string excelPictureSumary = String.Format("Left
Column: {0}, Top Row: {1}, Right Column: {2}, Bottom Row: {3}",
leftColumnIndex, topRowIndex, rightColumnIndex, bottomRowIndex);
|
|
4.9 Adding Chart Shapes and Chart Worksheets
|
|
|
The 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
The library is able to generate the chart series from the data source range or the
chart series can be manually defined.
The code sample below shows how to add a chart with automatically defined series:
#region ADD A CHART WITH AUTOMATICALLY GENERATED SERIES TO THE WORKSHEET
ExcelRange dataSourceRange = worksheet["B7:G12"];
bool seriesDataByRows = rbSeriesByRows.Checked;
ExcelChart chart = worksheet.Charts.AddChart(chartType, dataSourceRange, seriesDataByRows, 2, 16, 8, 33);
// set chart title
chart.Title.Text = "Product Units Sold per Quarter - Auto Generated
Series";
chart.Title.Font.Size = 12;
chart.Title.Font.Color = Color.DarkBlue;
// set chart area style
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
chart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;
chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
}
// set value axis title
chart.ValueAxis.Title.Text = "Units sold";
chart.ValueAxis.Title.Font.Size = 10;
chart.ValueAxis.Title.Font.Bold = true;
// set value axis text style
chart.ValueAxis.Font.Size = 8;
chart.ValueAxis.Font.Bold = false;
chart.ValueAxis.Font.Italic = true;
chart.ValueAxis.ShowVerticalTitleText();
// set category axis title
chart.CategoryAxis.Title.Text = "Analyzed products";
chart.CategoryAxis.Title.Font.Size = 10;
chart.CategoryAxis.Title.Font.Bold = true;
// set category axis text style
chart.CategoryAxis.Font.Size = 8;
chart.CategoryAxis.Font.Bold = false;
chart.CategoryAxis.Font.Italic = true;
// set chart legend style
chart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
chart.Legend.Font.Size = 8;
chart.Legend.Font.Bold = true;
The code sample below shows how to add a chart with manually defined series:
#region ADD A CHART WITH CUSTOM SERIES TO THE WORKSHEET
ExcelChart customSeriesChart = worksheet.Charts.AddChart(chartType, 2, 36, 8, 59);
// set chart data table
customSeriesChart.ShowDataTable = true;
customSeriesChart.DataTable.ShowLegendKey = true;
// set chart title
customSeriesChart.Title.Text = "Product Units Sold per Quarter - Custom
Series";
customSeriesChart.Title.Font.Size = 12;
customSeriesChart.Title.Font.Color = Color.DarkBlue;
// create the category names range
ExcelRange categoryNamesRange = worksheet["C7:E7"];
// Add chart series
// add first series for the first quarter sales
ExcelChartSeries firstQuarterSeries = customSeriesChart.Series.AddSeries("First
Quarter Sales");
firstQuarterSeries.ChartType = chartType;
firstQuarterSeries.CategoryNamesRange = worksheet["C7:E7"];
firstQuarterSeries.ValuesRange = worksheet["C8:E8"];
firstQuarterSeries.DataPoints.All.Label.ContainsValue = true;
firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
firstQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;
//add second series for second quarter sales
ExcelChartSeries secondQuarterSeries = customSeriesChart.Series.AddSeries("Second
Quarter Sales");
secondQuarterSeries.ChartType = chartType;
secondQuarterSeries.CategoryNamesRange = categoryNamesRange;
secondQuarterSeries.ValuesRange = worksheet["C9:E9"];
secondQuarterSeries.DataPoints.All.Label.ContainsValue = true;
secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
secondQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;
// add third series for fourth quarter sales
ExcelChartSeries fourthQuarterSeries = customSeriesChart.Series.AddSeries("Fourth
Quarter Sales");
fourthQuarterSeries.ChartType = chartType;
fourthQuarterSeries.CategoryNamesRange = categoryNamesRange;
fourthQuarterSeries.ValuesRange = worksheet["C11:E11"];
fourthQuarterSeries.DataPoints.All.Label.ContainsValue = true;
fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Size = 8;
fourthQuarterSeries.DataPoints.All.Label.LabelFormat.Font.Italic = true;
// set chart area style
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
customSeriesChart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
customSeriesChart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
customSeriesChart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
customSeriesChart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
customSeriesChart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;
customSeriesChart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
customSeriesChart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
}
// set value axis title
customSeriesChart.ValueAxis.Title.Text = "Units sold";
customSeriesChart.ValueAxis.Title.Font.Size = 10;
customSeriesChart.ValueAxis.Title.Font.Bold = true;
// set value axis text style
customSeriesChart.ValueAxis.Font.Size = 8;
customSeriesChart.ValueAxis.Font.Bold = false;
customSeriesChart.ValueAxis.Font.Italic = true;
customSeriesChart.ValueAxis.ShowVerticalTitleText();
// set category axis text style
customSeriesChart.CategoryAxis.Font.Size = 8;
customSeriesChart.CategoryAxis.Font.Bold = false;
customSeriesChart.CategoryAxis.Font.Italic = true;
// set chart legend style
customSeriesChart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
customSeriesChart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
customSeriesChart.Legend.Font.Size = 8;
customSeriesChart.Legend.Font.Bold = true;
#endregion
The current version of the library has limitation in reading Excel 2007 charts.
Excel 97-2003 charts can be both read and written by the library but Excel 2007
charts can only be written.
|
|
4.10 Importing Data from CSV
|
|
|
The 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:
// The data from CSV file (numbers, dates, etc) was saved for the
English US culture and
// the CSV parser uses the current thread culture
// Temporary set the en-US culture for the current thread and restore
the old culture after the CSV file was loaded
System.Globalization.CultureInfo oldCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// get the Excel workbook format
ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;
// create the CSV stream
string dataFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Data\awemployees.csv");
System.IO.FileStream csvDataStream = new System.IO.FileStream(dataFilePath, System.IO.FileMode.Open);
// load the data from the CSV stream to a new workbook in the specified
format
ExcelWorkbook workbook = ExcelWorkbook.LoadFromCsv(csvDataStream, Encoding.GetEncoding("windows-1252"), ",", 5, 1, workbookFormat, null);
// close the CSV stream
csvDataStream.Close();
// restore the current culture
System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture;
|
|
4.11 Importing Data from a DataTable Object
|
|
|
The 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 :
// load an existing Excel file to a temporary workbook
// create the Excel stream
string dataFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Data\awemployees.xls");
System.IO.FileStream sourceXlsDataStream = new System.IO.FileStream(dataFilePath, System.IO.FileMode.Open);
ExcelWorkbook tempWorkbook = new ExcelWorkbook(sourceXlsDataStream);
ExcelWorksheet tempWorksheet = tempWorkbook.Worksheets[0];
// get the data from the used range of the temporary workbook to a
.NET DataTable object
DataTable exportedDataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, true);
//close the temporary workbook
tempWorkbook.Close();
//close the data stream
sourceXlsDataStream.Close();
// Create the workbook in which the data from the DataTable will be
loaded
ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;
// create the workbook in the desired format with a single worksheet
ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);
// get the first worksheet in the workbook
ExcelWorksheet worksheet = workbook.Worksheets[0];
// set the default worksheet name
worksheet.Name = "Data Loaded from a DataTable";
// load data from DataTable into the worksheet
worksheet.LoadDataTable(exportedDataTable, 5, 1, true);
|
|
4.12 Data Validation
|
|
|
The 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:
#region Add Data Validation
#region Validate data from a list
worksheet["A5:E5"].Merge();
worksheet["A5:E5"].Style = textMessageStyle;
worksheet["A5:E5"].Value = "Select a value
from the list:";
// set the range to be validated
worksheet["G5"].Style = dataValidationStyle;
worksheet["G5"].ColumnWidthInChars = 25;
worksheet["G5"].AddComment("Click this cell
to select a value from list.");
ExcelDataValidator listValidator = worksheet["G5"].DataValidator;
listValidator.AllowedDataType = ExcelDataValidatorDataType.List;
listValidator.AllowedValues = new string[] { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET" };
listValidator.InputMessageText = "Select a value from the list";
listValidator.ShowInputMessage = true;
#endregion
#region Validate a whole number between 0 and 10
worksheet["A7:E7"].Merge();
worksheet["A7:E7"].Style = textMessageStyle;
worksheet["A7:E7"].Value = "Enter a whole
number between 0 and 10 :";
// set the range to be validated
worksheet["G7"].Style = dataValidationStyle;
worksheet["G7"].ColumnWidthInChars = 25;
worksheet["G7"].AddComment("Click this cell
to enter a whole number.");
// Data Validation for Numbers
ExcelDataValidator wholeNumberValidator = worksheet["G7"].DataValidator;
wholeNumberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber;
wholeNumberValidator.Operator = ExcelDataValidatorOperator.Between;
wholeNumberValidator.Value1 = 0;
wholeNumberValidator.Value2 = 10;
wholeNumberValidator.ErrorAlertText = "A number between 0 to 10 is
expected";
wholeNumberValidator.ShowErrorAlert = true;
wholeNumberValidator.ErrorAlertTitle = "Whole Number Validation Error";
wholeNumberValidator.InputMessageText = "Enter a whole number between
0 and 10";
wholeNumberValidator.ShowInputMessage = true;
#endregion
#region Validate a date between 01/01/2000 and 12/31/2009
worksheet["A9:E9"].Merge();
worksheet["A9:E9"].Style = textMessageStyle;
worksheet["A9:E9"].Value = "Enter a date
between 01/01/2000 and 12/31/2009 :";
// set the range to be validated
worksheet["G9"].Style = dataValidationStyle;
worksheet["G9"].ColumnWidthInChars = 25;
worksheet["G9"].Style.Number.NumberFormatString = "m/d/yyyy";
worksheet["G9"].Value = new DateTime(2008, 12, 15); // default value
worksheet["G9"].AddComment("Double-Click
this cell to enter a date in local format.");
ExcelDataValidator dateValidator = worksheet["G9"].DataValidator;
dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date;
dateValidator.Operator = ExcelDataValidatorOperator.Between;
dateValidator.Value1 = new DateTime(2000, 1, 1);
dateValidator.Value2 = new DateTime(2009, 12, 31); ;
dateValidator.ErrorAlertText = "A date between 01/01/2000 and 12/31/2009
is expected";
dateValidator.ShowErrorAlert = true;
dateValidator.ErrorAlertTitle = "Date Validation Error";
dateValidator.InputMessageText = "Enter a date between 01/01/2000
and 12/31/2009";
dateValidator.ShowInputMessage = true;
#endregion
#region Validate the length of a text
worksheet["A11:E11"].Merge();
worksheet["A11:E11"].Style = textMessageStyle;
worksheet["A11:E11"].Value = "Enter a text
with length between 2 and 5 chars:";
// set the range to be validated
worksheet["G11"].Style = dataValidationStyle;
worksheet["G11"].ColumnWidthInChars = 25;
worksheet["G11"].AddComment("Click this cell
to enter a text.");
ExcelDataValidator textLengthValidator = worksheet["G11"].DataValidator;
textLengthValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength;
textLengthValidator.Operator = ExcelDataValidatorOperator.Between;
textLengthValidator.Value1 = 2;
textLengthValidator.Value2 = 5;
textLengthValidator.ErrorAlertTitle = "Text Length Validation Error";
textLengthValidator.ErrorAlertText = "A text with length between 2
and 5 chars is expected";
textLengthValidator.ShowErrorAlert = true;
textLengthValidator.InputMessageText = "Enter a text with length between
2 and 5 characters";
textLengthValidator.ShowInputMessage = true;
#endregion
#endregion
|
|
4.13 Worksheet Page Setup
|
|
|
The 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:
#region WORKSHEET PAGE SETUP
// set worksheet paper size and orientation, margins, header and footer
worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
worksheet.PageSetup.LeftMargin = 1;
worksheet.PageSetup.RightMargin = 1;
worksheet.PageSetup.TopMargin = 1;
worksheet.PageSetup.BottomMargin = 1;
// add header and footer
//display a logo image in the left part of the header
string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "logo.jpg"));
worksheet.PageSetup.LeftHeaderFormat = "&G";
worksheet.PageSetup.LeftHeaderPicture = logoImg;
// display worksheet name in the right part of the header
worksheet.PageSetup.RightHeaderFormat = "&A";
// add worksheet header and footer
// display the page number in the center part of the footer
worksheet.PageSetup.CenterFooterFormat = "&P";
// display the workbook file name in the left part of the footer
worksheet.PageSetup.LeftFooterFormat = "&F";
// display the current date in the right part of the footer
worksheet.PageSetup.RightFooterFormat = "&D";
#endregion
|
|
4.14 Security Features
|
|
|
Using 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.
The ExcelWorkbook.DocumentSecurity property allows you to set an open password and
a write password for the workbook. The DocumentSecurity.OpenWorkbookPassword property
can be used to set the open password for the workbook required by an Excel viewer
before opening the workbook and the DocumentSecurity.WriteWorkbookPassword property
can be used to specify a password required by an Excel editor in order to modify
the workbook. The code sample below show how these properties can be set:
//set a password required when the workbook is opened by an Excel
viewer.
workbook.DocumentSecurity.OpenWorkbookPassword = "open";
//set a password required by an Excel editor in order to modify the
workbook.
workbook.DocumentSecurity.WriteWorkbookPassword = "write";
The ExcelWorkbook.DocumentSecurity.ProtectWorkbook() method allows you to protect
the workbook structure and windows. Optionally a password can be specified. This
password is required in order to remove the protection. The statement below shows
how to protect the workbook structure and windows:
workbook.DocumentSecurity.ProtectWorkbook(true, true);
Another security feature allows you to protect the worksheet data. This feature
can be accessed using the ExcelWorksheet.WorksheetSecurity property. The code below
will use the default protection flags to protect the worksheet data
worksheet.WorksheetSecurity.ProtectWorksheet(ExcelProtectionAllowFlags.AllowDefault );
|
|
4.15 Code Sample
|
|
|
Below there is a full code sample illustrating the main features of the 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.
protected void lnkBtnCreateWorkbook_Click(object sender, EventArgs e)
{
// get the Excel workbook format
ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;
// create the workbook in the desired format with a single worksheet
ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);
// set the license key before saving the workbook
workbook.LicenseKey = "RW51ZXZ0ZXVldGt1ZXZ0a3R3a3x8fHw=";
// set workbook description properties
workbook.DocumentProperties.Subject = "Getting started sample";
workbook.DocumentProperties.Comments = "Getting started with Winnovative
Excel library for .NET";
#region CREATE CUSTOM WORKBOOK STYLES
#region Add a style used for the cells in the worksheet title area
ExcelCellStyle titleStyle = workbook.Styles.AddStyle("WorksheetTitleStyle");
// center the text in the title area
titleStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
titleStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
// set the title area borders
titleStyle.Borders[ExcelCellBorderIndex.Bottom].Color = Color.Green;
titleStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Medium;
titleStyle.Borders[ExcelCellBorderIndex.Top].Color = Color.Green;
titleStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Medium;
titleStyle.Borders[ExcelCellBorderIndex.Left].Color = Color.Green;
titleStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Medium;
titleStyle.Borders[ExcelCellBorderIndex.Right].Color = Color.Green;
titleStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Medium;
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
// set the solid fill for the title area range with a custom color
titleStyle.Fill.FillType = ExcelCellFillType.SolidFill;
titleStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
// set the gradient fill for the title area range with a custom color
titleStyle.Fill.FillType = ExcelCellFillType.GradientFill;
titleStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204);
titleStyle.Fill.GradientFillOptions.Color2 = Color.White;
}
// set the title area font
titleStyle.Font.Size = 14;
titleStyle.Font.Bold = true;
titleStyle.Font.UnderlineType = ExcelCellUnderlineType.Single;
#endregion
#region Add a style used for all the cells in the index column
ExcelCellStyle indexStyle = workbook.Styles.AddStyle("IndexColumnStyle");
indexStyle.Font.Size = 12;
indexStyle.Font.Bold = false;
indexStyle.Fill.FillType = ExcelCellFillType.SolidFill;
indexStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
indexStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Thin;
indexStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Thin;
indexStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Thin;
indexStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Thin;
#endregion
#region Add a style used for all the cells in the country name column
ExcelCellStyle countryNameStyle = workbook.Styles.AddStyle("CountryNameStyle");
countryNameStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
countryNameStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
countryNameStyle.Font.Size = 12;
countryNameStyle.Font.Bold = true;
countryNameStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Dot;
countryNameStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Dot;
countryNameStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Dot;
countryNameStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Dot;
countryNameStyle.Fill.FillType = ExcelCellFillType.PatternFill;
countryNameStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_12_5_Percent;
countryNameStyle.Fill.PatternFillOptions.BackColor = Color.White;
countryNameStyle.Fill.PatternFillOptions.PatternColor = Color.Green;
#endregion
#region Add a style used for all the cells containing the hello world text
ExcelCellStyle helloWorldStyle = workbook.Styles.AddStyle("HelloWorldStyle");
helloWorldStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
helloWorldStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
helloWorldStyle.Font.Size = 14;
helloWorldStyle.Font.Bold = true;
helloWorldStyle.Borders[ExcelCellBorderIndex.Bottom].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Top].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Left].LineStyle = ExcelCellLineStyle.Hair;
helloWorldStyle.Borders[ExcelCellBorderIndex.Right].LineStyle = ExcelCellLineStyle.Hair;
if (workbook.Format == ExcelWorkbookFormat.Xls_2003)
{
helloWorldStyle.Fill.FillType = ExcelCellFillType.PatternFill;
helloWorldStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
helloWorldStyle.Fill.PatternFillOptions.BackColor = Color.White;
helloWorldStyle.Fill.PatternFillOptions.PatternColor = Color.Orange;
}
else
{
helloWorldStyle.Fill.FillType = ExcelCellFillType.GradientFill;
helloWorldStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.Vertical;
helloWorldStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 204, 153);
helloWorldStyle.Fill.GradientFillOptions.Color2 = Color.White;
}
#endregion
#endregion
// get the first worksheet in the workbook
ExcelWorksheet worksheet = workbook.Worksheets[0];
// set the default worksheet name
worksheet.Name = "Hello World";
#region WORKSHEET PAGE SETUP
// set worksheet paper size and orientation, margins, header and footer
worksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
worksheet.PageSetup.LeftMargin = 1;
worksheet.PageSetup.RightMargin = 1;
worksheet.PageSetup.TopMargin = 1;
worksheet.PageSetup.BottomMargin = 1;
// add header and footer
//display a logo image in the left part of the header
string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), @"Images");
System.Drawing.Image logoImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "logo.jpg"));
worksheet.PageSetup.LeftHeaderFormat = "&G";
worksheet.PageSetup.LeftHeaderPicture = logoImg;
// display worksheet name in the right part of the header
worksheet.PageSetup.RightHeaderFormat = "&A";
// add worksheet header and footer
// display the page number in the center part of the footer
worksheet.PageSetup.CenterFooterFormat = "&P";
// display the workbook file name in the left part of the footer
worksheet.PageSetup.LeftFooterFormat = "&F";
// display the current date in the right part of the footer
worksheet.PageSetup.RightFooterFormat = "&D";
#endregion
#region WRITE THE WORKSHEET TOP TITLE
// merge the cells in the range to create the title area
worksheet["A2:G3"].Merge();
// gets the merged range containing the top left cell of the range
ExcelRange titleRange = worksheet["A2"].MergeArea;
// set the text of title area
worksheet["A2"].Text = "Say 'Hello World'
in Different Languages";
// set a row height of 18 points for each row in the range
titleRange.RowHeightInPoints = 18;
// set the worksheet top title style
titleRange.Style = titleStyle;
#endregion
#region WRITE 'HELLO WORLD' IN DIFFERENT LANGUAGES
System.Drawing.Image usFlagImg = null;
System.Drawing.Image frFlagImg = null;
System.Drawing.Image deFlagImg = null;
System.Drawing.Image esFlagImg = null;
System.Drawing.Image ruFlagImg = null;
System.Drawing.Image itFlagImg = null;
System.Drawing.Image ptFlagImg = null;
System.Drawing.Image nlFlagImg = null;
// set the separator between index column and text column
worksheet.SetColumnWidthInChars(2, 3);
// set the country image column
worksheet.SetColumnWidthInChars(6, 5);
#region Say hello world in English
worksheet["A5"].Style = indexStyle;
worksheet["A5"].Value = 1;
worksheet["C5"].Text = "Hello World !!!";
worksheet["C5"].Style = helloWorldStyle;
worksheet["C5"].RowHeightInPoints = 19.5;
usFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "us.png"));
worksheet.Pictures.AddPicture(6, 5, usFlagImg);
worksheet[5, 6, 5, 7].StyleName = "CountryNameStyle";
worksheet[5, 7].Text = "English";
#endregion
#region Say hello world in French
worksheet["A7"].Style = indexStyle;
worksheet["A7"].Value = 2;
worksheet["C7"].Text = "Bonjour tout le monde
!!!";
worksheet["C7"].Style = helloWorldStyle;
worksheet["C7"].Style.Font.Color = Color.Blue;
worksheet["C7"].RowHeightInPoints = 19.5;
frFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "fr.png"));
worksheet.Pictures.AddPicture(6, 7, frFlagImg);
worksheet[7, 6, 7, 7].StyleName = "CountryNameStyle";
worksheet[7, 7].Text = "French";
#endregion
#region Say hello world in German
worksheet["A9"].Style = indexStyle;
worksheet["A9"].Value = 3;
worksheet["C9"].Text = "Hallo Welt";
worksheet["C9"].Style = helloWorldStyle;
worksheet["C9"].Style.Font.Color = Color.Red;
worksheet["C9"].RowHeightInPoints = 19.5;
deFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "de.png"));
worksheet.Pictures.AddPicture(6, 9, deFlagImg);
worksheet[9, 6, 9, 7].StyleName = "CountryNameStyle";
worksheet[9, 7].Text = "German";
#endregion
#region Say hello world in Spanish
worksheet["A11"].Style = indexStyle;
worksheet["A11"].Value = 4;
worksheet["C11"].Text = "Hola Mundo";
worksheet["C11"].Style = helloWorldStyle;
worksheet["C11"].Style.Font.Color = Color.Orange;
worksheet["C11"].RowHeightInPoints = 19.5;
esFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "es.png"));
worksheet.Pictures.AddPicture(6, 11, esFlagImg);
worksheet[11, 6, 11, 7].StyleName = "CountryNameStyle";
worksheet[11, 7].Text = "Spanish";
#endregion
#region Say hello world in Russian
worksheet["A13"].Style = indexStyle;
worksheet["A13"].Value = 5;
worksheet["C13"].Text = "Привет мир";
worksheet["C13"].Style = helloWorldStyle;
worksheet["C13"].Style.Font.Color = Color.Indigo;
worksheet["C13"].RowHeightInPoints = 19.5;
ruFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "ru.png"));
worksheet.Pictures.AddPicture(6, 13, ruFlagImg);
worksheet[13, 6, 13, 7].StyleName = "CountryNameStyle";
worksheet[13, 7].Text = "Russian";
#endregion
#region Say hello world in Italian
worksheet["A15"].Style = indexStyle;
worksheet["A15"].Value = 6;
worksheet["C15"].Text = "Ciao a tutti";
worksheet["C15"].Style = helloWorldStyle;
worksheet["C15"].Style.Font.Color = Color.Green;
worksheet["C15"].RowHeightInPoints = 19.5;
itFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "it.png"));
worksheet.Pictures.AddPicture(6, 15, itFlagImg);
worksheet[15, 6, 15, 7].StyleName = "CountryNameStyle";
worksheet[15, 7].Text = "Italian";
#endregion
#region Say hello world in Dutch
worksheet["A17"].Style = indexStyle;
worksheet["A17"].Value = 7;
worksheet["C17"].Text = "Hallo Wereld";
worksheet["C17"].Style = helloWorldStyle;
worksheet["C17"].Style.Font.Color = Color.Blue;
worksheet["C17"].RowHeightInPoints = 19.5;
nlFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "nl.png"));
worksheet.Pictures.AddPicture(6, 17, nlFlagImg);
worksheet[17, 6, 17, 7].StyleName = "CountryNameStyle";
worksheet[17, 7].Text = "Dutch";
#endregion
#region Say hello world in Portuguese
worksheet["A19"].Style = indexStyle;
worksheet["A19"].Value = 8;
worksheet["C19"].Text = "Olá Mundo";
worksheet["C19"].Style = helloWorldStyle;
worksheet["C19"].Style.Font.Color = Color.Red;
worksheet["C19"].RowHeightInPoints = 19.5;
ptFlagImg = System.Drawing.Image.FromFile(System.IO.Path.Combine(imagesPath, "pt.png"));
worksheet.Pictures.AddPicture(6, 19, ptFlagImg);
worksheet[19, 6, 19, 7].StyleName = "CountryNameStyle";
worksheet[19, 7].Text = "Portuguese";
#endregion
// autofit the index column width
worksheet.AutofitColumn(1);
// autofit the Hello World text column
worksheet.AutofitColumn(3);
// autofit the country name column
worksheet.AutofitColumn(7);
#endregion
#region WRITE WORKSHEET CREATION TIMESTAMP
worksheet[28, 1, 28, 3].Merge();
ExcelRange timestampTextRange = worksheet[28, 1].MergeArea;
timestampTextRange.Style.Font.Bold = true;
worksheet[28, 1].Text = "Workbook Creation Date & Time:";
worksheet[28, 4, 28, 7].Merge();
ExcelRange timestampDateRange = worksheet[28, 4].MergeArea;
timestampDateRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Left;
timestampDateRange.Style.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss";
timestampDateRange.Style.Font.Color = Color.DarkBlue;
timestampDateRange.Style.Font.Bold = true;
worksheet[28, 4].Value = DateTime.Now;
#endregion
#region ADD A SECOND WORKSHEET TO THE WORKBOOK
ExcelWorksheet secondWorksheet = workbook.Worksheets.AddWorksheet("Simple
Chart");
#region SECOND WORKSHEET PAGE SETUP
// set worksheet paper size and orientation, margins, header and footer
secondWorksheet.PageSetup.PaperSize = ExcelPagePaperSize.PaperA4;
secondWorksheet.PageSetup.Orientation = ExcelPageOrientation.Landscape;
secondWorksheet.PageSetup.LeftMargin = 1;
secondWorksheet.PageSetup.RightMargin = 1;
secondWorksheet.PageSetup.TopMargin = 1;
secondWorksheet.PageSetup.BottomMargin = 1;
// add header and footer
//display a logo image in the left part of the header
secondWorksheet.PageSetup.LeftHeaderFormat = "&G";
secondWorksheet.PageSetup.LeftHeaderPicture = logoImg;
// display worksheet name in the right part of the header
secondWorksheet.PageSetup.RightHeaderFormat = "&A";
// add worksheet header and footer
// display the page number in the center part of the footer
secondWorksheet.PageSetup.CenterFooterFormat = "&P";
// display the workbook file name in the left part of the footer
secondWorksheet.PageSetup.LeftFooterFormat = "&F";
// display the current date in the right part of the footer
secondWorksheet.PageSetup.RightFooterFormat = "&D";
#endregion
#region WRITE THE SECOND WORKSHEET TOP TITLE
// merge the cells in the range to create the title area
secondWorksheet["A2:G3"].Merge();
// gets the merged range containing the top left cell of the range
ExcelRange secondTitleRange = secondWorksheet["A2"].MergeArea;
// set the text of title area
secondWorksheet["A2"].Text = "Simple Chart
Demo";
// set a row height of 18 points for each row in the range
secondTitleRange.RowHeightInPoints = 18;
// set the worksheet top title style
secondTitleRange.Style = titleStyle;
#endregion
#region CREATE DATA TABLE FOR THE CHART IN SECOND WORKSHEET
ExcelCellStyle chartValuesStyle = workbook.Styles.AddStyle("ChartValuesStyle");
chartValuesStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
chartValuesStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
chartValuesStyle.Font.Color = Color.Black;
chartValuesStyle.Font.Bold = true;
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
chartValuesStyle.Fill.FillType = ExcelCellFillType.PatternFill;
chartValuesStyle.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_6_25_Percent;
chartValuesStyle.Fill.PatternFillOptions.BackColor = Color.White;
chartValuesStyle.Fill.PatternFillOptions.PatternColor = Color.Green;
}
else
{
chartValuesStyle.Fill.FillType = ExcelCellFillType.GradientFill;
chartValuesStyle.Fill.GradientFillOptions.Color1 = Color.LightGreen;
chartValuesStyle.Fill.GradientFillOptions.Color2 = Color.White;
chartValuesStyle.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown;
}
// set the products tile row text and style
secondWorksheet["C6:G6"].Merge();
secondWorksheet["C6"].Text = "Analyzed Products";
ExcelRange productsTitle = secondWorksheet["C6"].MergeArea;
productsTitle.RowHeightInPoints = 21;
productsTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
productsTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
productsTitle.Style.Font.Size = 12;
productsTitle.Style.Font.Bold = true;
productsTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
productsTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
productsTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
productsTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Orange;
// set the quarters title column text and style
secondWorksheet["A8:A11"].Merge();
secondWorksheet["A8"].Text = "Units Sold
per Quarter";
ExcelRange quartersTitle = secondWorksheet["A8"].MergeArea;
// set vertical orientation for the text from bottom to top
quartersTitle.Style.Alignment.Orientation = 90;
// wrap text inside the merged range
quartersTitle.Style.Alignment.WrapText = true;
quartersTitle.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
quartersTitle.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
quartersTitle.Style.Font.Size = 12;
quartersTitle.Style.Font.Bold = true;
quartersTitle.Style.Fill.FillType = ExcelCellFillType.PatternFill;
quartersTitle.Style.Fill.PatternFillOptions.PatternType = ExcelCellFillPatternType.Gray_25_Percent;
quartersTitle.Style.Fill.PatternFillOptions.BackColor = Color.White;
quartersTitle.Style.Fill.PatternFillOptions.PatternColor = Color.Green;
// set the style for the product names row
ExcelRange productNamesRange = secondWorksheet["B7:G7"];
productNamesRange.RowHeightInPoints = 21;
productNamesRange.ColumnWidthInChars = 16;
productNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
productNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
productNamesRange.Style.Font.Bold = true;
productNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
productNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
// set the styles for the quarter names range
ExcelRange quarterNamesRange = secondWorksheet["B8:B11"];
quarterNamesRange.RowHeightInPoints = 16.5;
quarterNamesRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
quarterNamesRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
quarterNamesRange.Style.Font.Bold = true;
quarterNamesRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
quarterNamesRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
// set the row height for the Yearly Total row
ExcelRange yearlyTotalRange = secondWorksheet["B12:G12"];
yearlyTotalRange.RowHeightInPoints = 21;
yearlyTotalRange.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center;
yearlyTotalRange.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
yearlyTotalRange.Style.Font.Color = Color.Blue;
yearlyTotalRange.Style.Font.Bold = true;
yearlyTotalRange.Style.Fill.FillType = ExcelCellFillType.SolidFill;
yearlyTotalRange.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(204, 255, 255);
// set the series name column
secondWorksheet["B8"].Text = "First Quarter";
secondWorksheet["B9"].Text = "Second Quarter";
secondWorksheet["B10"].Text = "Third Quarter";
secondWorksheet["B11"].Text = "Fourth Quarter";
secondWorksheet["B12"].Text = "Yearly Total";
// set the categories name row
secondWorksheet["C7"].Text = "HTML to PDF";
secondWorksheet["D7"].Text = "PDF Merge";
secondWorksheet["E7"].Text = "PDF Security";
secondWorksheet["F7"].Text = "Web Chart";
secondWorksheet["G7"].Text = "Excel Library";
// set the chart value style
ExcelRange chartValuesRange = secondWorksheet["C8:G11"];
chartValuesRange.Style = chartValuesStyle;
// set the chart values
secondWorksheet["C8"].Value = 1000;
secondWorksheet["D8"].Value = 500;
secondWorksheet["E8"].Value = 200;
secondWorksheet["F8"].Value = 400;
secondWorksheet["G8"].Value = 800;
secondWorksheet["C9"].Value = 850;
secondWorksheet["D9"].Value = 680;
secondWorksheet["E9"].Value = 350;
secondWorksheet["F9"].Value = 230;
secondWorksheet["G9"].Value = 640;
secondWorksheet["C10"].Value = 950;
secondWorksheet["D10"].Value = 450;
secondWorksheet["E10"].Value = 175;
secondWorksheet["F10"].Value = 350;
secondWorksheet["G10"].Value = 520;
secondWorksheet["C11"].Value = 500;
secondWorksheet["D11"].Value = 700;
secondWorksheet["E11"].Value = 250;
secondWorksheet["F11"].Value = 460;
secondWorksheet["G11"].Value = 320;
secondWorksheet["C12"].Formula = "=SUM(C8:C11)";
secondWorksheet["D12"].Formula = "=SUM(D8:D11)";
secondWorksheet["E12"].Formula = "=SUM(E8:E11)";
secondWorksheet["F12"].Formula = "=SUM(F8:F11)";
secondWorksheet["G12"].Formula = "=SUM(G8:G11)";
// auto fit the width of the quarter names column
secondWorksheet["B7"].AutofitColumns();
#endregion
#region ADD A CHART TO THE SECOND WORKSHEET
ExcelRange dataSourceRange = secondWorksheet["B7:G12"];
ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.ColumnClustered, dataSourceRange, true, 2, 15, 8, 32);
chart.ShowDataTable = false;
// set chart title
chart.Title.Text = "Product Units Sold per Quarter";
chart.Title.Interior.FillType = ExcelShapeFillType.NoFill;
chart.Title.Font.Size = 12;
chart.Title.Font.Color = Color.DarkBlue;
// set chart area style
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
{
chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153);
chart.PlotArea.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.PlotArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
}
else
{
chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill;
chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset;
chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat;
chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill;
chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery;
}
// set value axis title
chart.ValueAxis.Title.Text = "Units sold";
chart.ValueAxis.Title.Font.Size = 10;
chart.ValueAxis.Title.Font.Bold = true;
// set value axis text style
chart.ValueAxis.Font.Size = 8;
chart.ValueAxis.Font.Bold = false;
chart.ValueAxis.Font.Italic = true;
chart.ValueAxis.ShowVerticalTitleText();
// set category axis title
chart.CategoryAxis.Title.Text = "Analyzed products";
chart.CategoryAxis.Title.Font.Size = 10;
chart.CategoryAxis.Title.Font.Bold = true;
// set category axis text style
chart.CategoryAxis.Font.Size = 8;
chart.CategoryAxis.Font.Bold = false;
chart.CategoryAxis.Font.Italic = true;
// set chart legend style
chart.Legend.Interior.FillType = ExcelShapeFillType.SolidFill;
chart.Legend.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204);
chart.Legend.Font.Size = 8;
chart.Legend.Font.Bold = true;
// show a label with total number of units sold in a year
chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Size = 8;
chart.Series["Yearly Total"].DataPoints.All.Label.LabelFormat.Font.Italic = true;
#endregion
#endregion
#region CREATE HYPERLINKS
// create the merged range where to add the link to the second worksheet
worksheet[23, 1, 23, 3].Merge();
// create a named range used as target for the link to second worksheet
ExcelNamedRange worksheetLinkTarget = workbook.NamedRanges.AddNamedRange(secondWorksheet["A1"], "SecondWorksheet");
ExcelRange worksheetLinkSource = worksheet[23, 1];
ExcelHyperlink secondWorksheetLink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Range, worksheetLinkSource, "SecondWorksheet");
secondWorksheetLink.Text = "Go To Next Worksheet";
secondWorksheetLink.ToolTip = "Go To Next Worksheet";
// creat the merged range where to add the link to the product website
worksheet[25, 1, 25, 3].Merge();
// create a hyperlink to the product website
ExcelRange websiteLinkSource = worksheet[25, 1];
string websiteUrl = "http://www.winnovative-software.com";
ExcelHyperlink websiteHyperlink = worksheet.Hyperlinks.AddHyperlink(ExcelHyperlinkType.Url, websiteLinkSource, websiteUrl);
websiteHyperlink.Text = "Visit product website";
websiteHyperlink.ToolTip = "Visit product website";
#endregion
// SAVE THE WORKBOOK
// Save the Excel document in the current HTTP response stream
string outFileName = workbookFormat == ExcelWorkbookFormat.Xls_2003 ? "GettingStarted.xls" : "GettingStarted.xlsx";
System.Web.HttpResponse httpResponse = System.Web.HttpContext.Current.Response;
// Prepare the HTTP response stream for saving the Excel document
// Clear any data that might have been previously buffered in the
output stream
httpResponse.Clear();
// Set output stream content type for Excel 97-2003 (.xls) or Excel
2007 (.xlsx)
if (workbookFormat == ExcelWorkbookFormat.Xls_2003)
httpResponse.ContentType = "Application/x-msexcel";
else
httpResponse.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Add the HTTP header to announce the Excel document either as an
attachment or inline
httpResponse.AddHeader("Content-Disposition", String.Format("attachment; filename={0}", outFileName));
// Save the workbook to the current HTTP response output stream
// and close the workbook after save to release all the allocated
resources
try
{
workbook.Save(httpResponse.OutputStream);
}
catch (Exception ex)
{
// report any error that might occur during save
Session["ErrorMessage"] = ex.Message;
Response.Redirect("ErrorPage.aspx");
}
finally
{
// close the workbook and release the allocated resources
workbook.Close();
#region Dispose the Image object
// release the flag images
if (usFlagImg != null)
usFlagImg.Dispose();
if (frFlagImg != null)
frFlagImg.Dispose();
if (deFlagImg != null)
deFlagImg.Dispose();
if (esFlagImg != null)
esFlagImg.Dispose();
if (ruFlagImg != null)
ruFlagImg.Dispose();
if (itFlagImg != null)
itFlagImg.Dispose();
if (ptFlagImg != null)
ptFlagImg.Dispose();
if (nlFlagImg != null)
nlFlagImg.Dispose();
if (logoImg != null)
logoImg.Dispose();
#endregion
}
// End the response and finish the execution of this page
httpResponse.End();
}
|
|
|
5. Licensing
|
|
A unique license key string is generated for each purchase. In order to unlock the
Winnovative Excel Library for .NET product you have to set the LicenseKey
property of the ExcelWorkbook class with the license key
string you have received after the product purchase before saving the Excel workbook.
The license key contains the information about the purchased product like the product
version and license type and is uniquely associated with an order ID. More details
about the license types and pricing can be found on the Buy Now page of our website.
|
|
|
|
Copyright by Winnovative
Software Solutions
|
|
