.
ExcelApplication Guides
Addressing Cells
There are three ways to address cells, areas, and ranges using ExcelApplication:
Formulas
ExcelApplication supports all math, string, boolean, time, statistical, database, lookup and reference formulas or functions that are part of Excel. ExcelWriter does not calculate formulas at run-time; all formulas are calculated when the workbook is opened in Excel.
Styles in ExcelWriter
When a style is set, the object to which the style is assigned acquires all of that style's properties, including font properties and number formatting. When a style is applied, only the differences between the new style and existing style properties (assigned through the ExcelWriter API or in Microsoft Excel) will take effect.
Number Formats
A number format is a string that is used to format dates or numbers. For example, if you enter the value 0.563 in cell A4 and the number format string assigned to cell A4 is "0.00%", the value displayed will be 56.30%. Changing a cell's number format will not change the actual value that is used in calculations. In Microsoft Excel, you will see the cell's actual value in the formula bar.
The Color Palette
Each Excel document has a single color palette that manages formatting colors and some drawing layer colors. In Excel binary files (.xls), this palette is limited to 56 colors, and all formatting colors must come from this palette. Excel 2007 and higher (.xlsx files) can have unlimited colors. However, it still has a legacy palette with 56 colors. These colors are the only colors that are displayed when opening the file in Excel 2003 or earlier.
Modifying an Existing Spreadsheet
ExcelApplication can open and modify an existing spreadsheet. The new spreadsheet can either be saved to disk (overwriting the original file or saved with a new name) or streamed to the browser. This allows you to use a preset format for similar spreadsheets, rather than recreate the format for each.
Importing Data
ExcelApplication's ImportData method allows you to import blocks of data to a worksheet from a database or a rectangular array. ImportData is a method of both Worksheet and Area. The method returns a Area object representing a the set of cells that contain the imported data.
Charts in ExcelApplication
ExcelApplication supports all Excel chart types, and allows you to insert a chart in a worksheet, create a chart sheet (a worksheet that only contains a chart).
Output Options for ExcelApplication
The ExcelApplication object has several options for saving your ExcelApplication generated file.
Copying Data From a Worksheet
ExcelApplication's CopyPaste method allows you to copy data from an external worksheet. It can also be used to copy data from a worksheet within the same workbook.
Multilingual Support
ExcelWriter can be used to generate Excel spreadsheets which contain Unicode characters.
Areas and Ranges
ExcelApplication offers several methods that allow for the programmatic creation of areas and ranges.
Drawing Objects
In addition to Charts, ExcelApplication allows you to add the following drawing objects to a worksheet:
Formatting Headers and Footers
To create and format spreadsheet headers and footers, use the HeaderFooterSection object, which can be retrieved using the PageSetup.GetHeader(HeaderFooterSection.Section) and PageSetup.GetFooter(HeaderFooterSection.Section) methods.
Protecting Your Worksheet
There may be times when you want to lock your Excel worksheet so that end users cannot edit or manipulate the data in a worksheet. You can implement worksheet protection using ExcelWriter by calling the method Worksheet.Protect().
Creating a Basic PivotTable
The ability to manipulate PivotTables in ExcelApplication was introduced in OfficeWriter 8.4.0.
Convert strings to formatted numerical values
ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.
ExcelTemplate Guides
How to Use Templates
An ExcelWriter template is an Excel spreadsheet that contains ExcelWriter data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, variable, or array to insert in the spreadsheet column. A data marker may include modifiers.
Creating Data Markers
A data marker is a cell value or sheet tab name beginning with %%= or %%=$ that specifies a database column, a variable, or an array to insert in the spreadsheet column or sheet tab containing the marker. Data markers are created in Microsoft Excel and bound to data sources in code. When you run the code, ExcelWriter populates the data markers with values from a data source.
Stretching Formulas
A data marker is a single cell value, but a data marker will usually be populated with several rows of cell values. For example, if you set the data source for the data marker %%=Products.ProductID, to the "ProductID" column in a database table, the marker cell and the cells below it will be populated with ProductID values. New rows will be inserted between the data marker row and the next row in the template that contains a value. This can affect formulas because the range of cell values a form
Using Charts with ExcelTemplate
When you include a chart in a template spreadsheet, you can use a data marker column as a data source for the chart. Excel will automatically adjust charts to include the number of rows that ExcelWriter assigns to the data marker column.
Drawing Objects and Data Markers
If you include drawing objects (charts, comments, etc.) in a template, ExcelWriter will preserve the objects in the generated workbook. However, depending on where the object is in the template, ExcelWriter may move or modify it.
Using an Array as a Data Source
The following sample generates an Excel spreadsheet from a template using arrays as data sources.
Using a Database as a Data Source
The sample above generates a spreadsheet from the template using ADO.NET objects as the data source.
Using Grouping and Nesting with ExcelTemplate
Starting in version 7.1 of ExcelWriter, you can use [grouping and nesting markers|Creating Data Markers#grouping] to display flat data in a grouped and nested format using the ExcelTemplate object.
Templates and PivotTables
A PivotTable report is an interactive table which allows the user to have multiple views of data. By changing the view, the PivotTable report can show different summaries of the data or drill down to display more details for particular areas. ExcelWriter allows you to include PivotTables in an ExcelWriter template file. If there are PivotTables in the original template file, they will be preserved when the template is processed using ExcelWriter.
ExcelTemplate Output Options
ExcelTemplate has several different options for saving an ExcelTemplate generated file.