.
| Excerpt |
|---|
In ExcelWriter 8, support for the new Open Office XML (OOXML) file formats (XLSX, XLSM) was introduced in ExcelApplication. Since XLSX files have different file format defaults and a different file structure than XLS, ExcelWriter's behavior can change between the two file formats in specific situations. These differences are outlined below. |
Using the Color Palette
XLS files use a 56 color palette (see Excel's Color Palette Explained). When using ExcelApplication's Workbook.Palette, ExcelWriter will throw an error if Palette.GetColor is called on an XLS file and the color doesn't exist in the palette. Customers are encouraged to use Palette.GetClosestColor to avoid calling for colors that don't exist in the palette. ExcelWriter will approximate which color from the 56 color palette to use.
...
Note: XLSX files also have an underlying 56-color palette for the purposes of rendering XLSX files in Excel 2003 (with compatibility pack). This palette can still be modified by ExcelApplication v8. See this post for information on designing Excel 2007/2010 reports that might be viewed in Excel 2003.
Creating Files from Scratch
ExcelApplication can generate a spreadsheet either by opening an existing file with ExcelApplication.Open or by creating a new file in memory using ExcelApplication.Create(FileFormat). Starting in v8, ExcelWriter can create both XLS and XLSX files, but the file format must be specified when calling Create.
| Code Block |
|---|
//Specify either FileFormat.Xlsx or FileFormat.Xls to create a file
Workbook wb = ExcelApplication.Create(ExcelApplication.FileFormat.Xlsx);
|
The Create method uses an underlying template with defaults associated with the file format. XLS files use the defaults from Excel 2003; XLSX files use the defaults from Excel 2007. Switching to XLSX means the default template will change and the new defaults may change how output files look.
...
To ensure that output remains consistent while switching to XLSX, we recommend creating a blank XLSX file in Excel that has any desired defaults set, such as the Normal style set to Arial, size 10. Open that blank template . Then call ExcelApplication.Open on the Excel file, rather than creating a template from scratchcalling Create.
Chart defaults
Some very minor charting defaults have changed, such as:
...
The workaround is to set these properties to the desired values in ExcelWriter code. Most ExcelWriter-generated charts will appear the same regardless of whether they are in XLS or XLSX files.
New Excel features
New features were introduced in Excel 2007 and 2010. ExcelWriter does not currently support all the new features that are available in 2007/2010. We are constantly working to incorporate support for these features , but in future releases of OfficeWriter.
ExcelWriter can largely manipulate elements in XLS files without having to completely parse them, which includes features that were introduced in Excel 2007/2010. For example, Office introduced a series of new formulas (e.g. RANDBETWEEN, NORMAL.DIST). These formulas are mostly ignored by ExcelWriter in XLS files.
...