.
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.