Spreadsheet conversion is a unique DPL feature that allows you to translate an Excel spreadsheet into DPL Code. This yields a dramatic increase in runtime performance compared with a linked model, typically an order of magnitude or more.
Before converting your spreadsheet, set up links to the spreadsheet and make sure they work. When you are ready to convert, open the Manage Links dialog, select the spreadsheet and click Convert to Calc Program.
Preparing a Spreadsheet for Conversion
DPL converts most mathematical, financial, and statistical spreadsheet functions. It also converts array formulas. It does not support date functions, database functions, trigonometric functions, or macros. See Spreadsheet Functions for a list of DPL-supported functions.
While DPL supports the functions most commonly used in financial spreadsheets, it isn't practical for DPL to implement every Excel function. If you know in advance that the spreadsheet you are building will be used with DPL, these guidelines will help you build a spreadsheet which can easily be converted:
Table lookups Use VLOOKUP, HLOOKUP, INDEX. Don't use OFFSET.
Date functions Excel date functions are not supported. Use numerical calculations instead.
Calculation/Iteration Spreadsheets that iterate cannot be converted, and tend to be slow even when linked. Iteration is often used for recursive calculations such as interest, which can be solved explicitly.
Sheets in the workbook DPL's conversion utility supports multiple sheets. In Excel, recalculation time may depend on the arrangement of calculations across sheets, but this is not the case in DPL once the spreadsheet is converted.
Range names Use of cell and range names is recommended, as it produces DPL code which is easier to read.
Rounding Don't use ROUNDUP, ROUNDDOWN; use ROUND. If the rounding is cosmetic, consider removing it and using DPL number formatting (File | Options | Outputs).
Array formulas DPL supports array formulas, and they tend to result in compact, efficient code. DPL doesn't support SUMIF, COUNTIF or RANK; use array formulas instead.
Rows vs columns Spreadsheets which are arranged in rows (e.g., so that 2005 is to the right of 2004) produce more efficient code than spreadsheets arranged in columns (so that 2005 is below 2004).
Other functions The COL, ROW, and N functions are supported, but are completely processed during conversion.
Check our website or contact technical support if you have any questions about spreadsheet conversion.
Versions: DPL Professional, DPL Enterprise, DPL Portfolio
Linking Nodes to a Spreadsheet